SQL Server Premier Field Engineer Blog

A question was posed to me whether Flow Control which existed in Mirroring was still relevant for Availability groups. Flow Control is primarily a mechanism to gate or throttle messages to avoid use of excessive resource on the primary or secondary...

Choosing the database recovery model is one of the more important choices DBAs have.  If the business does not need to recover a database to a point in time (PIT) then the SIMPLE recovery model is the choice for you.  However, if PIT recovery...

It is a common check to see if all of the nodes in a cluster are equally patched.   The reason behind this is because in order to ensure stable operations and a consistent experience, it is best practice to have all nodes of a failover cluster instance...

In a previous blog post ( Troubleshooting SQL High CPU usage using Xperf ), we covered the xperf basics, what types of scenarios are appropriate for xperf, and more specifically, how to look at CPU sampling within xperf.    In general, user...

We have introduced the System Health Session in SQL 2008 in order to capture some critical Events to make post mortem analysis much easier. However in SQL 2008, by default the system_health session was only collected to a ring buffer and not persisted...

  It is a widely known and discussed performance hit when you don’t have an optimal power plan set on your database servers.   This is one of those easy ways to get great gains by simply flicking a switch…almost literally. But what happens if...

  I was dealing with an issue where we had an AlwaysOn availability group with 2 replicas configured for Automatic failover. There was a 30 second glitch where folks could not connect to the Database on the primary and automatic failover did not...

In this blog post I’ll take a look at what is actually stored in a non-clustered index and (re)introduce you to some tools that you can use for looking at the contents of a given data or index page.   Hopefully, by the end of the examples you’ll...

I encountered an interesting case with FileStream and Antivirus which I looked at with Bob Dorr, blogged about it on the PSS SQL Blog, thought I’d share that link here as well http://blogs.msdn.com/b/psssql/archive/2013/03/29/the-case-of-anti-virus-filter...

In this blog post we’ll take a look at how SQL Server interacts with the transaction log file using Extended Events and Process Monitor.  We will see how small transactions can have a negative impact on transaction log IO throughput and how larger...

  Xperf/WPA is a powerful Windows tracing tool that can be used to assist in troubleshooting numerous issues on a Windows server.    The utility allows for in depth tracing without the high overhead typically associated with such tracing...

  In this blog post I’ll (re)introduce you to a really neat tool to use alongside of SQL Server and use this tool to show you how SQL Server is handling IO under the covers.  We will view the IOs as they occur, and then tie the IO back to the...

  In my last post, I went over partial database containment and contained users.   This post will focus on the other piece of functionality in partial containment… collation.   The collation of data determines how data is sorted and compared...

The concept of database containment basically means that all instance level dependencies are severed from the database.   If you have ever implemented log shipping or mirroring, you are probably aware of many of these.   Instance level logins...

Scenario: You have a two instance, two node cluster. SQL1 is typically running on Node1, SQL2 is typically running on Node2. The Objective is to maximize memory usage when each instance is running on its own node, yet balance memory usage if both of them...

The SET overwrite is largely impacted by the attribute relationships and the behavior does depend on whether the attribute is below or above or no relationship in the relationship chains.   For illustrate the point, I run a test base on the following...

It has been a while since there has been activity on this blog, we as a team will be trying to post weekly going forwards so as to share what we do on a regular basis – happy reading! I was posed a question by a fellow PFE during his performance...

Attached is a sample Merge Conflict Handler sample I created.  I am including a walkthrough of the sample below.   1.   Create the sample merge publication/subscription databases Code Snippet:  From file  <1-CreatePub_Sub_DBs...

  There will be an upcoming Knowledge base article detailing this out as well   When you append a compressed backup to an existing Media, it inherits the compression setting from the Media set.   If you rely on the ‘backup compression’...

One common misconception about how merge replication handles column-level tracking (detection) is that when a conflict is encountered, the winning row will include column values which did not conflict in addition to the "winning columns". For...

  Based on Paul Randal’s post telling how to get the last known good DBCC ran against a database, based on my job requirements, I decided to write a T-SQL script which has nothing new but returns last known good DBCC execution time and last backup...

So what is part II about? It is about a change in Standard 64 bit Editions of SQL Server 2005 and 2008. This change implements the support of AWE APIs and “Lock Pages in Memory” privilege by these editions.   It has been already advertised by Bob...

You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. "Perform volume maintenance tasks") for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE...

A deadlock is a special type of situation wherein two or more transactions are waiting for the other to finish, and thus neither ever does and no one can proceed. Deadlocks can be often considered as normal in a high concurrent update environment. When...

You may have questioned why, on 64 bit enterprise Edition, DBCC MEMORYSTATUS reports non zero “AWE allocated” memory even if ‘AWE enabled’ option is not enabled. A short description of SQL Server memory model will help to answer. Here is an example of...