Today I found a new (for me), undocumented method for identifying the location of the SQL Error log.  I knew you could either check the registry or use SQL Server Configuration Manager and the "startup parameters" option.  I ran SQL Profiler while opening up a SQL Error log in SQL Server Management Studio and found that SERVERPROPERTY has an option called 'ErrorLogFileName', as demonstrated here:

SELECT SERVERPROPERTY('ErrorLogFileName')

Again - you won't find it in Books Online, so it is officially undocumented with all the risks that implies - but useful for large environments where you're not always sure where the error logs reside and need to collect them directly for root cause analysis.

The new SQL Server Premier Field Engineer Blog is now launched!  It will start off small, but hopefully we'll be able to get many of my team members to contribute on a frequent basis.  They have so much experience to share, and after prompting from Adam Machanic, I realized that it would indeed be a great idea to get something like this going.  Stay tuned for interesting and engaging content. 

Sanjay Mishra just published the following White Paper, "Data Compression: Strategy, Capacity Planning and Best Practices", highly recommended and much anticipated.

On this same topic, I received a question from a colleague the other day, asking what the impact of data compression on memory was.  The assumptions I had were as follows:

• Page or Row compression allows for more data to fit in memory (compressed page on disk matches compressed page in the buffer cache)
• When the data is read from memory for use in a query – it is uncompressed during that time

A little unsure of the specifics, I still wanted confirmation on these assumptions, so thank you to Sunil Agarwal for confirming that this was correct.  Compressed data does reside in the buffer pool in a compressed form (which you can test in a before/after data compression using
sys.dm_os_buffer_descriptors), but when compressed data is referenced, it is uncompressed as needed.  Sanjay's paper also discusses this topic in more detail in the "Application Workload" section.

I recently worked on a case where a significant number of deadlocks were being generated for concurrent UPDATE operations.  I looked at sys.dm_tran_locks and saw a significant number of table lock requests (with WAIT and CONVERT request_status) for the table in question.  I used trace flag 1222 to look at the full deadlocking chain details, and found the associated UPDATE statements involved in both the winner and victim deadlock processes.

I examined the UPDATE statement, and I saw that the WHERE clause was very selective.  In other words, the predicate in question should only have affected a single row at a time.  The column was indexed, and the statistics were up to date – so nothing was out of the ordinary there.  The query was not using locking hints and the isolation level for each session in the deadlock chain was READ COMMITTED.

I turned on profiler and looked for lock escalations during the frequent deadlock events.  There were none happening.  This told me that this was not a lock escalation issue (row converted to table, page converted to table), but rather, the initial lock granularity of the query was a table lock.

I looked at the execution plan for the UPDATE and saw that a single UPDATE impacted five different indexes (clustered index, and four non-clustered indexes).  For kicks, I added a ROWLOCK hint to the UPDATE against a dummy record, to see what kind of initial locking behavior would be generated.  When I did this, I received the following error message:

Msg 651, Level 16, State 1, Line 1

Cannot use the ROW granularity hint on the table "XYZTable" because locking at the specified granularity is inhibited.

That was an immediate prompt for me to check sys.indexes to determine if any indexes for the table had row and page locks disabled (if both are disabled, the only choice is a table lock):

SELECT name indexname

FROM sys.indexes

WHERE object_id = object_id('XYZTable') AND

               allow_row_locks = 0 AND allow_page_locks = 0

So sure enough, there was a single nonclustered index with both of these options disabled.  Since the UPDATE required access to this particular index, an initial table-lock was being selected by SQL Server.  There was no other choice since row and page locks were both disabled.

There may be situations where you wish to disable either row locks or page locks – however in my experience these situations are infrequent.  If concurrency and throughput is important to your application, these index options should usually be left ON.  If you are seeing unexpected table locks from queries that are highly selective, be sure to check these options via sys.indexes.

Heard a similar question today, related to my post on "Override READ_COMMITTED_SNAPSHOT with READ_COMMITTED."  Question was - what is the impact of NOLOCK when RCSI is enabled for a database.

Here is the demo script I sent, showing the behavior with and without NOLOCK for an RCSI enabled database.  Basically - you'll see uncommitted data modifications with NOLOCK as you would if RCSI was not enabled:

Session #1:

USE AdventureWorksDW2008

GO

BEGIN TRAN

UPDATE dbo.DimCustomer

SET LastName = 'Stevens'

WHERE CustomerKey = 11004

Session #2:

-- Returns Johnson

SELECT LastName

FROM dbo.DimCustomer

WHERE CustomerKey = 11004

-- Returns Stevens

SELECT LastName

FROM dbo.DimCustomer

WITH (NOLOCK)

WHERE CustomerKey = 11004

