Optimizing SQL Server Joins

Today a colleague asked me a question about performance optimization regarding joins.  I gave him a pretty detailed answer over the phone, but I do not think I really made my message stick, which is the reason for this post.  The first and most important thing to remember is the optimizer can only choose one physical operator per table.  The only exception to this is when the optimizer decides to use index intersection, http://www.sqlmag.com/Articles/ArticleID/94116/94116.html?Ad=1 (SQL 2005+).  Index intersection occurs when the optimizer creates its own join and uses two indexes to satisfy the predicate.   While index intersection is still aligned with what I said, index intersection does add an additional table reference to the query plan, which should be noted.  If the same table is referenced multiple times, the execution plan will have more than than one physical operator at varying stages of the execution plan.  When tables are joined together SQL Server creates a constrained Cartesian product, which is nothing more than matching rows based on a given join expression. To do this SQL Server uses a join (Merge, Hash or Nested Loop)  and creates an INNER (Bottom) and OUTER (Top) set.  I use the word set here because the INNER does not have to be a table.  INNER can actually be a constrained Cartesian product. Essentially, the optimizer chooses a base (OUTER) set and will then filter the INNER set based on the results from the outer set.  The optimizer then continues to filter the set for each join, until the query is satisfied.  It is important to know that the optimizer is under no obligation to join tables in the order you have specified.  The optimizer is free to rearrange joins as it sees fit.  Let’s start by creating our sample objects.

USE [tempdb]
GO

SET NOCOUNT ON;
GO

IF OBJECT_ID('tempdb.dbo.State') IS NOT NULL
BEGIN
    DROP TABLE dbo.[State];
END
GO

CREATE TABLE dbo.[State](
State_Cd CHAR(2),
Descr VARCHAR(150)
);
GO

INSERT INTO dbo.[State] ([State_Cd],[Descr]) VALUES ('AL','Alabama');
INSERT INTO dbo.[State] ([State_Cd],[Descr]) VALUES ('LA','Louisiana');
GO

IF OBJECT_ID('tempdb.dbo.City') IS NOT NULL
BEGIN
    DROP TABLE dbo.[City];
END
GO

CREATE TABLE dbo.[City](
State_Cd CHAR(2),
City_Cd VARCHAR(100)
);
GO

INSERT INTO dbo.[City] ([State_Cd],[City_Cd]) VALUES ('AL','Mobile');
INSERT INTO dbo.[City] ([State_Cd],[City_Cd]) VALUES ('LA','New Orleans');
INSERT INTO dbo.[City] ([State_Cd],[City_Cd]) VALUES ('LA','Luling');
GO

IF OBJECT_ID('tempdb.dbo.Zip') IS NOT NULL
BEGIN
    DROP TABLE dbo.[Zip];
END
GO

CREATE TABLE dbo.Zip(
City_Cd VARCHAR(100),
Zip_Cd VARCHAR(10),
);
GO

INSERT INTO dbo.Zip ([City_Cd],[Zip_Cd]) VALUES ('Mobile','36601');
INSERT INTO dbo.Zip ([City_Cd],[Zip_Cd]) VALUES ('New Orleans','70121');
INSERT INTO dbo.Zip ([City_Cd],[Zip_Cd]) VALUES ('Luling','70070');
GO

Now that we have our tables, lets execute a very simplistic query.

Note:   There are no indexes present on any tables, so all queries will result in a  table scan.

--Qry 1
SELECT s.[Descr]
FROM dbo.[State] s

Now let’s add a second table to the query to see what happens.  What we should see is a nested loop join and two table scans. 

--Qry 2
SELECT s.[Descr],c.City_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c    ON s.[State_Cd] = c.[State_Cd]

The screenshot above shows the State table has been chosen as the base query.  As you can see, each table is represented by a single Table scan operator.   Let’s add one more table to the mix to see how the optimizer will react.

--Qry 3
SELECT s.[Descr],c.City_Cd,z.Zip_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c    ON s.[State_Cd] = c.[State_Cd]
INNER JOIN dbo.[Zip] z ON z.[City_Cd] = c.[City_Cd]

 

The optimizer decided to make the first OUTER table State and then decided to make the INNER  a constrained Cartesian product of City and Zip .   This is especially important when you are optimizing code because it is a lot easier to fix problems when you know how indexes and joins really work.  For this example, I see that we are scanning Zip and City, so that tells me that I am missing an index on these two tables. Remember the general rule of thumb is to have indexes on all column participating in the join expression. I will start the optimization process by adding an index to the Zip table, on the City_Cd column.

