SQL Profiler, if installed in the default location, can be found under StartProgramsMicrosoft SQL ServerProfiler.

After launching SQL Profiler, select the server that you want to monitor and then enter your login information. After a connection has been established, the Trace properties dialog box will open. This dialog box has four tabs: General, Events, Data Columns, and Filters, which are used to specify the trace properties.

On the General tab, you can specify the name of a trace, specify a trace template, and choose to save the data to a trace file or a SQL table. If you choose to save to a file, you have to specify a directory and filename; if you choose to save the data to a SQL server, you need to specify the server, database, and table.

On the Events tab, you can select the events to monitor. The events are grouped into collections under which individual events are listed. Selecting an event should display a description on the bottom of the tab. Monitoring all the events might be overkill and can put a burden on the server. Some of the events that we typically capture are:

For more information, search under “event classes” in SQL Server Books Online.

On the Data Columns tab, you can specify the data columns that you want to capture. There are 43 columns, of which Event Class and SPID are required. Depending on the level of activity a given application might generate on the SQL server and how many users are in the environment, you might end up with a huge amount of data. Keep in mind that not all data columns will be populated, because every column is not meaningful for every event. For example, duration is only meaningful for complete events such as SP:Completed; this ­column will be blank for SP:Starting. Some of the data columns that we collect are listed in Table 8-1:

For more information, search under “data columns” in SQL Server Books Online.

On the Filters tab, you can specify filters to further limit the data collected. For example, if you are only interested in the SQL activity generated by one particular application, you can set a filter on the application name. By default, events triggered by SQL Profiler are excluded. You can familiarize yourself with all the filters available by examining the Filters tab in the Trace Properties dialog box. Selecting a filter should display an explanation of the filter at the bottom of the tab.

Now that you know what to track, you may be wondering how to analyze the trace you have captured. SQL Profiler gives you two choices for saving the data: a trace file or a SQL table. In fact, if you save the data to a file, you can always open the trace file and still choose to save the data to a SQL table. The choice is yours and depends on your preference.

We find that for large amounts of data, it is easier to analyze a trace by saving the data to a table and writing queries against this table to retrieve information such as the top few stored procedures that had the longest duration or to get the average duration across all instances of a stored procedure. Before you decide that some code is slow, you need to define criteria for a slow running query.

As you run more and more traces, you will find that you always track certain events and data columns or specify certain filters. SQL Profiler makes this task easy by letting you create a template containing the trace properties that you most often use. This will save you from having to define your trace properties every time you need to run a trace. The steps to create a template are very simple: Choose New Trace Template from the File menu to display the Trace Properties dialog box, in which you can specify events, data columns, and filters. You can then click Save As on the General tab. Alternatively, you can choose Open Trace Template from the File menu to open an existing template. You can then customize the template and save it under a different name.

SQL Server exposes a number of performance objects that can be monitored with System Monitor. Chapter 4 covers this tool in great detail and focuses on counters such as Processor, Memory, Disk, and Network that relate to system level resources. This section will only cover the SQL Server–specific counters that we tend to look at most often. The reference books listed at the start of this chapter cover the other counters in more detail. Depending on your specific needs, you may want to look at other counters. The SQL Server–specific counters that we use most often are:

SQL Query Analyzer can be used in various ways, for example, to execute SQL scripts to deploy new code, to count the number of records inserted or updated, to analyze query execution plans, or to execute various system stored procedures. Among the tools included with SQL Server 2000, Query Analyzer is by far the most frequently used tool in our team. Rather than describe Query Analyzer in detail, we will show you specific examples of how the tool can be used to analyze blocking and analyze execution plans in the later part of this chapter.

SELECT o.name, rows
FROM sysobjects o INNER JOIN sysindexes i on o.id = i.id
WHERE  i.indid < 2
ORDER BY o.name

Typical symptoms of blocking bottlenecks are low system resource (CPU and disks) utilizations while the SQL tier throughput is maxed out. If you suspect blocking is the bottleneck, the first step towards identifying the cause is to query the sysprocesses table in the master database. Example 8-1 shows a script that returns information on the blocked connections and the last statement sent by the root blocker:

