sqlserver分析性能常用语句

1.设置统计信息，查看IO和时间

通过执行时间，磁盘IO和执行计划查看sql语句情况，包括编译执行时间，索引扫描查找，读写等等
查看sql语句的执行时间，表扫描，物理读，逻辑读情况：
SET STATISTICS TIME ON
SET STATISTICS IO ON

 

2.检查数据库空间使用情况

 1 select o.name,
 2         SUM(p.reserved_page_count) as reserved_page_count,
 3         SUM(p.used_page_count) as used_page_count,
 4         SUM(
 5         case
 6         when(p.index_id<2) then (p.in_row_data_page_count+
 7         p.lob_used_page_count+p.row_overflow_used_page_count)
 8         else p.lob_used_page_count+p.row_overflow_used_page_count
 9         end
10         ) as DataPages,
11         SUM(
12         case
13         when (p.index_id<2) then row_count
14         else 0
15         end
16         ) as rowCounts
17         from sys.dm_db_partition_stats p inner join sys.objects o
18         on p.object_id=o.object_id
19         group by o.name
20         having SUM(p.used_page_count) >