How It Works: SQL Server (SQLNCLI11) ODBC Driver–Keyset Cursor

This blog is based on SQL Server 2014 CU7’s updated release of SQLNCLI11 and MSSQLODBC drivers. The basics behind KEYSET cursor behavior are described here: https://msdn.microsoft.com/en-us/library/windows/desktop/ms675119(v=vs.85).aspx  The critical part of the referenced link is the keyset cursors ability to see changes in the data.   When you open the cursor the sp_cursoropen or sp_cursorprepexec procedure is…

0

Operating System Error (665 – File System Limitation) Not just for DBCC Anymore

The operating system error 665, indicating a file system limitation has been reached continues to gain momentum beyond DBCC snapshot files.    Over the last ~18 months I have reproduced issues with standard database files, BCP output, backups and others. We have posted previous blogs talking about the NTFS attribute design and associated limitations (665) as…

5

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 and SSDs – RDORR’s Learning Notes – Part 1

I am very hesitant to post anything that I don’t have the full details on.  However, with SSD deployments moving so rapidly I thought it might be helpful to share some of my learning’s to date. I make no claims of being an expert in this area.   However, I have been doing research that I…

1

A faster CHECKDB – Part IV (SQL CLR UDTs)

I have been working on the various aspects of DBCC performance and SQL CLR based User Defined Data Types.    I encountered a few issues that I have outlined below.  1.      Memory Grant Bug There is a bug, prior to SQL Server 2014, causing the memory grant for the DBCC operations (checktable or checkdb per table)…

0

How It Works: sp_server_diagnostics – spinlock backoffs

There are numerous articles outlining how spinlocks work so I won’t cover the details in this post.   Instead, I want to focus on the spinlockbackoffs value recorded in the sp_server_diagnostics output. Component = System <system spinlockBackoffs=”0″ sickSpinlockType=”none” sickSpinlockTypeAfterAv=”none” … Querying select * from sys.dm_os_spinlock_stats the backoffs column is presented.   This is NOT the same as…

0

A faster CHECKDB – Part III

Bob Ward introduced Part 1 and Part 2 of ‘A faster CHECKDB’ as highlighted in the following links. Part 1: http://blogs.msdn.com/b/psssql/archive/2011/12/20/a-faster-checkdb-part-i.aspx  Part 2: http://blogs.msdn.com/b/psssql/archive/2012/02/23/a-faster-checkdb-part-ii.aspx  Recently,  Jonathan pointed out a memory grant issue in the following post. https://www.sqlskills.com/blogs/jonathan/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect/ I always enjoy my interactions with Jonathan and this is yet another positive experience for us all.  After…

0

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…

2

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