SQLServerPedia

One of my development teams needed a mechanism for identifying the value of a key that was part of a lock (don’t ask). I’d never tried doing that before. Obviously if you hit the DMV sys.dm_os_tran_locks you can see the hash of the key in the resource_description column. But how to pull the value back. After some research, I first found this excellent article by the late, great, Ken Henderson (I really wish he was still around). The article outlined, among other things, the use of an undocumented “virtual” column called %%lockres%%. Some more searching then uncovered this great article by James Rowland-Jones, AKA Claypole. He described how, in a very high volume system, he used %%lockres%% to identify the source of a deadlock as the internal mechanisms that SQL Server uses to manage locks, the hash of the key. Oh, and he opened an incident on Connect, which seems to be closed, but vote on it anyway, I did. %%lockres%% is also covered in Kalen Delaney’s excellent book on SQL Server 2008 Internals and even warrants a bit of discussion in Professional SQL Server 2008, but that was written by James Rowland-Jones, so I’m not sure it counts.

In the meantime, while I was investigating this stuff, evidently the development team was looking into it on their own. They came to the same set of resources and decided to use the virtual column as part of their real-time, transactional application. Yeah, an undocumented “virtual” column going into a major application. Since I would probably be unable to do anything about this, I decided to at least look into how this thing behaves so I can be aware of what types of problems I might run into.

First, a simple query:

SELECTa.City
–,%%lockres%%
FROM Person.Address AS a
WHERE a.AddressID = 432

If you run this query and take a look at the execution plan you’ll see a nice clean clustered index seek, just as you would suspect. If you take away the comment and run it again, the execution plan is identical. On the version of AdventureWorks2008 currently installed on my machine, I get two page reads, regardless of whether or not I include %%lockres%% or not. With the comments removed, it returns the hash of the primary key: (b0004e040bc2). This looks pretty painless, free even.

If we want to see %%lockres%% in action, it’s not too difficult:

BEGIN TRAN
UPDATE Person.Address
SET City = ‘dude’
WHERE AddressID = 432;
–ROLLBACK TRAN

Obviously this will put a key lock on that row in the table. If I just select against sys.dm_os_tran_locks, the data returned looks like this:

resource_type   resource_description   resource_associated_entity_id   request_mode
KEY                       (b0004e040bc2)            72057594043564032                      X 

The original request from the development team was for a way to get the key value back when you know that a table is locked, such as the case here. I wrote this simple query to make that happen:

SELECT a.AddressID
FROM person.address(NOLOCK) AS a
JOIN sys.dm_tran_locks AS dtl
ON a.%%lockres%% = dtl.resource_description
WHERE dtl.resource_type = ‘KEY’

This query works and returns our key value of 432 just as you would want. But, take a look at the execution plan:

Yes, that’s a clustered index (or table, same thing) scan followed by a Sort followed by a merge join, processing 19614 rows to return one. But hey, it was only 341 reads. To say the least, I’m not excited about seeing this in a production system. This was explicitly cautioned in Kalen Delaney’s book. While it appears that the remote scan operator, which is how the DMV is accessed in this case, is 59% of the operation, that’s the estimated cost and has been pointed out before, isn’t the best measure of real cost in the system.

The development team went off and developed their own query, they had said they were looking for the key value, but evidently they were looking for who was holding the lock on a particular key value:

SELECT s.nt_user_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s
on l.request_session_id = s.session_id
inner join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
where OBJECT_NAME(p.object_id) = ‘Address’ and
l.resource_description in (select %%lockres%%
from person.Address(NOLOCK) a WHERE a.AddressID = 432)

I actually had to adjust their query just a bit to get it to work correctly, but basically they had the right idea. Here’s the final execution plan:

This was still not terribly inspiring a thing to think about running in a production system although it only had one scan and seven reads. Whether or not putting this in a transactional system is a good idea, it certainly adds yet another tool, albeit an undocumented one, to the tool belt.

The SSWUG Spring 2010 Virtual Conference is coming up.  On April 7, 8, and 9, attend a conference from the comforts of your living room, office, or couch.  And while you are at it you’ll get to sit on on some incredible presentations, interaction with speakers, chat with other attendees, make new contacts, lots of fun and surprises.