CREATE NONCLUSTERED INDEX ncl_idx_Zip_City_Cd ON dbo.[Zip](City_Cd);

Wow, look at how the query plan changed.  We now see our index seek on Zip, but we now see a RID lookup.  Lookups can become performance bottlenecks really quickly and can sometimes cause blocking or even worse dead locks.  Key Lookups can cause blocking and deadlocks because the optimizer has to take a shared lock on the Clustered Index to get the data that is missing from the nonclustered  index and this causes a problem when an insert/update/delete occurs because it requires an exclusive lock, on the Clustered Index.  In our example, We have an index on Zip which only contains the column City_Cd; however, we are selecting Zip_Cd.  Because Zip_Cd does not exist in the index, the optimizer has to go back to the heap to get the remaining column data.  To solve this problem we need to add Zip_Cd to the index.  I will be adding the Zip_Cd column via the INCLUDE clause.  I chose INCLUDE because I am not using this column in the predicate and using the INCLUDE clause keeps the index relatively small because the value is only stored at the leaf level of the nonclustered index.  You may want to add the column to the index key if you use the column in a lot of predicates because SQL Server maintains statistics on index key columns, but not columns in the INCLUDE clause.  Statistics are used used by SQL Server to estimate cardinality.  Better cardinality estimates allow the optimizer to make better decisions about what operators are best for the given query.  Essentially, better cardinality estimates can be the difference between a scan and a seek. You have to weigh the cost of index maintenance and performance when deciding which method to choose.  For more information regarding INCLUDE please visit this link, http://msdn.microsoft.com/en-us/library/ms190806.aspx.

Note: If you are not sure what columns need to be added to the index you can hover your mouse over the lookup and look at the output list.  The index that you need to add the columns too will always be the seek operator to the right of the lookup operator.

IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'ncl_idx_Zip_City_Cd')
BEGIN
    DROP INDEX ncl_idx_Zip_City_Cd ON dbo.Zip;
END
GO

CREATE NONCLUSTERED INDEX ncl_idx_Zip_City_Cd ON dbo.[Zip](City_Cd)INCLUDE([Zip_Cd]);
GO

Now Execute the query again.

--Qry 3
SELECT s.[Descr],c.City_Cd,z.Zip_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c ON s.[State_Cd] = c.[State_Cd]
INNER JOIN dbo.[Zip] z ON z.[City_Cd] = c.[City_Cd]

Now that is a little better but lets make this query even faster. Next, I will add an index to the City table, making sure to include City_Cd.

CREATE NONCLUSTERED INDEX ncl_idx_City_State_Cd ON dbo.City(State_Cd)INCLUDE([City_Cd]);
GO

Now execute the query again.

--Qry 3
SELECT s.[Descr],c.City_Cd,z.Zip_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c ON s.[State_Cd] = c.[State_Cd]
INNER JOIN dbo.[Zip] z ON z.[City_Cd] = c.[City_Cd]

How about them apples?  By adding the proper indexes in place we can now get index seeks across the board.  It is important to note that we cannot seek State because we have no predicate filter on any columns in the state table, so the optimizer has to scan.  When you are trying to optimize queries the first place to look is the execution plan.  If you see a lot of scans, you have a lot of optimization potential.  Remember that you want to make sure all columns in the select, the join and the where clause are present in your index.  Please do not tell your boss that you need to create indexes to cover every query in your environment. You will not be able to fully cover every query in your environment, but the important thing is to optimize and cover the queries that are really expensive or causing problems. Who knows you maybe able to cover multiple queries by creating or modifying a single index.

That’s it for now.  I hope I have cleared up how the optimizer handles joins and given you greater insight on how to optimize joins. Until next time happy coding.

Determine Which Indexes Are Not Being Used

