MultiSubnet = TRUE Is Now Default Behavior

I get to be the a good new messenger today.    We have made changes to the SQL Server Client Provider.  The provider detects when multiple IP addresses are present for a listener.   The links below detail the behavior making it easier for your multi-subnet AlwaysOn deployments. Improved MultiSubnet Listener Behavior With Newly Released SQL Client…

1

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

Do I really need to use DTC Transactions?

It is sometimes common practice to enable Distributed Transaction (DTC) behavior but it can be unnecessary, and adds unwanted overhead.   DTC has the ability to determine single phase vs two phase commit requirements.  A DTC transaction involves resource managers (RMs) of which SQL Server can be one of them.  If a single resource manager is…

3

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

Read this if you have transactional replication configured and plan to upgrade from SQL 2008/2008 R2 to SQL 2012/2014

SQL Server online documentation makes very clear that you need to ‘drain’ your replicated transactions before doing any upgrade if you have replicated databases. Below are requirements for transactional replication: Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously. Stop user activity on published tables. Allow…

2

Capping CPU using Resource Governor – The Concurrency Mathematics

Here is what you need to know: A = πr2 Okay, not really as that is the formula for the area of a circle but it does set the stage for this discussion.   I have been working with the CAP_CPU_PERCENT (RESOURCE POOL) setting as it relates to concurrency.   This turned into a mathematical exercise I…

2

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

SQL Server 2014: TEMPDB Hidden Performance Gem

I ran across a change for TEMPDB BULK OPERATIONS (Select into, table valued parameters (TVP), create index with SORT IN TEMPDB, …) that you will benefit from. For example, I have a Create Index … WITH SORT IN TEMPDB that takes ~1 minute in SQL Server 2012.   On the same machine using a SQL Server…

17

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