Tracking isolation level

When I'm troubleshooting concurrency issues – be it deadlocking or long duration blocks, I’m immediately curious about the isolation level being used.   While I may be told that the isolation level is "XYZ", I don’t believe it until I actually see it for myself. There are multiple areas where this information can be retrieved.  This post lays […]

Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch

So picture the following scenario on a SQL Server 2008 R2 instance (an amalgam of various DBA situations you've no doubt seen before)…  You get a call from the application team reporting slow performance on a specific service.  They don’t know why it is slow but they do know the session that is running too slow […]

Setting indirect checkpoints by database in SQL Server 2012

This blog post applies to SQL Server 2012 CTP3 (11.0.1440). SQL Server 2012 introduces the “indirect checkpoints” feature.  At a high level, it allows you to adjust the target recovery time of a specific database, rather than relying entirely on the SQL Server instance-level ‘recovery interval (min)’ setting. To demonstrate the impact, I’ll walk through a […]

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 […]

Row and batch execution modes and columnstore indexes

This post covers examples from Denali  SQL Server 2012 CTP3, version 11.0.1440. In my last post I hinted towards some interesting findings regarding parallelism and columnstore indexes.  I’ll talk about what I observed in this post. Just as a quick level-set, columnstore indexing provides two new areas of functionality within SQL Server 2012 that can potentially […]

The case of the columnstore index and the memory grant

This post covers examples from Denali CTP3, version 11.0.1440.   I was working with Denali’s columnstore index feature this last week and was testing it on a virtual machine when I encountered the following error message when trying to create a new index: The statement has been terminated. Msg 8657, Level 17, State 5, Line 2 […]

Under the covers with sp_server_diagnostics data (Part II)

This post covers examples from Denali CTP3, version 11.0.1440. In my last post, “Under The Covers With Sp_server_diagnostics Data (Part I)” I started an exploration of the output surfaced from the sp_server_diagnostics stored procedure.  In this post, I’d like to continue this exploration, having a look specifically at the “query_processing”, “io_subsystem” and “events” component data. […]

Under the covers with sp_server_diagnostics data (Part I)

This post covers examples from Denali CTP3, version 11.0.1440. In this post I’ll be discussing the SQL Server health related output from Denali’s sys.sp_server_diagnostics system stored procedure.  This system stored procedure is used to check Denali SQL Server health status behind-the-scenes for both failover cluster instances and availability groups.   While this procedure is used […]

New Adventures with SQLskills

  As announced last week, after five and a half years with Microsoft I’m joining SQLskills as “Employee #4″.  I start my new job Monday and I thought I would take this quiet Sunday morning to write an off-SQL-topic blog post.   Microsoft has been very good to me over the years and I’m grateful […]