什么?有个 SQL 执行了 8 秒!
哪里出了问题?臣妾不知道啊,得找 DBA 啊。
DBA 人呢?离职了!!擦!!!
程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA"。
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#get_cpu_utilization_by_database">获取数据库的 CPU 使用率</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#get_cpu_utilization_history">过去一段时间里 CPU 利用率的历史情况</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#find_top_total_worker_time_for_entire_instance">谁用 CPU 工作的时间最长</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#check_system_memory_and_state">服务器上安装了多大的 Memory</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#sql_server_process_memory_usage">SQL Server 进程用了多少 Memory</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#memory_grants_pending">是否申请新的 Memory 无法得到</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#get_min_max_memory_config_items">SQL Server 的最大最小 Memory 配置</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#get_signal_waits_to_confirm_cpu_presure">通过 Signal Wait 判断是否 CPU 压力过大</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#get_total_buffer_usage_by_database">获取数据库的 Buffer 使用率</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#what_tables_and_indexes_using_most_memory_in_buffer_cache">查看哪张表占用的 Buffer 最多</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#look_memory_clerk_usage">查看 Memory Clerks 使用情况</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#get_memory_status_report">查看 Memory 分配状况</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#check_instance_memory_stress">查询 SQL Server 内存承担的压力</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#query_sql_server_performance_counters">查询 SQL Server 性能计数器</a>
<a href="http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html#query_last_several_seconds_avg_batch_requests">查询当前的 Batch Requests 计数</a>
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
可以查看那个时间点的 CPU 利用率较高。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
或许能找到哪个 SQL 语句占用了最多的 CPU 资源。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
如果 Memory Grants Pending 的值一直大于 0,则明确的说明 Memory 存在压力。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
通常,如果 Signal Waits 超过 10-15%,则说明 CPU 压力过大。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
参考资料:
<a href="http://technet.microsoft.com/en-us/library/aa337525(v=sql.105).aspx" target="_blank">SQL Server Buffer Management</a>
<a href="http://msdn.microsoft.com/en-us/library/ms189628.aspx" target="_blank">SQL Server, Buffer Manager Object</a>
<a href="http://msdn.microsoft.com/en-us/library/dn133176.aspx" target="_blank">Buffer Pool Extension</a>
<a href="http://stackoverflow.com/questions/3192579/buffer-and-cache-difference" target="_blank">Buffer and cache Difference?</a>
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
可以判断哪张表或索引占用的 Buffer 也就是 Memory 最多,可以考虑应用不同的 Compression Type。
<a href="http://msdn.microsoft.com/en-us/library/cc280449.aspx" target="_blank">Data Compression</a>
<a href="http://msdn.microsoft.com/en-us/library/cc280576.aspx" target="_blank">Row Compression Implementation</a>
<a href="http://msdn.microsoft.com/en-us/library/cc280464.aspx" target="_blank">Page Compression Implementation</a>
<a href="http://msdn.microsoft.com/en-us/library/hh710070.aspx" target="_blank">Enable Compression on a Table or Index</a>
SQL Server 2012 版本
SQL Server 2008 版本
<a href="http://msdn.microsoft.com/en-us/library/ms175019.aspx" target="_blank">sys.dm_os_memory_clerks (Transact-SQL)</a>
<a href="http://blogs.msdn.com/b/sqljourney/archive/2012/12/17/an-in-depth-look-at-sql-server-memory-part-1.aspx" target="_blank">An in-depth look at SQL Server Memory–Part 1</a>
<a href="http://blogs.msdn.com/b/sqljourney/archive/2013/02/13/an-in-depth-look-at-sql-server-memory-part-2.aspx" target="_blank">An in-depth look at SQL Server Memory–Part 2</a>
<a href="http://blogs.msdn.com/b/sqljourney/archive/2013/03/16/an-in-depth-look-at-sql-server-memory-part-3.aspx" target="_blank">An in-depth look at SQL Server Memory–Part 3</a>
可以直接运行:
查看 Memory 各项指标的细节。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
<a href="https://support.microsoft.com/kb/271624" target="_blank">Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage</a>
<a href="https://www.simple-talk.com/sql/database-administration/why-is-that-sql-server-instance-under-stress/" target="_blank">Why is that SQL Server Instance under stress?</a>
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
《人人都是 DBA》系列文章索引:
序号
名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
本文转自匠心十年博客园博客,原文链接:http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_resource_info_collection.html,如需转载请自行联系原作者