-- Script returns blocking information from the sysprocesses table 
SELECT spid, blocked, status, waittime, 
    waittype, waitresource, db_name(dbid) DatabaseName, cmd, 
    hostname, loginame
FROM master..sysprocesses 
WHERE blocked != 0 

DECLARE @spid int

-- Get the root blocker’s spid id
SELECT @spid =  A.spid
FROM master..sysprocesses A
    INNER JOIN master..sysprocesses B
    ON A.spid = B.blocked
WHERE A.blocked = 0 

IF NOT @spid IS NULL 
BEGIN
    -- Returns last statement sent from the connection
    DBCC INPUTBUFFER(@spid)
END

The IBuySpy sample site included with this book does not inherently have blocking issues on the SQL tier. For the purpose of demonstration, we will add a new stored procedure that recompiles each time it is executed to simulate a compile blocking. The following script will add such a stored procedure:

CREATE  PROCEDURE ProductCategoryList_Recompile
WITH RECOMPILE
AS

SELECT 
    CategoryID,
    CategoryName
FROM 
    dbo.Categories
ORDER BY 
    CategoryName ASC
GO

To observe the compile blocking behavior, we will have to run several instances of the recompiling stored procedure simultaneously. As discussed in Chapter 3, we can use ACT to simulate load on the SQL server. The ACT script in Example 8-2 can be used to apply the required load:

-- The ACT script that can simulate simultaneous
execution of the stored procedure.
Dim oConn

On Error Resume Next

Set oConn = CreateObject("ADODB.Connection")

oConn.Open "driver={SQL Server};Server=SQLServer;" & _
"Database=Store;uid=user;pwd=user"

If err.Number <> 0 Then
    Test.Trace("Error Opening connection: " & _
err.number & ", " & err.description)
ELSE
    oConn.Execute("EXEC ProductCategoryList_Recompile")
    If err.Number <> 0 Then
        Test.Trace("Error Executing: " & _
err.number & ", " & err.description)
    End If
End IF

You can set the simultaneous browser connections to 10 and run a test for five minutes. While stressing the SQL server, executing the script in Example 8-1 will give you information regarding the blocked connections and the last statement sent by the root blocker. In this example, the blocked connection’s waitresource column should indicate compile blocking with the following information:

TAB: 7:1173579219 [[COMPILE]]

The last statement sent by the root blocker should be displayed as ProductCategoryList_Recompile. Although we purposefully created the problem on ProductCategoryList_Recompile, querying the sysprocesses table revealed the recompile issue on the same stored procedure.

The occurrence of excessive compiles is just one of the numerous causes of blocking. Long running transactions, improper Transaction Isolation Level, and inappropriate indexes can also cause blocking. Regardless of the cause, the approach in this section can be used to identify the blocking stored procedures or ad-hoc queries. After they are identified, you can analyze the query further to pinpoint the problem.

In-depth knowledge of lock types used in SQL Server is essential for troubleshooting blocking problems. We strongly recommend that you thoroughly review the lock topics in the aforementioned reference materials. Understanding the topics will help you troubleshoot lock timeouts as well as deadlocks.

For applications that experience SQL blocking, we typically execute sp_lock to look at the granted or waiting locks. It’s a snapshot of locks at the time of execution of the sp_lock system store procedure; therefore, we execute it multiple times during the stress test to see a pattern of locking behavior. The output of sp_lock is cryptic and needs to be cross-referenced to get the table names or index names. Thus, we run a custom stored procedure that is similar to sp_lock_verbose, included in The Guru’s Guide to Transact-SQL, to eliminate the repetitive cross-referencing task.

Again, for demonstration purposes, we will introduce a lock problem in one of the stored procedures. The following script will add such a stored procedure:

CREATE PROCEDURE ProductCategoryList_XLOCK
AS

-- Use transaction to hold the exclusive lock
BEGIN TRANSACTION

SELECT 
    CategoryID,
    CategoryName
