Query Execution Plan

When you are writing a complex query, it is always a good idea to check out the query execution plan to see what is the most taxing operation and if you can do something about it.

When you run a query, SQL Server does two things...

1. First it checks the validity of the query to ensure there are no syntactical errors and the objects that you are trying to use such as a table, view or a UDF actually exists.
2. Once the first condition is satisfied, SQL Server then determines the fastest and shortest path it can take to execute your query. You may have indexes on the table(s), but whether SQL server will use them to execute your query largely depends on the optimizer. If a complete table scan is faster, then it will scan the table instead of indexes, but more often than not, it will use the indexes to determine the fastest route.

An execution plan is made up of a series of primitive operations, such as reading a table from top to bottom, using an index, performing loop operations or hash joins. All primitive operations produce an output. They may have one or more inputs and an output of one primitive operation may be an input in the next. Database Optimizer determines the optimal execution plan.

In order to see the query execution plan, open the Query Analyzer and navigate to Query > Display Estimated Execution Plan to see the execution plan without actually executing your query. If you want to execute query and also have query anlayzer display the execution plan select "Include Actual Execution Plan."

If you are running SQL Service Profile, you can capture an event called MISC:Execution Plan to show the execution plan used by the queries that are running in your environment.

You can also run "SET SHOWPLAN_TEXT ON" command in Query Analyzer to show execution plan for any subsequent queries that you may run. If your query uses temp tables, you will have run the command "SET STATISTICS PROFILE ON" before you run the query.

Below are some of the execution plans based off of some sample queries I ran.

1. Simple Select

SELECT U.UserName,S.StoreID FROM  SecUsers U INNER JOIN SecUserStores S ON U.UserID=S.UserID

In this case, 39% of the cost is in join, 40% and 21% in index scan on two tables.

 2. Union - note I am basically running same query twice, but you get the picture.

SELECT S.UserName,SS.StoreID FROM  SecUsers S INNER JOIN SecUserStores SS ON S.SecUserID=SS.SecUserID
UNION ALL
SELECT S.UserName,SS.StoreID FROM  SecUsers S INNER JOIN SecUserStores SS ON S.SecUserID=SS.SecUserID

In this case, since I am basically running same query twice, everything is doubled, but as you can imagine, more complex a query, more taxing.

3. Using Temp Table

DECLARE @tmpTable TABLE(UserName varchar(50), StoreID INT)

INSERT INTO @tmpTable(UserName,StoreID)
SELECT S.UserName,SS.StoreID FROM  SecUsers S INNER JOIN SecUserStores SS ON S.SecUserID=SS.SecUserID

SELECT * FROM @tmpTable

You should avoid temp tables as much as possible, because SQL Server caches the optimal execution plan for regular stored procedures, but it can't do so for a temporary table and must determine the best execution plan everytime.

 4. Sorting Example

SELECT S.UserName,SS.StoreID FROM  SecUsers S INNER JOIN SecUserStores SS ON S.SecUserID=SS.SecUserID ORDER BY SS.StoreID

As you can see, Sort is costly. Try to avoid ORDER BY clause as much as you can. If you must sort, either create an index or sort in your code.

5. WHERE Clause   

SELECT * FROM Orders WHERE BusDT > 30450

 Sometime when you don't have appropriate indexes in place and you show an execution plan, optimizer will provide you an index that you can add to your table.

6. Using a UDF in WHERE Clause

SELECT * FROM Orders WHERE BusDT < dbo.Func_TOOADATE('01/01/2012')

Using a user defined function in where clause in taxing. In essence you are executing two queries. Also, as with temp tables, execution plan for in-line functions is not cached and optimizer must determine the execution plan everytime. You should avoid using functions in WHERE clause whenever possible.

Benefits of Database Indexing

In my previous Post I talked about various types of indexes you can create in SQL Server. Today we will discuss the benefits of Indexing and why you should have at least a clustered index in every table.

Every database system offers some kind of indexing that allows for sublinear time lookup for increased performance as opposed to linear search which is highly inefficient. For example, if a table is un-indexed, database engine will examine each record to find the searched record, resulting in approximately half the data rows being scanned on an average. Further, if the searched record doesn't exist, all the data rows will be scanned before a match not found result is returned. Indexing allows for faster searches resulting in improved performance.

Indexing however is a double edged sword. While more indexes generally will result in faster data retrieval from a table, but the more indexes you have, the longer it will take to insert new data in the table.

Depending on your table definition, some indexes are automatically created by the SQL Server. For example, to enforce a UNIQUE constraint. You can create other indexes either via CREATE INDEX statement or by using SQL Management Studio.

Below are some of the guidelines you can use when creating indexes. This is not an exhaustive list and may not be suitable in all situations, so use this as a general guideline.

1. When creating an index, choose the right data type. Certain data types are more suitable for indexing than others. Integer data types (INT, BIGINT, SMALLINT, TINYINT) are good candidates for indexing because of their specific size and are easy to compare and perform other mathematical operation. CHAR, VARCHAR etc. on the other hand are much less inefficient.

