Share via


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