Monitoring Ms Sql Server Performance: Tips For Success

Monitoring the performance of your MS SQL Server is crucial to ensure optimal resource utilisation and performance. Microsoft SQL Server and Windows OS provide built-in utilities like Performance Monitor and Activity Monitor to help administrators gain insights into system resource utilisation, query execution statistics, and identify performance bottlenecks. Additionally, there are various third-party tools and add-ins available that offer real-time performance analysis and help in troubleshooting SQL Server issues. This article will explore the different tools and techniques for monitoring MS SQL Server performance, including system snapshots, query execution plans, and performance dashboards.

Using the Windows Performance Monitor

The Windows Performance Monitor or PerfMon is a tool that allows you to capture and graph many aspects of the Windows server, including .NET, Disks, Memory, Processors, and Network. It also includes several counters related to each instance of SQL Server on the box.

To launch the Windows Performance Monitor, go to Start > Control Panel > Administrative Tools > Performance Monitor or launch PerfMon.exe. Once running, you will see an interface with a counter "% Processor Time" being tracked, along with the following items: Last, Average, Minimum, Maximum, and Duration.

To add a counter, right-click on the big green plus button and select from the available options, such as Memory, Physical Disk, and SQL Server-specific counters. You can also change the duration and frequency of data collection by right-clicking on the graph and selecting Properties.

The Windows Performance Monitor is a useful tool to capture metrics for your entire server, including issues outside of SQL Server that may be causing performance issues. By capturing baseline values when your system is running fine, you can compare them to values captured during performance issues to identify the root cause.

Monitoring the MS SQL Server database

Microsoft SQL Server and the Microsoft Windows OS provide monitoring utilities to optimise resources and improve performance. The System/Performance Monitor tool, for example, can be used to monitor the utilisation of system resources. This tool can be launched by going to Start > Run, typing "perfmon" and selecting "OK". The Activity Monitor in SSMS is another useful feature, which displays statistics such as processes, resource waits, and recent and active expensive queries.

The Performance Dashboard in SSMS provides visual insights into the performance of the SQL Server instance, helping to identify system CPU utilisation, current waiting requests, and other performance metrics. The Query Store feature is also useful, as it captures a history of queries, plans, and runtime statistics, allowing for easy performance troubleshooting.

For real-time monitoring of SQL Server performance, the free add-in dbForge Monitor for Microsoft SQL Server Management Studio is a good option. It provides a rich collection of metrics, including read and write latencies, total data reads and writes, and summary statistics for all databases. It also includes a SQL query performance analyser to detect and optimise expensive queries.

Other tools available include the Database Engine Tuning Advisor (DTA), Database Experimentation Assistant (DEA), Live Query Statistics (LQS), and the SQL Server Profiler, which has been deprecated but is still available in SQL Server 2016.

Using the Activity Monitor

The Activity Monitor is a real-time monitoring tool within SQL Server that can be used to monitor everything from performance to I/O costs to expensive queries. It is mainly used for monitoring SQL Server with respect to databases and their queries, and other measures to help understand what is impacting SQL Server at a given time. This can help you troubleshoot issues and see how SQL Server is using server resources.

To open the Activity Monitor, right-click on the SQL Server instance name and click Activity Monitor. You can also launch it from the Standard toolbar by clicking the Activity Monitor icon, which is located in the middle, just to the right of the undo/redo buttons. If you are not already connected to an instance of SQL Server that you want to monitor, complete the "Connect to Server" dialog box.

The Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries, and Active Expensive Queries. When any pane is expanded, the Activity Monitor queries the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can expand one or more panes simultaneously to view different kinds of activity on the instance.

The Overview pane is the first thing you see when you open the Activity Monitor. The Processes pane gives you the ability to look at currently running processes so you can manage them. Right-clicking brings up the context menu, from which you can terminate, trace in the SQL Server Profiler, view as an execution plan, and view session details, which pops up a dialog showing the last T-SQL command batch. The Resource Waits pane shows information about waits for resources, while the Data File I/O pane shows current data file I/O information that is happening at the file level. The Recent/Active Expensive Queries pane shows recent or active expensive queries that use many resources (memory, disk activity, network).

