SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Small changes, big effects

We got a call last week about an application that was running slowly. The server was showing about 80% CPU utilization consistently, IO was through the roof etc. It was taking something like 15-20 seconds to log in (doesn’t seem like much unless you are used to 4 or 5). We started our analysis and using Diagnostic Manager by Idera we came up with a query that was being run the most frequently and had a large total run time. Individual run times fluctuated from around 30 milliseconds to 50 seconds.

Here is an example of what the query looked like:

CREATE PROCEDURE usp_MyProc (@SearchId int)
SELECT Column1, Column2, Column3, Column 4
WHERE SearchId = @SearchId

Not a whole lot to it is there? One saving grace when I started trying to tune this stored procedure was the full sized test database. Not something I see very often, but boy is it nice when you find it. I tried running the query with SET STATISTICS TIME and IO on. (If you haven’t tried them for query tuning you should.) The query took 40ms and did 224 logical reads. This isn’t really didn’t seem like very much. At this point I’m wondering what I can possibly do with this query. Well, it turns out that there was no index on the SearchId column. I should probably mention at this point that the table only had 80k rows, and each row is fairly small. I added the index to see what would happen. When I ran the query it now took 0ms and 4 logical reads. Well, some improvement is better than none right?

After some testing we added the index in production. Since that point we have seen 40% CPU usage and more normal IO usage. One simple index on a medium-small table and we cut our CPU usage in half and our IO usage dramatically. Login times are also back to normal.

This stored procedure wasn’t exactly slow, or resource intensive, however the cumulative effect of it running hundreds of times+ a minute caused a serious effect on the server. By adding one index to tune one stored procedure we caused an enormous difference to the application and users.

Now please understand all this did was to give us some breathing room. We are continuing to monitor, and we continue checking for other tuning opportunities and we probably will be over the next few months at least. Still, isn’t it amazing how sometimes a very small change can have such a huge effect.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...