How It Works: sp_server_diagnostics – spinlock backoffs

There are numerous articles outlining how spinlocks work so I won’t cover the details in this post.   Instead, I want to focus on the spinlockbackoffs value recorded in the sp_server_diagnostics output. Component = System <system spinlockBackoffs=”0″ sickSpinlockType=”none” sickSpinlockTypeAfterAv=”none” … Querying select * from sys.dm_os_spinlock_stats the backoffs column is presented.   This is NOT the same as…

0

How It Works: When is the FlushCache message added to SQL Server Error Log?

FlushCache is the SQL Server routine that performs the checkpoint operation.  The following message is output to the SQL Server error log when trace flag (3504) is enabled. 2012-05-30 02:01:56.31 spid14s     FlushCache: cleaned up 216539 bufs with 154471 writes in 69071 ms (avoided 11796 new dirty bufs) for db 6:0 2012-05-30 02:01:56.31 spid14s                 average throughput: …

9

Tracking Down Missing Indexes in SQL Azure

One of the challenges of SQL Azure is that not all of the TSQL that you are used to using is supported yet.  Since the underlying engine is plain ole’ SQL Server, the engine can understand the TSQL, but we just block its use because we haven’t yet made it work in the multi-tenant, multi-server…

7

RML How Are Comparison Diffs Calculated

A short but good discussion about the RML comparison DIFF calculations.   From: Robert Dorr Sent: Wednesday, January 26, 2011 10:21 AM Subject: RE: MSDN Blogs: Contact request: RML Tools: Estimated Comparison Differences   Thanks for the question and feedback.   For example ProjectedCPUDiff is one of the columns in tblComparisonBatchPartialAggs   ·         The hash…

0

Doctor, this SQL Server appears to be sick….

I thought the PASS Summit was a great success this year. I always enjoy speaking at the PASS Summit (this year I spoke on Wait Types. I’m working now on starting to populate the Wait Type Repository) but I also really enjoy working at the SQL Server Clinic. This year CSS combined its efforts with…

5

Reduce locking and other needs when updating data – Better Performance

The following pattern typically stems from an old practice used in SQL 4.x and 6.x days, before IDENTITY was introduced. begin tran declare @iVal int select @iVal = iVal from CounterTable (HOLDLOCK) where CounterName = ‘CaseNumber’ update CounterTable set iVal = @iVal + 1 where CounterName = ‘CaseNumber’ commit tran return @iVal This can be…

0

Attach of the Clone (… Databases)

Sorry Star Wars fans to mislead you. I thought our readers should know about a feature we use quite a bit in CSS called a “Clone Database”. It is better publically now known as a “Statistics-Only” copy of the database. Why is this useful? Because it provides CSS a way to reproduce and diagnose query…

1

Q&A on Latches in the SQL Server Engine….

I recently received a request to shed some light on a few advanced questions about latches and SQL Server. These were good questions so I thought the information might be useful to share with the community. I’ll provide this information in the form of Q&A as I was asked (I’ve paraphrased the questions): Question: What…

2

An update for Standard SKU Support for Locked Pages….

Note this article has been updated to include an update for SQL Server 2005 I posted in April that we would be releasing cumulative updates for SQL Server 2008 and 2005 to support the concept of Locked Pages for SQL Server Standard. SQL Server 2008 Cumulative Update 2 for SP1 was released yesterday to provide…

5

SQL Server reports working set trim warning message during early startup phase

In the previous posts we have discussed the working set trim message “A significant part of sql server process memory has been paged out” that gets written to the SQL Server Error log. You can find the details about the various parameters that determine when this message is written to the error log in the KB…

4