FROM 
    dbo.Categories (XLOCK) 
ORDER BY 
    CategoryName ASC

WAITFOR DELAY ‘00:00:01’ –- Hold the lock for 1 second

COMMIT TRANSACTION
GO

Executing sp_lock while stressing the SQL server with this new stored procedure will produce an output similar to the following:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51     7      0           0      DB                    S        GRANT
53     7      0           0      DB                    S        GRANT
53     7      1977058079  0      RID  1:89:0           X        WAIT
53     7      1977058079  0      PAG  1:89             IX       GRANT
53     7      1977058079  0      TAB                   IX       GRANT
54     7      1977058079  0      TAB                   IX       GRANT
54     7      1977058079  0      RID  1:89:2           X        GRANT
54     7      1977058079  0      RID  1:89:6           X        GRANT
...
(Result abbreviated)

The interesting parts of the sp_lock output are the ObjId, Type, Mode, and Status columns. For example, the output shows exclusive mode, row identifier (RID) type locks granted on the ObjId of “1977058079”. The RID lock type is shown instead of the KEY lock type due to the lack of a clustered index on the Categories table. In addition, an exclusive lock is waiting to be granted, as indicated by the WAIT status. You can execute SELECT Object_Name(<ObjId>) to get the object name, and in this case, “1977058079” represents the Categories table. Combining the results obtained by querying the sysprocesses table to get the blocking connection and the output of the sp_lock stored procedure to get the lock information will make it easier to narrow the problem down to a statement.

A deadlock occurs when two or more connections block each other and each are waiting on some resources that the other connection has locked. For instance, Connection A holds a lock on Row A and is waiting to get a lock on Row B. Connection B holds a lock a Row B and is waiting to get a lock on Row A. Each connection is waiting on the other, and neither can proceed to commit or roll back the transactions. This type of deadlock is referred as cyclical. Another possible deadlock type is a conversion deadlock. A conversion deadlock occurs when two or more connections have shared locks on some resources and they both want to convert their shared locks to exclusive locks. Regardless of deadlock type, we use trace flags to troubleshoot the deadlock problems. The following statement will write the deadlock trace report (1204) to the SQL Server error log (3605) and set the trace flag for all connections (-1):

DBCC TRACEON(-1, 1204, 3605)

After the trace flags are turned on, you can capture the ErrorLog event using SQL Profiler or view the error log using Enterprise Manager. For demonstration purposes, we created the following script to add a stored procedure that forces a conversion deadlock:

CREATE CLUSTERED INDEX IXC_ModelName
    ON dbo.Products (ModelName)
GO

CREATE PROCEDURE ProductsUnitCostUpdate_DeadLock
AS
BEGIN TRANSACTION

SELECT *
FROM 
    dbo.Products (HOLDLOCK)
WHERE ModelName = N’Bullet Proof Facial Tissue’

WAITFOR DELAY ‘00:00:05’

UPDATE dbo.Products
    SET UnitCost = UnitCost * 0.90
WHERE ModelName = N’Bullet Proof Facial Tissue’

COMMIT TRANSACTION
GO

You must run the deadlock stored procedure in the two separate SQL Query Analyzer windows within a few seconds apart to produce a deadlock. The following report is a sample produced by enabling trace flag 1204:

Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
KEY: 7:2041058307:1 (a60421ba9ed3) CleanCnt:2 Mode: Range-S-S Flags: 0x0
Grant List::
Owner:0x42be1340 Mode: Range-S-S Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0
SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 12
Input Buf: Language Event: ProductsPriceUpdate_DeadLock
Requested By: 
ResType:LockOwner Stype:’OR’ Mode: X SPID:51 ECID:0 Ec:(0x42f77568) _
    Value:0x42be1360 Cost:(0/0)
Node:2
KEY: 7:2041058307:1 (a60421ba9ed3) CleanCnt:2 Mode: Range-S-S Flags: 0x0
Grant List::
Owner:0x42be5240 Mode: Range-S-S Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0
SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 12
Input Buf: Language Event: ProductsPriceUpdate_DeadLock
Requested By: 
ResType:LockOwner Stype:’OR’ Mode: X SPID:52 ECID:0 Ec:(0x430b5568) _
    Value:0x42be1260 Cost:(0/0)
