Beginning Performance Monitoring For Sql: A Step-By-Step Guide

Monitoring the performance of your SQL Server is crucial to assess how your server is performing and to identify any bottlenecks or problems. Microsoft SQL Server offers a wide range of tools to monitor events and tune the physical database design. In this article, we will discuss the various methods and tools available to start monitoring your SQL Server's performance, including built-in functions, DBCC statements, Database Engine Tuning Advisor, Database Experimentation Assistant, Windows Application event log, Extended Events, Live Query Statistics, and more. We will also cover the differences between SQL Server Profiler and System Monitor, as well as provide step-by-step instructions for using Windows Performance Monitor for SQL Server tuning.

Utilise Microsoft SQL Server's built-in functions to display snapshot statistics about activity

Microsoft SQL Server comes with a built-in Activity Monitor that displays snapshot statistics about the current activity on the SQL Server instance. This includes information about running processes, resource waits, data file I/O, and recent and active expensive queries. The Activity Monitor is a real-time performance monitor that can be used to identify performance bottlenecks and expensive queries that are consuming a lot of resources.

To launch the Activity Monitor, right-click on the SQL Server instance in the Object Explorer and select "Activity Monitor" from the context menu. Alternatively, you can launch it by clicking on the Activity Monitor icon on the Standard toolbar.

The Activity Monitor is a tabbed document window with several expandable and collapsible panes: Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries, and Active Expensive Queries. Each pane provides specific information about the activity on the SQL Server instance. When a pane is expanded, the Activity Monitor queries the instance for the relevant information. Collapsing a pane stops all querying activity for that particular pane.

The Processes pane displays information about currently running processes, allowing users to manage and terminate them. It also provides the ability to trace processes in SQL Server Profiler, view execution plans, and access session details. The Resource Waits pane shows information about waits for resources, while the Data File I/O pane displays current data file I/O information at the file level.

The Recent and Active Expensive Queries panes are particularly useful for identifying performance issues. These panes display queries that are consuming a significant amount of resources, such as memory, disk activity, and network usage. By viewing the execution plan of these queries, users can identify hotspots and optimise query performance.

In addition to the Activity Monitor, Microsoft SQL Server also offers other tools for monitoring performance, such as Performance Dashboard Reports and the SQL Server Profiler. Performance Dashboard Reports provide interactive visual insights into the SQL Server's current state, including its configuration, version, services, and activity. The SQL Server Profiler, on the other hand, allows users to create and replay traces, capturing a workload and reproducing problems for troubleshooting.

Use the Database Console Command (DBCC) to check performance statistics and database consistency

Using DBCC to Check Performance Statistics and Database Consistency

Database Console Commands (DBCC) are used to perform different operations in your database. DBCC CHECKDB, in particular, is a command that checks the logical and physical integrity of all the objects in the specified database. It does this by running several other DBCC commands: CHECKALLOC, CHECKTABLE, and CHECKCATALOG.

CHECKDB also validates the contents of every indexed view in the database, the link-level consistency between table metadata and file system directories and files when storing varbinary(max) data, and the Service Broker data in the database.

The basic syntax for running DBCC CHECKDB is as follows:

Sql

DBCC CHECKDB ('DatabaseName')

This command can be automated using SQL Server Maintenance plans or custom scripts.

When running DBCC CHECKDB, there are a few options that can be used to modify its behaviour:

• `NOINDEX`: Specifies that intensive checks of nonclustered indexes for user tables will not be performed. This reduces the overall execution time.
• `NO_INFOMSGS`: Suppresses all informational messages.
• `PHYSICAL_ONLY`: Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This is designed to be a low-overhead check of the physical consistency of the database.
• `TABLOCK`: Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database, which will make the command run faster but decrease concurrency.
• `DATA_PURITY`: Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range.

It is recommended to run DBCC CHECKDB as part of your regular database maintenance routine. However, it is an I/O-intensive operation, so there are a few things to keep in mind:

• It is recommended to run DBCC CHECKDB while the database is ONLINE, but with minimal activity on the SQL Server.
• By default, DBCC CHECKDB performs parallel checking of objects, but this can be disabled using Trace Flag 2528.
• DBCC CHECKDB is supported on databases with memory-optimised tables, but validation only occurs on disk-based tables.
• DBCC CHECKDB uses an internal database snapshot to perform its checks. This prevents blocking and concurrency problems.

Compare two versions of the SQL Server Database Engine with the Database Experimentation Assistant (DEA)

The Database Experimentation Assistant (DEA) is a tool that helps users evaluate how the workload on their source server will perform in a new environment. It does this by guiding users through an A/B test, which involves three stages: capturing a workload trace on the source server, replaying the captured workload trace on two target servers, and analysing the replayed workload traces collected from the two targets.

