A first look at the query_optimizer_estimate_cardinality XE event

This weekend I set up a Windows Azure virtual machine running SQL Server 2014 CTP2 (12.0.1524).  I had a local copy of SQL Server 2014 CTP2 in VMware as well, but thought it would be more fun to explore it on Windows Azure (kill two explorations with one stone).  On a side note, I’m really […]

New Article on SQLPerformance.com “Exploring Partition-Level Online Index Operations in SQL Server 2014 CTP1”

My 9th guest blog post was published today on SQLPerformance.com: Exploring Partition-Level Online Index Operations in SQL Server 2014 CTP1 In this post I explore the online, single-partition rebuild improvement being introduced in SQL Server 2014 CTP1.

Data Page Count Influence on the Query Execution Plan

In my post Row and Page Count Influence on Estimated CPU and I/O Cost I demonstrated how I/O cost of a clustered index scan had sensitivity to page counts but not row counts.  For this post I’ll lay out a direct connection between data page counts and the query execution plan that gets generated. To […]

Validating Instance-Level Index View and MERGE Optimization Activity

You may have read the blog posts a few weeks ago regarding indexed views and MERGE statement usage and incorrect results. If not, please see the following blog posts: If you are using indexed views and MERGE, please read this! (blog | @AaronBertrand) NOEXPAND query hint returns wrong results – CU fix now available (blog | @jamiet) […]

Columnstore Segment Population Skew

My last post about nonclustered Columnstore indexes was written back on February 25th, 2012 (Exploring Columnstore Index Metadata, Segment Distribution and Elimination Behaviors).  Amazed by how quickly time passes. Anyhow, this is a quick post on segment population skew based on parallel nonclustered Columnstore index creations. I’ll use the same 123,695,104 row FactInternetSales table I […]

Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

Last November I blogged about how index usage stats don’t get updated when the associated index statistics (but not index) are used. This post will describe another scenario that you should be aware of (the topic came up today in class while Kimberly was teaching – as we were trying to recall tricks to clearing […]

Exceptions – what sys.dm_db_index_usage_stats doesn’t tell you

I was thinking back to a conversation I had some time ago regarding cases when sys.dm_db_index_usage_stats does not get updated after an index is used for just the statistics associated with the indexed columns.  What makes this profession so interesting is that almost every tool we use has a "yes, but…" associated with it.  What […]

Clearing “missing index” suggestions for a single table

Today there isn’t a fine-tuned way to clear the accumulated statistics from the “missing index” set of DMVs.  Certainly restarting SQL Server will do this, but you don’t have something like DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR) to reset statistics for dm_db_missing_index_columns, dm_db_missing_index_groups, dm_db_missing_index_group_stats and dm_db_missing_index_details. While we don’t have this direct option, there is an interesting […]