最近闲来无事，正好抽出时间，来总结总结 sql性能优化方面的一下小技巧,小工具。虽然都是些很杂的东西，但是我个人觉得，如果真的清楚了里面的一下指标，或许真的能抵半个DBA。
有些时候，找不到DBA或者根本就没有DBA的时候，程序员就只能靠自己想办法了解决。久而久之，久病成医，说不定就成了半个DBA了。 这里面的一些脚本，有自己总结的，也有网上找的。希望能给程序员在性能优化方面一些帮助。（PS: 这些脚本，都是SQL Server 下的）。
1. 当前连接的Session 有多少
SELECT login_name
,[program_name]
,COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name,[program_name]
ORDER BY COUNT(session_id) desc;
2. 每个数据库上的Session 数量是多少
SELECT DB_NAME(dbid) AS DBName
,COUNT(dbid) AS NumberOfConnections
,loginame AS LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid,loginame
3. 查看阻塞
SELECT SPID = er.session_id ,STATUS = ses.STATUS ,[LOGIN] = ses.login_name ,HOST = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_NAME(er.database_id) ,CommandType = er.command ,SQLStatement = st.text ,BlockingText = bst.text ,ObjectName = OBJECT_NAME(st.objectid) ,ElapsedMS = er.total_elapsed_time ,CPUTime = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con.net_transport ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id LEFT JOIN sys.dm_exec_requests ber ON er.blocking_session_id=ber.session_id OUTER APPLY sys.dm_exec_sql_text(ber.sql_handle) bst WHERE er.session_id > 50 ORDER BY er.blocking_session_id DESC,er.session_id
4. 找出哪些表的Index 需要改进
SELECT CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage] ,migs.last_user_seek ,mid.[statement] AS [Database.Schema.Table] ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns ,migs.unique_compiles ,migs.user_seeks ,migs.avg_total_user_cost ,migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle ORDER BY index_advantage desc
5. 查看Index 的Statistics 最后更新时间
SELECT SCHEMA_NAME(o.[schema_id]) + N'.' + o.[name] AS [Object Name] ,o.type_desc AS [Object Type] ,i.[name] AS [Index Name] ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date] ,s.auto_created ,s.no_recompute ,s.user_created ,st.row_count ,st.used_page_count FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id]WHERE o.[type] IN ('U','V') AND st.row_count > 0 ORDER BY STATS_DATE(i.[object_id], i.index_id) desc;
6. 查看Index 碎片化指数
SELECT DB_NAME(ps.database_id) AS [Database Name] ,OBJECT_NAME(ps.[object_id]) AS [Object Name] ,i.[name] AS [Index Name] ,ps.index_id ,ps.index_type_desc ,ps.avg_fragmentation_in_percent ,ps.fragment_count ,ps.page_count ,i.fill_factor ,i.has_filter ,i.filter_definition FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id WHERE ps.database_id = DB_ID() AND ps.page_count > 2500 ORDER BY ps.avg_fragmentation_in_percent desc;
7. 查询前 10 个可能是性能最差的 SQL 语句
SELECT TOP 10 TEXT AS 'SQL Statement' ,last_execution_time AS 'Last Execution Time' ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO] ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)] ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)] ,execution_count AS "Execution Count" ,qp.query_plan AS "Query Plan" FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_elapsed_time / execution_count DESC