• More than 80 technical sessions
• These are NOT sales presentations.
• Live keynote presentations and interaction
• On-Demand access to sessions for 45 days – miss a session? No problem.
• Chat, Twitter Integration, SKYPE integration for Q&A
• Experience-based learning – find out what you need to know from people that are using the technology every day
• 6-Month SSWUG.ORG Membership (or membership extension), included!
• ALL-ACCESS Pass: SQL Server and Business Intelligence… SharePoint and .NET technologies – all included, one low price
• Great vendor hall – learn about the best tools, technologies, publications and partners out there for your shop
• Learning, learning and more learning

If this sounds as awesome as it is, register for the event here.  Among the presenters will be myself with the following topics:

Using XML to Query Execution Plans

SQL Server stores its execution plans as XML in dynamic management views. The execution plans are a gold mine of information. From the whether or not the execution plan will rely on parallelism to what columns are requiring a key lookup after a non-clustered index seek. Through a the use of XML this information can be available at your finger tips to help determine the value and impact of an index and guide you in improving the performance of your SQL Server databases.

Prerequisites

• Understanding of basic T-SQL coding
• Understanding of basic XQuery statements

Goals:

1. Discuss information available in an execution plan
2. Demonstrate use of XQuery to query execution cache
3. Demonstrate methods to performance tune

Getting To Know Your Indexes

Without proper indexing SQL Server can be hard pressed to create efficient and performant execution plans. Dynamic Management Views (DMV) and system views provide a slew of information about indexes that can be used to analyze indexes within SQL Server. In this session we’ll go under the hood of SQL Server to look at DMVs and system views to know what indexes you have, should have, and how they feel about the way applications are treating them.

Prerequisites

• Understanding of performance tuning needs
• Experience with Database design

Goals:

1. Identify methods to analyze current and potential indexes
2. Learn how to alleviate stress found on indexes
3. Demonstrate methods for tuning indexes

A Function By Any Other Name Is A Function, Right? Right?!?

User defined functions provide a means to encapsulate business logic in the database tier. Often the purpose of the encapsulation is to provide standard method access segments of data within the database. Unfortunately, not all methods of creating user defined functions are equal. In this session we’ll review the types of user defined functions and investigate the performance impact in selecting the different types

Prerequisites

• Understanding of basic T-SQL

Goals:

1. Identify purposes for creating user defined functions
2. Discuss the types of user-defined functions
3. Demonstrate performance impact in selecting different types of functions

Stop in an check it out, there will be a lot of great sessions and great conversation.

After setting up backups on a client’s servers, I started to get the error message below each night at approximately the same time.  Sometimes on the same database and other times on other database.  The only constant appeared to be the time in which the error occurred.

Processed 1 pages for database ‘MyDatabase’, file ‘MyDatabase_log’ on file 1. [SQLSTATE 01000] Msg 16943, Sev 16, State 4, Line 116 : Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] Msg 3014, Sev 16, State 1, Line 82 : BACKUP DATABASE successfully processed 329 pages in 1.141 seconds (2.362 MB/sec). [SQLSTATE 01000]

The error message is complaining that the schema for a table that is being used by a CURSOR has changed.  At 1 AM, this seemed to be a very bizarre event to be occurring.

It turns out that the error message actually had nothing to do with the backup commands, instead it was due to the CURSOR that was being used in the backup script to loop through the databases.  The backup script uses a progress table to monitor the backups as they are occurring and this table is used to populate the CURSOR.

Around the time the backup script failed, the index maintenance script had also started executing.  It just so happened that the index maintenance script was re-indexing the table that the CURSOR was based on.  A little re-coding around the use of the CURSOR resolved this issue.  And plans to remove the CURSOR entirely are forthcoming.

If you see error messages similar to this in your environment, try looking for CURSOR that are having their underlying tables defragmented.  This could very well be your issue.

It’s come down to a decision… do you want to learn about SQL Server and network with your peers or don’t you.  Then again, the question could also be do you want some swag and pizza with some work buddies.  Hopefully, you’ll be stopping in for the first item.

