\nWhile I recalled the conversation, I needed to double check who I had actually discussed it with. <\/span>Admittedly, it’s a short list. <\/span>After checking with him over email, I realized that conversation had been with MVP, Microsoft RD, MCM <\/font><\/font>Greg Low<\/u><\/font><\/a>. <\/span>He also mentioned that <\/font>Rob Farley<\/u><\/font><\/a><\/font> once demonstrated the effect of creating a unique non-clustered index on a column which impacts the query plan but doesn’t actually register that use in sys.dm_db_index_usage_stats.<\/font><\/font>\n<\/p>\n \nWhile the conversation stuck with me, I had never taken time to test it out. <\/span>In this post, I’ll be testing out a different scenario, but essentially a similar situation where index column stats are used for a query (to provide more accurate estimates) – but its use results in no update to sys.dm_db_index_usage_stats.<\/font> <\/font><\/span><\/font>\n<\/p>\n \nBefore getting to the demo, I do want to say that I still think sys.dm_db_index_usage_stats is incredibly useful for evaluating usage patterns and helping to identify indexes that aren’t pulling their weight (high cost – low benefit).<\/em> <\/span>I also see the following scenario as an edge case – but something to most certainly be mindful of as one reason why you could all-of-the-sudden see cardinality estimate issues after dropping an index that wasn’t showing as being used at all.<\/font><\/font>\n<\/p>\n \nIn this demo, I’m using SQL Server 2008 R2 (10.50.1617) and the AdventureWorksDW database. <\/span>I’ll start off by disabling auto-creation of statistics (you’ll see why shortly). <\/span>While I see auto-creation enabled more often than not, I have seen cases where it has mindfully been disabled and cases where it was disabled for no good reason at all – a topic for another day:<\/font><\/font>\n<\/p>\n \n— Disable creation of statistics<\/font><\/font><\/span>\n<\/p>\n \nUSE<\/font><\/font><\/span> [master]<\/font><\/span><\/font>\n<\/p>\n \nGO<\/font><\/font><\/span>\n<\/p>\n \n \n<\/p>\n \nALTER<\/font><\/font><\/span> DATABASE<\/font><\/span> [AdventureWorksDW] SET<\/font><\/span> AUTO_CREATE_STATISTICS<\/font><\/span> OFF<\/font><\/span> WITH<\/font><\/span> NO_WAIT<\/font><\/span><\/font>\n<\/p>\n \nGO<\/font><\/font><\/span>\n<\/p>\n \nNext I’ll execute the following query against the dbo.FactInternetSales table – and I’ve enabled the “Include Actual Execution Plan” in SSMS so I can see the actual plan:<\/font><\/font>\n<\/p>\n \nUSE<\/font><\/font><\/span> [AdventureWorksDW]<\/font><\/span><\/font>\n<\/p>\n \nGO<\/font><\/font><\/span>\n<\/p>\n \n \n<\/p>\n \n<\/font><\/font><\/span>\n<\/p>\n \n— Estimated rows (no stats on TaxAmt) = 3,853<\/font><\/font><\/span>\n<\/p>\n \n— Actual rows (562)<\/font><\/font><\/span>\n<\/p>\n \nSELECT<\/font><\/font><\/span> RevisionNumber<\/font><\/span><\/font>\n<\/p>\n \nFROM<\/font><\/font><\/span> dbo.<\/font><\/span>FactInternetSales<\/font><\/span><\/font>\n<\/p>\n \nWHERE<\/font><\/font><\/span> TaxAmt =<\/font><\/span> 5.08<\/font><\/span><\/span><\/font>\n<\/p>\n \n<\/font><\/font>\n<\/p>\n \nWe can check the estimated versus actual rows in SSMS, but I’ll actually show the results in SQL Sentry Plan Explorer<\/a> because I like the tabular format (and I don’t have to “hover” to see it). <\/span>The following screen shot is from the Plan Tree tab:<\/font><\/font>\n<\/p>\n
![\"clip_image001\"](\"\/blogs\/joe\/wp-content\/uploads\/Windows-Live-Writer\/Wha.dm_db_index_usage_stats-may-not-tell\/5297060A\/clip_image001_thumb.png\" "\\"clip_image001\\"")
<\/a><\/span>\n<\/p>\n![\"clip_image002\"](\"\/blogs\/joe\/wp-content\/uploads\/Windows-Live-Writer\/Wha.dm_db_index_usage_stats-may-not-tell\/716D79E8\/clip_image002_thumb.png\" "\\"clip_image002\\"")
<\/a><\/span>\n<\/p>\n