SQL Server Performance study resources
SQL Server Performance study resources
SQL Server效能-學習資源
查詢處理架構 :
Query Processing Architecture https://technet.microsoft.com/en-us/library/cc280362(v=sql.105).aspx
效能監控與分析 :
Monitor and Tune for Performance https://msdn.microsoft.com/en-us/library/ms189081.aspx
| Topic | Task |
| Monitor SQL Server Components | Required steps to monitor any SQL Server component. |
| Performance Monitoring and Tuning Tools | Lists the monitoring and tuning tools available with SQL Server. |
| Establish a Performance Baseline | How to establish a performance baseline. |
| Isolate Performance Problems | Isolate database performance problems. |
| Identify Bottlenecks | Monitor and track server performance to identify bottlenecks. Monitor CPU Usage Monitor Disk Usage Monitor Memory Usage |
| Server Performance and Activity Monitoring | Use SQL Server and Windows performance and activity monitoring tools.Open Activity Monitor (SQL Server Management Studio) 活動監視器 https://msdn.microsoft.com/en-us/library/ms175518.aspxMicrosoft® SQL Server® 2012 Performance Dashboard Reports https://www.microsoft.com/en-us/download/details.aspx?id=29063 |
| Display and Save Execution Plans | Display and save execution plans to a file in XML format. |
Query Tuning(語法優化，前面2篇很重要)
Chapter 14 — Improving SQL Server Performance
https://msdn.microsoft.com/en-us/library/ff647793.aspx
SQL Server
Optimizing SQL Server Query Performance
Maciej Pilecki
https://technet.microsoft.com/en-us/library/2007.11.sqlquery.aspx
Query Tuning
https://technet.microsoft.com/en-us/library/ms176005(v=sql.105).aspx
Understanding how SQL Server executes a query
August 1st, 2013
https://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/
目前正在執行的Query
Determine the Currently Executing Statement in a Long Running SQL Stored Procedure https://blogs.msdn.microsoft.com/taylaf/2010/01/25/determine-the-currently-executing-statement-in-a-long-running-sql-stored-procedure/
SELECT requests.session_id,
requests.status,
requests.command,
requests.statement_start_offset,
requests.statement_end_offset,
requests.total_elapsed_time,
details.text
FROM sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) details
WHERE requests.session_id > 50
ORDER BY total_elapsed_time DESC
Status:
sys.dm_exec_sessions (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms176013.aspx
Status of the session. Possible values: Running - Currently running one or more requests Sleeping - Currently running no requests Dormant – Session has been reset because of connection pooling and is now in prelogin state. Preconnect - Session is in the Resource Governor classifier. Is not nullable.
sys.dm_exec_requests (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms177648.aspx
Status of the request. This can be one of the following:
Background
Running
Runnable
Sleeping
Suspended
Is not nullable.
Different Status of a SPID in SQL Server and What do they mean https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/08/different-status-of-a-spid-in-sql-server-and-what-do-they-mean/
How It Works: What is a Sleeping / Awaiting Command Session https://blogs.msdn.microsoft.com/psssql/2008/04/21/how-it-works-what-is-a-sleeping-awaiting-command-session/
Wait Type:
Wait statistics, or please tell me where it hurts https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
Doctor, this SQL Server appears to be sick…. https://blogs.msdn.microsoft.com/psssql/2009/11/24/doctor-this-sql-server-appears-to-be-sick/
Case Study: Part 1: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Introduction to Using Wait Stats to Identify & Remediate Query Parallelism Bottlenecks https://blogs.msdn.microsoft.com/jimmymay/2008/11/28/case-study-part-1-cxpacket-wait-stats-max-degree-of-parallelism-option-introduction-to-using-wait-stats-to-identify-remediate-query-parallelism-bottlenecks/
Troubleshooting ASYNC_NETWORK_IO, NETWORKIO https://blogs.msdn.microsoft.com/joesack/2009/01/08/troubleshooting-async_network_io-networkio/
What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server https://blogs.msdn.microsoft.com/sqlsakthi/2011/04/16/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server/
What’s that HTDELETE wait type? https://blogs.msdn.microsoft.com/ialonso/2014/07/23/whats-that-htdelete-wait-type/
Meditation on SQL Trace performance Impact and Wait types https://blogs.msdn.microsoft.com/sqlmeditation/2012/12/12/meditation-on-sql-trace-performance-impact-and-wait-types/
REPL_SCHEMA_ACCESS wait type https://blogs.msdn.microsoft.com/psssql/2014/06/03/repl_schema_access-wait-type/
sys.dm_os_wait_stats (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms179984.aspx
The SQL Server Wait Type Repository… https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/
Best Performer: Distributed query (Four-part) or OPENQUERY when executing linked server queries in SQL Server
https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/
issues where too many connections running distributed queries end up in a SOSHOST_MUTEX wait while SQL Server is collecting data distribution statistics from the remote server.
透過SQL Profiler trace分析 :
Troubleshooting and Analysis with Traces https://msdn.microsoft.com/en-us/library/cc293616.aspx
書名：SQL Server效能調校
書號：PG21414 作者：陳祥輝、陳臆如、黃浩 ISBN： 978-986-201-902-3
定價：NT$450元 印刷：單色 頁數：272頁
書籍規格：17*23 上市日：2014/3/30 譯者：(無)
https://www.drmaster.com.tw/Bookinfo.asp?BookID=PG21414
SQL Server Performance Tuning 效能調校
https://www.delightpress.com.tw/book.aspx?book_id=SKUD00026
分類｜ 資料庫
原文書名｜
作者｜ 胡百敬‧姚巧玫‧周妙謙 日盛金控 劉承修
譯者｜
校閱者｜
出版日期｜ 2014-06-06