表空间优化原因：表行数据越少，一个页存储的行也就越多，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

1，查询待建立的索引。sys.dm_db_missing_index_groups,sql如下：

SELECT * FROM (
	SELECT TOP 50
	ROUND(s.avg_total_user_cost * (s.avg_user_impact/100) *
	(s.user_seeks + s.user_scans),0) AS [improvement_measure]
	, s.avg_user_impact
	, d.statement AS TableName
	, d.equality_columns
	, d.inequality_columns
	, d.included_columns,
	  'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)
		  + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'
		  + ' ON ' + d.statement
		  + ' (' + ISNULL (d.equality_columns,'')
			+ CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
			+ ISNULL (d.inequality_columns, '')
		  + ')'
		  + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
	FROM sys.dm_db_missing_index_groups g
	INNER JOIN sys.dm_db_missing_index_group_stats s
	ON s.group_handle = g.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details d
	ON d.index_handle = g.index_handle
	ORDER BY improvement_measure DESC)M
WHERE m.improvement_measure > 10000 --大于10000者需要重建

2，注：1)，avg_user_impact，用户查询可能获得的平均百分比收益，因是数字，故要除以100，以取得可获取的提高性能的倍数。2)，此方法有局限性，指定的建立索引列的并不都是正确的，要手工判定。

3，查询可删除的索引，很少使用。

   

Select Top 30 database_id, t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) ,t.name,ix.name,sc.name
    from sys.dm_db_index_usage_stats ius
    JOIN sys.tables t ON ius.object_id = t.object_id
    JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id
    JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id
    JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id
    where user_updates > 10 * (user_seeks+user_scans)
    and ius.index_id > 1
    AND CHARINDEX('merge',t.name) <1
    order by user_updates / (user_seeks+user_scans+1) DESC
    

在Microsoft SQL Server 2008系统中，数据库管理员(Database Administration，简称为DBA)是最重要的角色。DBA的工作目标就是确保Microsoft SQL Server 2008系统正常高效地运行。DBA的工作也是最繁忙的工作，无论是性能调整，还是灾难恢复，都离不开DBA的支持。

　　一般地，作为一个DBA，至少应该做好以下12项任务：

• 　　任务一：安装和配置;
• 　　任务二：容量规划;
• 　　任务三：应用架构设计;
• 　　任务四：管理数据库对象;
• 　　任务五：存储空间管理;
• 　　任务六：安全管理;
• 　　任务七：备份和恢复;
• 　　任务八：性能监视和调优;
• 　　任务九：调度作业;
• 　　任务十：网络管理;
• 　　任务十一：高可用性和高可伸缩性管理;
• 　　任务十二：故障解决;

　　下面简单描述这些DBA的任务

　　任务一：安装和配置。

　 　DBA的第一项任务是安装和配置Microsoft SQL Server 2008软件系统，为顺利使用Microsoft SQL Server 2008软件创建良好的环境。无论是安装还是配置，都应该根据实际需要来进行，使得系统满足用户的实际需求。需要注意的是，系统配置不是一劳永逸的，应该 随时根据需求的变化和环境的需要，进行监视和适当地调整。

　　任务二：容量规划。

　　容量规划是对整个Microsoft SQL Server 2008系统进行一个总体的规划。规划的重点应该放在解决瓶颈问题上。可以从内容和期限两个方面考虑系统的容量规划。

　 　从内容上来看，应该考虑的主要内容包括：硬件容量规划、软件规划、网络规划。硬件容量规划包括磁盘空间、CPU、I/O等规划。软件规划包括操作系统的 安装和配置规划、数据库规划、数据库对象内容和数量规划等。网络规划包括网络硬件、网络软件和协议、网络客户数量流量和分布、网络拓扑结构等规划。

　 　从期限上来看，应该考虑短期、中期和长期规划。短期规划的目的是满足当前日常业务的需要。中期规划主要是满足业务发展和扩大的需要。长期规划主要是满足 业务极限需要等。例如，如果预测某个系统的当前并发用户数量是1000，3年后的用户可能达到1000万，那么这时既不能按照1000用户的需求来设计， 也不能一下子按照1000万用户的需求来设计，一定要采取一个折中的形式。

