SQL Server Performance study resources

Article
09/07/2016

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/

透過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

Share via

SQL Server Performance study resources