Out meeting is today starting at 2:30 PM for networking with announcements an presentations beginning at 3:00 PM.

This month we have Louis Davidson joining us for:

Database Design Fundamentals

In this session I will give an overview of how to design a database, including the common normal forms and why they should matter to you if you are creating or modifying SQL Server databases. Data should be easy to work with in SQL Server if the database has been organized as close as possible to the standards of normalization that have proven for many years. Many common T-SQL programming “difficulties” are the result of struggling against the way data should be structured and can be avoided by applying the basic normalization techniques and are obvious things that you find yourself struggling with time and again (i.e. using the SUBSTRING function in a WHERE clause meaning you can’t use an index efficiently).

And before that, I’ll be presenting:

ABCs of CTEs

Common Table Expressions (CTE) aren’t as common as their name implies. CTEs are often seen as a secret part of the dark art of recursion. This session will explore CTEs to show how they can be extremely useful in improving performance and legibility of T-SQL code. And, of course, we will look at their use in returning recursive data.

Hope to see you there.  If you have any questions, you can reach me on twitter.

Aaron Bertrand showed up to teach us tips and tricks for SQL Server Management Studio. We had to move our meeting night because of a conflict at our wonderful host, New England Tech. But we still had 12 people show up. For SNESSUG, that was a good turnout. I gave away some swag that I had received from Microsoft and some stuff that we had purchased. Bribary works (at least that’s my theory, so feel free to bribe me, whenever).

Aaron’s presentation was great. He’s just showing nothing but meat. There’s no fluff. He’s just showing a series of tips & tricks in SSMS and explains why you want to use them. First revelation, -nosplash has no effect whatsoever on load time. He called it a placebo. It just kept going from there. Aaron’s stated goal was to make everyone in the audience say “wow” or “cool” at some point during the presentation. I’m pretty sure he succeeded. The first one that got a lot of people is when he demonstrated setting the connection color so you can track different connections visually on your screen. My personal one was the Registered Servers import list so you can maintain a common list, move copies around, share registered server lists within your team… I love learning stuff at a good presentation.

Oh yeah, and everyone said “wow” or “cool” at least once.

A while ago I had read about the hidden threat of forwarded records.  These silent killers are like storm troopers in the bush waiting to get you when you least expect it.

And then the other day I saw a T-SQL Tuesday post by Sankar Reddy on (blog – twitter) on determining if your SQL Server is affected by forward records.  His post details what forwarded records are and how to repro and determine if they exist on a table.

Knowing about forwarded, I’ve monitored performance counters on these at clients throughout my engagements.  The performance counters can tell me if there is any performance impact being measured regarding forwarded records.  But what they can’t tell is what tables are causing these issues to occur.

So to that end, the following script is something I cooked up a while back to allow myself to check all of the heaps in a database and determine the count of forwarded records on those tables.  It uses a CURSOR, but that’s only because sys.dm_db_index_physical_stats doesn’t allow APPLY joins to it.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

IF OBJECT_ID('tempdb..#HeapList') IS NOT NULL
    DROP TABLE #HeapList

CREATE TABLE #HeapList
    (
    object_name sysname
    ,page_count int
    ,avg_page_space_used_in_percent float
    ,record_count int
    ,forwarded_record_count int
    )

DECLARE HEAP_CURS CURSOR FOR
    SELECT object_id
    FROM sys.indexes i
    WHERE index_id = 0

DECLARE @IndexID int

OPEN HEAP_CURS
FETCH NEXT FROM HEAP_CURS INTO @IndexID

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #HeapList
    SELECT object_name(object_id) as ObjectName
        ,page_count
        ,avg_page_space_used_in_percent
        ,record_count
        ,forwarded_record_count
    FROM sys.dm_db_index_physical_stats (db_id(), @IndexID,  0, null,  'DETAILED'); 

    FETCH NEXT FROM HEAP_CURS INTO @IndexID
END

CLOSE HEAP_CURS
DEALLOCATE HEAP_CURS

