Don’t assume it’s performant

When working as a contractor you inevitably inherit previous applications and databases with the remit to maintaining and upgrading them with a list of changes.

Sometimes you find something that just doesn’t look right and this week a colleague and I tackled just a problem.
When running SQL Studio Profiler on a server for another reason we noticed that a certain stored procedure that was effectively returning the information for one row was taking anywhere from 1.3 to 5 seconds to run – and this was a vital “data entity” that was constantly requested on the site.

Looking into the stored procedure we saw the SQL involved was something like:

SELECT t.A, t.B, t.C, t.D,……..t.Q,
, (SELECT COUNT(*) FROM t2 with (nolock) WHERE Field = t.ID) AS ACount
, (SELECT COUNT(*) FROM t3 with (nolock) WHERE Field = t.ID) AS OtherCount
FROM t
WHERE t.ID =@ID

The extra bit of information being queried from the other tables looked like it could be causing issues and by requesting an estimated execution plan we saw that there was a bottleneck of processing occuring on a clustered index scan of one of those tables:

Checking that secondary table for indexes we found that there was only one on the primary key – there wasn’t an index for the “Field” that was being queried in the above SQL.

We introduced a non-unique, non-clustered index on that field and did the appropriate testing.

Once the changes were committed to the live enviroment we saw a dramatic change in performance:

The change occurs halfway down the image – the CPU usage and timings have virtually disappeared and our query is now in the milliseconds.
Needlesss to say the site’s speed is now sizzling!

The moral of this post?

Don’t assume that what you inherit is performant in any way. In this example the lack of a simple index on that secondary table was introducing a slow down on the site on a large scale and as we know best practices are not always followed!

My new mantra: Profiler is my new friend on new projects.