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

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

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

How It Works: XEvent Output and Visualization

Each and every day I use XEvent more and more as I uncover the powerful feature set.   I am finding it helpful to understand some of the input and output capabilities in order to leverage the power of XEvent. Server File Output When setting up an session to write to a file use per CPU…

0

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

How It Works: FileStream (RsFx) Garbage Collection–Part (2)

In a previous post I outlined the basics of File Stream, garbage collection: http://blogs.msdn.com/b/psssql/archive/2011/06/23/how-it-works-filestream-rsfx-garbage-collection.aspx This post continues the discussion, outlining specific details as to how the garbage collection progresses. A Single GC Thread The instance of SQL Server contains a single, background worker performing the garbage collection activities.   The FSGC worker, wakes up every ~5…

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

Service status watcher in SQL Server Management Studio – How it works

Have you ever wondered about the mechanism using which SQL Server Management Studio(SSMS) – Object Explorer shows the service status for SQL Server and SQL Agent service? We recently worked with a customer on a issue related to this and thought that this might be useful information to share out. So here it is. Here…

3

How It Works: SQL Server 2012 Database Engine Task Scheduling

Over the years the SQL Server scheduling algorithms have been documented in various publications.  Specifically, ‘The Guru’s Guide to SQL Server Architecture and Internals’ has a chapter, written by the scheduler developer (Sameer) and Ken; and I reviewed the technical content, covering the details. This post outlines a few of the changes that were made…

0