SQL Query Optimization FAQ Part 1 (With video explanation)

• You cannot create composite indexes on data types like varchar(max) , XML.
• Size of the index increases the key size which further impacts performance.
• Can exceed the constraint of index key size of 900 bytes.

The best practice for creating covering index is by using the include keyword. So you create the non-clustered index as shown in the below code snippet.

You can see that we have included the customer code primary key as the part of our non-clustered index customer name and when you re-run the plan you can see how book mark lookup is removed and clustered indexes are used.

CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[Customers]
(
CustomerName
) INCLUDE (CustomerCode) ON [PRIMARY]

Do we have any other efficient way of creating covering indexes?

The composite non-clustered index creation has certain draw backs:-

->You cannot create composite indexes on data types like varchar(max) , XML.
->Size of the index increases the key size which further impacts performance.
->Can exceed the constraint of index key size of 900 bytes.

The best practice for creating covering index is by using the include keyword. So you create the non-clustered index as shown in the below code snippet.

You can see that we have included the customer code primary key as the part of our non-clustered index customer name and when you re-run the plan you can see how book mark lookup is removed and clustered indexes are used.

CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[Customers]
(
CustomerName
) INCLUDE (CustomerCode) ON [PRIMARY]

How does size of the index affect performance?

Let us make a statement before we answer this question:- 'The more the size of the index key, performance will also degrade accordingly'. Let's do a small practical example to understand the impact of the same. Below is a simple customer table with customer code and customer name field and below are the data types as shown in table.

* Please note that customer code has a clustered index and the data type is int.

Field name	Data type
Customer code ( Clustered index)	int
Customer name	Varchar(50)

Now fire the below SQL statement. In the below SQL script "Customer" is the database and "CustomerTable" is the table. This SQL statement gives details about number of pages which is used to store indexes.

Just to revise people who are new SQL Server pages. Pages are fundamental unit of storage. In other words the disk space allocated on the hard disk is divided in to pages. A page size is normally of 8 kb. Page has data rows of SQL Server. If you can fit your rows in one page you do not need to jump from on page to other page, which increases performance.


Now if you fire the below SQL statement it will give you number of pages consumed to store the clustered index. You can see from the results below clustered index with 'int' uses 12 pages.

SELECT Indextable.Name
,Indextable.type_desc
,PhysicalStat.page_count
,PhysicalStat.record_count
,PhysicalStat.index_level
FROM sys.indexes Indextable
JOIN sys.dm_db_index_physical_stats(DB_ID(N'Customer'),
OBJECT_ID(N'dbo.CustomerTable'),NULL, NULL, 'DETAILED') AS PhysicalStat
ON Indextable.index_id = PhysicalStat.index_id
WHERE Indextable.OBJECT_ID = OBJECT_ID(N'dbo.CustomerTable')

Name	type_desc	page_count	record_count	index_level
IX_Customers	CLUSTERED	12	3025	0
IX_Customers	CLUSTERED	1	12	1

Now let's change the data type to numeric as shown in the below table.

Field name	Data type
Customer code ( Clustered index)	numeric
Customer name	Varchar(50)

Now if you fire the above query you can see the number of pages have increased which means SQL engine has to jump between pages to fetch the data row. In other words this decreases performance.

Name	type_desc	page_count	record_count	index_level
IX_Customers	CLUSTERED	14	3025	0
IX_Customers	CLUSTERED	1	14	1

If you change the data type to 'char' or 'varchar' the 'page_count' will have the worst value. Below figure shows how the number of pages have increased because of the data type.


So concluding the more the size of the index, the more the number of pages and worse the performance. Avoid data types like 'char' , 'varchar' , 'int' is the preferred data type for indexes.

If the clustered indexes are small we get the following advantages:-

• Reduces I/O as the number of 8 KB page decrease.
• As the size decreases caching size is increased.
• Less storage space required.

How does unique column increase index performance?

Creating indexes on unique column values increases performance of indexes. So unique columns always are best candidates for clustered indexes. Let's demonstrate how creating clustered indexes on unique columns increases performances as compared to creating indexes on columns with non-unique values.

Below is a simple query which is fired on the customer table which has two fields 'active' and 'customercode'. 'customercode' field has unique values while 'active' field has non-unique values , it has only two values '1' and '0'. Below is a SQL statement which is fired with statistics IO ON.

set statistics io on
SELECT *
FROM Customers where active=1 and CustomerCode=500008

If you fire the above statement with clustered index on 'customercode' field the logical reads are '2'.
 

(1 row(s) affected)
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead 
reads 0.

If you fire the above SQL statement with clustered index on 'active' field the logical reads are 16. In other words the performance is hampered.

(1 row(s) affected)
Table 'Customers'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead 
reads 0.

How important is the order of indexes?

When you create composite indexes the order of columns in composite index is also of prime importance. For instance for the below customer table we have created an index with 'customercode' as the first column and 'customerid' as the second column.


