sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

SQL Server Performance Monitoring Tools

Issue 4 Number 7 July 2012

Performance monitoring for SQL Server is big business, not only for the many 3rd party vendors of performance monitoring tools, but also for the SQL server magazines, blogs, forums, events and other SQL web sites that depend on these vendors for advertising dollars. For that reason you won’t find a lot of criticism of these tools on those sites or in those magazines.

However, I have some deep reservations about the use of these tools. I do not have an argument with the capabilities of any of these tools and I do believe it is critically important to know what is happening on your SQL Server. However, I do not think that requires continuous monitoring of every aspect of SQL Server. That simply puts too much load on your system and can cause the problems you are trying to avoid.

I am OK with using these tools when the SQL Server has plenty of resources to handle the load, but that leads to the nonsensical conclusion that only SQL instances with few or no performance problems can afford the overhead to host third-party performance monitoring suites.

An Example

A couple of times in the last few months I have solved serious performance problems for my clients simply by turning off their third-party performance monitoring software.

In both cases, the clients had a well-defined time period when their daily peak load occurred. During those periods things were unacceptably slow and queries were timing out. Both clients had seemingly adequate hardware and many things had been tried before they called me in, but nothing had worked.

Turning off the performance monitoring software fixed both of their problems immediately.

These were exceptional cases in that the problems were critical, very visible and required immediate attention. However, I am certain there are many other cases where a SQL Server is barely chugging along with marginal performance while those responsible for the performance have no idea how much of the problem can be attributed to their performance monitoring software.

Rolling Your Own

All of the information provided by third party tools is available to you directly in SQL server. Performance suites just put a face on the same dynamic management queries, performance monitors, traces, etc that are also available to you directly. For example, here is a query that will show you the top 10 currently running queries on your SQL Server:

  • SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
  • ((CASE statement_end_offset
  • WHEN -1
  • THEN DATALENGTH(text)
  • ELSE statement_end_offset
  • END - statement_start_offset)/2) + 1) AS query_text, *
  • FROM sys.dm_exec_requests
  • CROSS APPLY sys.dm_exec_sql_text(sql_handle)
  • ORDER BY total_elapsed_time DESC

Almost anything you want to find out about current or ongoing performance can be queried from one of the many database management views available to you. The value, such as it is, of third-party tools lies in the fact that you do not have to know anything about the data management views that provide the data. Another benefit is that the tools display their results in intuitive speedometers, graphs, and other very cool graphics.

Even though it is relatively easy to get the performance data yourself, it’s true that you can justify the cost of a performance suite because it is so much easier to have all that data gathered automatically and displayed in one place. However, that can be awfully expensive in terms of system performance.

if you plan to become a very good DBA, I would advise you to learn everything you can about the data management views and functions in SQL Server.

What Does It All Mean?

Gathering the data is one thing, but the larger challenge is to understand what the data means. What is important and what is not? What numbers are too high, what numbers are too low? How do you fix the problems that might be indicated by the statistics? Unless you understand that, the statistics provided by the performance monitoring suites are not very useful.

The Accidental DBA - A Case for the use of 3rd-party tools

Many SQL Server DBA’s came into their position accidentally. While they were busily pursuing their real jobs the boss called them in and said “We don’t really need to hire someone else to handle this SQL thing we bought. Why don’t you take care of it?” Of course, the boss assumed that it was pretty simple to handle so he didn’t mention anything about proper training.

I know a lot of accidental DBA’s and I have great sympathy for them. I am in favor of anything that makes their life easier.

I have noted that many of them are very attached to their monitoring tool. I think this is good (within limits). There is a lot you can learn from following the changes in your SQL Server. However, it is important that performance is not compromised during the learning curve. You should not try to monitor everything, all the time.

The Free Option for Performance Monitoring

If you feel you need a performance monitoring tool but do not have the budget to purchase one, there is a pretty good performance monitoring application built into SQL Server 2008 and later. It is called Management Data Warehouse (MDW) and we will probably write an article specifically about MDW in a future issue.

MDW requires some set up, but is no more complex than setting up a third-party program. MDW lacks the bells and whistles of the 3rd party software but comes with 60 to 100 metrics you can monitor (or not) and a set of reports that will display the data you gather. You can even create custom metrics if the default set does not contain all of the metrics you want to see.

Of course, MDW can be over-used just as easily as one of the third-party products. However, it allows you to just monitor what you think is important, and only as often as you think important.

Compared to the multi-thousand dollar price of some of the other programs, MDW is a real bargain. It costs nothing extra. It comes with your SQL Server license.

Conclusion

Performance monitoring suites can be useful in the hands of a trained DBA so long as the SQL Server has adequate resources to handle a significant increase in load caused by the tool itself. Before implementing, make sure that you need and can interpret the boatload of statistics that will be dumped on you. The information gathered is of no use unless you know how to take action based on the performance stats.

Don’t use a third party tool as a substitute for learning as much as possible about the tools in SQL Server like data management views and functions. Also remember Management Data Warehouse is a no-cost substitute for the very expensive 3rd party tools available.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

SQL Server Performance Monitoring Tools

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator