How It Works: DROP AVAILABILITY GROUP Behaviors

I just learned something new about the DROP AVAILABILITY GROUP command behavior that I didn’t realize.  The comment on TechNet, alludes to how DROP works but we definitely need to update our documentation. Remove an Availability Group (SQL Server) – http://technet.microsoft.com/en-us/library/ff878113(v=sql.110).aspx "When the availability group is online, deleting it from a secondary-replica causes the primary…

2

How It Works: When is the FlushCache message added to SQL Server Error Log?

FlushCache is the SQL Server routine that performs the checkpoint operation.  The following message is output to the SQL Server error log when trace flag (3504) is enabled. 2012-05-30 02:01:56.31 spid14s     FlushCache: cleaned up 216539 bufs with 154471 writes in 69071 ms (avoided 11796 new dirty bufs) for db 6:0 2012-05-30 02:01:56.31 spid14s                 average throughput: …

9

How It Works: HealthCheckTimeout Interval Activities

As I wrote my recent blog posts and did more research I found that is would be helpful to highlight the HealthCheckTimeout behavior in more detail. Always On FCI (Failover Cluster Instance) vs Non-FCI Installations Documentation The first thing that I need to point out is the subtle wording difference in Books Online and other…

1

How It Works: Failover Cluster/Availability Group XEL Logging Frequency

I had a great question from my post on sp_server_diagnostics (http://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx).  As ‘luck’ would have it, Bob Ward, was working on some training and had a similar question.  As I investigated I uncovered a few details that can help us all. Connection Scope As soon as the Failover Cluster Instance (FCI) or Availability Group is…

2

How It Works: XEvent Action vs Field data values.

I have seen several traces and questions relating to the output of the XEvent so I thought I would try to explain them a bit in an effort to reduce confusion. Terms Field == Event Data Action == Action Data – The action data is gathered from the current session/request state.  Let’s look at the…


AlwaysON – HADRON Learning Series: Automated Failover Behaviors (Denali – Logging History Information, FCI and Default Health Capture, sp_server_diagnostics)

Quite an encompassing title I will agree but the features all work together to build a really nice product.  I had this tucked away during beta and thought it would be helpful to post. SQL Server AlwaysON and "Denali" has undergone a significant upgrade to the way the cluster resource (for an Availability Group or…


Analysis Services Thread Pool Changes in SQL Server 2012

As part of the SQL Server 2012 release several changes were made to Analysis Services that should alleviate some issues previously seen on large NUMA node machines. Separation of IO and Processing Jobs To better understand how the changes that were made work, it is helpful to have some background information about what types of…

4

How It Works: sys.dm_tran_session_transactions

For some reason I have been looking at DMV output closely the last couple of weeks.    I just blogged about the pending I/O requests and now I have a behavior to outline for dm_tran_session_transactions for todays blog. select * from sys.dm_tran_session_transactions The scenario I was looking at is as follows. Server A   Server B…


How It Works: sys.dm_io_pending_io_requests

I have been working an issue where the DMV was returning io_pending_ms_ticks incorrectly.  The following output is an example of ~164 hour delay. Unlikely to occur without any other side effects noted. select * from sys.dm_io_pending_io_requests In the process of this investigation I uncovered several details that I found helpful to share. Full Text and…

3

How It Works: Return codes from SQLIOSim

I have been asked how to automate SQLIOSim on several occasions.  SQLIOSim is a utility to test SQL Server I/O integrity (not performance) patterns against a system without needing to install SQL Server on the system.  It ships with SQL Server 2008 and SQL Server 2008 R2 and is located in the BINN directory. There…

1