Tag Archives: query optimization

T-SQL Tuesday #25 – Tips and Tricks

Posted on December 13, 2011 | 2 Comments

T-SQL Tuesday logoIt is T-SQL Tuesday once again, hosted by Allen White (b / t). The theme for this T-SQL Tuesday is Tricks.

One of my favorite tricks is to use INTERSECT when I have to rewrite a SELECT query, to make sure I am returning the same data.

Let’s say I have a slow query that returns 1640 rows:

I refactor it for speed, and make sure it still returns 1640 rows:

How am I sure my new query returns the same data? I use INTERSECT, and make sure the intersect-ed query returns the same number of rows as the original query:

Success is concrete. Just like the Mafia.

Now I know my changes have not affected what data is returned. Success!

I also have a more concrete example, using the AdventureWorks2008 R2 database, and Example.sql .

→ 2 Comments

Posted in Performance Tuning, SQL Development, T-SQL Tuesdays

Tagged , , ,

Uber-Update-Stats: keep stats up-to-date on large tables

Posted on January 3, 2011 | Leave a comment

Auto_Update_stats: Not helpful on large tables

Experienced DBAs know that auto-update-stats is not sufficient to keep statistics updated on large tables. This is doubly true for tables where the DML activity is near one end of the clustered key.

There are some common workarounds:

Angry Face

Stale stats, you have failed me for the last time!

  • Have all DML operations run an update statistics command after they’re done. However, this can require a lot of code changes.
  • Update all statistics on a schedule, using sp_updatestats. However, this can lead to bad blocking, and often can’t be run often enough.
  • Swearing at your systems. However,  too much will get you put in management.

Not content with any of these, I’ve gone ahead and built my own script:

Uber-Update-Stats

Script: Uber-Update-Stats.sql
Unit Test: Uber-Update-Stats-Test.sql

Now I can run this script on a near-constant schedule, and it will update each table as soon as a configurable percentage of rows or number of rows have changed.  You can also configure the sampling percentages. I think it is The New Hotness very useful.

Coming up next: A version that compensates for filtered indexes & filtered statistics.

→ Leave a comment

Posted in Performance Tuning, Statistics

Tagged , , ,