Month / June 2006

by Arvind Shyamsundar

Resources from TechEd sessions

Several attendees have asked for the resources section from our presentations at TechEd India. Enjoy!


Blogs
SQLCAT: http://blogs.msdn.com/sqlcat
WINCAT: http://blogs.msdn.com/wincat
Slava Oks’ blog: http://blogs.msdn.com/slavao


Product Feedback
http://lab.msdn.microsoft.com/productfeedback/


External Links:
NUMA FAQ: http://lse.sourceforge.net/numa/faq


Books:
Inside Microsoft SQL Server 2005: Query Processing and Optimization (Volume 3)
The Guru’s Guide to SQL Server Architecture and Internals (SQL Server 2000)


SQL Server 2005 – Operations 
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx


Whitepapers
Troubleshooting Performance Problems in SQL Server 2005 (http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx)
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005(http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
Statistics Used by the Query Optimizer in SQL Server 2005 (http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx)
SQL Server 2000 I/O Basics (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx)


Webcasts
Troubleshooting Performance Problems In SQL Server 2005 (http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032275646&EventCategory=5&culture=en-US&CountryCode=US)
Performance Diagnosis in SQL Server 2005 (http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032275586&EventCategory=5&culture=en-US&CountryCode=US)
Supportability features for SQL Server 2005
(http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032275593&EventCategory=5&culture=en-US&CountryCode=US)


PASS 2005 PSS Service Center Labs
http://www.microsoft.com/downloads/details.aspx?FamilyID=9b8098a7-e75f-462a-b296-e80199c9f323&displaylang=en

Advertisement
by Arvind Shyamsundar

Wait types in SQL Server

Troubleshooting performance issues in SQL Server involves studying the wait types and wait times associated with the queries executing. However, the relevance of each of the various wait types in the output can be a mystery.


For SQL Server 2000, the cumulative wait types can be observed using the DBCC SQLPERF(WAITSTATS) command. To clear the current wait times, use DBCC SQLPERF(WAITSTATS, CLEAR). The KB article The waittype and lastwaittype columns in the sysprocesses table in SQL Server 2000 explains the more common ones. A more comprehensive reference is found in Tom Davidson’s excellent article Opening Microsoft’s Performance-Tuning Toolbox. The article’s code download has an extremely useful table which details almost all the wait types and explains their correlation with the performance counters.


For SQL Server 2005, we have a good reference in SQL Books Online itself, as part of the sys.dm_os_wait_stats reference documentation. And if you haven’t read it yet, don’t waste a minute and read the excellent Troubleshooting Performance Problems in SQL Server 2005 white paper.

by Arvind Shyamsundar

Database Snapshots

DB Snapshots are a very useful new feature in SQL Server 2005 Enterprise Edition. They provide us the ability to preserve a consistent picture of the database as it was at a point in time. Both metadata and data are captured in the snapshot.


 


The main uses of snapshots are for preserving views of the database for reporting purposes, and also to easily recover data affected by user error. Snapshots also provide a means to view the data in the mirror database.


 


DB Snapshots use “sparse files”, which means that the actual snapshot size on disk will be very small initially and would only grow by recording the the changes to the database using a copy-on-write scheme.


 


Another cool feature of snapshots for recovering from administrative or user error is the ability to revert a database back to a snapshot. This is potentially much quicker than restoring a full backup, as we only copy the changed pages from the snapshot back into the data files. But there are other implications of reverting to a snapshot – most important being the fact that we rebuild the log in such cases. To ensure recoverability of all transactions committed after the snapshot was taken, be sure to backup the log prior to using this feature.