The scenario… An application is using the READ_COMMITTED_SNAPSHOT (RCSI) database option to minimize blocking of SELECT statements by concurrent data modification operations.  The application is successfully using this option to reduce the number of blocked processes.  They do notice, however, that their application still hard-codes explicit SET TRANSACTION ISOLATION LEVEL READ COMMITTED commands.  So the question I received was – does setting “SET TRANSACTION ISOLATION LEVEL READ COMMITTED” prior to a statement execution override the READ_COMMITTED_SNAPSHOT behavior?

To answer this, I used the AdventureWorksDW2008 database.  I ran the following code to enable RCSI:

USE master

GO

ALTER DATABASE AdventureWorksDW2008

SET READ_COMMITTED_SNAPSHOT ON

In a separate query editor window, I executed the following update:

USE AdventureWorksDW2008

GO

BEGIN TRAN

UPDATE dbo.DimCustomer

SET LastName = 'Stevens'

WHERE CustomerKey = 11004

In a second query editor window, I ran the following SELECT query:

USE AdventureWorksDW2008

GO

-- Returns Johnson

SELECT LastName

FROM dbo.DimCustomer

WHERE CustomerKey = 11004

As I expected – the query returns the previous value of “Johnson” – since the UPDATE I previously executed has not yet committed the transaction – changing the name to “Stevens”.

So next I’ll execute the same SELECT statement, this time explicitly designating READ COMMITTED:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT LastName

FROM dbo.DimCustomer

WHERE CustomerKey = 11004

The result?  It still returns “Johnson.”  Whether you implicitly or explicitly designate READ COMMITTED, having your database configured with RCSI will result in same version generating behavior.

PS:   Don’t forget to ROLLBACK TRAN for that UPDATE to dbo.DimCustomer…

Here is a link to the presentation deck for yesterday's Minnesota PASS presentation:

 "Performance Tuning with Wait Statistics"

 Was fun to see everyone. Thanks for attending!

If you live in Minnesota or are visiting next Tuesday, April 21st, please stop by the Bloomington Microsoft office and say hello.  I'll be presenting on the topic "Performance Troubleshooting with Wait Statistics"  (a subject made famous by Tom Davidson). Jason Strate from Digineer Inc will also be presenting on Dynamic Management Views and Dynamic Management Functions.

Presentations start at 4:30PM CST.

Hope to see you there!

Do you suspect you are encountering CPU pressure?  Some of the more common signs of CPU pressure include:

• Sustained "% Processor Time" values (>75% for extended periods)
• High signal wait time percentage from sys.dm_os_wait_stats
• SOS_SCHEDULER_YIELD waits

One way to measure if you have several tasks waiting for CPU time is to query the sys.dm_os_schedulers DMV.  This DMV has several interesting columns - however one in particular is useful for measuring queued tasks - the "runnable_tasks_count" column.  The "runnable_tasks_count" value indicates the count of workers that have tasks assigned to them that are just waiting for their share of time on the scheduler (logical CPU).  I checked various busy OLTP hosting SQL Server instances, and even though these systems are quite active, the value for runnable_tasks_count was very low (almost always showing zero values). 

Yesterday I did encounter a SQL Server instance where the value of runnable_tasks_count averaged about 13 across all schedulers with a status of "VISIBLE ONLINE".  The value of runnable_tasks_count should be as low as possible - and if it shows ongoing non-zero values, you have further evidence of CPU pressure.

Today I witnessed a performance load test against a 16 proc 64-bit SQL Server instance.  During the first run we saw a very high value for CXPACKET, followed up with SOS_SCHEDULER_YIELD.  After disabling parallelism and clearing the wait stats, CXPACKET disappeared and was replaced with a significant percentage of SOS_SCHEDULER_YIELD (representing > 80% of the total accumulated wait time).  The load test was driving all 16 CPUs quite high during the test, almost pegging them at 100%.

So here are two questions to ask in this situation:

·        What does the wait type mean (in this case SOS_SCHEDULER_YIELD)?

·        Is it actionable?

In answer to the first question, SOS_SCHEDULER_YIELD represents a SQLOS worker (thread) that has yielded the CPU, presumably to another worker.  My favorite white paper, SQL Server 2005 Waits and Queues, defines SOS_SCHEDULER_YIELD as occurring “when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.”  The over-simplified explanation of a quantum is the amount of time a thread is scheduled to run. The thread that yielded the scheduler (CPU) is now waiting for its turn again to run on the processor. SQL Server runs cooperatively (non-preemptive mode) for many activities, which means that SQL Server decides when a thread will yield – not the Operating System.  With thread yielding being voluntary – if the thread was greedy, we could see a risk of the thread running until it was complete - however that is not the case. The SQLOS was designed in such a way that active threads should not starve out other runnable threads.  Threads will voluntarily yield their time so that other tasks can execute. On a busy, highly concurrent SQL Server instance, this is a good thing.