Have you ever had the need to find out which indexes are actually being used?  If you are using SQL 2005 and greater, you are in luck.  SQL Server 2005 introduced a new dynamic management view that gives all the usage statistics, sys.dm_db_index_usage_stats (http://msdn.microsoft.com/en-us/library/ms188755.aspx).  What does this mean?  Well it means we can capture which indexes are being used and which ones are an administrative burden.  What’s the catch?  The catch is the usage statistics do not persist a SQL Server restart. The statistics will not be of much value after a server restart, so an option may be to insert the results of the view into a permanent table, for later analysis.  Let’s look at an example.

SELECT 

    QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TableName,

    i.name AS IdxName,

    i.type_desc AS IdxType,

    ius.user_seeks,

    (ius.user_seeks*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Seek],

    ius.user_scans,

    (ius.user_scans*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Scan],

    ius.user_lookups,

    (ius.user_lookups*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Lookup],

    ius.user_updates

FROM sys.indexes i

LEFT JOIN sys.dm_db_index_usage_stats ius

    ON  ius.object_id = i.object_id

        AND ius.index_id = i.index_id

        AND database_id = DB_ID()--Current DB

INNER JOIN sys.tables t

    ON t.object_id = i.object_id

INNER JOIN sys.schemas s

    ON t.schema_id = s.schema_id

WHERE

    t.type = 'U'

    AND t.is_ms_shipped = 0

ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

Note: I have used a left join.  If an index has never been used, it will not have an entry in sys.dm_db_index_usage_stats.

The columns that really drive this view are user_seeks, user_scans, user_lookups, and user_updates.  User_seeks represents the number of seeks on a given index, since SQL Server last started.  Likewise, user_scans and user_lookups represent the number of scans and the number of lookups respectively.  One of the most important columns is user_updates.  User_updates represents the number of inserts, updates, and deletes to the index. If you find your index in a scenario where the number of user_updates is greater than the number of seeks, lookups, or scans, you should consider dropping the index.  There is no magic number to dictate when an index should be dropped because of the maintenance overhead.  You should use your best judgment when dropping an index. 

There you have it a simplistic method to get the index usage details.  How else can this dynamic management view be used?  I leave this to you to find out, but I will leave you with one other use.

The other use for this view is to identify the most queried tables.  By looking at the index usage stats, we can deduce how often the table is queried. 

--Most Accessed Tables

SELECT 

    DB_NAME(ius.database_id) AS DBName,

    OBJECT_NAME(ius.object_id) AS TableName,

    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed    

FROM sys.indexes i

INNER JOIN sys.dm_db_index_usage_stats ius

    ON  ius.object_id = i.object_id

        AND ius.index_id = i.index_id

WHERE

    ius.database_id = DB_ID()

GROUP BY 

    DB_NAME(ius.database_id),

    OBJECT_NAME(ius.object_id)

ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

That is it.  I have provided two uses of sys.dm_db_index_usage_stats.  I hope this post will help you identify poorly performing indexes or you most active tables. 

Happy Coding.

Are all covering indexes created equal?

I have been getting a lot of questions regarding indexes over the past few weeks, so I thought I would make another post about indexes. In this post, I will be focusing on covering indexes.  A covering index is an index that covers all the columns for a given query.  A covering index can be created by either, adding the columns to the index key, or by adding the columns to the include clause of the create index statement. I will focus on the key differences between adding columns to the index key and adding columns to the INCLUDE clause.  I will look at the differences in size, statistics, and the execution plan.

Let’s get started by creating our test table.

USE [tempdb]

GO

SET NOCOUNT ON;

GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')

BEGIN

    DROP TABLE dbo.[TestData];

END

GO

CREATE TABLE dbo.TestData(

RowNum INT PRIMARY KEY,

SomeId INT,

SomeCode INT

);

GO

INSERT INTO dbo.[TestData] (

    [RowNum],

    [SomeId],

    [SomeCode]

) 

SELECT TOP 1000000 

    ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNum,

    ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, 

    CASE 

        WHEN ROW_NUMBER() OVER (ORDER BY t1.NAME) % 2 = 0

        THEN 1

    ELSE 0

    END AS SomeCode

FROM 

    Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2

GO

Now that we have our test table, I will create a covering index using the INCLUDE clause.

CREATE INDEX ncl_idx_TestData_Cover1 ON dbo.TestData(SomeID) INCLUDE(SomeCode);

GO

I will issue a simple query to select data from the table, using the new index.

SELECT 

    someid,

    somecode

FROM dbo.[TestData]

WHERE

    someid = 500 

    AND somecode = 1

The results of this query are inconsequential for our purposes, so I will not post them here; however, I will post the key attributes of the execution plan.

 

The above screenshot shows that our covering index is used.  The primary thing to note here is deviation between the actual and the estimated number of rows. I will get into why these numbers deviate later in the post.

Let’s have a look at the space consumed by the index.  I will  issue a system stored procedure called sp_spaceused to obtain the space consumption details.

EXEC sp_spaceused 'dbo.Testdata' 

As you can see from the screenshot above, our index size is 18008 KB or  17.58 MB = 18008 / 1024. Now that we have our index size, let’s look at the statistics. I will be using the DBCC command show_statistics to display statistical information about our index.

DBCC show_statistics('dbo.Testdata','ncl_idx_TestData_Cover1')

The main thing to note here is that the column “SomeCode” is not available in the density vector.  The density vector is located in the second result set produced by DBCC show_statistics.  Essentially, density is the uniqueness of the column data. For example, the density of some id is .0004.  The density calculation can be expressed as 1/(#Distinct Values). In the case of “SomeId” the density can be calculated as 1/2500= .0004.  The “SomeId,RowNum” density is calculated as 1/1000000 = .000001. I know the number of distinct values is equal to the number of rows because “RowNum” is a unique column.  If you want to calculate density programmatically, you can use a group by clause and a little creativity.

Note: you can use differing columns in the group by clause to get their respective density.

SELECT 1./SUM(cnt)

FROM (

SELECT 1 AS cnt

FROM dbo.testdata

GROUP BY someid,RowNum

) AS a

The optimizer uses density to estimate cardinality (estimated number of rows) when building a query plan.  Obviously, the better the cardinality the better the query plan. 

A benefit of the INCLUDE clause is the ability to supersede index limitations, such as the key column limit of  16, the  index key size limitation (900 bytes), and even include columns with data types like VARCHAR(MAX), VARBINARY(MAX), TEXT, IMAGE etc..

Next, I will create the second covering index, which includes all columns in the index key. I will drop the original covering  index, so I can accurately measure the index size.

DROP INDEX ncl_idx_TestData_Cover1 ON dbo.[TestData];

GO

----===== Covering index that includes both columns in the index key

CREATE INDEX ncl_idx_TestData_Cover2 ON dbo.TestData(SomeID,SomeCode)

GO

Now let’s run the same select query again.

SELECT 

    someid,

    somecode

FROM dbo.[TestData]

WHERE

    someid = 500 

    AND somecode = 1

As you can see the estimated number of rows deviates much less, from the actual, than the first covering index.  The difference in cardinality estimates is pretty negligible for this sample, but in a real world scenario, the deviation can be much larger.

Let’s have a look at the space used.

Note: You can use the same code as before.

As shown above, the index size is larger, when columns are added to the index key.  The new covering index is 18024 KB or 18024 / 1024 = 17.60 MB.  This index is larger because the columns are stored at all levels of the index, whereas, the include clause stores the column values at the leaf level only.  So why would you add columns to the index key?  This answer brings us to the statistics maintained by the new index. 

Note: You can use the same code as before but change the name of the index to ncl_idx_TestData_Cover2.

If you had not noticed from the screen shot above, SQL Server is maintaining the density of the “SomeCode” column in relation to the “SomeId” column, which was not the case for covering index 1.  This is why the optimizer is able to more accurately estimate cardinality.  In this case, the density value is represented as .0002.  Adding columns to the index key allows the optimizer to make better cardinality estimates, which may translate into more optimal query plans.

So what is the verdict?  My findings indicate that all covering indexes are  not created equal.  Both types of covering index has its own strengths and weaknesses. The type you choose will depend on your query and your business requirements.  There are a lot of advantages to using both types of covering indexes.  You will have to weigh performance against manageability and choose which is best for your environment.

References:

• Index with included columns: http://msdn.microsoft.com/en-us/library/ms190806.aspx
• Create Index: http://msdn.microsoft.com/en-us/library/ms188783.aspx
• DBCC Show_Statistics: http://msdn.microsoft.com/en-us/library/ms174384.aspx
• Sp_Spaceused: http://msdn.microsoft.com/en-us/library/ms188776.aspx

The Real SQL Pages, A Beginner’s Guide To Indexing

Okay, so the title may be a little lame :-) , but I think it suits the basic analogy to describe indexing quite well. Today I was asked on the MSDN forums, how indexing really works, in SQL Server. I began type a lot of techno babble and then thought to myself, will the OP even understand what I am talking about? The answer is probably not. I deleted all my text and came up with a simple analogy that is easy for everyone to visualize. Essentially, indexing in SQL Server works like a phonebook. That’s right ladies and gentleman, a phonebook.

How is indexing like a phonebook? Imagine that you have a phonebook in front of you and you have a handful of bookmarks. If you want to find all the phone numbers where the person’s name begins with A, you will place a bookmark at the first page of that letter and on each subsequent page of that letter. If you want a more granular bookmark, you can create a composite key, that contains information about names, within the letter A. These other keys will provide density details that allow the optimizer to make better cardinality estimates, or in your case allow you to find the pages you are looking for in the phonebook more quickly. For example, you have a composite index on (A,Adam’ Haines). The first letter will allow you to quickly identify all letters that start with A and the second column will allow you to more quickly identify names that are equal to ‘Adam Haines’ . You cannot directly retrieve the phone number for ‘Adam Haines’ , without using the letter bookmark because ‘Adam Haines’ is contained within the letter A. Essentially, we have use one of our letter A bookmarks and then jump to ‘Adam Haines’. Think about it this way. If I asked you to find all the phone numbers where the person’s name started with the letter A, you would place bookmarks on each page where the name starts with the letter A. If I asked you to find all the phone numbers where the person name is ‘Adam Haines’, your bookmarks would be useless because you are looking for a specific name and not a letter. Without knowing the letter, we have to flip through or scan the pages to find the value. If you want to be able to search for a specific name, you have to place a bookmark on the page where the name exists. Let’s see this in action.

We will start by creating our table structure:

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'PhoneBook')

