SQL Server 2008 Query Performance Tuning Distilled

Sajal Dam, Grant Fritchey

Mentioned 9

SQL Server 2008 Query Performance Tuning Distilled presents a direct trouble–shooting methodology for identifying poorly-performing stored procedures and queries, isolating the causes of that poor performance, and fixing the underlying problems. Each chapter is dedicated to one of the top causes of poorly performing queries and shows methods for identifying and dealing with the problems in that chapter’s domain. Emphasis is always put upon or placed upon practical methods that you can put to immediate use in your day–to–day work. SQL Server 2008 functionality, tips, and tricks are emphasized in each subject area. Emphasizes the practical. Does not bury readers in theory. Gives readers practical techniques to immediately apply in their daily work. Dedicates a chapter to each of the most common, performance–related problem areas. What you’ll learn Identify and fix poorly performing queries that are hurting your business. Read query execution plans and identify bottlenecks in performance. Record system performance metrics for trend analysis. Learn to design databases and write Transact–SQL code to avoid common problems. Understand what an index is, what it does, and how to build one. Use industry accepted best practices and understand how to develop your own to arrive at optimal database performance. Who this book is for SQL Server Query Performance Tuning Distilled is aimed at anyone writing business–critical Transact–SQL queries, and also at those responsible for the continued good performance of those queries. Developers, database administrators, business intelligence analysts, and any others who develop Transact–SQL queries will find this book an indispensable resource for getting the most out of what SQL Server 2008 has to offer.

More on Amazon.com

Mentioned in questions and answers.

What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area?

The idea of this question is to collect the shed load of resources that invariably exist, so that people can have a "one stop" knowledge shop of the good, peer approved resources.


General SQL

PostgreSQL (wiki) (PGsearch)

MySQL

Oracle

MS SQL Server

Sybase SQL Anywhere

JDBC

I'd start out by understanding how the database works at a fundamental level. How is data stored on disk, what does creating an index do, how does query plan optimization work, how are plans cached, when to cached plans expire.

If you can commit all that to memory, most of the advice about tuning seems obvious.

Here's a great book for MSSQL

SQL Server Internals

Xaprb is a must-read blog for MySQL DBAs. The author has written a book on high-performance MySQL

For the happy few working with Sybase SQL Anywhere I can only recommend Breck Carter's blog and his SQL Anywhere Studio 9 Developer's Guide

For Microsoft SQL, I'd recommend the books by Kalen Delaney (et al) called "Inside SQL Server". They offer a good insight into the internals of SQL Server, thus allowing readers to educate themselves on why particular statements might be faster than others.

Inside SQL Server 7.0
Inside SQL Server 2000
Inside Microsoft SQL Server 2005
Microsoft SQL Server 2008 Internals

There's also a book dedicated to performance tuning of SQL Server 2008 queries: SQL Server Performance Tuning Distilled

I also like the blogs by Paul Randal and Kimberly Tripp on SQLSkills.com. They are full of solid SQL advice:

Paul's blog
Kimberly's blog

For a current project I'm creating a data model. Are there any sources where I can find "best practices" for a good data model? Good means flexible, efficient, with good performance, style, ... Some example questions would be "naming of columns", "what data should be normalized", or "which attributes should be exported into an own table". The source should be a book :-)

Personally I think you should read a book on performance tuning before beginning to model a database. The right design can make a world of difference. If you are not expert in performance tuning, you aren't qualified to design a database.

These books are Database specific, here is one for SQl Server. http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr_1_1?s=books&ie=UTF8&qid=1313603282&sr=1-1

Another book that you should read before starting to design is about antipatterns. Always good to know what you should avoid doing. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=books&ie=UTF8&qid=1313603622&sr=1-1

Do not get stuck in the trap of designing for flexibility. People use that as a way to get out of doing the work to design correctly and flexible databases almost always perform badly. If more than 5% of your database design depends on flexibility, you haven't modeled correctly in my opinion. All the worst COTS products I've had to work with were designed for flexibility first.

Any decent database book will discuss normalization. You can also find that information easily on the web. Be sure to actually create FK/PK relationships.

As far as naming columns, pick a standard and stick with it consistently. Consistency is more important than the actual standard. Don't name columns ID (see SQL antipatterns book). Use the same name and datatypes if columns are going to be in several different tables. What you are going for is to not have to use functions to do joins because of datatype mismatches.

Always remember that databases can (and will) be changed outside the application. Anything that is needed for data integrity must be in the database not the application code. The data will be there long after the application has been replaced.

The most important things for database design:

  • Thorough definition of the data needed (including correct datatypes) and the relationships between pieces of data (including correct normalization)
  • data integrity
  • performance
  • security
  • consistency (of datatypes, naming standards etc.)

Are there any tools that do Query Optimizations for SQL Server 2005 and above?

I've searched & didn't find anything.

What do you recommend?

If this Question was repeated before you can close it but I didn't see anything similar

As John Saunders commented, the best tool at your disposal is your own mind. Following bernd_k's comment, here are a few suggestions for sharpening that tool.

I've been developing and administering Oracle database apps for many years, and would like to learn SQL Server. Does anyone have any book recommendations (preferably electronic). Naturally enough I already know SQL inside/out so I'd like to avoid any intro stuff and go straight to the meat, but not skip any basic SQL Server things I'd learn from an intro book. Any suggestions?