2. Make sure that indexes are actually used. I have often seen database schema where indexes were created in advance but any of the data retrieval operations don't use the keys that are indexed.

3. Also keep in mind when a retrieval query applies a SQL function to the keys that are part of the index, the indexed value is not used in the query. For example, you convert a date column which is part of the cluster to a string while retrieving the data. In this case, the index won't be used in the retrieval process.

4. If you create an index with multiple columns, pay attention to the order of the columns. Data is stored in order the columns are used in the index. The column with less unique data should be used first and so on.

5. Remember each non-clustered index column is a pointer to the clustered index columns (assuming clustered index exists in the table). Hence, care should be taken in selecting the number of columns used in the clustered index. For the same reason, try to avoid or limit using the columns that are frequently updated in a clustered index.

6. Be extra careful when rebuilding a clustered index. I have seen it many times where someone has created a SQL job to drop a clustered index and then used the "CREATE INDEX" command to recreate the index. When you drop a clustered index and then recreate it, you end up rebuilding non-clustered indexes multiple times. To avoid this, use DROP_EXISTING clause along with CREATE INDEX which will rebuild non-clustered indexes only once.

As explained in previous Post, use Fill Factor wisely.

I realize this is a long post but I wanted to keep all the crucial points in one blog to make it easier to read. As always your comments are appreciated and please don't hesitate to add other points I may have missed or let me know if I missed anything.

Creating Indexes using T-SQL

In my previous post, we discussed creating indexes using SQL Management Studio. You can achieve the same results using SQL Scripts.

Using T-SQL

1.  Create a Non-Clustered, Non-Unique Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:56:50 ******/
CREATE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)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

2. Create a Unique Non-Clustered Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:59:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)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

HINTS / CLAUSE DEFINITIONS

PAD_INDEX
This option specifies whether you want to leave some space in each node (also referred to as page) for future inserts/updates. This is only useful when you specify a fill factor, because it uses the % specified in fill factor. Default is OFF.

STATISTICS_NORECOMPUTE
This flag determines whether index statistics are automatically recomputed. If you set it to ON, your SQL Query Optmizer may not be able to pick the optimal execution plan for any queries using this table.

SORT_IN_TEMPDB
Specifies that during index build/rebuild, intermediate sort results will be stored in tempDB. If your tempDB is on a different disk(s) than your production DB, it may reduce the time needed to create an index.

IGNORE_DUP_KEY
As I discussed in one of my previous post, when this option is ON and an attempt is made to insert a duplicate key, server issues a warning and ignores the duplicate row. If this option is OFF, server issues an error message and rolls back the entire INSERT. This clause can only be turned on if you have specified UNIQUE clause in your index.

DROP_EXISTING
This clause signals the SQL Server to drop and rebuild the pre-existing index with the same name. When you drop a clustered index, all non-clustered indexes must be rebuilt because they contain pointers to clustered index keys. This clause is extremely useful when dropping a clustered index on the table that also has non-clustered indexes. The non-clustered indexes are rebuilt only once and only if the keys are different.

ONLINE
This clause needs some explanation. When this clause is ON, it means database can be online i.e. being used for other processes while Index is being built or rebuilt. In other words, Index operations do not need exclusive lock. Default is OFF meaning indexing operation requires exclusive lock on the table. This was a nice enhancement in SQL 2005. Prior versions required exclusive lock for index operations. Some of columns such as VARCHAR(MAX) cannot be indexed while online.

ALLOW_ROW_LOCKS
This clause specifies whether data row is locked when performing operations on the indexed keys. When performing OLTP operations, it is a good practice to leave turn this ON.

ALLOW_PAGE_LOCKS
This clause determines whether the entire data page will be locked during index operations.

If both clauses are OFF, SQL Engine will not lock data page or data rows instead entire table will be locked during the operation.

Generally, it is a good idea to leave the defaults alone unless you have a very good reason to change them.

Thank you and as always, your comments are welcome!

Creating Indexes using Management Studio

In my previous two posts, I wrote about the indexes in general and why you should create them. In this blog, I will walk through creating an index using Visual Studio Management.

Note: I have used SQL 2008 Management Studio, but the process is more or less same in SQL 2005.

Using SQL Management Studio

1. Open Management Studio and expand the database > table you want to index.

2. Select the appropriate name. I generally add a prefix to the name "idx_". Also select whether the index is a clustered or non-clustered index. (Remember, only one clustered index is allowed in a table). Check Unique checkbox if you want the index to not allow duplicate keys.

3. Click on "Add" and select the columns you want to index. As we discussed in previous post, CHAR/VARCHAR are not good candidate keys, unless you have to use them. The order is also very important. The key which will have most duplicate data should be ranked first.

4. Go to Options and adjust the settings as needed. Defaults are generally OK here but you may want to adjust fill factor. See my previous post to learn more about fill factor.

That's all there is to it. Click on OK and you are done!

As always, feel free to leave me comments!

Thank you