数据库性能优化所常用的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 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 碎片化指数

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;

　　6. 查看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;

　　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