BEGIN

    DROP TABLE dbo.PhoneBook;

END

GO

CREATE TABLE dbo.PhoneBook(

Letter CHAR(1),

Name VARCHAR(100),

PhoneNumber VARCHAR(12),

PRIMARY KEY (Letter,Name,PhoneNumber)

);

GO

INSERT INTO dbo.PhoneBook VALUES ('A','Adam''s DB Consulting','555-555-5555');

INSERT INTO dbo.PhoneBook VALUES ('A','Alex BI Consulting','555-555-1234');

GO

Now we can run a few simple queries. The first query will filter the data using a filter on the letter A. This query will yield an index seek because Letter is the first column of our index.

SELECT *

FROM dbo.[PhoneBook]

WHERE [Letter] = 'A' --Index seek

Results:

In the second query will use the same letter, but we will also include a specific name we want to find. The query plan is the same as the prior. On larger datasets, this query should be less costly because we are using a more granular predicate, so the optimizer should be able to find the row more quickly.

SELECT *

FROM dbo.[PhoneBook]

WHERE [Letter] = 'A' AND Name = 'Adam''s DB Consulting' --Index seek

In the third query we will try searching for just the specific name.

SELECT *

FROM dbo.[PhoneBook]

WHERE Name = 'Adam''s DB Consulting' --Clustered Index scan