Now is this wait type actionable?  If this wait type represents a high percentage of overall wait time accumulated for a workload or important period of time, we can surmise that SOS_SCHEDULER_YIELD is a symptom of CPU pressure.  If you see a high value for SOS_SCHEDULER_YIELD you should check perfmon counters to see if you also see sustained high values for % Processor Time and Processor Queue Length.  You should also validate that it is indeed the SQL Server process that is causing the CPU pressure using the Process: % Processor Time counter, checking specifically for the SQL Server instance (or other processes responsible).  You may also want to validate the number of workers in a runnable state in sys.dm_os_workers and sys.dm_os_schedulers, revealing if a significant number of tasks are waiting in line for their chance to run on a scheduler.

If you determine that SQL Server is responsible for the sustained CPU usage, then you should follow the standard CPU pressure troubleshooting path ( for example - query sys.dm_exec_query_stats ordering by total_worker_time, check for high compilations and recompilations, optimization time spent with sys.dm_exec_query_optimizer_info, and more).

Hope this helps,

Joe

Today I received a copy of SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey (aka ScaryDBA).  I was the technical reviewer for this book and I really enjoyed the project.  SQL Server performance is one of my favorite topics, and Grant did an amazing job.  As the drafts flowed in - it was obvious to me that Grant had a significant amount of practical experience. Regarding his writing skills, he followed the "show - don't tell" style which in my opinion is so critical to the learning process (not to mention the readability of a book).  I also liked that this was a distilled title - meaning that Grant spent 490+ pages on the most important performance topics instead of 1000.  I felt he hit all the marks, emphasizing quality over page-stuffing quantity. 

Full disclosure - technical reviewers get paid "by the page" to review a book. We don't get paid royalties for units sold (or at least I wasn't).  So once the review is done, that's it for the money.  This endorsement\recommendation doesn't line my own pocket any further - just wanted to get the word out on an excellent new reference. Highly recommended!

I recently heard from a reader of SQL Server 2008 Transact-SQL Recipes who was looking for the downloadable Transact-SQL source code from the book.  Turns out he was using my book in a class he was teaching.  Although Transact-SQL Recipes is not really written in a step-by-step format, it occurred to me that if you are using it in a classroom environment, you'll want to follow along with some recipes and also avoid all that extra typing.  Not sure why I didn't think of that earlier...

So over the last few evenings, I extracted out the source code for each chapter and you can download it from the http://apress.com/book/downloadfile/4312.  The formatting of each statement wasn't entirely preserved, and although not as pretty, but you'll still save keystrokes.

If you have ever interupted Database Tuning Advisor prior to a full completion of a workload evaluation, you may notice that your tables have indexes with naming conventions like "_dta_index_tablename_NNN".  You can see these hypothetical indexes in sys.indexes, however you will not see them in sys.dm_db_partition_stats because they do not consume disk space in the traditional sense.  Hypothetical indexes have column-level statistics used by DTA, but they cannot actually be used for data access by your queries.  If not being used by DTA, you'll want to clean these up.

The following is a query you can use to identify which indexes in the database are hypothetical.  It uses the INDEXPROPERTY function and the IsHypothetical property:

SELECT object_name(object_id), name indexname

FROM sys.indexes WHERE INDEXPROPERTY(object_id, name, 'IsHypothetical') = 1

ORDER BY object_name(object_id), name

Cheers!

I have two blog subject follow-ups tonight:

Follow-up #1... Regarding my "Page Cleaning with sp_clean_db_file_free_space and sp_clean_db_free_space" post - my friend and colleague Jimmy May let me know that the always amazing Kalen Daleney just posted on this subject today which you can read here: Clean Up Your SQL Server Databases.  One good point that I didn't mention in my post but that Kalen did - you can use these procedures to compensate for the potential Instant File Initialization security considerations. 

Follow-up #2... Regarding my post "SQL Server and HBA Queue Depth Mashup" - I had a chance to test out HBA Queue Depth settings recently and had unexpected results.  First of all, I used SQLIO against a QLogic HBA and EMC SAN.  In my testing scenario I was primarily interested in write-performance with varying queue depths.  I tested random 8K writes, sequential 64K writes, and sequential 256K writes using depths of 16, 32, 64, and 96.  The result?  For random 8K writes, 32 was the best setting by far (for example - issuing 83 MB/sec versus 47 MB/sec for a 64 queue depth).  I found that a 64 queue depth was only marginally better for sequential 64k writes and that for 256k sequential writes a 32 queue depth was significantly better than a 64 queue depth.  So the default queue depth of "32" for the Qlogic HBA worked best for random 8k and sequential 256k writes.  Not what I expected (was hoping to eek out a little more throughput with a higher queue depth) - but that is why we test, no?  There are several factors involved, so always test for yourself given your own hardware components, topology, and environment.

Happy Thursday night!

Joe