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

Today I had an interesting question about the CTRL+C handler for the SQLIOSim console execution.    After some investigation I found that a design change request is in order as the CTRL+C handler has a few holes-  (I am working on that part). The CTRL+C handler does not prompt you...

I just completed a lengthy bit of research and thought I would share it with everyone.   There are several trace flags and registry keys outlined in this post.    !!! As always USE WITH APPROPRIATE CAUTION !!!   From: Robert Dorr Sent: Tuesday, November 17, 2009 3:23 PM...

Previously I have posted details on backup/restore designs and how to tell what backup/restore is doing. http://blogs.msdn.com/psssql/archive/2009/03/03/how-it-works-sql-server-vdi-vss-backup-resources.aspx http://blogs.msdn.com/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange...

The ring buffer records (which can be sent to XEvent) for Resource Monitor and Memory Broker are the key aspects to understanding RM.  The record is produced when a change is detected in state monitored by RM.    CREATE EVENT SESSION RingBufferInfo ON SERVER     ADD EVENT...

I recently received a request to shed some light on a few advanced questions about latches and SQL Server. These were good questions so I thought the information might be useful to share with the community. I’ll provide this information in the form of Q&A as I was asked (I’ve paraphrased the questions...