Since it took me several days to track down this bug, and I did learn a couple of new things along the way, I thought I would share some of my work. 16 or More CPUS When a system presents SQL Server with 16 or more CPUs, and you are using a high end SQL Server SKU, SQL Server will enable lock partitioning...

This would be a perfect time to post a blog talking about the new SQL Server 2012 features.  However, I am going to leave that activity to the marketing folks ( AlwaysOn, T-SQL Enhancements, … ).   I want to talk about something that might not appear on the top of a marketing or sales...

Suresh brought to my attention that we have been getting questions as to why SQL Server starts on group 1 and then group 2 and it is not predictable?  Then Tejas brought up another issues and since I worked on this way back before we released SQL 2008 R2 I went back to my notes to pull up some details...

The increased number of cores per socket is driving NUMA designs and in SQL Server support we are seeing more NUMA machines and less pure SMP machines.    For whatever reason over the past 2 weeks I have fielded a bunch of questions around NUMA and the information is good for everyone...

Applies To:   SQL 2008, 2008 R2 and Denail builds The SQL Server developer can elect to partition memory allocations at different levels based on the what the memory is used for.   The developer may choose a global, CPU, Node, or even worker partitioning scheme.   Several of...

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

In 2002 the SQLOS team added specific checks for non-yielding scheduler issues. You may be familiar with the 178** series of errors like 17883 scheduler non-yield. Since 2002 the test matrix for SQL Server has flagged these errors and corrected them. With the evolution of SQL Server 2005, 2008 and now...

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’ve run into two cases which fell down the same path and figured we should have some information out there regarding it.  The issue was that the customer’s instance of Reporting Services would just spike to 100% CPU usage.  During this time the end users did not notice any interruption in...

In the previous posts we have discussed the working set trim message “ A significant part of sql server process memory has been paged out ” that gets written to the SQL Server Error log. You can find the details about the various parameters that determine when this message is written to the error log...

SQLDumper utility is used by various components of the product to generate and save diagnostic information in the form of mini-dump and other log files. You will normally find the output files [with extension .mdmp, .txt and .log] created by sqldumper.exe in the LOG folder of the specific instance for...

SQL Server 2005 introduced a background worker to flush trace event streams.   The trace buffer is flushed when it becomes fully populated but a partially populated trace buffer remains in-memory until events fill the buffer or the background worker flushes the events to the stream.  Periodically...

The SQLIOSim knowledge base article outlines the various configuration files in detail.  http://support.microsoft.com/kb/231619 - How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem. I still get inquires about what should I run by default.   My answer is...

Many of you have encountered the RDTSC timing variances that I outlined in an earlier blog post:   http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx and http://blogs.msdn.com/psssql/archive/2007...

I was asked a question as the CSS First Aid Station at SQL PASS 2008 where the Activity Monitor kept showing the SQLCLR wait category as the top waiter, even when no other activity was going on.  After some digging I found that this is a bug and currently marked to be fixed SQL Server 2008 SP1. ...

From: Robert Dorr [mailto:rdorr@microsoft.com] Sent: Thursday, November 20, 2008 11:29 AM To: A SQL PASS MEMBER - CSS First Aid Station Question About SQLIOSim As we discussed at SQL PASS – SQLIOSim is an independent utility and has been used as part of the Windows Hardware Compatibility Testing...

The following is from a lengthy conversation I had with Kevin Kline.  I believe he will be posting more details along with his "Understanding SQLIOSim Output - http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx )   ********** Final Summary for file...

Error 17888: All schedulers on Node ## appear deadlocked due to a large number of worker threads waiting on <<RESOURCE>>. Process Utilization ##%. SQL Server creates a Scheduler Monitor thread for each node.    One of the Scheduler Monitor tasks is to watch the schedulers assigned...

I have addressed similar conditions error in a white paper: www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx 2008-01-20 19:01:26.11 Server      ***Stack Dump being sent to K:\MSSQL.3\MSSQL\LOG\SQLDump0001.txt 2008-01-20 19:01:26.11 Server     ...

Recently I have fielded several questions related to I/O affinity.  Allow me to outline the behavior and clear up misconceptions. When I/O affinity is enabled SQL Server creates a hidden scheduler for the Log Writer process as well as a special lazy writer thread for each of the bits set in the...

SQLIOSim, like its predecessor SQLIOStress, is designed to read pages it has written and validate the data.   SQLIOSim does this using a checksum algorithm. When SQLIOSim starts up it creates a set of buffers and used the Cryto APIs to generate random data on them.  It then calculates...

Microsoft support continues to receive a steady flow of cases indicating poor, SQL Server performance symptoms.   When the issue is narrowed down we are finding that the working set of SQL Server and many of the processes on the computer have been significantly trimmed. What we have found is...

I have talked about how connections and tasks get assigned as PASS and during many other mentoring opportunities.  I just finished working on an issue that forced me to dig deeper into the subject. The customer reported that on a NUMA machine the connections are always getting assign to the same...

Both SQLIOSim and SQLIOStress use DeviceIOControl to determine information about the target.   On disk caches are typically not battery backed and when enabled can lead to data loss during a power outage. The utilities attempt to warn the user when on disk caching is reported as enabled.  ...