Should Query Tuning be Proactive or Reactive

performanceperformance-testingquery

As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server). I make the best possible design prior to and during development.

But, just like any other software developer, there is added functionality, bugs, and just change of requirements that demands altered/created database objects.

My question is, should query tuning be proactive or reactive? In other words, a few weeks after some heavy code/database modification, should I just set aside a day to check out query performance and tune based off of that? Even if it seems to be running okay?

Or should I just be aware that less-than-average performance should be a database check and going back to the proverbial chalkboard?

Query tuning can take up a lot of time, and depending on the initial database design it could be minimal benefit. I'm curious as to the accepted modus operandi.

Best Answer

Both, but mostly proactive

It's important to test during development against realistic volumes and quality of data. It's unbelievably common to have a query running on a developers 100 or 1000 rows then fall flat with 10 million production rows.

It allows you to make notes too about "index may help here". Or "revisit me". Or "will fix with new feature xxx in next DB version".

However, a few queries won't stand the test of time. Data distribution changes or it goes exponential because the optimiser decides to use a different join type. In this case, you can only react.

Saying that, for SQL Server at least, the various "missing index" and "longest query" DMV queries can indicate problem areas before the phone call

Edit: to clarify...

Proactive doesn't mean tune every query now. It means tune what you need to (frequently run) to a reasonable response time. Mostly ignore the weekly 3am Sunday report queries.

Related Solutions

Sql-server – Query Performance Tuning

For a quick assessment, get the execution plan out of SSMS and in to Plan Explorer.

Review the most expensive operations for anything unexpected. Sorts, worktables, inappropriate join operators (e.g. nested loop where you expect a merge or hash).
Look at the rowcounts at each stage of the plan, are they broadly within the range you expected to see?
Look at the estimated vs actual rows. If you're actuals are close to the estimates, it's more likely you have a good plan. If there are big variations, find out why (missing and/or out of date statistics for instance).
Evaluate the potential for parameter sniffing issues. Look for areas where cardinality may vary and test against a range of input parameters.

Lots of freely available reference material out there, Grant Fitchley's SQL Server Execution Plans is a good start. I also found Joe Chang's blog posts and ebook on execution plan costs very useful.

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate.

When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly distributed according to date.)

See my answer here for a similar issue

The ideal index for this query would be one on SomeIndexedValue, CreatedDate. Assuming that you can't add that or at least make your existing index on SomeIndexedValue cover CreatedDate as an included column then you could try rewriting the query as follows

SELECT MIN(DATEADD(DAY, 0, CreatedDate)) AS CreatedDate
FROM MyTable
WHERE SomeIndexedValue = 1

to prevent it from using that particular plan.

Best Answer

Related Solutions

Sql-server – Query Performance Tuning

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

Related Question