DEA is the new A/B testing solution for SQL Server upgrades. It enables customers to gather performance insights for upgrades by conducting experiments on production database workloads across two versions of SQL Server. DEA supports migrating from SQL Server 2005 or higher versions to SQL Server 2012 and higher versions.

DEA analysis metrics include queries with compatibility errors, degraded queries and query plans, and other workload comparison data. This comparison data can lead to higher confidence and help ensure a successful upgrade experience.

DEA has three main features:

• Capture Workload: Ability to automatically capture a production workload trace with minimal inputs.
• Replay Workload: Ability to replay a trace on current and new/proposed instances of SQL.
• Analysis Reports: Ability to generate new reports that provide insight into how workload performance changes across versions of SQL.

DEA will be retired on 15 December 2024, and Microsoft will stop supporting the tool and issuing bug fixes or updates.

Use the Windows Application event log to view application events generated by SQL Server and other apps

To use the Windows Application event log to view application events generated by SQL Server and other apps, follow these steps:

On your computer, access the Search bar and type "Event Viewer". Select the Event Viewer desktop app from the options presented. Within the Event Viewer, expand the Windows Logs folder by clicking on it, and then select the "Application event log" from the available options.

SQL Server events will be identified by the entry "MSSQLSERVER" in the Source column, while named instances will be identified as "MSSQL$". SQL Server Agent events will appear as "SQLSERVERAGENT", and for named instances of SQL Server, Agent events will be denoted as "SQLAgent$". Microsoft Search service events will be identified by the entry "Microsoft Search".

To focus solely on SQL Server events, navigate to the View menu and select Filter. From the Event source list, choose "MSSQLSERVER". If you wish to view only SQL Server Agent events, simply select "SQLSERVERAGENT" from the same list.

To view more detailed information about a specific event, double-click on the event entry. This will provide additional context and details about the event in question.

Additionally, you can use the Windows Performance Monitor, also known as PerfMon, to capture metrics for your entire server. This tool allows you to capture and graph various aspects of your Windows server, including counters for .NET, Disks, Memory, Processors, Network, and more. PerfMon also offers several counters related to each instance of SQL Server on the box, enabling you to monitor and identify issues at the instance level.

Use the Windows Performance Monitor to capture metrics for your entire server

Windows Performance Monitor, also known as PerfMon, is a powerful tool that can be used to capture metrics for your entire server. It allows you to monitor and collect data on various aspects of your Windows server, including .NET, Disks, Memory, Processors, and Network. With PerfMon, you can also monitor multiple instances of SQL Server running on a single server, providing insights into what is happening at the instance level.

To launch the Windows Performance Monitor, you can follow these steps:

• Go to Start > Control Panel > Administrative Tools > Performance Monitor, or simply launch PerfMon.exe.
• Alternatively, use the Windows key + R keyboard shortcut to open the Run command, type "perfmon", and click OK.
• Another option is to use the Windows key + X keyboard shortcut to open the Power User menu, select Computer Management, and then click on Performance.

Once you have opened the Windows Performance Monitor, you will see an interface with a single counter, typically the "Processor Time" or ""% Processor Time" counter, which displays the processor load in real-time. The real power of PerfMon lies in its ability to add additional counters and capture performance metrics over an elapsed period of time.

To add new counters, follow these steps:

• Click the green plus button above the Performance Monitor graph.
• Select Local Computer or your computer's name from the drop-down menu.
• Choose the category of the item you want to monitor, such as "Network Adapter".
• Select the specific counters you want to monitor, such as "Bytes Total/sec".
• If applicable, select the instances you want to monitor, such as multiple network adapters.
• Click the "Add" button, followed by "OK" to confirm and add the new counters.

In addition to adding counters, you can also customize various aspects of the data displayed in the graph. To do this, double-click on one of the counters to open the Performance Monitor Properties window. From here, you can select the counter you want to customize, choose the color, scale, width, and style, and even change the graph type to Line, Histogram bar, or Report.

Another important feature of the Windows Performance Monitor is the ability to create Data Collector Sets. These sets allow you to capture data from your system over a specified period of time, providing insights into changes in configuration and performance information. You can create custom Data Collector Sets by following these steps:

• In the Performance Monitor, expand Data Collector Sets and right-click on "User Defined".
• Select "New" and then click on "Data Collector Set".
• Name your Data Collector Set and choose the "Create manually (Advanced)" option.
• Select "Create data logs" and check the boxes for the types of data you want to collect, such as counter, trace, or configuration.
• Set the counter interval rate and specify the counters you want to capture.
• Click "Add" and select the counters you want, paying attention to whether you want PerfMon to collect the data as a total or per processor.
• Specify the location where you want to save the data.
• Finally, select a user that will run the data collector set, especially in secure environments.

With the Windows Performance Monitor, you can capture metrics for your entire server, including CPU, memory, disk, and network bandwidth. By setting up Data Collector Sets, you can collect performance data over time and gain valuable insights into your server's performance and overall health.

Frequently asked questions