Below given are some facts those can be used to optimize our query performance
Lets understand with one example
Create one table CityInfo with four columns ID, FirsName, LastName, City, no primary key & no index.
1. Below plan shows Sql uses 'Table Scan' because there is not exist any index
2. Here we created clustered index on ID, sql uses 'Index Scan' not 'Index Seek' because resulting record set is large.
3. Here we created non-clustered index on 'City', so sql found non clustered index on column of where clause, so it uses this non clustered index. But still columns of select list are not covered in this index so sql has to fetch data for these columns (id, LastName) from data pages for that sql either uses clustered index lookup or RID lookup.
So, Clustered index on ID present so here sql uses Clustered index lookup.
4. Now for above scenario if we removed clustered index that is on ID.
Sql will first use non clustered index created on city, but columns of select list are not covered in this non clustered index. so sql need to fetch data for these columns from pages for that sql need to use (Heap) RID Lookup because we removed clustered index, as shown in below example
When Sql uses Key Lookup / RID Lookup it down grades performance.
So how we can remove RID / Key Lookups?
Solution 1: Cover Index Solution 2: Include Index
Cover Index : Create non-clustered index with column of where clause and columns of select list.
Column of where clause is 'City', and select list has 'Id' and 'LastName' so will create Non-clustered index with 'City', 'Id' and 'LastName', as shown in pic.
Include Index : Create non-clustered index on column of where clause as ID, and include columns of select list in that non-clustered index, as shown in pic.
- Unique Key: If there is no primary key defined on a table sql uses "Table Scan" operator.table scan operator reads records one by one until it gets required record, so this operator is costly if table having large number of rows.
When we create primary key on table it improve performance drastically. As displayed in picture.
- Scan Vs Seek: If we've clustered / non clustered index on table then sql uses 'Index Scan' or 'Index Seek' operators.Index Scan : Touches every row whether it is qualified or not, start to end. So cost associated with this search is proportional total number of rows.
This scan is right choice if table is small or most of the rows are qualified for condition of query.
Index Seek : Index seek touches only rows / pages those are qualified for condition of query.
So, if we provide some condition sql prefer to choose 'Index Seek', Even if condition provided result record number is large then optimizer change the plan uses Scan instead of Seek.
This is right choice if table having large number of rows or qualified rows count is very much smaller(few) compare to all rows.
- Removing Bookmark Lookup (Key Lookup), Row Identifier Lookup (RID): Key Lookup or RID Lookup can be seen into the execution plan of query when
1. Resulting number of rows not large
2. Columns of select list not covered in the clustered / non clustered index
When small number of rows are requested by a query, sql tries to use non clustered index on columns used in where clause, If columns of select are not present in non clustered index, sql needs to fetch data from pages for those columns using lookup or clustered index.
Create one table CityInfo with four columns ID, FirsName, LastName, City, no primary key & no index.
1. Below plan shows Sql uses 'Table Scan' because there is not exist any index
2. Here we created clustered index on ID, sql uses 'Index Scan' not 'Index Seek' because resulting record set is large.
3. Here we created non-clustered index on 'City', so sql found non clustered index on column of where clause, so it uses this non clustered index. But still columns of select list are not covered in this index so sql has to fetch data for these columns (id, LastName) from data pages for that sql either uses clustered index lookup or RID lookup.
So, Clustered index on ID present so here sql uses Clustered index lookup.
4. Now for above scenario if we removed clustered index that is on ID.
Sql will first use non clustered index created on city, but columns of select list are not covered in this non clustered index. so sql need to fetch data for these columns from pages for that sql need to use (Heap) RID Lookup because we removed clustered index, as shown in below example
When Sql uses Key Lookup / RID Lookup it down grades performance.
So how we can remove RID / Key Lookups?
Solution 1: Cover Index Solution 2: Include Index
Cover Index : Create non-clustered index with column of where clause and columns of select list.
Column of where clause is 'City', and select list has 'Id' and 'LastName' so will create Non-clustered index with 'City', 'Id' and 'LastName', as shown in pic.
Include Index : Create non-clustered index on column of where clause as ID, and include columns of select list in that non-clustered index, as shown in pic.
- Small size index column: Storage of data in sql is stored like given in diagram. Now think about you are creating index on column what will be the difference if column having data type int or numeric or decimal or varchar.Size of index will be different. 1 page of storage is 8 kb. So more bigger size index may increase number of pages.
When executing select statement sql fetch data from pages, so more pages more jumps one page to another that increases cost of query.
(int is good to use than numeric)
- Numeric index instead of textual index: Use numeric index instead of text (varchar/nvarchar/nvarchar/text). Numeric data type index are faster than textual data type indexes
No comments:
Post a Comment