Sql Server General performance
SQL SERVER 2005
Statistics used by SQL 2005
As we know, existence and the subsequent updates of statistics is a very important factor governing SQL Server query performance. However, the implementation details are different in SQL 2005. Understand the nuances by referring to this article https://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
Troubleshooting Performance Problems in SQL Server 2005 - Whitepaper https://sqlcat.com/whitepapers/archive/2007/11/19/troubleshooting-performance-problems-in-sql-server-2005.aspx
Caching in SQL Server 2005 and how it impacts performance
SQL Server, just like any other RDBMS, tries to minimize disk I/O. It achieves this balance by caching the data in memory. However, since memory is a limited resource, it is imperative that you implement the best practices to maximize your use of it. This is an excellent white paper that talks about the internals of SQL Server caching, with a focus on compilations and re-compilations of batches in SQL 2005.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 - Whitepaper https://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Performance Analysis and Troubleshooting SQL Server 2005
This is an excellent whitepaper that provides scripts and queries to monitor, identify performance bottlenecks and suggests improvements. I would strongly recommend this - a must read.
https://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
SQL 2005 Webcasts
I thought I will also provide some links to useful webcasts. I love the format of the webcasts and the ease of replaying stuff at will.
Performance Diagnosis in SQL 2005
All production databases need to be high performing. However, often times, we don't have solid performance benchmark numbers which we should aim and maintain in the long run. The first step, then is to monitor SQL Server performance during the good and bad times. Based on the data collected during bad times and comparing them to the one collected during good times, we can see what is causing this slow performance. This 300 level webcast talks about performance monitoring and troubleshooting best practices.
Troubleshooting Performance Problems in Microsoft SQL Server 2005
Once you have monitored the performance over a period of time, you can use the techniques in this webcast to troubleshoot the performance bottlenecks. This webcast provides some really good practical advise on troubleshooting performance https://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032275647&eventcategory=5&culture=en-us&countrycode=us
This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005 https://technet.microsoft.com/en-us/library/cc966540.aspx
How to troubleshoot the performance of Ad-Hoc queries in SQL Server https://support.microsoft.com/default.aspx?scid=kb;EN-US;243588
Tuning options for SQL Server 2005 and SQL Server 2008 when running in high performance workloads https://support.microsoft.com/default.aspx?scid=kb;EN-US;920093
How to troubleshoot SQL Server performance issues https://support.microsoft.com/default.aspx?scid=kb;EN-US;298475
Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS https://support.microsoft.com/default.aspx?scid=kb;EN-US;887057
Understanding and Resolving SQL Server Blocking Problems: This article discusses how to troubleshoot and resolve blocking problems https://support.microsoft.com/kb/224453
Wait Types
SQL Server 2005 Waits and Queues - Whitepaper https://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx
The SQL Server Wait Type Repository. https://blogs.msdn.com/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx
Tempdb
TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild - Whitepaper
https://sqlcat.com/whitepapers/archive/2007/11/20/tempdb-capacity-planning-and-concurrency-considerations-for-index-create-and-rebuild.aspx
Working with tempdb in SQL Server 2005 - Whitepaper
https://technet.microsoft.com/hi-in/library/cc966545(en-us).aspx
This white paper describes how SQL Server 2005 uses tempdb. Many improvements in SQL Server 2005 optimize tempdb usage and make it easier to manage and to troubleshoot.
https://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/WorkingWithTempDB.doc
SQL SERVER 2008
Troubleshooting Performance Problems in SQL Server 2008 - Whitepaper https://sqlcat.com/whitepapers/archive/2009/04/14/troubleshooting-performance-problems-in-sql-server-2008.aspx
Statistics Used by SQL 2008
This paper describes what data is collected, where it is stored, and which commands create, update, and delete statistics https://msdn.microsoft.com/en-us/library/dd535534.aspx
Improving Performance with SQL Server 2008 Indexed Views
This document describes the indexed views capability of SQL Server 2005 and SQL Server 2008, including the new support for partition-aligned views added to SQL Server 2008.
https://msdn.microsoft.com/en-us/library/dd171921.aspx
Troubleshooting Performance Problems in SQL Server 2008
This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005 https://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc
You may experience slow performance when you run 32-bit SQL Server tools on 64-bit operating systems https://support.microsoft.com/default.aspx?scid=kb;EN-US;906892
SQL Server 2000 IO basics :
Learn the I/O requirements for Microsoft SQL Server database file operations. This will help you increase system performance and avoid I/O environment errors. Applicable to SQL 2005 and 2008
SQL Server I/O Basics https://technet.microsoft.com/en-us/library/cc966500.aspx
SQL Server I/O Basics, Chapter 2 https://technet.microsoft.com/en-us/library/cc917726.aspx
PERFORMANCE TOOLS FOR SQL SERVER:
SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues https://www.codeplex.com/sqlnexus
RML Utilities/ReadTrace :This article describes one utility suite that is called the Replay Markup Language (RML) Utilities for Microsoft SQL Server https://support.microsoft.com/kb/944837
SQL Server Performance Dashboard:
The SQL Server Performance Dashboard is an invaluable tool for monitoring your server's performance. It can be installed on any SQL Server 2005 machine with SP2 that you want to monitor. Just run the msi package and then run the setup.sql script that comes with it on any instance that you want to monitor. It monitors the instance on the local machine. The setup.sql script is located in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Performance Dashboard after running the msi script.
After you install the Performance dashboard and run setup.sql, just right-click on the server in SQL Management Studio. Choose 'Reports' -> 'Custom Reports'. The first time you do this you'll have to navigate to the same Performance Dashboard directory to find the performance_dashboard_main.rdl file. Choose this file and the dashboard will open to show you the activity on your SQL Server, using the native Distributed Management Views from SQL 2005.
Get the SQL Server 2005 Performance Dashboard at https://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en
Get the SQL Server 2008 Performance Dashboard at https://blogs.technet.com/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
SQL Server Performance Blogs
The folks who wrote the code in SQL Server have blogs of their own. These blogs provide in-depth knowledge about SQL Server internals - like the query optimizer, the SQL OS etc. - stuff that is not there either in Books Online or at MSDN. I would strongly encourage you to read these blogs, if you really want to learn about the esoteric internals of SQL Server.
Slava Oks Blog on SQL OS and Memory https://blogs.msdn.com/slavao/
Blog on Query Optimizer written by the folks who wrote the query optimizer https://blogs.msdn.com/queryoptteam/
SQL Tips and Tricks Blog - handy tips and tricks to do mundane tasks in SQL 2005 https://blogs.msdn.com/sqltips/default.aspx
Craig Freedman's SQL Server blog : A discussion of query processing, query execution, and query plans in SQL Server https://blogs.msdn.com/craigfr/
Tips, Tricks, and Advice from the SQL Server Query Processing Team : Provides tips, tricks, advice and answers to frequently-asked questions in a continued effort to make your queries run faster and smoother in SQL Server https://blogs.msdn.com/sqlqueryprocessing/