It happens often that people know their SQL Server is performing very badly but they don’t know how to find out the root cause nor how to resolve the problems. The answer is very simple – monitor the server performance and collect information to identify the performance bottleneck. Up to 88% of all application performance issues is related to the database. Database performance monitoring tools help us to stop guessing and quickly identify the root cause of the problem.
The general idea is to collect some key metrics about your SQL Server installation and to define an initial baseline of these metrics. After establishing the baseline you can start to capture data and identify the problems. Very important rule when resolving performance issues is to make only one change at a time – after applying a change you should measure your key performance metrics again and compare them to the established baseline to check if the change was a good or a bad one.
Hundreds of SQL Server metrics exist, you have to decide which ones are important for your database. The most commonly monitored performance metrics are processor usage, memory usage, disk activity and network traffic. Based on their values, more specific metrics could be monitored in order to find the root cause of the problem. It’s also important to monitor some key parameters for the specific database such as processor time, page reads and writes per second, page life expectancy, buffer cache hit ratio, processor utilization, network usage, processor queue length, etc.
A lot of SQL Server performance monitoring tools are available but most of them only display the monitored metrics. Only a very few of them offer analysis and solutions for the detected problems. SQL Server management studio provides a combination of tools for database tuning – Server profiler and Database engine tuning advisor. Using them you can capture and optimize SQL queries:
SQL Server Profiler and Database engine tuning advisor
A few steps are needed to monitor and optimize your SQL queries:
- – Start SQL Server profiler from SQL Server Management studio Tools menu – It captures all the SQL queries for the selected SQL Server instance. You can filter queries by database by using the column options. Run the profiler and wait for a while to collect information. This is how it will look like after executing some SQL Queries:
- – From the File -> Save as … menu, save the file as a Trace log file. Now we have a list of executed SQL Queries which we want to analyze and optimize.
- – For the optimization process we will use Database engine tuning advisor which can be started from the SQL Server management studio Tools menu.
- – Start the tuning advisor, connect to the SQL Server instance and a new session will be automatically started. You can give the analyzing session an appropriate name in the first textbox, then select a source with queries to analyze, in our case this is a trace file. From the dropdown below you have to select a database for workload analysis and tuning. The same databases should be selected from the dropdown and the select list box.
- – Start Analysis. As a result you will receive a list of recommendations on how to optimize your SQL Queries with easy to copy-paste code definitions.
There are also third-part tools for database analysis which offer much more complex analysis and monitoring of your SQL Server database. Such a tool is Solarwinds Database Performance Analyzer. This product can help you with:
- – Pinpointing poorly performing code in a few clicks
- – Valuable recommendations to correct problematic code
- – Daily Advisors outlining the top five errors, warnings and informational messages
- – Real time and historical data to correct issues and determine trends
- – Intuitive browser based interface
- – Reporting
- – Low impact
The home screen provides a section with dashboards for wait time and trends, environment wide metrics and database instances list.
From home screen we can easily see where we have problems and easily navigate to problem details by clicking on error or warning icons. We can access instance level data by selecting and instance from the list. The instance level data is really where most SQL Server Professionals will spend most of their time analyzing, troubleshooting and correcting performance issues. At this level, there are three key data sets:
- – Trends – Overall status for the last 30 days by default
- – Current – Real time data over the last 60 minutes
- – Resources – Charts with key metrics over the last hour, day, week, month, 6 months or year
When you launch the Trend interface you will easily notice the bar chart for the T-SQL Statements – the tallest bars require most attention. As you hover your mouse the name of the code, wait time, total wait time, percentage of wait time, average executions in seconds, executions and actual code are displayed as shown below.
At the bottom of the screen there are also three tabs to check out the advisors (i.e. recommendations) for the day, the resource utilization and a prioritized list of T-SQL code to review. The combination of the visualization and the three tabs at the bottom of the interface help you prioritize the performance issues.
From Resources tab you can monitor and analyze a lot of useful metrics about CPU, Memory, Network, etc. at any moment. There are also warning and error icons with more details to problems which need your attention.
Database performance monitoring tools are useful to determine whether you can improve performance, to evaluate user activity or troubleshoot any problems related to database.