Monitoring Sql Performance: Strategies For Optimization

Monitoring SQL performance is essential to ensure optimal speed, health, and functionality. It involves taking periodic snapshots of current performance to isolate problem-causing processes and continuously gathering data to track performance trends.

There are various tools available for monitoring SQL performance, including native Windows and SQL Server tools, such as SQL Server Profiler, Windows Performance Monitor, and SQL Server Activity Monitor. These tools can help identify issues like rogue queries, missing indexes, and resource contention.

Additionally, third-party tools like SolarWinds Database Performance Analyzer, SolarWinds Server & Application Monitor, and Idera SQL Diagnostic Manager offer more advanced features and functionality for monitoring SQL performance.

By leveraging these tools, database administrators can effectively monitor and optimize SQL performance, ensuring a smooth and efficient database experience.

Windows Performance Monitor

One of the key advantages of PerfMon is its ability to monitor both Windows operating system and SQL Server counters simultaneously. For example, by observing the Windows disk input/output (I/O) counters alongside the SQL Server Buffer Manager counters, administrators can gain insights into the overall system behaviour. This helps in determining if issues are originating from within SQL Server or from external factors.

PerfMon provides a range of features to help with performance monitoring and analysis. It allows users to view data from multiple computers simultaneously and customise charts to reflect current activity. The data can be exported to spreadsheets or databases for further analysis and reporting. Additionally, PerfMon enables users to set system alerts, run predefined applications based on counter values, and create log files for long-term archiving.

When using PerfMon, it is important to consider the performance overhead on the monitored system. Monitoring a computer running PerfMon can slightly affect its performance. To mitigate this, it is recommended to log the PerfMon data to another disk or run PerfMon from a remote computer. Users should also monitor only the counters of interest to avoid adding unnecessary resource usage overhead.

To set up PerfMon for SQL Server tuning, users can follow these steps:

• Access Performance Monitor by going to Start > Control Panel > Administrative Tools > Performance Monitor or by running PerfMon.exe.
• Add relevant counters by right-clicking on the plus button and selecting from options such as Memory, Physical Disk, and SQL Server-specific counters.
• Customise the duration and frequency of data collection by navigating to the graph's properties and adjusting the sampling settings.
• Establish baseline values by capturing data when the system is running optimally.
• Monitor specific SQL Server counters, such as Buffer Manager and SQL Statistics, to identify potential performance issues.

SQL Server Activity Monitor

The Activity Monitor consists of several panes: Overview, Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. The Overview pane contains graphs for the most important SQL Server instance information, including % Processor Time, Waiting Tasks, Database I/O, and Batch Requests/sec. The Processes pane shows the currently running processes on the SQL database, who runs them, and from which application. The Resource Waits pane shows information about waits for resources, including Wait Category, Wait Time, Average Waiter Count, and Cumulative Wait Time. The Data File I/O pane displays information about the database files on the SQL Server instance, including their paths and names. Finally, the Recent Expensive Queries pane shows the queries recently executed that use the most memory, disk, and network resources.

To start the Activity Monitor, right-click the SQL Server instance in Object Explorer and select Activity Monitor. It is also possible to set Activity Monitor to be opened when SQL Server Management Studio is started. However, Activity Monitor cannot provide data for deep investigation, store historical records, or monitor additional parameters.

SQL Server Data Collector

The Data Collector is a core component of the data collection platform for SQL Server and the tools that are provided by SQL Server. It provides one central point for data collection across database servers and applications. This collection point can obtain data from various sources and isn't limited to performance data, unlike SQL Trace.

The Data Collector enables you to adjust the scope of data collection to suit your test and production environments. It also uses a data warehouse, a relational database that enables you to manage the data that you collect by setting different retention periods.

Data Collector can gather performance information from multiple SQL Server instances and store it in a single repository. It has three built-in data collecting specifications (data collectors) designed to collect the most important performance metrics. The information collected by default is about disk usage, query statistics, and server activity. You can also create additional data collectors and monitor more metrics.

To open Data Collection reports, in SQL Server Management Studio Object Explorer, expand Management, right-click Data Collection, and select Reports. Select any of the available reports: Server Activity History, Query Statistics History, or Disk Usage Summary.

Dynamic Management Views (DMVs)

DMVs were introduced in SQL Server 2005 and have been expanded upon in subsequent releases. They come in two forms: Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). DMVs act like regular views, allowing users to select data from them, while DMFs require values to be passed to the function. DMVs cover a wide range of categories, including database mirroring, execution, security, and more.

Some commonly used DMVs for performance monitoring include:

• Sys.dm_exec_sessions: Returns information about sessions, such as the program that initiated the session, session status, and time counters.
• Sys.dm_exec_requests: Provides details on user and system requests being executed, including blocked requests.
• Sys.dm_exec_query_stats: Offers insights into query plans, including processor time used and other performance analysis metrics.
• Sys.dm_os_performance_counters: Delivers performance counter values related to SQL Server, aiding in understanding current performance.

DMVs are a valuable tool for DBAs, offering a detailed view of SQL Server performance and facilitating issue resolution. They are an essential component of the SQL Server toolkit, providing insights that were previously difficult or impossible to obtain.

SQL Server Extended Events

To create an Extended Events session, users need to specify the events they are interested in and how they want the system to report the data to them. They can use SQL Server Management Studio (SSMS) or Transact-SQL to create, modify, and manage event sessions. SSMS provides a fully functional user interface (UI) for Extended Events, allowing users to accomplish many scenarios without having to use T-SQL or dynamic management views (DMVs).

When creating an Extended Events session, users can specify the events they want to monitor, such as the sql_statement_completed event, which captures all queries executed against the database. They can also configure event fields, which provide information about specific parts of the events, such as the client application name. Additionally, users can filter event fields to track specific events, such as filtering by database name or duration.

On the Data Storage tab, users specify where the Extended Events will store the collected data, such as writing it to an event file or storing it in Azure Storage. The Advanced tab allows users to set options like data loss tolerance and maximum dispatch latency, which can impact system performance.

Overall, SQL Server Extended Events is a powerful tool for monitoring and troubleshooting SQL Server performance, providing detailed insights into the database engine's internal operations.

Frequently asked questions