SELECT *
FROM #HeapList
WHERE forwarded_record_count > 0
ORDER BY 1

It’s that time of the month again.  Time to network and learn about SQL Server with your peers.

Please click here for meeting details and to RSVP.

Registration has changed with the move from our previous hosting site and you will be required to log into www.sqlpass.org in order to register for our events. If you have any issues with this, please contact support@mnssug.org.

ABCs of CTEs
Jason Strate

Common Table Expressions (CTE) aren’t as common as their name implies. CTEs are often seen as a secret part of the dark art of recursion. This session will explore CTEs to show how they can be extremely useful in improving performance and legibility of T-SQL code. And, of course, we will look at their use in returning recursive data.

Database Design Fundamentals
Louis Davidson

In this session I will give an overview of how to design a database, including the common normal forms and why they should matter to you if you are creating or modifying SQL Server databases. Data should be easy to work with in SQL Server if the database has been organized as close as possible to the standards of normalization that have proven for many years. Many common T-SQL programming “difficulties” are the result of struggling against the way data should be structured and can be avoided by applying the basic normalization techniques and are obvious things that you find yourself struggling with time and again (i.e. using the SUBSTRING function in a WHERE clause meaning you can’t use an index efficiently).

Update: This post is participating in the 4th installment of the T-SQL Tuesday hosted this time by Mike Walsh.

One of the side affects of having Heaps in a system is the possibility of generating forwarded records. When reading data from a heap, Forwarded records (if they are present in a heap) can generate extra, random and potentially physical IOs when compared to a table with clustered index and this may hurt the performance if one has to read lot of data from that heap. Note that having a heap doesn’t mean it is guaranteed to have forwarded records. Enough has been said and blogged about the details of forwarded records already and in this post I am NOT going to rehash the details of forwarded records and how it affects the performance but will go over an example and a script that will help to find out if forwarded records were used to fetch the data in the heap. Armed with that information one can see the true cost of these extra IOs generated by forwarded records. Looking at sys.dm_db_index_physical_stats shows if forwarded records are present in the heap but it doesn’t necessarily tell if the workload is indeed affected by them, how much and how many times. In the script I am going to show at the end of this post, one can see how many times forwarded records have been used to retrieve the data and understand the total cost of *extra IOs* and the performance implications of forwarded records.

I will be borrowing Kalen’s example for this test and here is the script for the test data.
USE 
AdventureWorks
GO
IF OBJECT_ID ('Details', 'U') IS NOT NULL
DROP TABLE Details;
GO

/*Table 'Details'. Scan count 1, logical reads 87117, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*/
So far we have seen the existence of forwarded records using sys.dm_db_index_physical_stats and the number of logical reads go up using SET STATISTICS IO ON. The next query will tell you how many times forwarded records have been traversed to satisfy the queries against the heap.

SELECT

DB_NAME(database_id) AS database_name

, OBJECT_NAME(OBJECT_ID) AS OBJECT_NAME

, forwarded_fetch_count

FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks'), OBJECT_ID('dbo.Details'), NULL, NULL)

In our example, our heap had 84408 forwarded records and so far the entire heap was traversed 2 times 84408 * 2 = 168816
/*
database_name  object_name forwarded_fetch_count
AdventureWorks Details     168816
*/


Before we take off, lets take the forwarded records for a spin and see if the DMV sys.dm_db_index_operational_stats displays the true count of how many times these forwarded records are used.

SELECT * FROM dbo.Details;

GO 10

SELECT

DB_NAME(database_id) AS database_name

, OBJECT_NAME(OBJECT_ID) AS OBJECT_NAME

, forwarded_fetch_count

FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks'), OBJECT_ID('dbo.Details'), NULL, NULL)

So far our heap was traversed 2 times and we spinned the table another 10 times above with a total of 12 * 84408 forwarded records = 1012896.
/*
database_name  object_name forwarded_fetch_count
AdventureWorks Details     1012896
*/


