SQL Server index performance

Question

I'm interested in databases and have started to play around with SQL Server 2008, I've read that using appropriate Indexes on tables can help to improve the overall performance of the database.

I have two tables and auto generated 1 million rows in each table using SQL Data Generator, Table 1 is a customers table and Table 2 is a renters table, the designs are as follows:

Customer                                    Renters
CustomerID (PK)                             RentersID (PK)
ForeName (Non clustered index)              StartDate
SurName                                     EndDate
Email                                       RentalNights
                                            CustomerID (FK) (Non Clustered index)

I've read that placing a non clustered index on the most commonly used columns as well as foreign key columns will help to improve performance. I created a simple join query before using indexes and after using indexes, but for me I can't seen the increased performance when using indexes, can any body help me out? The images below are the execution plans before indexes and after using them.

Before Indexes: enter image description here

After Indexes: enter image description here

EDIT: this is the SQL syntax i am using

SELECT cu.ForeName + ' ' + cu.SurName AS 'Name' 
FROM dbo.Customers cu
INNER JOIN dbo.Renters re ON re.CustomerID = cu.CustomerID
WHERE cu.ForeName = 'Daniel'

EDIT This is my index syntax using the ones posted in the reply below:

CREATE NONCLUSTERED INDEX [ix_Customer] ON [dbo].[Customers] ( [ForeName] ASC, [CustomerID] ASC ) INCLUDE ( [SurName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Can you please also show the SQL query that lead to these execution plans? — marc_s
– marc_s, Commented Mar 28, 2013 at 14:10
Also it does look like you are using your indexes, is the performance the same? does it take the same time to run both queries? — JNK
– JNK, Commented Mar 28, 2013 at 14:16
Hey JNK, yeh the performance is the same and so are the timings, im just looking for a way to improve the timing etc when a name is searched for — Peter
– Peter, Commented Mar 28, 2013 at 14:18
@Peter please post your index syntax, and let me know if you have tried the ones I included in my answer below. — JNK
– JNK, Commented Mar 28, 2013 at 14:31

JNK · Accepted Answer · 2013-03-28 14:15:24Z

4

Based on your query the best nonclustered indexes to build would be:

CREATE NONCLUSTERED INDEX ix_IndexA on dbo.Customers (Forename, CustomerID) 
     INCLUDE (SurName)

CREATE NONCLUSTERED INDEX ix_IndexB on dbo.Renters (CustomerID)

You want your key fields to be on your filter or JOIN columns, and your INCLUDE columns are at the leaf level to get returned in the SELECT.

answered Mar 28, 2013 at 14:15

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Over a year ago

Hi @JNK, i just wanted to ask you about the INCLUDE (SurName) and what this does as I am not 100% sure?

JNK Over a year ago

@Peter INCLUDE keeps the data for the INCLUDED columns in the leaf level of the index but not in the tree - if you think of an index like a phone book, it's keyed on LastName, Firstname (how it's sorted) but the leaf level (the entries) has PhoneNo, Address, Zipcode data as well. You can't sort by PhoneNo but once you get to the entry you have that data. In your query you are SELECT ing surname but it's not in any of your index keys and you don't sort on it, so I included it. This avoids a key lookup or bookmark lookup where the engine queries the clustered index for the data.

Collectives™ on Stack Overflow

SQL Server index performance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related