Sajal Dam, Grant Fritchey
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.
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.
MS SQL Server
Sybase SQL Anywhere
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
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.
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:
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 :-)
Two books that I highly recommend:
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:
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?
I would definitely recommend:
Microsoft SQL Server 2008 Internals: goes deep but does not neglect the fundamentals. It is a must read.
Professional SQL Server 2008 Internals and Troubleshooting: similar to the first book, but with a slightly more performance based focus.
SQL Server 2008 Query Performance Tuning Distilled: great techniques for identifying and fixing poorly performing queries, from the metal to the application.
If you have more of a development focus:
Expert SQL Server 2005 Development (SQL Server 2005 but still relevant for SQL Server 2008)
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.
Using SQL Server Profiler (2005 Version)
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:
I've also been very satisfied with the Head First series for introducing new concepts:
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.
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?
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.