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 […]
Joe Sack
SQL Server Performance Tuning, High Availability and Disaster Recovery Blog
Tracking isolation level
Posted on: October 26, 2011 7:34 am
Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch
Posted on: October 24, 2011 6:29 am
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
Posted on: October 18, 2011 8:17 am
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
Posted on: October 16, 2011 4:35 am
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
Posted on: October 12, 2011 3:48 am
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
Posted on: October 9, 2011 2:10 am
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)
Posted on: October 5, 2011 8:43 am
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)
Posted on: October 4, 2011 2:42 am
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
Posted on: October 2, 2011 1:20 am
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 […]