If forwarded records are a new concept for you then I would urge you to look at these excellent posts by Kalen, Paul and Simon on this topic. This post is participating in the 4th installment of the T-SQL Tuesday hosted by Mike.
whats-worse-than-a-table-scan
fragmentation-and-forwarded-records-in-a-heap
Forwarding-and-forwarded-records-and-the-back-pointer-size
INSERT and UPDATE loading practice – The impact of forwarding pointers.

                          

Update: This post is participating in the 4th installment of the T-SQL Tuesday hosted this time by Mike Walsh

.SQL Server 2005 came few years ago with lots of optimizations and in this post I will add some details on one of the lesser known IO enhancement that wasn’t present in previous versions.

CHECKSUM was introduced as a PAGE_VERIFY option starting from SQL Server 2005 and the two widely known options are PAGE CHECKSUM and BACKUP CHECKSUM. The other kind and the lesser known option is log block CHECKSUM.

What is a log block?

The smallest unit of data storage in SQL Server is a page which is 8 kb in size. SQL Server manages space as Extents, which are made of eight contiguous pages. When it comes to Transaction Log, the fundamental unit of IO is a log block. The size of the log block is *NOT* fixed and it varies from 512 bytes to 60 kb. Virtual log files known as VLFs are made up of log blocks. And the Transaction Log is made up many VLFs, which are variable in size. For more details on the log block, refer Paul’s post listed below.

How do I turn on log block CHECKSUM?

PAGE CHECKSUMs are enabled using the ALTER DATABASE PAGE_VERIFY option. Log block CHECKSUMs are enabled automatically when the CHECKSUM option is set for PAGE_VERIFY. Note that turning on TORN_PAGE_DETECTION has no effect on the log block and is NOT applicable.

How does log block CHECKSUM works?

When SQL Server commits a transaction and issues a write for the transaction log, just before the log block is written to disk the CHECKSUM over the log block is computed and stamped on the header of the log block very similar in nature to the data page. When the transaction log is read during the backup and restore (and other activities listed in Paul’s post below), the CHECKSUM value is re-computed and checked against the previously written value in the header.

How does log block CHECKSUM help?

If the CHECKSUM value of the log block doesn’t match with the existing CHECKSUM value then IO subsystem has compromised your data. One has to check and validate the IO Subsystem extensively to find the root cause of the problem.

Does Transact SQL and PAGE/BACKUP/Log Block CHECKSUM use the same algorithm?

No. Transact SQL CHECKSUM algorithm is different from the PAGE/BACKUP/Log Block CHECKSUM, which uses simple a yet efficient algorithm compared to T-SQL CHECKSUM.

                          

From SQL Server 2005 onwards, CHECKSUM is available as a PAGE_VERIFY option at the database level. All databases created in SQL Server 2005 & above will have CHECKSUM enabled by default. Even tempdb in SQL Server 2008 has this option enabled. It is recommended by MSFT to change the PAGE_VERIFY option of databases to CHECKSUM when migrating from earlier versions to SQL Server 2005 and above.

Why should I enable PAGE CHECKSUM?

CHECKSUM has a robust algorithm compared to TORN_PAGE_DETECTION option which was the default in previous versions. Database pages can get corrupted outside the scope of SQL Server and CHECKSUMS are a great way to detect corruption caused by IO subsystems. When a dirty database page is written to disk, SQL Server computes the CHECKSUM of the contents of the page and stamps the value in the page header just before the page is written to disk. When that page is read back into memory, SQL Server re-computes the CHECKSUM and if that value doesn’t match with the value stamped on the header then someone else wrote to this page outside the scope of SQL Server. By enabling CHECKSUMs one should be able to isolate the IO subsystem as the root cause of corruption.

The key to un-covering corruption above is, only if the page is read back from the disk. What happens if the page is *NOT* read back for a week or say a month. Corruption will be your house guest and you may not notice it and there is a potential chance that you may end up with loosing data.

Why should I use BACKUP CHECKSUM?

When BACKUPs are taken with the CHECKSUM option, SQL Server will verify and re-compute the CHECKSUM/ TORN_PAGE_DETECTION of all pages and gives you extra protection that PAGE CHECKSUMS can’t handle. This comes very handy if the PAGE_VERIFY is set to CHECKSUM or TORN_PAGE_DETECTION. Even if both the options are turned off then BACKUP CHECKSUM will still compute a combined CHECKSUM of all pages and stamp the value on the backup media. This can be used to re-compute the CHECKSUM before restoring to make sure the backup itself is intact.