Victim Resource Owner:
ResType:LockOwner Stype:’OR’ Mode: X SPID:52 ECID:0 Ec:(0x430b5568) _
    Value:0x42be1260 Cost:(0/0)

Detailed information is produced by the trace flag 1204 for each deadlock occurrence and we usually focus our attention on a few sections. We look for the lock resource in “Key:”; check the lock mode in “Mode:”; and search for the stored procedure name or the ad-hoc query in “Input Buf:”. With this information, we can narrow our troubleshooting to a few stored procedures or queries. More detailed information on troubleshooting deadlocks and the trace flag 1204 report can be found in SQL Server Books Online and Inside Microsoft SQL Server 2000.

So far we discussed manual methods of troubleshooting blocking problems. Several articles have published the blocking scripts. Using those scripts can reduce the human error factor, and we highly recommend the following article:

In addition, the following articles are recommended:

Before we begin, we will add 100,000 rows to the Orders table and 1,000,000 rows to the OrderDetails table in the IBuySpy sample site database. Actual numbers of rows used for analyzing the SQL tier will vary depending on the business requirements and the projected database growth. We chose an arbitrary number of rows to demonstrate how to analyze with large data sets. SQL Server behaves differently depending on the number of rows and the uniqueness of column data; therefore, it is crucial to tune queries on the correctly sized database. Example 8-3 shows the T-SQL used to generate the additional records.

-- The T-SQL used to load additional data
SET NOCOUNT ON  -- Stop the number of rows effected messages

DECLARE @Count int
DECLARE @OrderID int
DECLARE @CustomerID int
DECLARE @DateAdd int
DECLARE @DateAdd2 int
DECLARE @Today DateTime
DECLARE @SQL nvarchar(4000)

-- Count the records before the inserts
SELECT Count(*) Orders FROM Orders
SELECT Count(*) OrderDetails FROM OrderDetails
SELECT Count(*) Customers FROM Customers

SET @Today = GetDate()

SET @Count = 1

-- Add 100000 Orders and 10000 Customer
WHILE @Count <= 100000
BEGIN

    --Insert a new customer every 10 records
    IF @Count % 10 = 0 OR @Count = 1
    BEGIN
        INSERT INTO Customers
        (
            FullName,
            EMailAddress,
            Password
        )
        VALUES
        (
            ‘TestUser_’ + Cast(@Count as varchar(10)),
            ‘TestUser_’ + Cast(@Count as varchar(10)) + ‘@test.com’,
            ‘password’
        )

        SET @CustomerID = @@Identity
    END

    -- Vary the OrderDate and ShipDate
    SET @DateAdd = (-1 * (@Count % 365))
    SET @DateAdd2 = (-1 * (@Count % 365)) + 1

    INSERT INTO Orders
    (
        CustomerID,
        OrderDate,
        ShipDate
    )
    VALUES
    (
        @CustomerID,
        DateAdd(d, @DateAdd, @Today),
        DateAdd(d, @DateAdd2, @Today)
    )
    SET @OrderID = @@Identity

    SET @SQL = N’INSERT INTO OrderDetails
        (
            OrderID,
            ProductID,
            Quantity,
            UnitCost
        )
        SELECT TOP 10 
            @OrderID, ProductID, 1, UnitCost
        FROM Products ‘


    -- Use 4 different sorts to add different products
    IF @Count % 4 = 1
        SET @SQL = @SQL + N’ORDER BY CategoryID’

    IF @Count % 4 = 2
        SET @SQL = @SQL + N’ORDER BY ModelNumber’

    IF @Count % 4 = 3
        SET @SQL = @SQL + N’ORDER BY ModelName’
    
    IF @Count % 4 = 0
        SET @SQL = @SQL + N’ORDER BY UnitCost’
        
    EXEC sp_executesql @SQL, N’@OrderID int’, @OrderID = @OrderID

    SET @Count = @Count + 1

