Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to understand multi column indexes and statistics, here is my real world example:

Table: TaskExecutions, size=~1 million rows.
Query SARGS: TaskExecStatusID = 3 AND TaskExecUpdatedDate IS NULL

Distribution:: TaskExecStatusID: 1=160k rows, 2=370k rows, 3=560k rows.  
TaskExecUpdatedDate: Null=171k rows.

I have an index on (TaskExecStatusID,TaskExecUpdatedDate),

My problem/question is that the estimated number of rows for the above query is 256, the actual number of rows is 560K.

I want to understand why there is such a big difference between these two numbers?

Stats are up to date, the stats for the index are multi-column stats. I've tried creating in index/stats on (TaskExecUpdatedDate, TaskExecStatusID), and the estimate/actual does not change.

If I run a DBCC SHOW_Statistics, the density section has both columns in it, the histogram does not. Does SQL server produce a histogram for the combination of columns in multi-column statistics?

dave

share|improve this question
    
Can you show the statistics output? –  JNK Nov 17 '11 at 20:16
3  
And the query too please –  gbn Nov 17 '11 at 20:25
1  
And one more for luck, the execution plan please. –  Mark Storey-Smith Nov 17 '11 at 23:59
    
You could also try to follow the steps mentioned in a specific Paul White's blog post to find out which statistics your query is using. –  Gonsalu Nov 18 '11 at 10:48
    
SQL Server only histograms the first column in multi-column statistics. –  Simon Righarts Dec 1 '11 at 3:11

1 Answer 1

You've actually got a few questions in here, so I'll break 'em out individually.

My problem/question is that the estimated number of rows for the above query is 256, the actual number of rows is 560K. I want to understand why there is such a big difference between these two numbers?

In order to answer that question, the first thing we would need is the actual execution plan for the query. In SQL Server Management Studio, you can get that by clicking Query, Include Actual Execution Plan. Run the query, click on the Execution Plan tab, and right-click anywhere in the whitespace to click Save Plan As. Save that, and post it somewhere for people to download and examine.

The next thing we would need is the output from DBCC SHOW_STATISTICS for the stats on that table. You've hinted at the output, and that's a good start, but the raw output will help us understand exactly what's going on.

If I run a DBCC SHOW_Statistics, the density section has both columns in it, the histogram does not. Does SQL server produce a histogram for the combination of columns in multi-column statistics?

No.

I have an index on (TaskExecStatusID,TaskExecUpdatedDate),

If you frequently use the query in the example (with TaskExecUpdateDate IS NULL), then you might check out filtered indexes. They're a new feature in SQL Server 2008 that allows you to put a where clause on your index, basically.

http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.