GO

Results:

Wait what the heck happened? The optimizer scanned the index even though the Name column is included in the index key!!!!! The answer is quite simple. As I stated earlier, the only column that is “seekable” is the first column in the index key. The other columns are used for density vectors, which help with cardinality estimates. In no way do the other columns help satisfy a predicate, where the first column is not present. Think back to where we put that bookmark. We put the bookmark on the letter A and the names within A, but we did not include the letter, in our search. How do you know where that name is specifically, without knowing the letter? The reality is we cant, so we have to scan. This is why we have to create an index on the name column.

--Create an index on Name to get an index seek

CREATE UNIQUE NONCLUSTERED INDEX ncl_idx_PhoneBook_PhoneNumber ON dbo.[PhoneBook](Name);

GO

Now lets run the same query.

SELECT *

FROM dbo.[PhoneBook]

WHERE Name = 'Adam''s DB Consulting' --Nonclustered Index seek

GO

Results:

I hope this analogy has made things a little clearer. Now this by no means all there is to know about indexing, but it is a good fundamental look at how they work. Let me know if you guys have anything to add, or anything I may have missed.

Happy coding.

Superfluous columns…. more than a bad habit?

Are using superfluous columns, in a SELECT list, bad? Sure they are and for a multitude of reasons. The better question is, why do developers still use superfluous columns, knowing that they should not?  I believe developers get tunnel vision and forget to look at the big picture,  perhaps it is laziness, or perhaps the developer does not care because company policy is to fix performance problems with bigger hardware, which is common these days. For whatever the reason, tunnel vision and laziness usually lead to rapidly developed, non-performant code. Developers often forget the overall impact bad code can have on a system, no matter how simple.  The impact depends on varying factors including: access frequency, bytes returned, query performance etc... I am going to explore why superfluous columns are not a best practice.

