T-SQL Tuesday #14: SQL Azure

11 January 2011

It is T-SQL Tuesday again, brought to you by Jen McCown (@MidnightDBA). The theme for this T-SQL Tuesday is Resolutions. Mine is, “This year I will learn and use SQL Azure“.

Why SQL Azure?

The professional answer? Companies are always looking to do more with less. Also, the spread of the Internet to all areas of life means that businesses are having to deal with the brave new world of disintermediation, scale challenges, and thinner margins.

The personal answer? It’s a fun technology to experiment with. The concepts of sharding and queue-based architectures are, well, cool.

SQL Azure means more databases per DBA. Smaller outfits may not have DBAs at all. Unfortunately, that’s what the cloud does to our profession; it automates some of our work away. So take a deep breath, sigh, and move on.

More practically, work for talented computer professionals isn’t likely to decrease. Migration work to SQL Azure-based architecture takes a lot of time, even with the migration tools and tips that experts have come up with. Some industries, such as health-care & banking, are not likely to move their data to a third party. Database design and architecture work should always be around.

So, time to learn! How can I make a 1TB database fit into 40GB chunks? How can I leverage the worker queues in Windows Azure to my benefit? How can I migrate half a database to SQL Azure, and still provide my application access to all data? How do I do a backup? How do I figure out the costs of moving to SQL Azure vs. the costs of the status quo?

Time to learn!


Uber Update Stats 1.1: keep stats up to date with filtered indexes

10 January 2011

Auto_Update_Stats & Filtered Indexes: they don’t play well together

Anyone who has ever put a filtered index on a table knows that auto-update-stats is inadequate at keeping the query optimizer informed about them. The reason is that to fire a stats update, ~20% of the table has to change. If you have a table with 500 million rows, and a filtered index covering 5 million of those rows, you’d still need ~100 million rows to change before stats would be automatically updated.

Uber-Update-Stats 1.1

Luckily we already have a script that will use advanced criteria to update statistics for us. I refactored the original script and test to account for filtered indexes. Now, if we want to update stats if 15% of the table changes, we will also update stats if 15% of the filtered index changes.

Looking at our original example, the big table with 500 million rows would get its stats updated if 750,000 rows inside the filtered index are changed. That’s much better.

The key is that the sys.partitions view includes the estimated number of rows in an index. If an index is filtered, it will have a lower number of rows. The ratio of rows in the index to rows in the table tells you roughly what percentage of the table the index covers.

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