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...

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...

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...

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...

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...

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...

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...

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...

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...

In a previous blog I outlined how file stream transactions work and retain the before and after images of the file to allow various forms of recovery.  Reading that blog should be considered a prerequisite: http://blogs.msdn.com/b/psssql/archive/2008/01/15/how-it-works-file-stream-the-before-and...

I have had several questions on my blog post: http://blogs.msdn.com/b/psssql/archive/2010/08/03/how-it-works-error-18056-the-client-was-unable-to-reuse-a-session-with-spid-which-had-been-reset-for-connection-pooling.aspx related to SQL Server 2008's honoring of an query cancel (attention) during the...

The IO Affinity mask question has come across my desk several times in the last week so it is time to blog about it again. The IO Affinity mask is a very targeted optimization for SQL Server.  I have only seen 6 cases where the use of it improved performance and was warranted.  It is much more...

        SQL Server and other documentation have always indicated that float values are not precise and comparison or conversion of them can be problematic and imprecise.   Working on a recent customer case it required me to dig into the single/double precision...

I was posed a good question today about how the wait_info* event works in SQL Server 2008. The easiest way for me to answer the question was to prove the behavior. using WAITFOR DELAY it shows the behavior nicely. From: Robert Dorr Sent: Wednesday, October 20, 2010 2:07 PM Subject: RE: Extended Events...

I would love nothing more than to take you back to my high-school days running the 440 yard hurdles (yes yards not meters) where timing was done with a stop watch (you know the old, moving dial style) but timers on the PC don't allow that simplicity. I have discussed the timing behavior is SQL Server...

I ran into an issue which has some aspects I found interesting and thought you might as well. When you do a select against the sys.messages virtual table the data is retrieved from the resource files (.RLL) files stored under <<Instance>>\Binn\Resource directory. This is done by loading the...

This message has come across my desk a couple of times in the last week and when that happens I like to produce blog content. The error is when you are trying to use a pooled connection and the reset of the connection state encounters an error. Additional details are often logged in the SQL Server error...

I encountered another interesting research issue to share with you. The documentation states RecordsAffected are for INSERT, UPDATE and DELETE but it might fool you. ------------------------------------------------------------------------------------ Sent: Monday, June 07, 2010 9:06 PM Subject: "set...

A question arose asking - "What does the LPE_BATCH spinlock type represent?" You can see the LPE_* series of spinlocks using the DMV query " select * from sys . dm_os_spinlock_stats " A spinlock is a lightweight, user mode synchronization object used to protect a specific structure. The goal of the spinlock...

I am still working to resolve the customers problem but during my investigation I briefly looked a the SQLAgent logging format and thought you all might like some of these details. From: Robert Dorr Sent: Monday, May 24, 2010 9:47 AM Subject: RE: SPID in SLEEP state indefinitely The error itself is from...

It looks like it would be a good post to help clarify that -2 does NOT mean ORPHAN . _____________________________________________________________________________________ CURRENT EXCHANGE _____________________________________________________________________________________ From: Robert Dorr Sent: Tuesday...

There seems to be some semantic(s) confusion on the books online description of SOFT NUMA.    The area of confusion is from the SQL Server 2008 Books Online section, shown below. Soft-NUMA SQL Server allows you to group CPUs into nodes referred to as soft-NUMA. You usually configure soft...

I put a presentation together quite some time ago going over various SQL Server I/O behaviors and relating them to the SQL Server I/O whitepapers I authored. I keep getting requests to post the presentation and the information is relevant from SQL 7.0 to SQL Server 2008 and beyond. Here are the RAW...

I have received numerous questions about 'foreign pages' so I have put this post together to answer the questions. The SQL Server buffer pool goes through three (3) distinct states. Initial / Ramp-up This state is established during buffer pool initialization and only transitions once committed buffers...

I was recently developing a set of SQLCLR functions and procedures for an internal project.    One of the tests I added to my suite was to cancel the query (attention) and make sure I handled it properly in my .NET implementation.    What I found was much more than I expected...