Let’s get started by creating a sample table with a million rows.

USE [tempdb]

GO

IF OBJECT_ID('test..t') IS NOT NULL

DROP TABLE t

GO

SELECT TOP 1000000 --<<<LOOK! CHANGE THIS NUMBER TO CHANGE THE NUMBER OF ROWS!

        RowNum   = IDENTITY(INT,1,1),

        SomeID   = ABS(CHECKSUM(NEWID()))%2500+1, --<<<LOOK! CHANGE THIS NUMBER TO 1/400th THE ROW COUNT

        SomeCode = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

                 + CHAR(ABS(CHECKSUM(NEWID()))%26+65),

        SomeCode2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

                    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

                    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

                    + CHAR(ABS(CHECKSUM(NEWID()))%26+65),

        SomeCode3 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

                    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

                    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

                    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

   INTO dbo.t

   FROM Master.dbo.SysColumns t1,

        Master.dbo.SysColumns t2 

--===== Add the index to support both types of queries

CREATE INDEX IXC_TestData_Cover1 ON dbo.t(SomeID,SomeCode)

GO

Now that we have our table and test data, let’s run a simple query using select * and another select statement that uses fewer columns.  You should enable client statistics to view the bytes returned to the client.

Note: You can enable this via the toolbar, shown below, or by clicking the option from the query menu.

 

First run the query with superfluous columns ( Make sure you enable client statistics)

SELECT *

FROM dbo.t

WHERE

    someid < 500

GO

Now let’s run the query with fewer columns:

SELECT 

    RowNum,

    SomeID,

    SomeCode

FROM dbo.t

WHERE

    someid < 500

GO

The client statistics between the two runs should look similar to the screenshot below.  You will notice that the bytes received from the server has drastically decreased, for the query with fewer columns in the SELECT list. The bytes sent from client is the number of bytes that make up the query text, which is irrelevant to our tests.

The value of interest is Bytes received from server.  This represents the number of bytes to the client, from the server.   In our case, the bytes dropped from 1024209 to 551576.  To put the number of bytes reduced into perspective, lets crunch the numbers.  We know that the superfluous query used 1024209 bytes. We can transform this into the number of  MBs returned. We are going to base these numbers on a concurrent user load of 500 users. Our calculation becomes. ((Number of bytes * number of concurrent users) / 1024.0) / 1024.0.  The result of this calculation is shown below.

	Bytes	KB	MB
Superfluous Columns	512104500	500102.1	488.381
Non-Superfluous Columns	275788000	269324.2	263.012

As you can see, the number of bytes returned by the superfluous query is over 200 MB larger. This may not seem like much, but this is only one query and imagine if you have a 1000 concurrent users.  The superfluous column query would return approximately 500MB more data. This means the superfluous query would return nearly 1GB of data… now that is huge!

By selecting the columns we need, we are able to reduce the amount of data the web server has to process, which can lead to faster load times, less consumed resources, and a better end user experience.

So, your probably sold on the idea that superfluous columns are bad, but I have one more point to make.  Not only does the query return more bytes, but superfluous columns can affect the optimizer’s ability to use a covering index, even in a derived table/CTE.

Using the same sample, with a slight modification.

Superfluous query:

SELECT *

FROM dbo.t

WHERE

    someid = 100

GO

Below is the query plan.  What you see is that our index was able to be used; however, the index does not contain all the columns in our SELECT list, thus the optimizer has to go back to the HEAP to get the remaining column data.

Note: depending on the indexes in place the RID look could be replaced by a Key Lookup, if a clustered index exists.

Here is the non-superfluous column query:

SELECT 

    SomeID,

    SomeCode

FROM dbo.t

WHERE

    someid = 100

GO

These are the results from the query with non-superfluous columns:

You can test the same results with a CTE:

--this yeilds an index RID lookup

;WITH cte

AS

(

    SELECT 

        [RowNum]

        [SomeId],

        [SomeCode],

        [SomeCode2],

        [SomeCode3]

    FROM t

    WHERE 

        [SomeId] = 100

)

SELECT 

    [SomeId],

    [SomeCode]

FROM cte

--this yields an index seek via covering index