If most of the SQL statements in your project are as shown below, i.e. with 'customerid' as the first column in the where clause then 'customerid' should be the first column in the composite index.

Select * from Customer where Customerid=1 and CustomerCode=9898998

If 'customercode' is the first column in the where clause as shown below then the composite key should have 'customercode' as the first 
column in your joint index key.

Select * from Customer where CustomerCode=9898998 and Customerid=1

What is the preferred data type for indexes?

'integer' data type is the preferred data type for indexes as compared to string data types like 'char' , 'varchar'. If needed you can also choose 'bigint' , 'smallint' etc. Arithmetic searches are faster as compared to string searches.

What are indexed views and how does it improve performance?

Indexed view is a virtual table which represents an output of a select statement. In general when we create a view the view does not store any data. So when we query a view it queries the underlying base table.

But when we create indexed views, result set is persisted on the hard disk which can save lot of overheads. So let's understand advantages and disadvantages of indexed views.

Advantages

By creating indexed views you can store pre-computed values , expensive computations and join in the indexed views so that we do not need to recalculate them again and again.

Disadvantages

Indexed views are not suitable for tables which are highly transactional because SQL engine needs to also update indexed views if base table changes.

Can we see practically how indexed views increase performance?

Below is a simple query which calculates count as per 'registerdate' from the customers table.
SELECT dbo.Customers.RegisterDate as RDate, count(*) as CountCust
FROM dbo.Customers
group by dbo.Customers.RegisterDate
In order to understand the same we will do the following:-

• We will run the above aggregate select query on the table without indexes and measure logical reads for the same. We will also measure logical reads for inserts on the same.
• In the next step we will create a clustered index and again record logical scan for select and insert.
• Finally we will create an indexed view and again measure logical scans for select and insert.

Benchmarking without indexes

So let's fire the above aggregated statement without any indexes with statistics io on as shown in the below snippet.
Set statistics io on
go
SELECT dbo.Customers.RegisterDate as RDate, count(*) as CountCust
FROM dbo.Customers
group by dbo.Customers.RegisterDate
If you see the SQL plan it uses hash match.


The total logical reads for select query for the above operation is 16.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Customers'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
For insert the total logical read operation is 1.
set statistics io on

insert into Customers values(429,'Shiv','1/1/2001')

Table 'Customers'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Benchmarking with clustered indexes

Now let's go and create a clustered index on the 'customers' table , set the statistics io on and see the plan. You can see that it uses the index scan and uses the stem aggregate logical operator to give the results.

The select query takes 10 logical reads to execute the above SQL query.
(6 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Customers'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
With clustered index insert takes 2 logical reads.
Table 'Customers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Benchmarking with indexed views

Now let's create an indexed view and try to benchmark again for select and insert queries. Below is the SQL snippet to create index views. Please note 'count' will not work with indexed views, we need to use 'count_big'. Some function like 'avg' etc are also not allowed with indexed views.

CREATE VIEW IndexedView
WITH SCHEMABINDING as
SELECT dbo.Customers.RegisterDate as RDate, COUNT_BIG(*) as CountCust
FROM dbo.Customers
group by dbo.Customers.RegisterDate

GO

CREATE UNIQUE CLUSTERED INDEX NonInx ON IndexedView(RDate);

GO
If you see the plan you will see that there are no aggregate logical operators because it's pre-calculated in the indexed views.


The select statistics show the logical reads as 1.

Table 'Customers'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Insert logical scan is 18 for indexed views as shown below.
Table 'IndexedView'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 3, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

So concluding indexed views are good for select aggregate queries they increase performance. On the other hand if your table is highly transactional then indexed views can decrease your performance.

Below is a simple performance impact graph which plotted using the above benchmark data. You can see how logical scan is decreased with select plus indexed views and how insert logical is increased with indexed views.



Performance is impacted in insert's on indexed views because any updates to the base table needs to be also reflected back to indexed views which decreases performance.



Note: - If your base table is highly transaction indexed views will decrease performance of your insert, update and delete queries.

How do we specify index hints?

Does index hints increase performance?
References

• http://en.wikipedia.org/wiki/Query_plan
• Execution plan basics by Grant FritChey http://www.simple-talk.com/sql/performance/execution-plan-basics/
• The best guide on logical and physical operators http://msdn.microsoft.com/en-us/library/ms191158.aspx
• Inside Microsoft SQL Server 2005: Query Tuning and Optimization by Kalen Delaney

Detail links which discuss Physical and logical read

• http://netindonesia.net/blogs/kasim.wirama/archive/2008/04/20/logical-and-physical-read-in-sql-server-performance.aspx
• http://msdn.microsoft.com/en-us/library/aa224763(SQL.80).aspx
• http://msdn.microsoft.com/en-us/library/ms184361.aspx