Databases Reference
In-Depth Information
| | | | | | |
--Compute
Scalar(DEFINE:([C].[AccountNumber]=isnull('AW'+[AdventureWorks].[dbo].[ufnLeading
Zeros]([AdventureWorks].[Sales].[Customer].[CustomerID] as [C].[CustomerID]),")))
| | | | | | |
--Clustered Index
Seek(OBJECT:([AdventureWorks].[Sales].[Customer].[PK_Customer_CustomerID] AS [C]),
SEEK:([C].[CustomerID]=[AdventureWorks].[Sales].[Individual].[CustomerID] as [F].
[CustomerID]) ORDERED FORW
| | | | | |
--Clustered Index
Seek(OBJECT:([AdventureWorks].[Sales].[CustomerAddress].[PK_CustomerAddress_Customer
ID_AddressID] AS [G]), SEEK:([G].[CustomerID]=[AdventureWorks].[Sales].[Individual].
[CustomerID] as [F].[CustomerID]) ORD
| | | | |
--Clustered Index
Seek(OBJECT:([AdventureWorks].[Person].[Address].[PK_Address_AddressID] AS [E]),
SEEK:([E].[AddressID]=[AdventureWorks].[Sales].[CustomerAddress].[AddressID] as [G].
[AddressID]) ORDERED FORWARD)
| | | |
--Clustered Index
Seek(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS [D]),
SEEK:([D].[ContactID]=[AdventureWorks].[Sales].[Individual].[ContactID] as [F].
[ContactID]) ORDERED FORWARD)
| | |
--Index
Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[IX_SalesOrderHeader_
CustomerID] AS [A]), SEEK:([A].[CustomerID]=[AdventureWorks].[Sales].[Customer
Address].[CustomerID] as [G].[CustomerID]) ORDERED FORWARD)
| |
--Clustered Index
Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_
SalesOrderID] AS [A]), SEEK:([A].[SalesOrderID]=[AdventureWorks].[Sales].[Sales
OrderHeader].[SalesOrderID] as [A].[SalesOrderID]) LOOKUP ORDERED FO
|
--Clustered Index
Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_
SalesOrderID_SalesOrderDetailID] AS [B]), SEEK:([B].[SalesOrderID]=[Adventure
Works].[Sales].[SalesOrderHeader].[SalesOrderID] as [A].[SalesOrderID]) ORD
Notice the remarkable difference between the query plans. There are a lot more index seeks than scans
as the query is attempting to return the first 40 rows as quickly possible. This is great as the user is not
kept waiting for the data to be returned. This has been accomplished without making any changes to the
original query.
Best Practice
Plan guides are a fantastic way to avoid changing code, but they have limitations.
Summary
Size your environment correctly, understand the implications of incorrect sizing, and always remember
to leave room for growth. Manage your changes effectively; a good set of change management procedures
along with an enterprise change management tool will always help diagnose problems and help revert
back to normalcy quicker. Always ensure that your backups are good and test them on an ongoing basis;
don't assume anything. Use some of the cool new features available in SQL 2005 such as partitioning,
plan guides, and online index creation. Keep your database healthy — defragment on a regular basis and
rebuild indexes as necessary. Take the correct steps to a successful and trouble-free deployment.
