SQL Server resources and links

Below are SQL Server resources we have found useful for solving SQL Server problems. If you are a SQL Server consultant or the person who must deal with the SQL Server problems in your organization, we hope that you too may find them useful.

SQL Server Technical Resources

Books

Like most information technologies, SQL Server is evolving so fast that long before a book can be written and published, the information in it is at least partially obsolete. That is why we recommend getting most of your detailed technical information from web sources or monthly periodicals. Still, there are very useful books out there that deal in concepts and technologies that are slow to change. Those books often remain relevant for years.

We have updated the following reviews for the 2005 release of SQL Server but we have not had the time to make a fair sampling of the other books available. When we do find something else we can recommend, we will add it here.

MicroSoft SQL Server 2005 Performance Optimization and Tuning HandBook

Ken England, Gavin Powell

In the past I could only speak in superlatives of Ken England's work. I thought this book was a thorough and practical handbook for finding and fixing performance bottlenecks in the SQL Server environment, useful to experts as well as those with intermediate expertise. Query optimization, indexing, locking, hardware, configuration, etc. It was all there in a concise and readable form.

I was so enthusiastic that the publishers placed my review of the SQL Server 2000 edition on the back cover of this new edition. I am thankful for the publicity and flattered by the recognition. But frankly, I do not have the same opinion of this newest version.

The book has been updated for SQL 2005 and expanded by a hundred pages of new material. Unfortunately Ken England took no part in the update and the new material is full of errors and omissions. It came as an unhappy surprise to Ken when he saw his name on the front cover of this book. I am not crazy about having my name on the back cover either.

Review by Kurt Survance

SQL Tuning

Dan Tow

Dan Tow's book is a platform-independent treatment of tuning SQL queries in a systematic and scientific manner. It is above all about determining the optimum order in which the query engine should access the tables involved. One may quibble that join order is only a part of the whole tuning problem and most of the time the query optimizer gets it right anyway. While this is true, it misses the point.

The fact that the optimizer gets it right so often allows us to take join order for granted. But how are we sure the optimizer got it right unless we know what the optimum join order is? That is what this book teaches, a methodology and an elegant system of notation that allows us to positively determine the optimum join order of the most complex query. As the author points out, the number of possible join orders increases factorially with the number of tables involved. An 8 table join has 40,320 possible join orders. That rules out trial and error for all but the simplest queries.

It also turns out that analyzing and diagramming according to Tow's method gives us a deeper architectural understanding of the query and the ability to recognize patterns in queries whose similarities are not readily apparent.

Inside Microsoft SQL Server 2005

Kalen Delaney

Originally authored by Ron Soukup, project lead for the original SQL Server development team, Inside SQL Server was a single volume, widely considered to be the best book written on SQL Server internals. As of this edition, it is no longer a single book; it is a lot of books, a set of encyclopedia, if you will.

Kalen Delaney took over from Ron Soukup a couple of versions ago and Ron's unique knowledge and experience have been notably absent in later editions. However, Ms. Delaney has proven to be equal to the intimidating task of keeping everything updated as SQL Server has grown into a huge, multi-faceted product.

Don't look here for insights or interesting anecdotes. This is a compendium of facts, broken into separate volumes for the 2005 release. We have browsed several of the books but we have not, and do not intend to read it in it's entirety. (Who has time?) If you want a complete print reference for all things SQL Server, this is it.

Links

Novick Software Andy Novick has been a SQL Server consultant, programmer, writer and trainer for decades. He is the author of SQL Server User-Defined-Functions as well as countless articles and white papers on all aspects of SQL Server. All of these are available on his site. This is a very useful site in the best traditions of the early world wide web with free and open access to information. You don't have to log in or give any personal information and you won't be spammed by advertisers.

SQLSecurity.com The motto on their home page reads, "There is no patch for stupidity". Fortunately, there is a patch for ignorance and that is to keep ourselves informed. This site is focused on information about securing SQL Server. It also has some downloadable tools such as SQLPing

SQLTeam.com An excellent site for code and technical articles. Submit your best work and take home some very clever code contributed by others.

SQL Server Central A wealth of good information. Excellent library of T-SQL scripts sorted by function and SQL Server version. It now requires registration for a free membership.

return to top

SQL Server technical Articles

Here is a collection of SQL Server technical papers that we have found useful. Hopefully they will also prove useful to you. We are constantly updating this section, so check back frequently. If you have some suggestions about links to include here, let us know.

What the IT Decision-Maker Needs to Know About SQL Server Performance Tuning Kurt Survance, President, SQL Consulting, Inc