　　任务三：应用架构设计。

　　应用架构设计包括数据库设计、应用程序设计和相应的技术架构设计。

　　数据库设计应该考虑数据库的逻辑需求、数据库的创建方式和数量、数据库数据文件和日志文件的物理位置等。一般情况下，可以在Microsoft SQL Server 2008系统成功安装之后，根据规划的目标，手工创建数据库。

　　应用设计应该考虑开发工具的选择、API技术、内部资源和外部资源的结合、应用架构的分布等。需要强调是在应用设计时，DBA应该与开发人员共同工作，确保他们编写出优化的代码，尽可能地使用服务器的资源。

　　技术架构设计主要包括表示层、逻辑层和数据层的分布。这些分布不应该考虑到硬件资源和用户需求。既不能片面地追求过高的硬件资源，也不能仅仅局限于当前的环境，一定要按照可扩展的观点来综合考虑。

　　任务四：管理数据库对象。

　　管理数据库对象是使用数据库的最基本、最重要的工作。这些对象包括表、索引、视图、存储过程、函数、触发器、同义词等。为了完成管理数据库对象的工作，DBA应该能够很好地回答诸如下面的这些问题。

• 　　系统应该包括哪些数据?
• 　　应该怎样存储这些数据?
• 　　应该在系统中创建哪些表?
• 　　应该在这些表中创建哪些索引，以便加速检索?
• 　　是否应该创建视图?为什么要创建这些视图?
• 　　应该创建哪些存储过程、函数、CLR对象?
• 　　应该在哪些表上创建触发器?应该针对哪些操作创建触发器?
• 　　是否应该创建同义词?

　　任务五：存储空间管理。

　　存储空间管理任务就是怎样为数据分配空间、怎样保持空间可以满足数据的不断增长。随着业务量的继续和扩大，数据库中的数据也会逐渐地增加，事务日志也不断地增加。存储空间管理任务主要围绕下面几个问题。

• 　　当前的数据库由那些数据文件组成?
• 　　事务日志的大小应该如何设置?
• 　　数据的增长速度是多大?
• 　　如何配置数据文件和日志文件的增长方式?
• 　　数据库中的数据何时可以清除或转移到其他地方?

　　任务六：安全管理。

　　安全性是DBA重要的日常工作之一。安全管理的主要内容包括账户管理和权限管理。账户管理就是在数据库中应该增加哪些账户、这些账户应该组合成哪些角色等等。权限管理是对象权限和语句权限的管理，应该回答下面这些问题：

• 　　这些账户或角色应该使用哪些对象?
• 　　这些账户或角色应该对这些对象执行哪些操作?
• 　　这些账户或角色应该在数据库中执行哪些操作?
• 　　如何设置架构?如何建立架构和对象、架构和用户的关系?

　　任务七：备份和恢复。

　 　无论系统运行如何，系统的灾难性管理是不可缺少的。天灾、人祸、系统缺陷都有可能造成系统的瘫痪、失败。怎样解决这些灾难性问题呢?办法就是制订和实行 备份和恢复策略。备份就是制作数据的副本，恢复就是将数据的副本复原到系统中。备份和恢复工作是DBA的一项持续性的重要工作，其执行频率根据数据的重要 程度和系统的稳定程度来确定。

　　任务八：性能监视和调优。

　　根据企业的经营效益评价企业的管理水平，根据学生的考试成绩 评价学生的学习好坏。作为一个大型软件系统，Microsoft SQL Server 2008系统的运行好坏必须得到正确地监视、评价和相应的调整。这是DBA的一项高级工作。借助一些工具和运行性能指标，DBA应该能够监视系统的运行。 如果某些运行指标出现了问题，DBA应该及时地采取补救措施，使得系统始终保持高效运行状态。

　　任务九：调度作业。

　　DBA不可能一天24小时不停地盯住系统的运行，及时地执行某些指定的操作。Microsoft SQL Server 2008系统提供了许多工具，DBA应该充分利用这些工具和机制，解决下面一些问题。

• 　　调度哪些作业应该由系统执行?
• 　　这些作业应该在何时执行?
• 　　如何确保这些作业可以正确地执行?
• 　　如果自动执行的作业执行失败时，应该如何处理?
• 　　如何使得系统可以均衡地执行相应的操作?

