什麼？有個 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>
可以檢視那個時間點的 CPU 使用率較高。
或許能找到哪個 SQL 語句占用了最多的 CPU 資源。
如果 Memory Grants Pending 的值一直大于 0，則明确的說明 Memory 存在壓力。
通常，如果 Signal Waits 超過 10-15%，則說明 CPU 壓力過大。
參考資料：
<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>
可以判斷哪張表或索引占用的 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 各項名額的細節。
<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>
《人人都是 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，如需轉載請自行聯系原作者