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