数据库性能优化常用SQL脚本总结

最近闲来无事，正好抽出时间，来总结总结 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 LoginNameFROM sys.sysprocessesWHERE 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_schemeFROM sys.dm_exec_requests erOUTER APPLY sys.dm_exec_sql_text(er.sql_handle) stLEFT JOIN sys.dm_exec_sessions sesON ses.session_id = er.session_idLEFT JOIN sys.dm_exec_connections conON con.session_id = ses.session_idLEFT JOIN sys.dm_exec_requests berON er.blocking_session_id=ber.session_idOUTER APPLY sys.dm_exec_sql_text(ber.sql_handle) bstWHERE er.session_id > 50ORDER 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_impactFROM 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_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handleORDER 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_countFROM 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_idINNER 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 > 0ORDER 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_definitionFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS psINNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]AND ps.index_id = i.index_idWHERE ps.database_id = DB_ID()AND ps.page_count > 2500ORDER 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 qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_elapsed_time / execution_count DESC

原文作者：章为忠
转自：http://www.cnblogs.com/zhangweizhong/