Are My Statistics Correct?

The question is often “Are my statistics up-to-date?” which can be a bit misleading.   I can make sure I have up-to-date statistics but the statistics may not be accurate.  I recently engaged in an issue where the statistics were rebuilt nightly.   A maintenance job change had been made moving from FULLSCAN to WITH SAMPLE statistics…

1

How It Works: MAX DOP Level and Parallel Index Builds

I have been working on an issue where rebuilding an index leads to additional fragmentation.   Using XEvents I debugged the page allocations and writes and was able to narrow in on the behavior. There are lots of factors to take into account when rebuilding the index.   I was able to break down the behavior to…

4

SQL Server MAX DOP Beyond 64 – Is That Possible?

I recently posted a blog outlining how the partitions of a table can be used in the calculation for the achievable max degree of parallelism (MAX DOP). http://blogs.msdn.com/b/psssql/archive/2014/09/04/a-partitioned-table-may-limit-the-runtime-max-dop-of-create-alter-index.aspx  Discussing this with various peers I uncovered a perception that SQL Server was always limited to a max of 64 CPUs, even if the machine had more…

3

A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

I was working with a 1.3 trillion row table in the Microsoft lab when I learned more about the ins and outs of this behavior.  This issue is alluded to in SQL Server Books Online but allow me to expand on the behavior a bit more. (http://msdn.microsoft.com/en-us/library/ms190787.aspx) The lab machine is a 128GB, 64 CPU…

1

How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

I ran into this behavior working on a 1 trillion row, spatial index build but the behavior can apply to any Gather Streams operator, retaining the sort order as rows pass though it.   I was just surprised a bit by the behavior until I dug deeper to understand. The index was taking just short of…

6

All about RollbackSnapshotTempDB…

  I’ve been recently involved in several cases where Databases named RollbackSnapshotTempDB +<someGUID> were generating confusion. The purpose of this post is to clarify their origin and use and to enable SQL Server admins to know what to do if they need to deal with them. The following topics will be discussed (click for a…

0

Every time I ‘ATTACH DATABASE’ SQL logs error 1314 for SetFileIoOverlappedRange

Turns out this is an issue in the SQL Server code and the error is a bit noisy during attach database. When opening the database files, SQL Server calls SetFileIoOverlappedRange (when enabled properly) in order to help improve I/O performance.  This is commonly done under the SQL Server, service account; which requires locked pages privilege. …

2

How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)

I was working on an index build issue for an 80 CPU system and kept seeing that only 64 CPUs were getting used. I had carefully studied sys.dm_os_spinlock_stats and sys.dm_os_wait_stats along with performance counters, memory usage pattern, and I/O activities.   In fact, I had an 80 CPU, 2TB RAM, 4TB SSD system so I was…

1

When Does sp_prepare Return Metadata

I was running an RML Utilities Suite test pass and encountered varying behavior from our sp_prepare suite.  Here is what I uncovered. The command sp_prepare returns (or does not return) metadata depending on the server version.  For the client version, it is only significant whether it is prior to SQL 2012 or it is a…

0