效能的監視與微調Monitor and Tune for Performance

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

監視資料庫的目標在於評估伺服器的執行效能。The goal of monitoring databases is to assess how a server is performing. 有效的監視包括定期建立目前效能的快照集以隔離造成問題的處理序,以及持續蒐集資料來追蹤效能趨勢。Effective monitoring involves taking periodic snapshots of current performance to isolate processes that are causing problems, and gathering data continuously over time to track performance trends.

持續進行的資料庫效能評估可協助您將回應時間降到最低並產生最大產能,以達最佳效能。Ongoing evaluation of the database performance helps you minimize response times and maximize throughput, yielding optimal performance. 有效率的網路流量、磁碟 I/O 與 CPU 使用量是達到最佳效能的關鍵。Efficient network traffic, disk I/O, and CPU usage are key to peak performance. 您必須徹底分析應用程式需求、了解資料的邏輯與實體結構、評估資料庫使用,以及商議使用衝突的折衷方案,如線上交易處理 (Online Transaction Processing,OLTP) 之於決策支援。You need to thoroughly analyze the application requirements, understand the logical and physical structure of the data, assess database usage, and negotiate tradeoffs between conflicting uses such as online transaction processing (OLTP) versus decision support.

監視和微調資料庫效能Monitoring and tuning databases for performance

Microsoft SQL ServerSQL Server 和 Microsoft Windows 作業系統提供公用程式,以檢視資料庫的目前狀況並隨著狀況變更來追蹤效能。Microsoft SQL ServerSQL Server and the Microsoft Windows operating system provide utilities to view the current condition of the database and track performance as conditions change. 您可以使用各種工具和技術來監視 MicrosoftMicrosoft SQL ServerSQL ServerThere are a variety of tools and techniques you can use to monitor MicrosoftMicrosoft SQL ServerSQL Server. 監視 SQL ServerSQL Server 可協助您:Monitoring SQL ServerSQL Server helps you:

  • 判斷是否可以改善效能。Determine whether you can improve performance. 例如,監視常用查詢的回應時間,您可以判斷是否需要變更資料表的查詢或索引。For example, by monitoring the response times for frequently used queries, you can determine whether changes to the query or indexes on the tables are required.

  • 評估使用者活動。Evaluate user activity. 例如,藉由監視嘗試連接 SQL ServerSQL Server執行個體的使用者,您可以判斷安全性是否設定適當,並測試應用程式和開發系統。For example, by monitoring users trying to connect to an instance of SQL ServerSQL Server, you can determine whether security is set up adequately and test applications or development systems. 例如,藉由監視執行中的 SQL 查詢,您可以判斷查詢是否撰寫正確並產生預期的結果。For example, by monitoring SQL queries as they are executed, you can determine whether they are written correctly and producing the expected results.

  • 對問題進行疑難排解或對應用程式元件進行偵錯,例如預存程序。Troubleshoot problems or debug application components, such as stored procedures.

動態環境中的監視Monitoring in a dynamic environment

變更條件會導致效能變更。Changing conditions result in changing performance. 評估過程中,當使用者數目增加、使用者存取與連接方式變更、資料庫內容成長、用戶端應用程式變更、應用程式中的資料變更、查詢變得更複雜,以及網路流量提高時,效能也會跟著變更。In your evaluations, you can see performance changes as the number of users increases, user access and connection methods change, database contents grow, client applications change, data in the applications changes, queries become more complex, and network traffic rises. 藉由使用工具來監視效能,可協助您找出條件變更或複雜查詢與效能變更之間的關聯。Using tools to monitor performance helps you associate changes in performance with changing conditions and complex queries. 範例:Examples:

  • 藉由監視常用查詢的回應時間,您可以判斷是否需要變更執行查詢之資料表的查詢或索引。By monitoring the response times for frequently used queries, you can determine whether changes to the query or indexes on the tables where the queries execute are required.

  • 藉由監視執行中的 Transact-SQLTransact-SQL 查詢,您可以判斷查詢是否撰寫正確並產生預期的結果。By monitoring Transact-SQLTransact-SQL queries as they are executed, you can determine whether the queries are written correctly and producing the expected results.

  • 藉由監視嘗試連接 SQL ServerSQL Server執行個體的使用者,您可以判斷安全性是否適當地設定,並對應用程式或開發系統進行測試。By monitoring users that try to connect to an instance of SQL ServerSQL Server, you can determine whether security is set up adequately and test applications or development systems.

回應時間就是將結果集的第一個資料列傳回給使用者所需的時間長度,以視覺化確認的形式表示查詢已經過處理了。Response time is the length of time required for the first row of the result set to be returned to the user in the form of visual confirmation that a query is being processed. 輸送量是指在指定的期間內,伺服器所處理的查詢總數。Throughput is the total number of queries handled by the server during a specified period of time.

隨著使用者數目的增加,伺服器資源的爭奪現象也會隨之增加,連帶使回應時間增加,整體輸送量降低。As the number of users increases, so does the competition for a server's resources, which in turn increases response time and decreases overall throughput.

監視和效能微調工作Monitoring and performance tuning tasks

主題Topic 工作Task
監視 SQL Server 元件Monitor SQL Server Components 監視任何 SQL Server 元件所需的步驟,例如,活動監視器、擴充事件,以及動態管理檢視與函數等。Required steps to monitor any SQL Server component, such as Activity Monitor, Extended Events, and Dynamic Management Views and Functions, etc.
效能監視及微調工具Performance Monitoring and Tuning Tools 列出可供 SQL Server 使用的監視及微調工具,例如,即時查詢統計資料和 Database Engine Tuning Advisor。Lists the monitoring and tuning tools available with SQL Server, such as Live Query Statistics, and the Database Engine Tuning Advisor.
使用查詢調整小幫手來升級資料庫Upgrading Databases by using the Query Tuning Assistant 在升級到較新的資料庫相容性層級期間,保持工作負載效能穩定性。Keep workload performance stability during the upgrade to newer database compatibility level.
使用查詢存放區監視效能Monitoring Performance by Using the Query Store 使用查詢存放區來自動擷取查詢、計劃和執行階段統計資料的記錄,並加以保留供您檢閱。Use Query Store to automatically capture a history of queries, plans, and runtime statistics, and retain these for your review.
建立效能基準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.
使用 DMV 來判斷檢視表的使用方式統計資料和效能Use DMVs to Determine Usage Statistics and Performance of Views 涵蓋可用來取得查詢效能相關資訊的方法和指令碼。Covers methodology and scripts used to get information about the performance of queries.
伺服器效能與活動監視Server Performance and Activity Monitoring 使用 SQL ServerSQL Server 以及 Windows 效能和活動監視工具。Use SQL ServerSQL Server and Windows performance and activity monitoring tools.
監視資源使用量Monitor Resource Usage 使用系統監視器 (也稱為 perfmon),利用效能計數器來測量 SQL ServerSQL Server 效能。Using System Monitor (also known as perfmon) to measure the performance of SQL ServerSQL Server using performance counters.

另請參閱See also

將整個企業的管理自動化 Automated Administration Across an Enterprise
比較和分析執行計畫 Compare and Analyze Execution Plans
顯示並儲存執行計畫Display and Save Execution Plans