;WITH cte

AS

(

    SELECT 

        [SomeId],

        [SomeCode]

    FROM t

    WHERE 

        [SomeId] = 100

)

SELECT 

    [SomeId],

    [SomeCode]

FROM cte

Results:

Superfluous columns:

Non-superfluous columns:

So there you have it. Selecting more columns than you need, not only affects application performance, it affects the number of bytes returned to the client. All of these factors can  directly affect the user’s experience, which should be very important to developer/DBA staff.  Additionally, superfluous columns can cause bookmark lookups, which will degrade database performance. 

Scripting indexes using an inline TVF

It is sometimes necessary for a person to script  indexes, so that they can synchronize indexes between databases, programmatically drop/create indexes in TSQL code, or create index scripts to the file system.  SSMS gives users multiple methods to script indexes to a file, the clip board, or to a new query window; however, SSMS does not really offer a solid method to script indexes programmatically.  I have chosen to use a TVF (Table Valued Function) because of its functionality.  TVFs allow code logic to be encapsulated in a single object like a view; however, unlike views, TVFs accept parameters. The use of parameters allow TVFs to behave like parameterized views; however, do not expect huge performance gains from query plan reuse and caching, for metadata queries. The main purpose for supplying parameters is to extend the functionality, such that it would allow me to script indexes to differing filegroups.  The results of this TVF will generate create/drop statements for all the indexes and some other data relative to the index, such as type and id. 

Now to the code:

I will be using the AdventureWorks database to demonstrate this code.  You can find a copy of the database here: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

The TVF has two input parameters.  The first parameter is @Dest_FG.  @Dest_FG accepts the filegroup that the indexes should be created on.  The second parameter is @Move_NCL_Idx_FG, which accepts the filegroup nonclustered indexes should be created on.  The nonclustered indexes can be created on a different filegroup than the clustered index or heap.

The code to create the TVF

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_CreateIndexScripts]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    DROP FUNCTION [dbo].[fn_CreateIndexScripts]
END
GO

/*
==============================================================================
Author: Adam Haines
------------------------------------------------------------------------------
Date: 5/1/2009
------------------------------------------------------------------------------
Inputs:
@Dest_FG - This is the filegroup we should create the index on.  The default
    value for this parameter is the Primary filegroup.
@Move_NCL_Idx_FG - Use this to move nonclustered indexes to a different
    filgroup than the clustered index/heap. If this value is null, the non
    clustered indexes are created in the same filegroup as the clustered
    index/heap.
-----------------------------------------------------------------------------
Description:
-----------------------------------------------------------------------------
This inline TVF select indexes that meet the parameters and creates drop and
and create scripts for each returned row.
------------------------------------------------------------------------------
Sample execution:
SELECT *
FROM [dbo].[usp_MoveTablesToNewFG]('PRIMARY','Test_FG',NULL)
==============================================================================
*/

