Backup compression behavior when appending backups to an existing Media Set

 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’  sp_configure setting and are appending to existing media sets you may end up with a backup…


Merge Replication Conflict Detection v/s Conflict Resolution

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 example: Originally Row100= col1=a, col2=b, col3=c, col4=d, col5=e User1 updates Row100: col1=f,    col2=g,    col3=h    — note: col4 and col5…


SQL Server memory models (Part II)

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 Ward and worth to be known especially by the…


How and Why to Enable Instant File Initialization

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 AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the "Perform volume maintenance tasks"…


SQL Server memory models (Part I)

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 a DBCC MEMORYSTATUS output on a 32GB X64 Enterprise SQL Server 2005:…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…


The Case of the Phantom Spid: Troubleshooting and Resolving an Orphaned DTC Transaction

Several weeks ago one of our customers encountered an issue in which an index maintenance job that had always completed successfully, began failing with each nightly run. While troubleshooting the issue, the customer ran the DBCC OPENTRAN command against the affected database and discovered that another session, which had been running for several days, might…


Three Usage Scenarios for sys.dm_db_index_operational_stats

There are several dynamic management objects (DMOs) that I use on an ongoing basis in performance tuning scenarios. However, I’ve lately gained an increased appreciation of the sys.dm_db_index_operational_stats DMO.   This 44 column (as of SQL 2008) DMO returns data at a database-object-index-partition granularity and tracks an assortment of allocation, latching, locking and I/O related statistics….