華山論劍第1式-SQL Server查詢最佳化!

 本期主題: SQL Server查詢最佳化. 
查詢最佳化是一個比較常見的主題, 
在實際的生產應用中,大資料量的查詢會消耗伺服器大量的資源, 
如何合理地最佳化查詢,提高系統效能, 
是每個系統架構人員,系統開發熱源,系統維護人員以及資料庫管理員共同關心的話題. 
本期就此做一下討論. 
歡迎大家積极參与.:) 
先貼幾篇作為拋磚引玉. 
如何?最佳化查詢. 
http://topic.csdn.net/u/20080416/08/24b62a73-ab5a-4927-97af-81e7e790445d.html 
索引的基本原理，以及資料是如何被訪問的 
http://topic.csdn.net/u/20080521/15/c5ee330e-596d-4957-8032-5bb9a80a9218.html 
100分，求sqlserver 中提高查詢效能的方法!越全越好！ 
http://topic.csdn.net/u/20080624/10/0ac05d43-b285-49f0-85b2-73e0d989d2ae.html 
深度探索:Clustered Index Scan vs Table Scan 
http://topic.csdn.net/u/20080707/09/3c64cc80-5f7a-4116-8942-81c6deae9c07.html 
五種提高 SQL 效能的方法 
http://dev.csdn.net/author/griefforyou/082b9b29299e4584b78bf6f7ccb57c0b.html 
資料庫的查詢最佳化技術 
http://bbs.chinaunix.net/viewthread.php?tid=83481 

查詢最佳化 
http://msdn.microsoft.com/zh-cn/library/ms176005.aspx 

分析查詢 
http://msdn.microsoft.com/zh-cn/library/ms191227.aspx 

尋找缺失索引 
http://msdn.microsoft.com/zh-cn/library/ms345417.aspx 

查詢最佳化建議 
http://msdn.microsoft.com/zh-cn/library/ms188722.aspx 

進階查詢最佳化概念 
http://msdn.microsoft.com/zh-cn/library/ms191426.aspx 


一些Blogs和英文資料. 
-------------------------------------- 
Tips, Tricks, and Advice from the SQL Server Query Optimization Team 
http://blogs.msdn.com/QueryOptTeam/ 
http://blogs.msdn.com/sqlqueryprocessing/default.aspx 

SQL Server 2000 Query Tuning 
http://msdn.microsoft.com/en-us/library/aa178417(SQL.80).aspx 
Analyzing a Query 
http://msdn.microsoft.com/en-us/library/aa178303(SQL.80).aspx 
Query Tuning Recommendations 
http://msdn.microsoft.com/en-us/library/aa178391(SQL.80).aspx 
Advanced Query Tuning Concepts 
http://msdn.microsoft.com/en-us/library/aa178578(SQL.80).aspx 

Some "Hints" for Mastering SQL Tuning 
http://www.devx.com/dbzone/Article/26995 

Transact-SQL Query Performance Tuning Tips 
http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx 

SQL Server 2005 Query Tuning 
http://msdn.microsoft.com/en-us/library/ms176005.aspx 

Analyzing a Query 
http://msdn.microsoft.com/en-us/library/ms191227.aspx 

Finding Missing Indexes 
http://msdn.microsoft.com/en-us/library/ms345417.aspx 

Query Tuning Recommendations 
http://msdn.microsoft.com/en-us/library/ms188722.aspx 

Advanced Query Tuning Concepts 
http://msdn.microsoft.com/en-us/library/ms191426.aspx 

Deeper insight into unused indexes for SQL Server 2005 
http://www.mssqltips.com/tip.asp?tip=1545 

Use Missing-Index Groups for Query Tuning 
http://www.sqlmag.com/Article/ArticleID/95220/sql_server_95220.html 

2 Stored Procedures to Tune Your Indexes 
http://www.sqlmag.com/Article/ArticleID/98019/98019.html?feed=articleLink 

What I Wish Developers Knew About SQL Server (Presentation) 
http://www.sqlteam.com/article/what-i-wish-developers-knew-about-sql-server-presentation. 
This is the latest version of this presentation including all demonstration scripts. The presentation has been converted to a PDF to make viewing easier. The presentation provides an overview of query plans, query tuning tips, nulls, error handling and transactions. 
the presentation and demo scripts. 
http://www.sqlteam.com/downloads/WhatIWishPresentation.zip 

Avoid enclosing Indexed Columns in a Function in the WHERE clause 
http://www.sqlteam.com/article/avoid-enclosing-indexed-columns-in-a-function-in-the-where-clause 

Introduction to Parameterization in SQL Server 
http://www.sqlteam.com/article/introduction-to-parameterization-in-sql-server 

Best Practices for Query Parameterization 
http://www.sqlteam.com/article/best-practices-for-query-parameterization 

Query Parameterization and Plan Cache 
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx 
This comes to us from the SQL Server Programmability and API Team Blog. They write "Using parameters or parameter markers in queries increases the ability of SQL Server to reuse compiled plans. There are two places where parameterization of queries can be done: on the client side application (or mid tier) or on the server side." 

Analyzing and Optimizing T-SQL Query Performance on Microsoft SQL Server using SET and DBCC (PDF) 
http://www.sqlteam.com/article/analyzing-and-optimizing-t-sql-query-performance-on-microsoft-sql-server-using-set-and-dbcc-pdf 

Five Ways to Rev up Your SQL Performance 
http://msdn.microsoft.com/en-us/magazine/cc301622.aspx 
這篇文章已經有中譯本(五種提高 SQL 效能的方法) 

最後推薦一本調優的書 
Inside SQL Server 2005: Query Tuning and Optimization 
http://download.csdn.net/source/421022



〖分享〗資料庫最佳化策略 
http://topic.csdn.net/u/20081007/22/71c40590-3bac-4848-b82a-416692b8b8c6.html