Fixing bottlenecks
Now that you have pinpointed the bottlenecks to prioritize, skip to the appropriate subsection to find out how to fix those bottlenecks.
Missing indexes
Just as using an index in a book to find a particular bit of information is often much faster than reading all pages, SQL Server indexes can make finding a particular row in a table dramatically faster by cutting down the number of read operations.
This section first discusses the two types of indexes supported by SQL Server: clustered and non-clustered. It also goes into included columns, a feature of non-clustered indexes. After that, we'll look at when to use each type of index.
Clustered index
Take the following table (missingindexes.sql in the downloaded code bundle):
CREATE TABLE [dbo].[Book]( [BookId] [int] IDENTITY(1,1) NOT NULL, [Title] [nvarchar](50) NULL, [Author] [nvarchar](50) NULL, [Price] [decimal](4, 2) NULL)
Because this table has no clustered index, it is called a heap table. Its records are unordered, and...