UDN-企业互联网技术人气社区

板块导航

浏览  : 1114
回复  : 0

[讨论交流] 数据库性能优化常用sql脚本总结

[复制链接]
菊花一朵朵的头像 楼主
发表于 2016-3-24 22:18:42 | 显示全部楼层 |阅读模式

  最近闲来无事,正好抽出时间,来总结总结 sql性能优化方面的一下小技巧,小工具。虽然都是些很杂的东西,但是我个人觉得,如果真的清楚了里面的一下指标,或许真的能抵半个DBA。

  有些时候,找不到DBA或者根本就没有DBA的时候,程序员就只能靠自己想办法了解决。久而久之,久病成医,说不定就成了半个DBA了。 这里面的一些脚本,有自己总结的,也有网上找的。希望能给程序员在性能优化方面一些帮助。(PS: 这些脚本,都是SQL Server 下的)。

  1. 当前连接的Session 有多少
  1. SELECT login_name

  2.     ,[program_name]

  3.     ,COUNT(session_id) AS [session_count]

  4. FROM sys.dm_exec_sessions WITH (NOLOCK)

  5. GROUP BY login_name,[program_name]

  6. ORDER BY COUNT(session_id) desc;
复制代码

  2. 每个数据库上的Session 数量是多少
  1. SELECT DB_NAME(dbid) AS DBName

  2.     ,COUNT(dbid) AS NumberOfConnections

  3.     ,loginame AS LoginName

  4. FROM sys.sysprocesses

  5. WHERE dbid > 0

  6. GROUP BY dbid,loginame
复制代码

  3. 查看阻塞
  1. SELECT

  2.     SPID                = er.session_id

  3.     ,STATUS             = ses.STATUS

  4.     ,[LOGIN]            = ses.login_name

  5.     ,HOST               = ses.host_name

  6.     ,BlkBy              = er.blocking_session_id

  7.     ,DBName             = DB_NAME(er.database_id)

  8.     ,CommandType        = er.command

  9.     ,SQLStatement       = st.text

  10.     ,BlockingText     = bst.text

  11.     ,ObjectName         = OBJECT_NAME(st.objectid)

  12.     ,ElapsedMS          = er.total_elapsed_time

  13.     ,CPUTime            = er.cpu_time

  14.     ,IOReads            = er.logical_reads + er.reads

  15.     ,IOWrites           = er.writes

  16.     ,LastWaitType       = er.last_wait_type

  17.     ,StartTime          = er.start_time

  18.     ,Protocol           = con.net_transport

  19.     ,ConnectionWrites   = con.num_writes

  20.     ,ConnectionReads    = con.num_reads

  21.     ,ClientAddress      = con.client_net_address

  22.     ,Authentication     = con.auth_scheme

  23. FROM sys.dm_exec_requests er

  24. OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

  25. LEFT JOIN sys.dm_exec_sessions ses

  26. ON ses.session_id = er.session_id

  27. LEFT JOIN sys.dm_exec_connections con

  28. ON con.session_id = ses.session_id

  29. LEFT JOIN sys.dm_exec_requests ber

  30. ON er.blocking_session_id=ber.session_id

  31. OUTER APPLY sys.dm_exec_sql_text(ber.sql_handle) bst

  32. WHERE er.session_id > 50

  33. ORDER BY er.blocking_session_id DESC,er.session_id
复制代码

  4. 找出哪些表的Index 需要改进
  1. SELECT CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]

  2.     ,migs.last_user_seek

  3.     ,mid.[statement] AS [Database.Schema.Table]

  4.     ,mid.equality_columns

  5.     ,mid.inequality_columns

  6.     ,mid.included_columns

  7.     ,migs.unique_compiles

  8.     ,migs.user_seeks

  9.     ,migs.avg_total_user_cost

  10.     ,migs.avg_user_impact

  11. FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

  12. INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle

  13. INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle

  14. ORDER BY index_advantage desc
复制代码

  5. 查看Index 的Statistics 最后更新时间
  1. SELECT SCHEMA_NAME(o.[schema_id]) + N'.' + o.[name] AS [Object Name]

  2.     ,o.type_desc AS [Object Type]

  3.     ,i.[name] AS [Index Name]

  4.     ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date]

  5.     ,s.auto_created

  6.     ,s.no_recompute

  7.     ,s.user_created

  8.     ,st.row_count

  9.     ,st.used_page_count

  10. FROM sys.objects AS o WITH (NOLOCK)

  11. 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]

  12.     AND i.index_id = s.stats_id

  13. INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]

  14.     AND i.[index_id] = st.[index_id]WHERE o.[type] IN ('U','V')

  15.     AND st.row_count > 0

  16. ORDER BY STATS_DATE(i.[object_id], i.index_id) desc;
复制代码

  6. 查看Index 碎片化指数
  1. SELECT DB_NAME(ps.database_id) AS [Database Name]

  2.     ,OBJECT_NAME(ps.[object_id]) AS [Object Name]

  3.     ,i.[name] AS [Index Name]

  4.     ,ps.index_id

  5.     ,ps.index_type_desc

  6.     ,ps.avg_fragmentation_in_percent

  7.     ,ps.fragment_count

  8.     ,ps.page_count

  9.     ,i.fill_factor

  10.     ,i.has_filter

  11.     ,i.filter_definition

  12. FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps

  13. INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]

  14.     AND ps.index_id = i.index_id

  15. WHERE ps.database_id = DB_ID()

  16.     AND ps.page_count > 2500

  17. ORDER BY ps.avg_fragmentation_in_percent desc;
复制代码

  7. 查询前 10 个可能是性能最差的 SQL 语句
  1. SELECT TOP 10 TEXT AS 'SQL Statement'
  2.     ,last_execution_time AS 'Last Execution Time'
  3.     ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
  4.     ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
  5.     ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
  6.     ,execution_count AS "Execution Count"
  7.     ,qp.query_plan AS "Query Plan"
  8. FROM sys.dm_exec_query_stats qs
  9. CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
  10. CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
  11. ORDER BY total_elapsed_time / execution_count DESC
复制代码
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部