END

-- Count after the inserts
SELECT Count(*) Orders FROM Orders
SELECT Count(*) OrderDetails FROM OrderDetails
SELECT Count(*) Customers FROM Customers

Now that we have loaded the data successfully, we will execute ProductsMostPopular, one of the stored procedures that is affected adversely by an increase in the database size. To get the execution plan, the Show Execution Plan option must be on in Query Analyzer (press CTRL+K). Figure 8-1 shows the output of the ProductsMostPopular’s execution plan.

Figure 8-1. The execution plan created by running the ProductsMostPopular stored procedure

In this relatively simple example, ProductsMostPopular contains the following code:

CREATE Procedure ProductsMostPopular
AS

SELECT TOP 5 
    OrderDetails.ProductID, 
    SUM(OrderDetails.Quantity) as TotalNum, 
    Products.ModelName    
FROM    
    OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY 
    OrderDetails.ProductID, 
    Products.ModelName
ORDER BY 
    TotalNum DESC
GO

All icons representing physical operators used to execute statements are too numerous to list here. They are well documented in SQL Server Books Online. In addition, the SQL Server Books Online provides details on how to interpret the graphical output. In this section, we will focus on the things to look for that can cause long call durations.

Among the various physical operators, look for the operators that are showing a high percentage of cost. For example, in Figure 8-1, the Hash Match/Aggregate and Table Scan operators indicate high costs, 54 percent and 46 percent respectively. When you pause the pointer on top of each icon in the execution plan, you will see a ToolTip appear with detailed information. Figure 8-2 shows the ToolTip information on the Table Scan icon.

Figure 8-2. ToolTip information appears when the pointer is above the Table Scan icon

The ToolTip shows that Query Optimizer picked a Table Scan operator to retrieve data from the object, [store].[dbo].[OrderDetails]. In a typical execution plan, Table Scans and Index Scans can cause longer duration compared to an execution plan that uses Index Seeks. In our case, however, reading the entire OrderDetails table is required to determine which top five products are the most popular. Table Scans and Index Scans on small tables are not unusual and in such cases may be more efficient. You should not be automatically alerted when you find such operators in the execution plan; adding more indexes to a small table may not make any difference in terms of the query execution duration. Therefore, you will need additional information to the physical operators to perform an effective index tuning.

SQL Server has useful statistic options with which you can measure the resource utilizations, such as STATISTICS IO. A combined analysis of the query execution plan and the I/O utilization statistics can help you discover the tables that are the source of the performance problem. Ultimately, the goal is to reduce the execution duration of the queries, and this is where the STATISTICS TIME option can help you measure the durations. Turning on the STATISTICS TIME option displays the parse and compile time for the query execution, letting you can measure how long it takes to generate the query’s execution plan.

In addition to the STATISTICS options, there are two DBCC commands that clear the data buffer and procedure cache. With DBCC DROPCLEANBUFFERS you can test queries with a cold buffer cache without restarting SQL Server, and is useful for measuring consistent query statistics. Another helpful command is DBCC FREEPROCCACHE. With this command you can compare the query performance with or without the procedure cache.

Now, let’s take a look at the performance metrics obtained by executing the stored procedure ProductsMostPopular via the following script in Query Analyzer:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

EXEC ProductsMostPopular

Depending on the complexity of the stored procedure, the amount of extra information generated by the DBCC commands and the STATISTICS options will vary. In the case of ProductsMostPopular, the following output is what we are interested in:

...
SQL Server parse and compile time: 
   CPU time = 32 ms, elapsed time = 187 ms.

(5 row(s) affected)

Table ‘Products’. Scan count 5, logical reads 10, _
    physical reads 4, read-ahead reads 0.
Table ‘OrderDetails’. Scan count 1, logical reads 3800, _
    physical reads 0, read-ahead reads 3801.

SQL Server Execution Times:
   CPU time = 1813 ms,  elapsed time = 1826 ms.