Here is a link to an article by SQL Consulting's president, Kurt Survance. The article was recently published at SQL-Server-Performance.com. The article is written for someone with a working knowledge of information technology, but with little specific SQL Server expertise. The typical reader would be an IT manager or executive who needs background information to properly evaluate all the performance tuning suggestions being offered to him/her before spending significant amounts of money on performance tuning.

Is your Java driver killing performance? If you are connecting a java application to SQL Server using the default configuration settings for the Java database driver, it could be killing application performance. Here is how to fix it. This short article by SQL Consulting president Kurt Survance was originally published by SQL-Server-Performance.com PAE and 3GB and AWE, Oh My!

Here is a paper by Chad Boyd that covers just about everything you need to know about setting up or troubleshooting AWE memory in SQL Server. Recommended reading.

Recovery

Recovering the master database Dealing with a Suspect database

All DBAs should know how to restore a user database from a backup, but here are a couple of recovery scenarios that are less well known.

Performance Monitoring

How to Perform a SQL Server Performance Audit

This is a link to a series of articles by Brad McGehee. Together, they give a nicely detailed methodology for performing a comprehensive performance audit of SQL Server. The methodology is similar to the one we use at SQL Consulting, Inc. for auditing the performance of our clients' SQL Servers.

SQL Server locking

How to monitor SQL Server blocking Understanding and resolving SQL Server blocking problems

The first link gives you the code for sp_blocker_pss80, a diagnostic procedure that captures SQL Server locking, blocking and wait state information. The second is a whitepaper that shows you how to interpret the detailed output. sp_blocker_pss80 was written for SQL 2000 but it also works in SQL Server 2005. SQL 2005 has some data management views that produce much of the same output, but we continue to use this out of habit. The information it provides is pure gold if you need to diagnose and fix performance problems caused by blocking.

SQL Server lock types SQL Server wait types

These links contain more information that is useful in understanding and resolving locking problems.

SQL Server tools for download

SQLPing 3

SQL Server installations are becoming increasingly difficult to discover, assess, and maintain in the enterprise due to the proliferation of personal firewalls, etc. SQLPing 3.0 is designed to remedy this problem by combining all known means of SQL Server/MSDE discovery into a single tool which can be used to ferret-out servers you never knew existed on your network so you can properly secure them. SQLPing also does brute force password cracking to identify weak passwords. Requires .NET Framework v2.

OSTRESS and Read80Trace There are a number of full-featured third-party tools that allow you to simulate a realistic user load on your database, but none come at a better price than OSTRESS. Microsoft supplies this tool free for download from the link above. With this simple command line utility, you can simulate any number of users executing single statements or sql scripts any number of times in your database. It's good for analyzing blocking, deadlock or performance problems in a multiuser environment. This is and invaluable tool for developers, DBAs and others who don't have the budget (or perhaps the need) for a more sophisticated tool.

In addition, you get Read80Trace in the same download. Read80Trace reads, summarizes, and presents an analysis of trace files of any size. It's purpose is similar to the trace-scrubbing tools presented below. However, it does have the addiional capability of breaking up a trace into individual sessions that can be replayed via multiple OSTRESS sessions to exactly simulate user behavior against a backup version of the database. Powerful stuff, but Read80Trace does not work with SQL Server 2005 and so far it is unknown if or when a new version will be available. Read80Trace's capabilities, as described in the documentation, seem impressive but, to be honest, we haven't tried it yet. We use the trace scrubbing tools below because they are fast, simple, and they do what we need done. SQL Profiler Trace Scrubbing tools

To solve the problem of large trace analysis, Andrew Zanevsky developed this trace-scrubbing process that strips less significant details of all SQL Profiler trace records and allows grouping by transaction type. For example, if the trace file contains 50,000 records about execution of the same stored procedure (each with different parameters), then the process groups them into one line with duration, CPU, reads and writes–all totaled and averaged. This makes it easy to quickly identify the transaction types that are responsible for a large percentage of overall system utilization.

SQL 2005 has some data management views and functions that replace a large part of the functionality but we continue to use this for SQL 2000 installations. Right now we are trying to find time to write a paper summarizing the use of some key DM views and functions. Check back later.

PSSDIAG data collection utility

PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output.

SQLDiag, a similar tool, is now included in SQL Server 2005, so for SQL diagnostics, you probably don't need PSSDiag. However, Microsoft Product Support will often require output from PSSDiag. Our advice is wait until they tell you that you need it. They will send it to you along with a configuration file designed for your environment. Since creating this file is the most difficult part of using the tool, let them do it.

return to top

home about us contact us email us SQL Server Newsletter

Copyright © 2004-2010 SQL Consulting Inc. All rights reserved