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)
AS
SELECT Column1, Column2, Column3, Column 4
FROM TableA
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.