...
(Result abbreviated)

The resulting statistics will vary depending on the database size, the SQL Server version, and the server hardware capability. The output that we obtained shows that it took 187 milliseconds to parse and compile the stored procedure ProductsMostPopular, and 1826 milliseconds to execute it. This timing information is what you obtain by enabling the STATISTICS TIME option. The most interesting part of the output is the logical reads on the table OrderDetails. The logical reads indicates the number of pages that were read from the buffer; the value of “3800” that we obtained confirms that the Table Scan operator used to retrieve the data is expensive compared to the reads on the Products table. The scan count measure can also be useful from time to time. It represents how many times the object was accessed. It is directly correlated with the type of join used. For example, a query that uses a loop join and returns 100 rows may show a scan count value of 100, while the same query using uses a merge join instead may show a scan count value of 1. In our case, scan counts are negligible due to their low values. The physical and read-ahead reads varies depending on whether the required data was already in the buffer. For query tuning purposes, the two reads counters can be ignored.

After the execution plan of a poorly performing query has been analyzed, you can decide whether to add, drop, or modify indexes, modify queries, or make database schema changes. The best scenario would be to pick a modification that gives the most gain, and our team has seen the most performance gains from index tuning. Also, index optimizations typically minimize the risk of introducing new functional bugs. In the next section, we will show you how we go about index tuning.

A nonclustered index does not contain the entire table data. Instead, it contains duplicates of chosen column data and pointers to the location of the rest of the table data. The location may be a clustered index key or a row identifier if the table lacks a clustered index. Such characteristics can make the nonclustered index compact, and as a result, scanning the entire index tree will takes fewer pages to read compared to a clustered index scan. Therefore, when you are looking for a range of data, as long as all required columns are contained in the nonclustered index, a nonclustered index is typically faster than a clustered index.

Our team frequently sees significant performance gains by applying appropriate nonclustered indexes. Although we can’t discuss every possible nonclustered index usage case in this chapter, we will discuss the covering index that we encounter often.

A covering index can be created by choosing all columns that are referenced by a query to be part of the index. Given the right situation, a covering index can greatly improve a query’s performance by eliminating bookmark lookups. Because a nonclustered index does not contain table data other than the selected column(s), any need for column data that is not part of the index will have to be retrieved from either a clustered index or a heap. By adding extra column(s) not specified in the Searchable Arguments (SARG) or join conditions, all data can be resolved from the nonclustered index and this extra lookup can be eliminated.

Covering Index Example 1

From the earlier analysis of the execution plan generated by the ProductsMostPopular stored procedure, we discovered that most of the logical reads occurred on the OrderDetails table. Let’s first look at the index information on OrderDetails by executing following statement:

sp_helpindex OrderDetails

The output shows one nonclustered index on the OrderID and ProductID columns. Moreover, the index_description field indicated Primary Key constraint. Based on the index information and the query used in ProductsMostPopular, we can build Table 8-3 to help us determine which columns should be used and in what order.

Table 8-3. Column Choice and Order

OrderDetails Table	Columns
Searchable Argument (SARG)	None
GROUP BY or ORDER BY	ProductID
JOIN	ProductID
All other columns used in the query	Sum(Quantity)
Existing Index	Primary Key, Nonclustered Index on (OrderID, ProductID)

Query Shortcuts

Stored procedures or SQL statements can be saved in the Customize window under the Tools menu. Once configured, the stored procedure will be executed every time the shortcut key is pressed. For example, sp_helpindex can be mapped to CTRL+F1 as shown in Figure 8-3.

Figure 8-3. Stored procedures can be saved in the Customize window for quick execution.

Next time you need to execute sp_helpindex, simply highly the table name you are interested in and press CTRL+F1. sp_helpindex and sp_helptext are good ones to customize.

As indicated in Table 8-2, creating a nonclustered index on the ProductID and Quantity columns will cover all columns. In addition, ProductID is used in GROUP BY clause; therefore, an index that is first sorted by ProductID should be more efficient than sorted by Quantity. Regardless, let’s create two indexes with different column orders and see which one is more efficient. The following script creates the two indexes:

