The 52 Hour Query

I had an interesting experience this week with a client using Analysis Services. They have an OLAP cube which holds all the transactions at a hospital for the past 3 years. There are 35 million records to keep track of every $3200 pacemaker and $3 aspirin tablet used.

The cube reprocesses on schedule every weekend – this typically takes less than 8 hours. This week the cube had not refreshed and I had an email asking to investigate. I logged in over a VPN and saw the SELECT statement from Analysis Services was still going strong after 52 hours. Not good!

First we checked current activity to see if any SPIDs were blocked - no. Any other extraordinary activity on the servers? No. As the plot thickened, I decided to look at the estimated query plan for the OLAP SELECT statement in Query Analyzer.

The cube has 8 date dimensions, meaning 8 joins to a date dimension table that yields the fiscal quarters and calendar quarters and other date attributes analysts love to use when producing trendy charts for board meetings. The estimated query plan showed SQL Server was thinking each join would double the size of the resultset. By the time all the joins are complete SQL estimates there will be almost 9 billion rows instead of the 35 million there should be.

I’m making a wild guess that when SQL Server thinks it needs to process 9 billion rows it figures 2GB of RAM won’t cut it and goes straight to the hard drive. Even though the query wasn’t producing 9 billion records – it was certainly taking a long time and not using much CPU.

I immediately thought this had to do with indexes or index statistics. I looked at the indexes on the date dimension table. The clustered index was on the column used in the 8 joins. I realized the column was not the primary key column but did hold unique date values – one row for each day over a 20 year span. Unfortunately, nobody told SQL Server the values were unique. Just adding a unique constraint put everything back to normal- the query plan looked better and processing finished in 8 hours.

The moral of the story is: proper design can also yield better performance. The more you can tell SQL Server about the data the less it has to guess and prepare for worst case scenarios.