　　任务十：网络管理。

　　作为一种分布式的网络数据库，网络管理的任务更加的重要。Microsoft SQL Server 2008系统提供了网络管理工具和服务，DBA应该借助这些工具进行服务规划和管理网络操作。

　　任务十一：高可用性和高可伸缩性管理。

　 　作为一个DBA，必须保持系统具有高可用性和高可伸缩性。可用性是一项度量计算机系统正常运行时间的指标。可伸缩性描述应用程序可以接受的并发用户访问 的数量问题。影响系统可用性的主要因素包括：网络可靠性、硬件故障、应用程序失败、操作系统崩溃、自然灾害等。无论是数据库系统管理员，还是应用程序设计 人员，都应该最小化系统破坏的几率，最大化系统的可用性。在设计系统的可用性时，应该确定采取什么样的可用性策略来满足可用性的需求。

　　 可用性的需求可以通过3个方面描述，即运行的时间、连接性需求和数据的紧密和松散要求。在确定可用性的需求时，首先考虑系统的运行时间。一般地，数据库应 用程序有两种运行时间，即在工作时间是可用的和在任何时间都是可用的。如果只是要求在工作时间是可用的，那么可以把系统的维护等工作安排在周末进行。但 是，有许多应用程序要求每天运行24小时、每周运行7天，例如，在线超市等，这时必须采取措施保证系统总是运行的。不同的应用程序有不同的连接性要求。大 多数的应用程序和电子商务解决方案要求采用可靠的网络连接。这时，要求永久性的在线连接，必须最小化各种异常现象的发生。有些应用程序允许用户离线使用。 这时，系统的可用性要求降低了。大多数应用程序要求数据是同步使用的。用户对数据的请求，系统必须立即做出回应。这是紧密型的数据要求，这种情况必须保证 系统的高可用性。有些应用程序不需要数据是同步的，对用户的请求可以延迟回应。这种要求是数据松散型的要求，这时系统的可用性需求比较低。

　　任务十二：故障解决。

　 　虽然不希望Microsoft SQL Server 2008系统出现故障，但是故障可能是无法避免的。这些故障可能每天都会发生。有些故障是人为不小心造成的，有些故障可能是系统中的缺陷形成的，有些故障 可能是莫名其妙的。作为一个DBA，在系统中的其他用户心目中是Microsoft SQL Server系统的权威。无论是大事还是小事，DBA都应该做到迅速诊断、准确判断、快速修复。从这个意义上来说，DBA是一个数据库系统的专业医生。

1. 1,SQLdiag is a successor of PSSdiag. SQL Nexus is a tool used to load and analyze performance data collected by SQLdiag
2. SQLdiag作用是收集以下信息。

• Windows服务器的软硬件信息（通过MSInfo32公用程序提取）
• Windows性能计数器，事件记录
• SQLSERVER服务器Profiler跟踪
• SQLSERVER错误记录
• SQLSERVER封锁信息
• SQLSERVER分配信息
• 自定义信息。
• 可用pssdiag图形化配置:http://diagmanager.codeplex.com/

   3，SQLdiag信息

• 存在于\Program Files\Microsoft SQL Server\90\Tools\Binn目录下
• 日志文件放在\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLdiag目录下。
• 指定配置文件产生地址：sqldiag -PC:\SQLdiag,会产生以下配置文件
  •  MSDiagProcs.sql：对sqlserver实例进行的初始化操作，多是在Tempdb系统库上建立对象。
  • SQLDiag.XML：默认读取的配置文件，提供标准的数据搜集，包含Perfmon,SqlServer错误处理，设置状态等。
  • SD_Detailed.XML:设置SQLDiag.XML更详细的信息
  • SD_General.XML：设置SQLDiag.XML精简的信息
  • SQLDiag_Schema.XSD：用来编写，修改，验证既有的XML配置文件