CREATE INDEX IX_OrderDetails_ProductID_Quantity
    ON OrderDetails (ProductID, Quantity)

CREATE INDEX IX_OrderDetails_Quantity_ProductID
    ON OrderDetails (Quantity, ProductID)

When we execute ProductsMostPopular again, different operators and the covering index on ProductID and Quantity were chosen by Query Optimizer. Again, the same dual1-GHZ Pentium processor server was used for this example. Figure 8-4 shows new execution plan.

Figure 8-4. New execution plan after creating covering indexes on the OrderDetails table

The Table Scan operator was replaced with the Index Scan operator, and the Stream Aggregate was chosen instead of Hash Match/Aggregate. For the second covering index comparison, an INDEX hint was used, and the following code shows the hint usage in bold:

SELECT TOP 5 
    OrderDetails.ProductID, 
    SUM(OrderDetails.Quantity) as TotalNum, 
    Products.ModelName
    
FROM    
    OrderDetails (INDEX=IX_OrderDetails_Quantity_ProductID)
  INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
  
GROUP BY 
    OrderDetails.ProductID, 
    Products.ModelName
    
ORDER BY 
    TotalNum DESC

Note

Most of the time, Query Optimizer chooses the most efficient indexes and operators to retrieve data. However, it is good practice to validate the chosen plan by forcing alternate plans and measuring the differences.

In this example, three execution plans were observed. For comparison purposes, statistics from each plan are listed in Table 8-4.

Table 8-4. Comparing Execution Plan Queries

	Original Query	Covering index on ProductID and Quantity	Covering index on Quantity and ProductID
Operator used and cost ­percentage	Table Scan (46 percent) and Hash Match/Aggregate (54 percent)	Index Scan (51 percent) and Stream Aggregate (49 percent)	Index Scan and Hash (38 percent) Match/Aggregate (61 percent)
Logical Reads on OrderDetails	3800	2184	2370
Query Duration in milliseconds	1826	1021	2095

As predicted earlier, the first covering index on ProductID and Quantity column results in the most improvement. In fact, when order of the columns used in the covering index was reversed, the query duration took longer than without the index. Therefore, each index modification should be tested and validated. Without thorough testing, your index tuning attempts may hurt the application’s performance.

In this example, approximately 800 milliseconds were reduced by implementing a covering index. Typically we would analyze the query further to see if an indexed view can help reduce the duration even more. We will discuss indexed views in the clustered index section.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

EXEC OrdersList @CustomerID = 10704

CREATE Procedure OrdersList
(
    @CustomerID int
)
As

SELECT  
    Orders.OrderID,
    Cast(sum(orderdetails.quantity*orderdetails.unitcost) as money) _
    as OrderTotal,
    Orders.OrderDate, 
    Orders.ShipDate

FROM    
    Orders 
  INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

GROUP BY    
    CustomerID, 
    Orders.OrderID, 
    Orders.OrderDate, 
    Orders.ShipDate
HAVING  
    Orders.CustomerID = @CustomerID

CREATE INDEX IX_Orders_CustomerID_Covered
    ON OrderDetails (CustomerID,OrderID, OrderDate, ShipDate)
CREATE INDEX IX_OrderDetails_OrderID_Quantity_UnitCost
    ON OrderDetails (OrderID, Quantity, Unitcost)

Since a clustered index physically sorts the table data, you can only have one clustered index per table. Such characteristics can be beneficial in many cases. For instance, having a clustered index on a table allows you to defragment the table. Unless there is a good reason not to use clustered indexes, one should be created for each table. In addition, you should consider choosing narrow columns as clustered index column since all nonclustered indexes will contain the clustered index key. Using a wide clustered index key will cost you in terms of extra space used by the nonclustered indexes and it will also cause additional I/O.

SQL Server makes it easy to create a clustered index on each table by making it the default index that is created when defining a primary key constraint. The difficult part of clustered index tuning is to pick the proper column(s) to use in the index. It is beyond the scope of this chapter to go over every possible clustered index usage. Instead, we will discuss the following typically neglected areas that our team faces frequently:

