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

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)…


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…


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

VSS backup of AlwaysOn Secondaries

Hi Everyone, Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2, which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases. Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against a DB that is an AlwaysOn secondary was failing by design. Our…

5

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…


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