Monthly Archives: February 2011

SQL Server Profiler

Profiler is a powerful GUI to help DBAs conduct traces of SQL traffic. Its a diagnostic tool mainly used to hunt down queries that taking too long and affecting system wide performance. Running the GUI comes with a minor performance impact, however the impact is exponential when the server is already under stress.

When you open the GUI first you will start a new trace by connecting to the instance you want to monitor. Of the predefined templates “tuning” is very valuable. You can take the tuning results of a trace and feed it into the Database Engine Tuning Advisor. This will suggest indexes and statistics based on the traffic you captured. Be sure to capture traffic representivtive of your users full workflow. Creating indexes can take up a lot of space and slow down all write activity.

The first option you have is where to save the output of the trace. File would create a .trc file somewhere on your filesystem that you can double click to open and view later. Table would save to a database of your choice. Table has the great advantages of being able to query and compile results of multiple traces. Just know that its actually writing data on your database server when you select this option and that comes with all the expected performance hits.

If you want to see what suggestions the DETA has to offer just pick the tuning template and fire away. If you start a trace you should be able to see it in the results of this query

select * from sys.traces

The first result is the system trace that always runs and any additional lines will be user traces.

If you need to get a closer look at what is running on your server you’ll have to pick certain events to watch. BatchCompleted and StatementCompleted are events that you will probably want to select. Also, in the columns, you’ll want to capture the effect of these events such as CPU/Duration/Reads etc… On this screen, you can also filter based on database so you don’t have to see all the system database activity.

Even after you choose these events you may not ever get quite what you expect out of the trace. When tracing production, queries are often represented how the sql driver requests it. Unless you application runs only adhoc queries you won’t get a simple

SELECT CustID FROM customers where un = 'test' and pw = 'hack'

What you will probably find is “test” and “hack” getting passed in as parameters in the profiler then you will have to locate the corresponding statement in the cache to actually reconstruct this query.

select sql from sys.syscacheobjects where dbid = '5'
Leave a comment

Posted by on February 20, 2011 in SQL Admin, SQL Dev


SQL Server High CPU

One of the first things to do is gather baseline metrics so you can actually tell if your CPU is really “high”. As a rule of thumb, anything over 80% for 10 seconds is high. There is a short list of items that are usually ok to cause high CPU and a long list that is not so good. Fortunately this is something that can usually be fixed without an outage. If you found out about your high CPU from your users it might be time to invest in some monitoring software.

If you can connect to the instance the first thing you’ll probably want to do is sp_who. This is a very light duty way of looking at the current connections to the instance.

Next, remotely take a look at the server’s event log and the sql logs. They are another, minimally invasive, way to collect hints to your problem quickly.

If you don’t have any stand-out items to investigate, you might consider logging into the server to verify that the sqlserver process is the culprit. This will rule out things like virus scan or backup software running a muk. At this point, check all of your drives free space %. A full log file can cause high CPU.

If you have determined that the sqlserver process is the problem, your job gets a bit difficult. If sp_who, sp_who2, the activity monitor or my favorite sp_whoisactive don’t show you anything out of the ordinary, you are in this for the long haul. Based on the server, now might be a good time to ask for the “reboot” option from your users. Its worth a shot and at least it lets your users know you are willing to admit there is a problem and try something.

Most of the databases I have worked with I had no control over the design. It was a third party application that had a support line that someone would call if the reboot option didn’t work. Often times support would ask for a trace. SQL Server Profiler is under performance tools and can be run remotely on your sql instance. This data is vital debugging data that I do not claim to be an expert in. To get started check out this article

Some of the more difficult items to locate are mentioned at the bottom of the article:
“High CPU problems may indicate large numbers of stored procedure recompilations, ad-hoc query compilations, or intensive use of hash and merge joins.”

Leave a comment

Posted by on February 8, 2011 in SQL Admin