Let’s look at each scenario with examples.

ALTER TABLE Orders
    ADD OrderDesc Varchar(500) NULL
GO

CREATE CLUSTERED INDEX IXC_Orders_OrderID
    ON Orders (OrderID)
GO

-- Gathers query performance based on varying fragmentation
-- Clears cached data buffer and execution plans
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- Displays IO and TIME related statistics
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
-- A query on the Orders table.
SELECT TOP 10 WITH TIES CustomerID, _
    MAX(DateDiff(hh, OrderDate, ShipDate)) MaxShippingDelay
FROM Orders (INDEX=IXC_Orders_OrderID)
GROUP BY CustomerID
ORDER BY MaxShippingDelay DESC, CustomerID
GO
-- Displays Fragmentation information on the Orders table
DBCC SHOWCONTIG(‘Orders’)
GO

DBCC SHOWCONTIG scanning ‘Orders’ table...
Table: ‘Orders’ (2025058250); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 418
- Extents Scanned..............................: 54
- Extent Switches..............................: 53
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 98.15% [53:54]
- Logical Scan Fragmentation ..................: 0.24%
- Extent Scan Fragmentation ...................: 1.85%
- Avg. Bytes Free per Page.....................: 25.2
- Avg. Page Density (full).....................: 99.69%
DBCC execution completed. If DBCC printed error messages, _
    contact your system administrator.
...
Table ‘Orders’. Scan count 1, logical reads 419, _
    physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 302 ms.
...
(Result abbreviated)

-- REPLICATE function isn’t necessary here, but it can be
-- useful when you want to generate test data.
UPDATE Orders
    SET OrderDesc = ‘Some description ‘ + REPLICATE(‘X’, OrderID % 10)
    WHERE OrderID % 10 = 0

Table: ‘Orders’ (2025058250); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 834
- Extents Scanned..............................: 110
- Extent Switches..............................: 833
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 12.59% [105:834]
- Logical Scan Fragmentation ..................: 50.00%
- Extent Scan Fragmentation ...................: 36.36%
- Avg. Bytes Free per Page.....................: 3768.9
- Avg. Page Density (full).....................: 53.44%
DBCC execution completed. If DBCC printed error messages, _
    contact your system administrator.
...
Table ‘Orders’. Scan count 1, logical reads 836, _
    physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 307 ms,  elapsed time = 307 ms.
...
(Result abbreviated)

SELECT TOP 5 
    OrderDetails.ProductID, 
    SUM(OrderDetails.Quantity) as TotalNum, 
    Products.ModelName
    
FROM    
    OrderDetails
  INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
  
GROUP BY 
    OrderDetails.ProductID, 
    Products.ModelName
    
ORDER BY 
    TotalNum DESC

-- User option requirements for creating an indexed view
SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF 
GO

if exists (select * from dbo.sysobjects 
    where id = object_id(N’[dbo].[ProductOrderCount]’) and _
        OBJECTPROPERTY(id, N’IsView’) = 1)
drop view [dbo].[ProductOrderCount]
GO

CREATE VIEW dbo.ProductOrderCount
WITH SCHEMABINDING 
AS 
SELECT  od.productid,  p.modelname,  SUM(od.Quantity) OrderSum
    , COUNT_BIG(*) RecordCount 
-- COUNT_BIG aggregate function is required
FROM  dbo.orderdetails od
    INNER JOIN dbo.products p  
        ON od.productid = p.productid
GROUP BY  od.productid,  p.modelname  
GO

-- The first index on the indexed view must be clustered and unique
CREATE UNIQUE CLUSTERED INDEX IXUC_ProductOrderCount
    ON dbo.ProductOrderCount (ProductID)

...
(5 row(s) affected)

Table ‘ProductOrderCount’. Scan count 1, logical reads 2, _
    physical reads 0, read-ahead reads 0.
...
(Result abbreviated)