May 21, 2012 at 11:57 am
I am pretty new to resolving query performance issues, just bear with me...Why should i create an index with Include Columns(why can't i just say create a composite index with Include Column as one of the field)
What's the actual difference between EqualityUsage and InequalityUsage Column??
May 21, 2012 at 1:34 pm
PradeepVallabh (5/21/2012)
I am pretty new to resolving query performance issues, just bear with me...Why should i create an index with Include Columns(why can't i just say create a composite index with Include Column as one of the field)What's the actual difference between EqualityUsage and InequalityUsage Column??
Good question.
the index is used to resolve what is in the WHERE statement, whereas the INCLUDE is used for the columns specifically selected.
for example, SELECT ADDRESS,City, State,ZIP where LastName = @x AND Firstname=@y
an index ON LastName,Firstname would be ideal for that query's WHERE statement...if the index was ON (LastName,Firstname ,ADDRESS,City,State,Zip) in it, first it would be pretty wide, and second, it might not be used in favor of just scanning the table.
now if that index included the columns typically returned as part of the dataset, ie INCLUDE(ADDRESS,City,State,Zip), the index seek on theh firstname/lastname would have the data as part of the index, and could save a trip to the table to get the values for those columns.
Lowell
May 21, 2012 at 1:58 pm
Thanks for your good explanation....I was just playing around without using include statement while creating a non clustered index(analysed the execution plan), it give's a RID Lookup for
Address, City, State, Zip etc (Probably it was making another trip to the table to fetch those data)....
What exactly is a LookUp(I m guessing it means a lookup of the data based upon Index i.e Last Name and First Name)
So Lookup hinder the performance of queries....Is that right??
Does Hash Match improves the performance of the queries???
May 21, 2012 at 2:14 pm
PradeepVallabh (5/21/2012)
Thanks for your good explanation....I was just playing around without using include statement while creating a non clustered index(analysed the execution plan), it give's a RID Lookup forAddress, City, State, Zip etc (Probably it was making another trip to the table to fetch those data)....
What exactly is a LookUp(I m guessing it means a lookup of the data based upon Index i.e Last Name and First Name)
So Lookup hinder the performance of queries....Is that right??
Does Hash Match improves the performance of the queries???
you are right, the RID lookup is the fetching of the values to satisfy the SELECT; and if you can eliminate that lookup by using INCLUDE columns, you save at least that portion of the operation, right? if the work must be done, you want to try and make it as efficient as you can.
For the HASH MATCH and improving performance, "it depends".
Gail has written a really good article on the differnece between join types, i'd suggest reading that, as it's my personal reference as well:
http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/
Lowell
May 22, 2012 at 4:09 am
By the way, if you're getting RID lookups it means that you're dealing with hash tables, tables without a clustered index. Unless you have a very thorough set of tests on your specific system that say otherwise, you should have a clustered index on all tables in the system. It absolutely helps performance in many ways.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 23, 2012 at 6:42 am
Grant Fritchey (5/22/2012)
By the way, if you're getting RID lookups it means that you're dealing with hash tables, tables without a clustered index. Unless you have a very thorough set of tests on your specific system that say otherwise, you should have a clustered index on all tables in the system. It absolutely helps performance in many ways.
Minor correction: Grant meant HEAP tables, not HASH tables. 😉
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 23, 2012 at 6:47 am
TheSQLGuru (5/23/2012)
Grant Fritchey (5/22/2012)
By the way, if you're getting RID lookups it means that you're dealing with hash tables, tables without a clustered index. Unless you have a very thorough set of tests on your specific system that say otherwise, you should have a clustered index on all tables in the system. It absolutely helps performance in many ways.Minor correction: Grant meant HEAP tables, not HASH tables. 😉
D'oh!
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 23, 2012 at 10:57 am
Thanks for the info...
While analysing execution plan i noticed something called Estimated Number of Execution...what does it mean??
I know creating too many indexes on a table are harmful....How do we decide where to stop??
Does size of the index matter(how does it impact during rebuilding and reorganizing indexes)??
Is it advisable to create indexes on where clause for insert and update statements?
May 23, 2012 at 12:46 pm
PradeepVallabh (5/23/2012)
Thanks for the info...While analysing execution plan i noticed something called Estimated Number of Execution...what does it mean??
Parts of the execution plan are iterative. This means parts can be executed multiple times.
I know creating too many indexes on a table are harmful....How do we decide where to stop??
That's a judgement call. There's no number I can tell you. In general, I would not expect to see more than 2-3 indexes per table for your average system.
Does size of the index matter(how does it impact during rebuilding and reorganizing indexes)??
By size do you mean the number & size of the key values or just the sheer volume of the index? Both affect it. Bigger or more columns mean fewer key values per page, so fragmentation can be more severe. Bigger indexes... are bigger & slower & have additional overhead. That's not to say you would throw them out, but you need to be ready for that fact
Is it advisable to create indexes on where clause for insert and update statements?
Well, not for INSERT statements. There is no WHERE clause when adding a row. For UPDATE statements, yes.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 23, 2012 at 1:06 pm
PradeepVallabh (5/21/2012)
Why should i create an index with Include Columns(why can't i just say create a composite index with Include Column as one of the field)
Several reasons, all explained quite well in the Books Online entry Creating Indexes with Included Columns. One main reason is to keep the index key size small, while providing other columns needed by the query (but not useful for searching the index) at the lowest (leaf) level of the index.
What's the actual difference between EqualityUsage and InequalityUsage Column??[/quote]
This is explained in the Books Online entry sys.dm_db_missing_index_details (Transact-SQL)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 1:08 pm
PradeepVallabh (5/23/2012)
Is it advisable to create indexes on where clause for insert and update statements?
Perhaps, if the index is useful in locating the records to INSERT or UPDATE.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 31, 2012 at 5:36 pm
Thanks...I created indexes and tried to observer Reads, Writes, CPU and Duration for some of Stored procedure(using Profiler)
After creating indexes Reads, CPU decreased considerably where as Duration increased. Isn't it a disadvantage(of creating the index) as Duration of the stored procedure increased???
June 1, 2012 at 12:55 am
If the index is useful, duration usually decreases as well. Just be careful how you're measuring duration. If you use statistics time, that includes any time necessary to compile the query, fetch the data off disk, execute the query and send the results to the client (and display the execution plan if that option is enabled)
Make sure you run the query twice and discard the first results, make sure the exec plan is not displaying when you do time tests.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply