Capturing session_id scheduler assignment using SQLCMD Mode

Yesterday I was working on a SSMS SQLCMD-mode script and I noticed that periodically my session_id changed across test executions.  I was tracking wait statistics based on specific session IDs, so I had to periodically add new session ids to my extended events session definition to make sure I captured my session’s wait stats accordingly. […]

Should you ignore DBMIRROR_DBM_MUTEX?

The DBMIRROR_DBM_MUTEX wait type is undocumented. Or rather, it is documented in books online as being “Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.” Paul Randal had some additional information on this wait type in his “Wait statistics, or please tell me where it hurts” post, defining the wait type as […]

Temporary Statistics for Database Snapshots in SQL Server 2012

You may have already heard that SQL Server 2012 adds automatic generation of statistics for AlwaysOn availability group readable secondaries, storing the statistics in tempdb.  This functionality was also extended to other areas beyond just AlwaysOn availability groups as I’ll demonstrate next… Using the AdventureWorksDWDenali database, I’ll start by checking the current statistics on the […]

Comparing Query Performance when using an “ideal” Nonclustered Index versus Columnstore Index

I gave a presentation on columnstore indexing last week and one question I received was regarding the performance of a (hypothetical) narrow, supporting nonclustered index versus a columnstore index.  We discussed how nonclustered indexes were still going to be more effective for specific types of queries (for example – singleton operations, smaller range scans, etc.).  […]