表空间优化原因：表行数据越少，一个页存储的行也就越多，I/O就会减少，提高了性能。
方法有：1，基表用int,自增，表中多用int
        2，存储大的数据放在另外表中。

效果评估：查看优化前与后每页存储的平均行数，越大越好。

SELECT m.tablename 表名,m.indexname 索引名,m.total_pages 总页数,m.ROWS 总行数,m.AvgPerPage 每页平均行数 ,
CONVERT(VARCHAR(5),8060/AvgPerPage) + '字节'   每行平均字节数
FROM (
    SELECT OBJECT_NAME(idx.OBJECT_ID) tablename,idx.NAME indexname,
    SUM(total_pages) total_pages,SUM(ptt.rows) ROWS,SUM(rows)/SUM(total_pages) AvgPerPage
    FROM sys.indexes idx
    JOIN sys.partitions ptt ON idx.index_id = ptt.index_id AND idx.object_id = ptt.object_id
    JOIN sys.allocation_units au ON au.container_id = ptt.partition_id
    WHERE idx.object_id = object_id('bdorder')
    GROUP BY OBJECT_NAME(idx.OBJECT_ID),idx.name
)m

--EXEC SyncSpToLinkServer @LinkServer='remoteserver1', @Name ='perfmonFix'
--向链接服务器tempdb上同步存储过程、函数、视图
--关键点：在链接服务器上执行sql：EXEC('exec tempdb.dbo.sp_executesql @statement =N''select 1''') AT remoteserver1
CREATE PROCEDURE SyncSpToLinkServer
@LinkServer VARCHAR(255),
@name VARCHAR(255) --存储过程、函数、视图名称
AS
BEGIN
 DECLARE @sql VARCHAR(max)
 SET @sql = '
 DECLARE @IsDrop BIT,@IsCreate BIT,@type VARCHAR(100),@tmp VARCHAR(1000)
 SET @IsDrop = 0 --默认不删除链接服务器上的存储过程
 SET @IsCreate = 1 --默认在链接服务器上创建存储过程

 SELECT
 @IsCreate = CASE WHEN  rp.modify_date <= lp.modify_date THEN 1 ELSE 0 END,--链接服务器存储过程是否不是最新
 @IsDrop = CASE WHEN  rp.modify_date <= lp.modify_date THEN 1 ELSE 0 END,
 @type = CASE(rp.type)
  WHEN ''V'' THEN ''VIEW''
  WHEN ''P'' THEN ''PROCEDURE''
  WHEN ''FN'' THEN ''FUNCTION''
  WHEN ''IF'' THEN ''FUNCTION''
  WHEN ''TF'' THEN ''FUNCTION'' END
 FROM ' + QUOTENAME(@LinkServer) +'.tempdb.sys.objects rp
 JOIN sys.objects  lp ON rp.name = lp.name
 WHERE rp.name = ' + QUOTENAME(@name,'''') +'
 
 SELECT CASE(@IsDrop) WHEN 1 THEN ''drop'' ELSE ''nodrop'' END,
 CASE(@IsCreate) WHEN 1 THEN ''create'' ELSE ''nochanged'' END,@type

 IF @IsDrop = 1
 BEGIN
  
  --SET @tmp = ''exec tempdb.dbo.sp_executesql @statement =N'''' DROP ''  + @type +   ' + '  ' + QUOTENAME(@name,'''') +'
  SET @tmp = ''exec tempdb.dbo.sp_executesql @statement =N''  + ''''''  DROP ''  + @type +   ' + '''  '   + @name +'''  + ''''''''
  PRINT @TMP
  EXEC(@tmp) AT  ' + QUOTENAME(@LinkServer) +'
 END
 IF @IsCreate = 1
 BEGIN
  DECLARE @CreateSql VARCHAR(max), @ExecuteSQL VARCHAR(MAX)
  SELECT  @CreateSql =  REPLACE(definition,'''''''','''''''''''')--处理引号
  from sys.sql_modules where object_id=object_id(' + QUOTENAME(@name,'''') +' )
  IF @CreateSql IS NOT NULL
  BEGIN
   SET @ExecuteSQL = ''EXEC tempdb.dbo.sp_executesql @statement =N'''''' + @CreateSql +''''''''
   EXEC(@ExecuteSQL)  AT ' + QUOTENAME(@LinkServer) +'
  END
 END '

 --PRINT @sql
 EXEC (@SQL)
END

1，sys.objects是用户级的对象存储，如用户的表，约束，主键，函数，存储过程，视图，以及2000版本的不常用的系统基表
2，system_objects是系统级的对象存储，包含系统存储过程，如sp_helptext，动态视图sys.dm等
3，sys.objects + sys.system_objects = sys.all_objects

据统计信息来作预估，如执行计算预估的返回行
查看统计信息：DBCC SHOW_STATISTICS ('bdship','IX_bdship_2')
建立索引后，若想看其好坏，用DBCC SHOW_STATISTICS ('bdship','IX_bdship_2')查看重复率，越小越好。
统计信息维护：
    1，表格从没有数据变成有数据时，如索引重建。
    2，小表（500行以下数据），当统计信息第一个字段累计变化大于500以后
    3，大表（500行以上），当统计信息第一个字段累计变化大于500+（20%*表格数据总量）时，即在表五分之一以上的数据发生变化时，会自动更新统计信息。
    4，手工更新，如下：
手工更新统计信息：
UPDATE STATISTICS  mdDept –表
UPDATE STATISTICS  mdDept IX_mdDept –表上索引

1,索引要建立在重复少的字段上才有意义，试想若字段全相同，那做索引就没有意义。
索引建立后可通过DBCC SHOW_STATISTICS ('bdship','IX_bdship_2')来判断，
第一个表的Density
若Density接近于1，说明重复率非常小，索引建立的很好
若Density小于0.1，说明重复很大，建不建立索引影响不大，某些时候会用扫描方式替代索引查找

第三个表：
RANGE_HI_KEY:
每组数据的最大值
RANGE_ROWS:每组数据区间行数，不含最大值。即：(上一行值，当前行值)
EQ_ROWS：等于RANGE_HI_KEY的值的数量
DISTINCT_RANGE_ROWS:每组数据非重复数量
AVG_RANGE_ROWS:重复值的平均数量,不含上限值
=RANGE_ROWS/DISTINCT_RANGE_ROWS
这样，通过给定的一个条件，就能预估大概有多少行数据，有多少M数据。

执行计划编译是需要时间的，为了提高效用率，是要缓存的，可用如下方式查看：
–查看执行计划的缓存

SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,dbid,
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY dbid,usecounts DESC;

 SELECT cacheobjtype,objtype,sql FROM sys.syscacheobjects a
 WHERE cacheobjtype = 'compiled plan' AND dbid = 7

 
–清空执行计划缓存
DBCC FREEPROCCACHE
GO
–从缓存中清除给定数据库的计划缓存
DBCC FLUSHPROCINDB(<dbid>)
GO

执行计划重新编译放到缓存中的时机：
1，表或视图的架构发生了更改，索引发生了改变造成统计信息发生更改。
2，运行了sp_recompile
3,执行计划被清除，如服务器重启，服务重启，放在内存中的执行计划就会被清空。
4,缓存会用LRU算法找出很少使用的，然后从内存中删除，在sys.dm_exec_query_stats中有一字段creation_time标识计划的创建时间，一般一、两个小时就会重新建立一份的。
可通过观察以下事件看缓存的变更。SP:CacheInsert SP:CacheMiss SP:CacheHit

执行计划要保持重用，以提高利用率，可以查出经常重新编译的sql以做优化，当然重用并不一定好，因为执行计划不适合，如参数化问题等。

sys.dm_exec_query_stats 有一个栏位plan_generation_num，计划编译次数，可用此来分析最常编译的计划。
select top 25
    plan_generation_num,
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (case when qs.statement_end_offset = -1
        then DATALENGTH(qt.text)
        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)
        as stmt_executing,
    qt.text,
    execution_count,
    sql_handle,
    dbid,
    db_name(dbid) DBName,
    objectid,
    object_name(objectid,dbid) ObjectName
from sys.dm_exec_query_stats as qs
    Cross apply sys.dm_exec_sql_text(sql_handle) qt
where plan_generation_num >1
--AND qs.last_execution_time >='2011-08-28 10:00' 限定时间
order by plan_generation_num desc

1,
  SCAN   Seek
堆  Table Scan  无
聚集索引 Clustered Index Scan Clustered Index Seek
非聚集索引 Index Scan  Index Seek
非聚集索引扫描产生的原因：因Index只包含了表的一小部分字段，占用的页面数量会比表格本身要小得多，故扫描索引表就比聚集索引扫描要高得多。
前提是索引中包含了所要求的字段，但为什么没用索引查找呢，可能是在此字段上运行了运算，或此字段是联合索引的非首项。
2，扫描一定比查找慢吗？不一定
构造场景：订单明细表， DetailID聚集索引，OrderID非聚集索引。返回产品，操作人。
利用OrderID过滤，那么正常流程是先通过OrderID非聚集索引查找满足条件的字段，因要返回产品，操作人等信息故
要通过lookup查到聚集索引上的产品，操作人。注意这里面有一个lookup.现在假设同一个OrderID对应的明细非常多，那么在
lookup阶段要花费大量的时间，大大超过在OrderID查找带来的性能提升，这种情况下，还不如直接来个聚集索引扫描快一些。
故在实际中，会发现应用非聚集索引查找的地方换成了聚集索引扫描，因为此种情况下，扫描来得还快一些，即非聚集索引存在并不一定使用，
若出现这种情况，请检查非聚集索引是否有大量重复，看能否消除。

1,查看执行计划方式：
1）以行表方式返回执行计划信息（树状结构，自上而下，自左而右查看，通过TotalSubtreeCost查看开销），不执行sql语句
set showplan_all on
go
select * from AWBuildVersion
set showplan_all off

2）XML方式返回执行计划信息，不执行sql语句
set showplan_XML on
select * from AWBuildVersion
set showplan_XML off

3)以行表方式返回执行计划信息，执行sql语句，因为执行了sql语句，故在返回的执行计划结果中包含了每一步执行后返回的行数（表头的rows字段），可与
预估的行数做比较，分析执行计划的效果。
set statistics profile on
select * from AWBuildVersion
set statistics profile off
4)对于执行计划，分析其是否恰当，首先要看预估行数 estimaterows与rows是否相同，若差别较大，差几千，那么说明预估出问题，
原因是统计信息不对，这时可通过重建索引来更新统计信息。（重建索引的好处：1，更新了统计信息，执行计划预估更加正确，2，处理了数据碎片，查找速度提升。）
第二就是查看totalsubtreecost开销。

执行计划结果表信息：
ROWS:执行计划的每一步返回的实际行数。
Executes:执行计划的每一步被运行了多少次：若此值很大，又发生在nested loop中，要看是否应重新编译，以使用Hash JOIN
StmtText:执行计划的具体内容。
EstimateRows:预估的每一步返回的行数。要与ROWS做比对，以分析预估是否正确。
EstimateIO:每一步预估的IO开销。
EstimateCPU:每一步预估的CPU开销。
TotalSubtreeCost:据EstimateIO与EstimateCPU通过某种计算公式，计算出每一步执行计划子树的cost，包含自己的cost和它的所有下层步骤的cost总和。
Warnings:SqlServer执行每一步时遇到的警告，如某一步没有统计信息运行预估等。
Parallel:是否使用了并行的执行计划。

2，JOIN连接种类
1）Nested Loops JOIN:对于A表中满足条件的每一行，遍历B表,在执行计划信息中，以Nested包括，共有两行，每行执行过滤后，以Nested进行连接。
                     选过滤后连接。
强制用: inner loop/merge/hash join
优点：不需要SQLSERVER为JOIN建立额外的数据结构，也不用占用tempdb空间。
不足：复杂度是两表行数相乘，适用于两表数据集比较小的情况下。B表最好有索引。
2)Merge Join：从A表中取一条记录，B表中也取一条，比较是否相等，若相等，则两行连接起来返回，若不等，则舍掉小的那个值，
接顺序取下一个较大的值进行比较，直到A与B表其中之一遍历完。
由此定义可以：1，Merge Join要求两表是排序过的，若没有排序，则会执行先排序，故附加作用是返回的结果都是有序的。
              2，只能进行值相等的运算,ON条件后为=。
              3，时间复杂度是每表符合条件的行数，它适用于大数据量的情况。
              4，为防止数据有重复而被过掉的结果，会对B表已遍历过的放到tempdb中，以备查询，称之为many-to-many
          故由上知，它的复杂度低，若表中无索引，要排序。它还需要额外的many-to-many，不过若表字段限定unique，则相当于告诉sqlserver
这是唯一的，不会重复，那么也就不会用many-to-many了。
3）Hash Join:以哈希算法为主的连接方法，分为两步，第一步，在内存或tempdb中对B表建立hash表，第二步，将另一表值代入查找。适用于
大数据量的情况，特别是无索引这种的无序数据。它建立hash表，故要汪消耗内存，要进行hash匹配，要消耗CPU.

对照表如下：
  Nested Loop Joion Merge Join Hash Join
适用场景 小数据量  数据量中等 数据量大
ON是否要求相等 不需要   需要  需要
是否使用内存 不需要   不使用  需要使用内存建立hash表。
是否使用tempdb 不需要   many-to-many用 使用建立hash表

3, 其它运算符
1）流运算：Stream Aggregation（将数据集排成一个队列以后做运算）,如执行聚合函数、Group By时，会出现流聚合。
2) 哈希匹配：Hash Aggregation（类似于Hash Join，需要SQL Server先建立Hash表，然后才能做运算）如执行聚合函数时，数据量大时，会出现哈希匹配。
3)连接：Concatenationunion all与union:会有一个连接操作，名为Concatenation,将数据集连接起来，不过，union在Concatenation之后，会再做一步排序（以便易于分组）与分组，以取出唯一，比较消耗资源。
4）并行计算：Parallelism:当sqlserver发现某个要处理的数据集比较大，cost比较高时，若服务器有多个cpu，sqlserver就会尝试使用
并行的方法，将一个数据集分成若干个，由若干个线程同时处理.

—————————————-参数化问题———————————————————
参数问题
调用存储过程时，执行计划会重用，但因数据的不同，传入不同的数据，可能会造成现有执行计划不符合实际，造成效果慢，此时用
sql构造反而快的结果出现。
1，存储过程的参数分为两类：1，是传入参数，2是内部参数
1，传入参数，若执行过程的执行计划与拿出来执行得到的执行计划不同，可能就发生了参数化问题，比如第一次执行时是nested loop，
执行计划缓存起来，下次查询的数据范围很大，应用hash join的，但因重用了执行计划，仍用nested loop 执行。由此知，参数问题只会
发生在一些表格里的数据分布不均匀，或者用户传入的参数很不均匀的情况下，如：大部分客户查询的是一天的数据，但有一个查一年的
数据就会出现问题，常见的是该用hash join的用了nested join.
解决之道：1）动态sql执行。
2）强制重新编译：
（1）存储过程级：with recompile :在创建存储过程时加入，那么整个存储过程会实时编译，效率低一些。
（2）语句级：option(recompile)：在语句后加入，比较精细，只有加了这个语句的sql才会重新编译执行计划，整个存储过程不会重新编译，故效率会高一些，
并且如果有if else,并用没有经过此语句，也不会重新编译。

2，对入内部参数，是在执行过程时才能得到的，在编译时并不知道它的大小，会根据表格数据量预估一个中间值，性能不是最好也不是最坏。For example, consider a column X, containing 10 rows
with values 1, 2, 3, 3, 3, 3, 3, 4, 5, 5. The optimizer would always estimate that a "WHERE
X=value" query will return 2 rows (total number of rows divided by number of distinct
values)

—————————————总结—————————————————————————–
查看执行计划
SET statistics profile on
1）比较rows与estimaterows是否差别较大，
若较大，原因是1）统计信息不对，这时可通过重建索引来更新统计信息。（重建索引的好处：1，更新了统计信息，执行计划预估更加正确，2，处理了数据碎片，查找速度提升。）
2）Executes若Executes值很大，又发生在nested loop中，不适合自己的执行计划，多发生在存储过程中，此时要强制重新编译 option(recompile)
3）查看totalsubtreecost开销，该建索引建索引。
4）若是扫描而不是查找，原因是lookup查找主键开销很大，不如直接扫描来得快，此种情况下看能否消除select中的字段数，避免lookup。
5)清空缓存：dbcc freeproccache

/*
用途：将sql语句传来的参数#化，以得到标准的sql语句
使用举例：SELECT dbo.SQLSig('select * from t1 where t1.name=2',1000)
*/

IF OBJECT_ID('dbo.SQLSig', 'FN') IS NOT NULL
  DROP FUNCTION dbo.SQLSig;
GO

CREATE FUNCTION dbo.SQLSig 
  (@p1 NTEXT, @parselength INT = 4000)
RETURNS NVARCHAR(4000)

-- Strips query strings
AS
BEGIN 
  DECLARE @pos AS INT;
  DECLARE @mode AS CHAR(10);
  DECLARE @maxlength AS INT;
  DECLARE @p2 AS NCHAR(4000);
  DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);
  DECLARE @p2len AS INT;

  SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));
  SET @maxlength = CASE WHEN @maxlength > @parselength 
                     THEN @parselength ELSE @maxlength END;
  SET @pos = 1;
  SET @p2 = '';
  SET @p2len = 0;
  SET @currchar = '';
  set @nextchar = '';
  SET @mode = 'command';

  WHILE (@pos <= @maxlength)
  BEGIN
    SET @currchar = SUBSTRING(@p1,@pos,1);
    SET @nextchar = SUBSTRING(@p1,@pos+1,1);
    IF @mode = 'command'
    BEGIN
      SET @p2 = LEFT(@p2,@p2len) + @currchar;
      SET @p2len = @p2len + 1 ;
      IF @currchar IN (',','(',' ','=','<','>','!')
        AND @nextchar BETWEEN '0' AND '9'
      BEGIN
        SET @mode = 'number';
        SET @p2 = LEFT(@p2,@p2len) + '#';
        SET @p2len = @p2len + 1;
      END 
      IF @currchar = ''''
      BEGIN
        SET @mode = 'literal';
        SET @p2 = LEFT(@p2,@p2len) + '#''';
        SET @p2len = @p2len + 2;
      END
    END
    ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')
      SET @mode= 'command';
    ELSE IF @mode = 'literal' AND @currchar = ''''
      SET @mode= 'command';

    SET @pos = @pos + 1;
  END
  RETURN @p2;
END
GO

1，在目标机上收集性能指标：
   运行：
    配置文件
    执行以下命令：
    以服务运行
    早上8点到晚18点。
    SQLDIAG -P"C:\sqldiag" -B 08:00:00 -E 18:30:00 -N2 -R
     net start sqldiag

     卸载：
     net stop sqldiag
     sqldiag -u
2，将最后生成的sqldiag.blg文件用PAL工具转化，生成分析结果，观察此结果，并重点解决问题处。
    将sqldiag.blg通过 relog sqldiag.blg -f csv -o sqldiag.csv 转成csv,导入到sqlserver中，以做数据分析，如平均数等。
3,打开收集的sqlserver trc文件，文件-导入性能数据，选择生成的blg性能计数器文件，这样就可对比性能计数器与具体的sql来查看。

1，图形化界面：powershell -ise
2,命令缓存：F7
3，获取帮助：get-help 如：get-help get-service [-detail|-full]
4,执行ps1文件：默认是不执行的，安全限制级别如下：

• Restricetd:受限制，不执行任何配置文件和脚本。
• AllSigned:必须签名的才执行。
• RemoteSigned:远程文件需要签名才能执行，本地文件可执行
• Unrestricted:不受限制，任意执行。

显示当前级别： get-executionpolicy
设置方法：set-executionpolicy remotesigned .
5，powershell传参：

• powershell -nologo -noexit -command get-process #不退出无logo方式显示进程信息。
• powershell -noexit -file run.ps1 #执行run.ps1文件

6，变量定义以$开头，执行字符串命令用&,若字符串命令带参数，那需要用大括号括起。或者用Invoke-Expression
7.格式化输出结果
  Format-list:列表形式
  Format-table:表
  Format-wide:指定宽度
  Group-Object:分组
  Sort-Object:排序
  where-object:筛选
 get-process|sort-object Handles
get-process|sort-object Handles|format-table handles,id,processname
 get-process|where-object {$_.processname -eq "powershell"}
get-alias|where-object {$._Definition -eq "get-service"}
8，输出到屏幕：write-host 录入：read-host
9，输出到文件： -file …或者 > 单>表示新建或覆盖，>>表示追加
10，别名：get-alias .如get-process = gps
11,常用：get-process,get-service,get-eventlog
12,逻辑： -eq = -ne 不=   -lt 小于 -gt 大于  -le:小于等于 -ge:大于等于 -contails 包含 -like 类似于 -replace 替换 -match 匹配
13，$args:接收参数。 $args[0], $args.count
14,单引号：直接输出。 双引号，除直接输出外，若有变量，则将变量转换成值再输出，反单引号执行转义。
15，数组：
  单数组 1)用逗号分隔。$arr=1,2,3 使用$arr[0],$arr[-1] 最后一位。
     2)多种类型，用小括号及分号隔开。$myArray=@(14;"this";get-process)
  多维数组
3行4列5层数组
 $arr = new-object 'object[,,]' 3,4,5
16，使用.net对象
1）静态成员变量  [类名]::静态方法名
[system.environment]::ProcessorCount . 输入::后按tab键可选择。
2）实例化
new-object -type 类名 -argumentlist application
3)加载程序集
如：
[Reflection.Assemble]::LoadWithPartialName("System.Windows.Forms")
17，注释#，多行注释<#…#>
18,固有变量
$env:computername
$env:username
$env:userdomain
19,系统日志操作
最近20条应用程序日志
 get-eventlog "Application" -newest 20
日志类型：Application, System, Ddirectory Service,"security"

用relog合并perfmon生成的多个blg文件时，若直接relog 1.blg,relog 2.blg -f bin -o result.blg，当blg文件比较多时，有一个无法理解的现象产生，生成的文件只是抓取最近65分钟内的数据，其他数据丢失[经了解,这是因relog合并多个文件，最大数量为32造成]，为避免这种情况，现将blg两个两个合成，不一次合成多个，powershell脚本如下：

$logPath = "E:\教程\技术资料\试验厂\powershell\perfmon\211\SqlDiag"
$SavePath = "E:\教程\技术资料\试验厂\powershell\perfmon\211\"

$RunTime = ((Get-Date).ToString("MMddhhmmss"))
$Prefix = "Temp_"
$tmpFileNameBase = $SavePath + $Prefix + ((Get-Date).ToString("MMddhhmmss"))
$FileNameBase = $SavePath + ((Get-Date).ToString("MMddhhmmss"))

$fileList = Get-ChildItem  $logPath -recurse *.BLG | %{$_.FullName}
$relog = "relog " + $fileList[0]  + " -f bin -o  " + $tmpFileNameBase +"[0]"  + ".blg"
Invoke-Expression $relog 

for( $i = 1; $i –lt $fileList.Length; $i++  ){
	$FileName = $tmpFileNameBase +"["+($i-1)+"]"  + ".blg" + "  " + $fileList[$i]
	$relog = "relog " + $FileName  + " -f bin -o  " + $tmpFileNameBase +"["+($i)+"]"  + ".blg"
	$relog
	Invoke-Expression $relog 
}
$relog = "relog " + $tmpFileNameBase +"["+($i-1)+"]" + ".blg"  + " -y -f bin -o " + $FileNameBase+ ".blg"
Invoke-Expression $relog
$relog = "relog " + $FileNameBase + ".blg"  + " -y -f csv -o " + $FileNameBase+ ".csv"
Invoke-Expression $relog

#删除临时文件
$del = " Remove-Item " + $SavePath + $Prefix + "*.blg"
Invoke-Expression $del

relog /?:
查看记数器 relog sqldiag.blg -q -o counter.txt