Impact of using PAGE CHECKSUM:

Linchi Shea did some testing on this topic a while back and I strongly recommend you to visit his work at Performance impact of enabling page checksum and default trace. As per Linchi and MSFT, the total cpu cost of enabling PAGE CHECKSUM is very small and mostly in the range of 1-2%.

Impact of using BACKUP CHECKSUM:

BOL mentions there is a performance penalty for enabling the BACKUP CHECKSUM and here is a quote from BOL.
“ When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup. Due to the overhead verifying and generating backup checksums, using backup checksums poses a potential performance impact. Both the workload and the backup throughput may be affected. Therefore, using backup checksums is optional. When deciding to generate checksums during a backup, carefully monitor the CPU overhead incurred as well as the impact on any concurrent workload on the system. ”

I was curious to measure the impact of the BACKUP CHECKSUM and went ahead with 6 different options listed below with my testing. SQL Server 2005 with Service Pack 2 is used and I measured the performance using SQL Profiler. I also did similar testing on SQL Server 2008 with SP1 with almost identical results on small databases. I wish I had better hardware on SQL Server 2008 instance.

1) Database with PAGE_VERIFY set to NONE & use BACKUP WITH CHECKSUM
2) Database with PAGE_VERIFY set to NONE & use BACKUP WITH OUT CHECKSUM
3) Database with PAGE_VERIFY set to TORN_PAGE_DETECTION & use BACKUP WITH CHECKSUM
4) Database with PAGE_VERIFY set to TORN_PAGE_DETECTION & use BACKUP WITH OUT CHECKSUM
5) Database with PAGE_VERIFY set to CHECKSUM & use BACKUP WITH CHECKSUM
6) Database with PAGE_VERIFY set to CHECKSUM & use BACKUP WITH OUT CHECKSUM

Environment:

SQL Server 2005 SP2 64 bit Developer Edition.
4 Processors, 8 GB RAM, RAID 5

Testing:
Each data point has been run 3 times and the average of them is considered. CPUTime and Duration are measured for every test using Profiler. A new database is created with the right PAGE_VERIFY option and data is populated for every test. These tests are done for databases with 4, 8, 16, 32 GB.

The results were startling to me and I didn’t expected the results.

BACKUP WITH CHECKSUM vs BACKUP when PAGE_VERIFY is set to NONE (least recommended)

PAGE VERIFY NONE

BACKUP WITH CHECKSUM vs BACKUP when PAGE_VERIFY is set to TORN_PAGE_DETECTION

PAGE VERIFY TORN PAGE DETECTION

BACKUP WITH CHECKSUM vs BACKUP when PAGE_VERIFY is set to CHECKSUM

PAGE_VERIFY CHECKSUM

BACKUP CHECKSUM when PAGE_VERIFY is set to NONE, TORN_PAGE_DETECTION & CHECKSUM

PAGE VERIFY OPTIONS

From the above results, the least expensive method is when PAGE_VERIFY is set to CHECKSUM and the backups are taken with CHECKSUM option and the most expensive is when TORN_PAGE_DETECTION is set for PAGE_VERIFY.

So, Do I recommend adding CHECKSUM option while taking the backups? Definitely if you have cpu cycles to spare then I would highly recommend adding this option [CHECKSUM] to the BACKUP or if you don’t have enough confidence on your IO subsystem. In this post I tried to show the actual cost of adding this option so that one can take an informed decision about the added cost. Also, note that BACKUP CHECKSUM is *NOT* a replacement to run the full-fledged DBCC CHECKDB which does a lot more checking to identify corruption.

Acknowledgements:
Performance Impact of Enabling Page Checksum and Default Trace
Example 2000/2005 corrupt databases and some more info on backup, restore, page checksums and IO errors
How do I know if my earlier backups used CHECKSUM?
Checksum in SQL2005