CREATE FUNCTION dbo.fn_CreateIndexScripts
(
    @Dest_FG VARCHAR(50) = 'PRIMARY',
    @Move_NCL_Idx_FG VARCHAR(50) = NULL
)
RETURNS TABLE
RETURN
(
SELECT
    SCHEMA_NAME(t.schema_id) AS [SchemaName],
    t.[name] AS [TableName],
    FILEGROUP_NAME(i.[data_space_id]) AS [FilegroupName],
    i.[index_id] AS [IdxId],
    i.[type] AS [IndexType],
    --************* CREATE "CREATE" INDEX SCRIPT ******************
    CASE WHEN i.[type] = 3 THEN --0=heap,1=clustered,2=nonclustered,3=xml,4=spatial
        'CREATE ' +
            CASE WHEN xi.secondary_type IS NULL --when no secondary type is present we know it is a primary index
            THEN 'PRIMARY '
            ELSE ''
            END    + 'XML INDEX ' +
            QUOTENAME(xi.[name]) + ' ON ' +
            QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +
            QUOTENAME(t.[name]) +
            '(' + QUOTENAME(COL_NAME(t.[object_id],ic.column_id)) + ')' +
            CASE WHEN xi.secondary_type IS NULL 
            THEN '' --when no secondary type is present we know it is a primary index and do need a using clause
            ELSE ' USING XML INDEX ' + QUOTENAME(PRI.name) +
                 ' FOR '+ 
                CASE xi.secondary_type
                    WHEN 'V' THEN 'VALUE'
                    WHEN 'P' THEN 'PATH'
                    WHEN 'R' THEN 'PROPERTY'
                ELSE '' 
                END
            END + ' WITH (Pad_Index = ' +
                CASE WHEN xi.[is_padded] = 1 
                THEN 'ON);'
                ELSE 'OFF);'
                END
    ELSE
        CASE WHEN i.is_primary_key = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(i.[name]) +
            ' PRIMARY KEY ' + COALESCE(i.[type_desc],'') + '(' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT 
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON ' + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON ' + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        WHEN i.is_unique_constraint = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(i.[name]) +
            ' UNIQUE ' + i.[type_desc] + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT 
            ELSE ''
            END  +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        WHEN i.is_unique = 1 THEN
            'CREATE UNIQUE ' + i.[type_desc] + ' INDEX ' + 
            QUOTENAME(i.[name]) + 
            ' ON ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT + ');'
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        ELSE
            'CREATE ' + i.[type_desc] + ' INDEX ' + 
            QUOTENAME(i.[name]) + 
            ' ON ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT + ');'
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        END 
    END AS CreateIdxScript,
    --************* CREATE "DROP" XML INDEX SCRIPT ******************
    CASE WHEN i.[type] = 3 THEN
        'DROP INDEX ' + QUOTENAME(xi.Name) + ' ON ' +
        QUOTENAME(SCHEMA_NAME(t.schema_id)) +     '.' +
        QUOTENAME(t.name)
    ELSE
        CASE WHEN i.[is_primary_key] = 1 OR i.[is_unique_constraint] = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' DROP CONSTRAINT ' + QUOTENAME(i.[name])  + ';'
        ELSE
            'DROP INDEX ' + QUOTENAME(i.[name]) + ' ON ' +
            + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ';'
        END
    END AS DropIdxScript,
    CASE 
    WHEN i.[type] IN(0,1) THEN 1
    WHEN i.[type] = 2 THEN 2
    WHEN i.[type] = 3 THEN
        CASE WHEN xi.[secondary_type] IS NULL 
        THEN 3 --Primary XML index
        ELSE 4 --Secondary XML index
        END
    --WHEN i.[type] = 4 THEN 5 --I am not generating spatial indexes
    ELSE 6
    END AS [CreateSeq],
    CASE 
    WHEN i.[type] IN(0,1) THEN 6
    WHEN i.[type] = 2 THEN 5
    WHEN i.[type] = 3 THEN
        CASE WHEN xi.[secondary_type] IS NULL 
        THEN 4 --Primary XML index
        ELSE 3 --Secondary XML index
        END
    --WHEN i.[type] = 4 THEN 2 --I am not generating spatial indexes
    ELSE 1
    END AS [DropSeq]
FROM sys.tables t
INNER JOIN sys.[indexes] i
    ON t.[object_id] = i.[object_id]
LEFT JOIN sys.[xml_indexes] xi
    ON xi.[object_id] = i.[object_id] AND
       xi.[index_id] = i.[index_id]
LEFT JOIN sys.xml_indexes AS PRI --primary XML Index info
    ON xi.object_id = PRI.object_id AND
       xi.using_xml_index_id = PRI.index_id
LEFT JOIN sys.[index_columns] ic
    ON xi.object_id = ic.Object_ID AND
       xi.Index_id = ic.Index_id
WHERE
    t.[is_ms_shipped] = 0 AND 
    t.[type] = 'U' 
GROUP BY
    t.[object_id],
    t.[name],
    t.[schema_id],
    i.[name],
    i.[type_desc],
    i.[object_id],
    i.[index_id],
    i.[is_primary_key],
    i.[is_padded],
    i.[is_unique],
    i.[is_unique_constraint],
    i.[is_hypothetical],
    i.[is_disabled],
    i.[type],
    i.[data_space_id],
    xi.[name],
    xi.[secondary_type],
    xi.[is_padded],
    ic.[column_id],
    Pri.[name]
)
GO

Sample Execution:

SELECT *
FROM [dbo].[fn_CreateIndexScripts](DEFAULT,DEFAULT)
WHERE
    IndexType IN(1,2,3)

Results:

There you go. A script that will generate indexes in the same or differing filegroups.  There are a lot of different uses for a script like this. I have a script in mind that will use this code to move tables and indexes to a different filegroup programmatically. 

More to come.. stay tuned.