tia

I would definitely recommend:

If you have more of a development focus:

There are also some very good, free electronic books from Redgate:

to list a few.

One of my jobs it to maintain our database, usually we have troubles with lack of performance while getting reports and working whit that base.
When I start looking at queries which our ERP sending to database I see a lot of totally needlessly subselect queries inside main queries.
As I am not member of developers which is creator of program we using, they do not like much when I criticize they code and job. Let say they do not taking my review as serious statements. So I asking you few questions about subselect in SQL

Does subselect is taking a lot of more time then left outer joins?
Does exists any blog, article or anything where I subselect is recommended not to use ?
How I can prove that if we avoid subselesct in query that query is going to be faster ?

Our database server is MSSQL2005

"Show, Don't Tell" - Examine and compare the query plans of the queries identified using SQL Profiler. Particularly look out for table scans and bookmark lookups (you want to see index seeks as often as possible). The 'goodness of fit' of query plans depends on up-to-date statistics, what indexes are defined, the holistic query workload.

Run the queries in SQL Server Management Studio (SSMS) and turn on Query->Include Actual Execution Plan (CTRL+M)

Think yourself lucky they're only subselects (which in some cases the optimiser will produce equivalent 'join plans') and not correlated sub-queries!

Identify a query that is performing a high number of logical reads, re-write it using your preferred technique and then show how few logicals reads it does by comparison.

Here's a tip. To get the total number of logical reads performed, wrap a query in question with:

SET STATISTICS IO ON
GO

-- Run your query here

SET STATISTICS IO OFF
GO

Run your query, and switch to the messages tab in the results pane.

If you are interested in learning more, there is no better book than SQL Server 2008 Query Performance Tuning Distilled, which covers the essential techniques for monitoring, interpreting and fixing performance issues.

I have a stored proc in an SQL Azure database. If I run this stored proc via SSMS it always takes around 1 second to run. If I run from a web role application running on Azure with an SqlCommand object it often (not always) runs slowly and eventually times out. What would be the different from running via SSMS or a SqlCommand?

SQL Server will choose and cache an execution plan for the stored procedure. This plan may be optimized for a given set of parameters, and be a poor choice for others.

You should review the execution plan it is choosing and assess whether it's adequate for your most common or most heavy queries.

Possible solutions include creating (or dropping) indexes as necessary, or using optimization hints.

Query optimization is more complex and subtle that it would seem at first. Some helpful references:

There's a gap in my SQL knowledge I'd like to fill and I'm after recommendations on where to find resources, eg. websites, how-tos, books, etc.

I've been using SQL databases for a long time. I'm quite comfortable with: basic SQL and its syntax; creating tables and indexes; inserting data; and basic DBMS maintanenance.

Where I struggle is getting the data out for anything beyond basic SELECT statements. I've recently got my head around some basic INNER JOINs and GROUP BY so I can use commands like SUM and MAX. But the "SQL way", ie. set logic, eludes me. Is there a solid overview of this stuff so I can properly understand set logic, JOINs (all types), nested SELECTs, WITH, CROSS APPLY, ROW_NUMBER(), etc.

I've found a couple of good questions on SO (here and here). But the answers are a bit too general. I don't want a beginners guide to SQL or databases. I get that. I'm after stuff on the relatively narrow topic of smart queries. Vendor-agnostic stuff would be preferable. Many thanks.

In summary, my question is: can you recommend some good resources (online or print) to help me learn how to take my SQL queries beyond the basic level. That is, something other than introductory/basic texts. As I said, I'm comfortable with the basics, I need intermediate to advanced coverage of SQL queries.

UPDATE 2009-10-12 re Selected Answer: A wide variety of answers, mostly recommending books, rather than online resources, which was a bit of a surprise to me. Many authors were recommended (here and in other SO questions). The two most popular were Chris Date and Joe Celko. It was difficult to pick not having read either. What settled it in the end was DBA friend had a copy of SQL for Smarties I could borrow, but nothing from Date.

This should serve as a good introductory text:

Book Cover

Sams Teach Yourself SQL in 10 Minutes

I've also been very satisfied with the Head First series for introducing new concepts:

alt text

Head First SQL: Your Brain on SQL -- A Learner's Guide

The Manga Guide to Databases by Takahashi and Azuma is surprisingly helpful in conveying the basic concepts. I've been working with SQL for years, and read this to see if it might help prepare one of my sons for a database course this semester. I was very impressed with the amount of material covered, and the clarity and effectiveness of the presentation. Goofy, but quite good.

link text

Suggest you look for some performance tuning books for the database you are working with. They tend to cover advanced concepts. Something like http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr_1_3?ie=UTF8&s=books&qid=1255021788&sr=8-3

Anyone have any recommendations for books about performance tuning on either frontend or backend? I'm looking specifically for asp.net or sql (MS-SQL) performance tuning, but anything is good to go. Thanks!

Can you please recommend resources to write efficient store procedures, refactor and optimize them?

Thank you.

SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey is a great tool for advancing your performance tuning skills. He is renowned in the SQL Server community as an expert.