CPU_ID Gaps in SQL Server sys.dm_os_schedulers

We all know (or should!) that SQL Server is NUMA aware. In fact, there are certain background processes that are created and run per NUMA node inside of SQL Server, which is why the soft-numa changes in 2016 can make a big performance gain. There was a need to affinitize certain cpus for a SQL…

0

How Many Tables Can I Have In SQL Azure DB & SQL Server

Hello Dear Reader!  This is my first full fledged blog for my friends here at the PFE blog.  I wanted to start off with something interesting.  A friend asked a very intriguing question yesterday.  “How many tables can a SQL Azure Database have?”.   The context was a migration from MySQL to SQL Server and they…


SQL PFE has a Pre-Con at the PASS Summit!

At the 2014 PASS Summit in Seattle on Tuesday November 4, Denzil Ribeiro and I (Tim Chapman) will be giving a Pre-Conference seminar entitled “Troubleshoot Customer Performance Problems like a Microsoft Engineer.”  In this full-day session Denzil and I will cover a plethora of troubleshooting tools and methodologies that we use as field engineers when confronted…


Generating a trusted TDE Certificate in the proper format from a Certificate Authority

I recently worked with a customer who was attempting to deploy Transparent Database Encryption using a trusted certificate which was generated by a certificate authority (CA). They were unable to import the certificate using the CREATE CERTIFICATE command as it kept failing with a 15468 error. This blog post attempts to explain this error and…


Inadvertently Promoting a Local Transaction to a Distributed Transaction

I recently dealt with a customer issue where they were troubleshooting MSDTC, and upon hearing the explanation of exactly what they were doing, I was a bit surprised that a distributed transaction was being used. Upon further investigation, they were unintentionally promoting a local transaction to a distributed transaction.  The reason behind this that they…


Connection Pooling for the SQL Server DBA

  There are a handful of questions that DBAs get in the wild that aren’t necessarily under the jurisdiction of the typical DBA.  One of those aspects is connection pooling.  Far too often application teams, or network admins, or <insert non-DBA professional here> approach the DBA with questions regarding connection pooling, and whether or not…


Forced Parameterization Can Lead to Poor Performance

Something that is a relatively common performance eye opener is when you have a large ad hoc workload, and you’re getting a huge gap in the lack of plan reuse.  You talk to the application team that is responsible for this possibly dreaded scenario and relay your concerns about the high CPU caused by the…


Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to retrieve Execution plans for high Resource statements.

Extended events is a powerful feature that allows us to troubleshoot performance issues within SQL Server. One of the ways you can utilize extended events to aggregate statements is described in a blog post below by Bob Dorr SQL Server 2012: RML, XEvent Viewer and Distributed Replay Let’s apply that methodology to a contrived scenario,…


Oops… I forgot to leave an empty SQL table partition, how can I split it with minimal IO impact?

One of the several advantages you get with partitioning a very large table is the ability to add or remove partitions instantaneously to help you with sliding window scenarios. However there are caveats if best practices aren’t followed. If the right most partition (in case of a RANGE LEFT partition) or the left most partition…


How to: Restoring SQL File stream Data

It is not uncommon to have multiple copies of the same database on a single instance or to restore copy of a database from one instance to another for testing.  I was asked – “How does this work when I have filestream data in my database?”  The thought process is pretty much the same, but…