SQL SERVER汇总

1.MSSQLSERVER执行计划详解

原文：https://www.cnblogs.com/knowledgesea/p/5005163.html

• 清除缓存的执行计划

dbcc freeprocache

dbcc flushprocindb（db_id）

 

• 执行类型

Table Scan（表扫描）：当表中没有聚集索引，又没有合适索引的情况下，会出现这个操作。这个操作是很耗性能的，他的出现也意味着优化器要遍历整张表去查找你所需要的数据。

 

Clustered Index Scan(聚集索引扫描)：几乎等于全表扫描，聚集索引的数据体积实际是就是表本身，也就是说表有多少行多少列，聚集所有就有多少行多少列，那么聚集索引扫描就跟表扫描差不多，也要进行全表扫描，遍历所有表数据，查找出你想要的数据。

 

Index Scan（非聚集索引扫描）：非聚集索引的体积是根据你的索引创建情况而定的，可以只包含你要查询的列。那么进行非聚集索引扫描，便是你非聚集中包含的列的所有行进行遍历，查找出你想要的数据。

 

Key Lookup(键值查找)：查找与扫描在性能上完全不是一个级别的，扫描需要遍历整张表，而查找只需要通过键值直接提取数据，返回结果，性能要好。

 

RID Lookoup（RID查找)：跟键值查找类似，只不过RID查找，是需要查找的列没有完全被非聚集索引包含，而剩余的列所在的表又不存在聚集索引，不能键值查找，只能根据行表示Rid来查询数据。

 

Clustered Index Seek（聚集索引查找）：聚集索引包含整个表的数据，也就是在聚集索引的数据上根据键值取数据。

 

Index Seek（非聚集索引查找）：非聚集索引包含创建索引时所包含列的数据，在这些非聚集索引的数据上根据键值取数据。

 

2. SQL Server数据库碎片整理

原文链接：https://www.jianshu.com/p/c8536f582d2b

• 碎片产生原因：

在SQL Server中，存储数据的最小单位是页，每一页所能容纳的数据为8060字节.而页的组织方式是通过B树结构，SQL Server向每个页内存储数据的最小单位是表的行(Row)当叶子节点中新插入的行或更新的行使得叶子节点无法容纳当前更新或者插入的行时，分页就产生了在分页的过程中，就会产生碎片

• 查询碎片

 

--选择好目标数据库，新建查询执行下列语句
--显示数据库里所有索引的碎片信息
DBCC SHOWCONTIG WITH ALL_INDEXES 
--显示指定表的所有索引的碎片信息
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES   
--显示指定索引的碎片信息
DBCC SHOWCONTIG (authors,aunmind)

解释：

　　扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片


  扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少


  扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片


  每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片


  扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片


  逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片


  扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片


  每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）


  平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片

 

• 碎片整理

原文：https://www.cnblogs.com/lgx5/p/9729337.html

第一步：查看是否需要维护，查看扫描密度/Scan Density是否为100%

declare @table_id int
set @table_id=object_id('表名')
dbcc showcontig(@table_id)
第二步:重构SQL Server数据库表索引

dbcc dbreindex('表名',pk_索引名,100)
重做第一步，如发现扫描密度/Scan Density还是小于100%则重构表的所有索引，并不一定能达100%。

dbcc dbreindex('表名','',100)

重建数据库所有表的索引

USE My_Database; 
DECLARE @name varchar(100)

DECLARE authors_cursor CURSOR FOR  Select [name]   from sysobjects where xtype='u' order by id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor  INTO @name

WHILE @@FETCH_STATUS = 0 
BEGIN    

　DBCC DBREINDEX (@name, '', 90)

 FETCH NEXT FROM authors_cursor     INTO @name 
END

deallocate authors_cursor

•  查询长耗时语句

SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
    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 [使用CPU的语法], qt.text [完整语法],
    dbname=db_name(qt.dbid),
    object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC