| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | monikerRange | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Monitor and Tune for Performance |
Learn about monitoring databases to assess server performance, using periodic snapshots and gathering data continuously to track performance trends. |
MikeRayMSFT |
mikeray |
07/18/2016 |
sql |
performance |
conceptual |
|
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance] 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. Efficient network traffic, disk I/O, and CPU usage are key to peak performance. 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.
Microsoft [!INCLUDEssNoVersion] and the Microsoft Windows operating system provide utilities to view the current condition of the database and track performance as conditions change. There are a variety of tools and techniques you can use to monitor [!INCLUDEmsCoName] [!INCLUDEssNoVersion]. Monitoring [!INCLUDEssNoVersion] 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. For example, by monitoring users trying to connect to an instance of [!INCLUDEssNoVersion], you can determine whether security is set up adequately and test applications or development systems. 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.
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.
-
By monitoring [!INCLUDEtsql] queries as they are executed, you can determine whether the queries are written correctly and producing the expected results.
-
By monitoring users that try to connect to an instance of [!INCLUDEssNoVersion], 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.
| Topic | Task |
|---|---|
| Monitor SQL Server Components | 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 | 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. |
| 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 | Use [!INCLUDEssNoVersion] and Windows performance and activity monitoring tools. |
| Monitor Resource Usage | Using System Monitor (also known as perfmon) to measure the performance of [!INCLUDEssNoVersion] using performance counters. |
Automated Administration Across an Enterprise
Compare and Analyze Execution Plans
Display and Save Execution Plans