神刀安全网

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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » 数据库性能优化常用sql脚本总结

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