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

Slow query using non-deterministic user defined function

Recently we worked with a customer who reported a query that used to run a few seconds in SQL Server 2000 but it never finishes in SQL Server 2008 R2 following upgrade. We went around and tried quite a few things but couldn’t get SQL Server 2008 R2 to generate similar plan. Upon closer look…

2

Getting Cross Domain Kerberos and Delegation working with SSIS Package

I started working on this issue started by way of a Twitter conversation between myself, Jorge Segarra (@sqlchicken) and Matt Masson (@mattmasson). I then found out that Jorge was working with a customer that had opened up a Support Case.  Also special thanks to Joey Seifert for letting me bounce some Active Directory questions off…

3

Kerberos Configuration Manager updated for Analysis Services and SQL 2014

Kerberos Configuration Manager was released back in May of 2013.  It initially released with only SQL Server support. This was followed up with support for Reporting Services in November 2013. You can download the latest release from the following link: Microsoft® Kerberos Configuration Manager for SQL Server® http://www.microsoft.com/en-us/download/details.aspx?id=39046 This month we have released Version 3…

2

These resources may help resolve your issue….

Some of you may have seen a list of links that pop up when opening a case through our Microsoft support site. These resources are internally referred to as solution assets and are meant to be top bets for the problem category and are aimed at helping you solve the problems on your own. We…


Using SQL Server in Microsoft Azure Virtual Machine? Then you need to read this…

Over the past few months we noticed some of our customers struggling with optimizing performance when running SQL Server in a Microsoft Azure Virtual Machine, specifically around the topic of I/O Performance. We researched this problem further, did a bunch of testing, and discussed the topic at length among several of us in CSS, the…

4

REPL_SCHEMA_ACCESS wait type

Recently we have worked with a customer on replication latency issue with transactional replication. Customer has over 30 published databases on a single server. All of them are very active. Periodically, they will see up to 30 minutes latency from publisher to distributor. When they see waits on REPL_SCHEMA_ACCESS. Below is a sample screenshot of…

4

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