No Perf for You! Read-only Databases stops Auto Create Stats

13 January 2011

Read-only databases cannot create new statistics

A while back, I was working with Kendra Little (@KendraLittle, blog), and we were using log shipping to make warm standby copies of reporting databases. The warm standby databases were marked as READ_ONLY. However, this meant the query optimization process couldn’t create new statistics (MSDN) when users ran ad-hoc SELECT statements.

Query Plans With Missing Statistics

Using a one-table unit test, we are able to see that a read-only database cannot create stats. The estimating query plan shows a warning symbol, symbolizing that the query optimizer cannot create statistics on necessary columns.

Click on the image to see the warning in detail and actual query plan

Query Plans With Manually-Created Statistics

Let’s suppose that there are stats on every single column of every single table. Then, the query optimizer can do a better job of producing good plans. Using the query in our one-table unit test now produces a plan without warnings.

Click on the image to see the actual query plan

MANUALLY CREATED STATISTICS SCRIPT AND TEST

My solution is to manually create statistics on every column where they don’t already exist. Below is an example script and a unit test for it.

Script: Manually-Create-Stats.sql Unit Test:  Manually-Create-Stats-Test.sql

Performance results

The performance results are pretty clear. Using statistics in a simple, one-table unit test creates a slight performance boost. I have seen gains of 10-30% with many reporting queries; the effect of missing statistics as you join more tables together, use larger tables, and use columns for filtering.

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!