Browse by Tags

Blog Post: How It Works: sp_server_diagnostics – spinlock backoffs

SQL Server Support Content

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

on 12 Dec 2014
Blog Post: How It Works: XEvent Output and Visualization

SQL Server Support Content

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

on 25 Jul 2014
Blog Post: How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

SQL Server Support Content

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

on 29 Apr 2014
Blog Post: How It Works: FileStream (RsFx) Garbage Collection–Part (2)

SQL Server Support Content

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

on 4 Apr 2014
Blog Post: How It Works: SQL Server AlwaysOn Lease Timeout

SQL Server Support Content

The lease is used between the SQL Server resource DLL and the SQL Server instance to prevent split-brain from occurring for the availability group (AG). The lease is a standard signaling mechanism between the SQL Server resource DLL and the SQL Server ...read more

on 7 Sep 2012
Blog Post: How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

SQL Server Support Content

The NODE an operating system page, physically belongs to can be acquired using the QueryVirtualMemoryEx Windows API. SQL Server uses this API to track locality of memory allocations. This blog is a very high level view of SQL Server behavior but ...read more

on 14 Dec 2012
Blog Post: How It Works: SQL Server 2012 Database Engine Task Scheduling

SQL Server Support Content

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

on 13 Aug 2013
Blog Post: How It Works: Gotcha: *VARCHAR(MAX) caused my queries to be slower

SQL Server Support Content

The scenario: Table has a NTEXT column that the customer wanted converted to NVARCHAR(MAX) Data has both small and large storage for different rows Issued ALTER TABLE … ADD COLUMN …NVarCharColumn… NVARCHAR(MAX) Issued update MyTable set NVarCharColumn ...read more

on 3 Dec 2012
Blog Post: How It Works: The I/O Path: SQL Server Running in Windows Azure Virtual Machine (IaaS)

SQL Server Support Content

Note: This blog is based on behavior as of June 2013. At Microsoft we continue to evolve and enhance our products so the behavior may change over time. The I/O path for SQL Server, running on Windows Azure Virtual Machines, uses Windows Azure storage ...read more

on 3 Jun 2013
Blog Post: How It Works: Always On–When Is My Secondary Failover Ready?

SQL Server Support Content

I keep running into the question: “When will my secondary allow automatic failover?” Based on the question I did some extended research and I will try to summarize in is blog post. I don’t want to turn this post into a novel so I am ...read more

on 22 Apr 2013
Blog Post: How It Works: Online Index Rebuild - Can Cause Increased Fragmentation

SQL Server Support Content

SQL Server Books Online alludes to the fragmentation possibility but does not fully explain that the Online Index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives . The process of ...read more

on 5 Sep 2012
Blog Post: How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!

SQL Server Support Content

This issue was puzzling until we stepped through the code and studied it in some detail. The report was “SQL Server won’t use the physical memory I expect it to use.” Scenario: SQL Server 2008 R2, 32GB RAM - SQL only using ~22GB ...read more

on 11 Dec 2012
Blog Post: How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)

SQL Server Support Content

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

on 27 Sep 2013
Blog Post: Service status watcher in SQL Server Management Studio – How it works

SQL Server Support Content

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

on 22 Aug 2013
Blog Post: How It Works: CMemThread and Debugging Them

SQL Server Support Content

The wait type of CMemThread shows up in outputs such as sys.dm_exec_requests. This post is intended to explain what a CMemThread is and what you might be able to do to avoid the waits. The easiest way to describe a CMemThread is to talk about ...read more

on 20 Dec 2012

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

SQL Server KBs and Blogs

Browse by Tags

Blog Post: How It Works: sp_server_diagnostics – spinlock backoffs

Blog Post: How It Works: XEvent Output and Visualization

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

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

Blog Post: How It Works: SQL Server AlwaysOn Lease Timeout

Blog Post: How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

Blog Post: How It Works: SQL Server 2012 Database Engine Task Scheduling

Blog Post: How It Works: Gotcha: *VARCHAR(MAX) caused my queries to be slower

Blog Post: How It Works: The I/O Path: SQL Server Running in Windows Azure Virtual Machine (IaaS)

Blog Post: How It Works: Always On–When Is My Secondary Failover Ready?

Blog Post: How It Works: Online Index Rebuild - Can Cause Increased Fragmentation

Blog Post: How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!

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

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

Blog Post: How It Works: CMemThread and Debugging Them