SQL Consultant Resources

Welcome to our SQL Server resources and links. Below are all kinds of SQL Server resources we have found useful for solving SQL Server problems.

If you are having a problem with SQL Server, sometimes all you need is the answer to a question to solve the problem yourself. For a free 15 minute consultation with a Microsoft-certified SQL consultant, give us a call at 877.201.3428 toll-free. If 15 minutes of our time can help you out, we are glad to give it to you.

If you have a SQL Server website, you can give visitors to your website the opportunity to access the information here and like you, they can also take advantage of a free consultation with a Microsoft-certified SQL consultant. Just click below for instructions for linking your site to the SQL information and expertise available here.

Resource Page Contents

Resources for IT Managers and Executives

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.

Books

Secrets and Lies: Digital Security in a Networked World
Bruce Schneier
Required reading for anyone who is responsible for data security. Like The Social Life of Information, this is another big picture treatment of a subject in which we are too likely to get ensnared in details.

IT Doesn't Matter
Nicholas G. Carr
With the publication of this provocatively-titled article in the May, 2003 issue of The Harvard Business Review, Nicholas Carr stirred up a storm of animated dialog among both critics and supporters. So far as we can tell, all copies of the article have been removed from the web, possibly because Carr now offers an expanded version of it for sale in book form. This link points to Carr's website where the book can be obtained.

A measure of the impact of Carr's article is the number of prominent IT experts who have responded to it. Search the web on "IT doesn't matter" to find some of the differing opinions.

Links

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.

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 consultant, programmer, writer and trainer for 35 years. 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.

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.

WhitePapers and tools

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.

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

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.

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 diagnostic tools for download

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 an invaluable tool for developers, dba's, etc. 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 or 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.