• 4,SQLDiag.XML配置。各节点中，EventlogCollector：收集系统信息，PerfmonCollector收集监控系统，Instances收集SQLSERVER信息
• 默认的Instances的事件是各跟踪事件信息，对应事件可在sys.trace_events中查看。
• sqldiag的唯一缺点是不会定期查询系统视图，
  所以若加上PerfStatsScript.sql监视脚本输出，就比较全面了。
  PerfStatsScript:说明：建立两个存储过程，
      1）sp_perf_stats09：–列出最耗时的请求及找到阻塞源头，#tmp_requests：查询当前活动的请求，任务，等基本信息–#tmp_requests2：据#tmp_requests查询当前的活动事务，阻塞的进程
      2）sp_perf_stats_infrequent09：收集sqlserver的性能计数器，如内存管理，CLR等sys.dm_os_performance_counters，CPU运行情况，I/O情况
      每10分钟执行一次sp_perf_stats09，每小时执行一次sp_perf_stats_infrequent09

  定制执行SQL
  <CustomTask enabled="true" groupname="ByronSP" taskname="ReusePlanPercentage" type="TSQL_Command"  point="Startup"  wait="OnlyOnShutdown" cmd="exec tempdb.dbo.GetTable" />
  输出结果在：
  SQLDI\taskname_Startup.OUT

  sql是执行一次就结束了，若想多次执行，

  则调用spPollSP存储过程，定时执行。
  if object_id('dbo.spPollSP') IS NOT NULL –加在MSDiagProcs.sql内
  DROP PROC DBO.spPollSP
  GO
  PRINT ''

  RAISERROR('====建立存储过程dbo.spPollSP,以重复调用其他存储过程',0,1)
  WITH NOWAIT
  GO

  CREATE PROC dbo.spPollSP @spName sysname, @interval char(8)='00:01:00'
  AS
  DECLARE @SQL NVARCHAR(200)
  SET @SQL = 'EXEC ' +@spName + '; WAITFOR DELAY ''' + @interval + ''''
  WHILE 1=1
      EXEC(@SQL)
  GO

  sqldiag参数
  如：sqldiag -Pc:\sqldiag /B +00:01:00 /E +00:10:00 /n 1 /L
  1分钟后开始执行，10分钟后停止。连续运行

  以服务运行，直到10:19分时停止
  sqldiag -P"C:\sqldiag" -E20120508_10:23:00 -N2 -R
  net start sqldiag

  卸载：
  net stop sqldiag
  sqldiag -u

  日志文件转成csv文件：
  relog SQLDIAG.BLG -f csv -o "C:\sqldiag\SQLDIAG_00001\a
  .csv"

  relog sqldiag.blg -q -o counter.txt — 查看记数器

  /I configuration_file
  设置 SQLdiag 要使用的配置文件。默认情况下，/I 设置为 SQLDiag.Xml。

  /O output_folder_path
  将 SQLdiag 输出重定向到指定文件夹。如果未指定 /O 选项，则 SQLdiag 输出结果将会写入 SQLdiag 启动文件夹下名为 SQLDIAG 的子文件夹中。如果 SQLDIAG 文件夹不存在，则 SQLdiag 将会尝试创建该文件夹。

  /P support_folder_path
  设置支持文件夹路径。默认情况下，将 /P 设置存放 SQLdiag 可执行文件的文件夹。

  /B [+]start_time
  按照以下格式指定开始收集诊断数据的日期和时间：

  按照以下格式指定开始收集诊断数据的日期和时间：

  YYYYMMDD_HH:MM:SS

  使用 + 并且不带日期（只使用 HH:MM:SS），可以指定相对于当前日期和时间的时间。例如，如果指定 /B +02:00:00，则 SQLdiag 将会在 2 小时后开始收集信息。

  不要在 + 和指定的 start_time 之间插入空格。

  /E [+]stop_time
  按照以下格式指定停止收集诊断数据的日期和时间：

  使用 /B +02:00:00 /E +03:00:00 指定开始时间和结束时间，则 SQLdiag 将会在 2 小时后开始收集信息，经过 3 小时信息收集后停止收集并退出。

  /R
  将 SQLdiag 注册为服务。您将 SQLdiag 注册为服务时指定的所有命令行参数，都将留到以后用来运行该服务。

  /U
  撤消 SQLdiag 服务注册。

  /N output_folder_management_option ,如：sqldiag /N 1
  设置 SQLdiag 在其启动时，是覆盖还是重命名输出文件夹。可用选项包括：

  1 = 覆盖输出文件夹（默认）

  2 = 当 SQLdiag 启动时，将输出文件夹重命名为 SQLDIAG_00001、SQLDIAG_00002 等等。重命名当前输出文件夹之后，SQLdiag 将输出写入默认输出文件夹 SQLDIAG。

  /L：连续运行sqldiag

  5,一般设置提纲为：记录两小时，每4秒记录一次，记录一天，每30秒记录一次，记录5天，每180秒记录一次，以免影响系统性能，且能放映系统持久变化。

• 要收集的perfmon信息在PerfmonCounters下面增加：

SQL SERVER BEST PRACTICES:
http://technet.microsoft.com/en-us/sqlserver/bb671430

DMV:
http://gallery.technet.microsoft.com/scriptcenter/site/search?f[0].Type=RootCategory&f[0].Value=databases&f[0].Text=%E6%95%B0%E6%8D%AE%E5%BA%93&f[1].Type=SubCategory&f[1].Value=sqlserver&f[1].Text=SQL%20Server&f[2].Type=Tag&f[2].Value=DMV&sortBy=Ratings

http://www.sqlcrunch.com/PerformanceTuning/tabid/100/Default.aspx

http://www.cnblogs.com/flysun0311/

SELECT TL.resource_type AS ResType
,TL.resource_description AS ResDescr
,TL.request_mode AS ReqMode
,TL.request_type AS ReqType
,TL.request_status AS ReqStatus
,TL.request_owner_type AS ReqOwnerType
,TAT.[name] AS TransName
,TAT.transaction_begin_time AS TransBegin
,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
,ES.session_id AS S_Id
,ES.login_name AS LoginName
,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
,PARIDX.name AS IndexName
,ES.host_name AS HostName
,ES.program_name AS ProgramName
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2继续阅读

SELECT CASE WHEN TDT.database_id = 32767
THEN ‘MSSQLSystemResource’
ELSE DB.name END AS DatabaseName
,REQ.start_time AS ReqStart
,TAT.transaction_begin_time AS TransBegin
,TAT.name AS TransName
,CASE TDT.database_transaction_type
WHEN 1 THEN N’Read/Write’
WHEN 2 THEN N’Read-only’
WHEN 3 THEN N’System’
ELSE N’Unkown’ END AS TransType
,CASE TAT.transaction_state
WHEN 0 THEN N’Not initialized’
WHEN 1 THEN N’Not started’
WHEN 2 THEN N’Active’
WHEN 3 THEN N’Ended’
WHEN 4 THEN N’DTC active’
WHEN 5 THEN N’Preparing’
WHEN 6 THEN N’Committing’
WHEN 7 THEN N’Being rolled back’
WHEN 8 THEN N’Rolled back’
ELSE N’Unkown’
END AS TransState
,REQ.[status] AS ReqStatus
,TDT.database_transaction_log_record_count AS LogRec
,TDT.database_transaction_log_bytes_used AS LogBytes
,SES.login_name AS LoginName
,REQ.wait_type AS ReqWaitType
,REQ.percent_complete AS [ReqCompl%]
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE REQ.statement_end_offset END – REQ.statement_start_offset) / 2
) AS SqlStatement
FROM sys.dm_tran_active_transactions AS TAT
INNER JOIN sys.dm_tran_database_transactions AS TDT
ON TAT.transaction_id = TDT.transaction_id
INNER JOIN sys.databases AS DB
ON TDT.database_id = DB.database_id
LEFT JOIN sys.dm_tran_session_transactions AS TST
ON TAT.transaction_id = TST.transaction_id
LEFT JOIN sys.dm_exec_requests AS REQ
ON TAT.transaction_id = REQ.transaction_id
LEFT JOIN sys.dm_exec_sessions AS SES
ON REQ.session_id = SES.session_id
CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST
WHERE TAT.transaction_id > 255
AND ISNULL(REQ.session_id, -1) <> @@SPID
AND TDT.database_id <> DB_ID(N’tempdb’)
ORDER BY DatabaseName
,TransBegin
,TransName;