SQL Server Performance study resources (SQL Server效能-學習資源)

SQL Server Performance study resources
SQL Server效能-學習資源
from my MSDN blog - September 7, 2016

查詢處理架構:

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

opic	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.aspx Microsoft® 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

http://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

http://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

Troubleshooting and Analysis with Traces.pdf

書名：SQL Server效能調校

書號：PG21414  作者：陳祥輝、陳臆如、黃浩 ISBN： 978-986-201-902-3

定價：NT$450元  印刷：單色 頁數：272頁

書籍規格：17*23 上市日：2014/3/30 譯者：(無)

http://www.drmaster.com.tw/Bookinfo.asp?BookID=PG21414

SQL Server Performance Tuning 效能調校

http://www.delightpress.com.tw/book.aspx?book_id=SKUD00026

分類｜ 資料庫

作者｜ 胡百敬‧姚巧玫‧周妙謙  日盛金控  劉承修

出版日期｜ 2014-06-06