To set the Activity Monitor refresh interval, open the Activity Monitor, right-click Overview, select Refresh Interval, and then select the interval in which Activity Monitor should obtain new instance information. When the refresh interval is set to less than 10 seconds, the time used to run these queries can affect server performance. To view actual activity, you must have VIEW SERVER STATE permission. To view the Data File I/O section of Activity Monitor, you must have CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permission in addition to VIEW SERVER STATE.

Using the Performance Dashboard

The Performance Dashboard in SSMS provides visual insights into the performance of the SQL Server instance. It helps to quickly identify whether there is any current performance bottleneck in SQL Server and if a bottleneck is found, it can help capture additional diagnostic data to resolve the problem.

To access the Performance Dashboard, right-click on the SQL Server instance name in the Object Explorer, then select Reports > Standard Reports > Performance Dashboard. The Performance Dashboard will appear as a new tab.

The Performance Dashboard is divided into several sections and sub-reports:

• System CPU Utilization
• Current Waiting Requests
• Historical Information
• Miscellaneous Information
• Active XEvent Sessions

The System CPU Utilization section helps identify CPU bottlenecks and the queries consuming the most CPU. It also provides information on Current Waiting Requests, which includes details on any requests that are currently waiting for resources.

The Historical Information section provides insights into past performance, allowing users to identify trends and issues over time. The Miscellaneous Information section includes additional data that may be useful for troubleshooting, such as information on the SQL Server instance configuration, version, and activity.

The Performance Dashboard also helps identify expensive queries that may have been executed in the past. It provides several metrics to define high-cost queries, including CPU usage, Logical Writes, Logical Reads, Duration, Physical Reads, and CLR Time.

Additionally, the Performance Dashboard uses Execution, Index, and I/O-related Dynamic Management Views (DMVs) and Functions (DMFs) to provide detailed insights into the performance of the SQL Server instance.

Using the Database Engine Tuning Advisor

The Database Engine Tuning Advisor (DTA) is a tool that examines how queries are processed in the specified databases and recommends ways to improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning. It provides two user interfaces: a graphical user interface (GUI) and the dta command prompt utility. The GUI allows users to quickly view the results of tuning sessions, while the dta utility facilitates the incorporation of DTA functionality into scripts for automated tuning. Additionally, the DTA can process XML input, providing more control over the tuning process.

To start the Database Engine Tuning Advisor, a user with system administrator permissions can either use the GUI to connect to an instance of SQL Server or utilise the dta utility to tune the first workload. When using the GUI, it is important to ensure that SQL Server is not running in single-user mode, as this will prevent the DTA from starting. The DTA can be launched from various locations, including the Windows Start menu, the Tools menu in SQL Server Management Studio, the Query Editor in SQL Server Management Studio, and the Tools menu in SQL Server Profiler.

The DTA utilises workloads, which are sets of Transact-SQL statements that execute against a database or databases. Workloads can be created using the Query Store, the plan cache, the Query Editor in SQL Server Management Studio, or SQL Server Profiler to create trace file or trace table workloads. Workloads can also be embedded in an XML input file, where weights for each event can be specified.

Once the DTA is initialised and the workload is defined, users can tune a database using either the GUI or the dta utility. The GUI provides an intuitive interface for tuning a database, allowing users to specify the workload type (plan cache, workload files, or workload tables) and select the databases and tables to be tuned. The dta utility, on the other hand, is a command-prompt executable file that enables the use of DTA functionality in batch files and scripts. It accepts plan cache entries, trace files, trace tables, and Transact-SQL scripts as workloads, along with XML input conforming to the DTA XML schema.

The DTA is a valuable tool for database administrators, providing insights into the performance of Transact-SQL statements and offering recommendations for improvement. By following the steps outlined above, administrators can effectively utilise the DTA to optimise database performance.

Frequently asked questions