Successful Deployment Strategies - Professional SQL Server 2005 Performance Tuning - page 534

problems and a database can be defragmented either online or offline. Online defragmentation is one
of the new features introduced in SQL Server 2005, but care should be taken while carrying out this
operation, especially during peak production hours. I have also noticed that if the database is in full
recovery model, online defragmentation can cause the log to grow very quickly. A suggestion here would
be to change the recovery model to bulk-logged before defragmenting tables. Online defragmentation can
easily be accomplished by using the following code:
ALTER INDEX IX_SQLROCKS REBUILD
WITH ONLINE = ON
Of course, the offline option is always available for the less adventurous. There have been some changes
with respect to the syntax, but for the most part the underlying fundamentals are still the same.
Indexes can also be added online now — another new feature with SQL Server 2005. However, as always,
care should be taken when adding indexes to tables in a production environment. From personal expe-
rience, I still see some blocking behavior when trying to add indexes to heavily used tables, though it is
much reduced from earlier versions. You can create indexes online with the following code:
CREATE INDEX IX2_SQLROCKS ON SQLROCKS (COL2)
WITH ONLINE = ON
Best Practice
Keep your database defragmented. Don't add indexes without testing and unless
absolutely essential. Adding unnecessary indexes can have a negative impact on per-
formance.
Avoiding Changing Code to Fix Issues
One of my other favorite new features of SQL 2005 is the ability to use plan guides. Plan guides give
DBAs the ability to add hints to queries without changing the physical query. Plan guides can be used
to force query plans, supply join hints, or restrict the use of parallel processing to a specific number of
processors. Plan guides are perfect for a production scenario where queries that are performing poorly
cannot be changed due to restrictions posed by the application vendor.
The Query
Here is a query that could benefit from the use of Plan Guides. The following query returns 604,000 rows,
and you want that query to return the first 40 rows as soon as possible so that the user can start seeing
the data before having to wait for all 604,000 rows to be returned.
SELECT A.RevisionNumber,A.OrderDate,A.DueDate,A.ShipDate,A.Status,
B.OrderQty,B.ProductId,
C.AccountNumber,C.CustomerType,
D.FirstName,D.MiddleName,D.LastName,D.EmailAddress,
E.AddressLine1,E.AddressLine2,E.StateProvinceId,E.PostalCode
FROM
SALES.SALESORDERHEADER A