August 28, 2017 at 4:01 am
Hi All,
As a beginner to query tuning, if someone had asked to create some supporting non-clustered indexes to improve the performance of a SELECT query, how can i take a decision on what columns the index has to be created? How can I determine what columns should be kept as part of index key and what columns should be part of INCLUDED list in case of a covering index. I am assuming that I am not looking at missing index warnings in the actual plan and I am doing it in the hard way.
In short, What columns to indexed ? what columns should be part of index keys and what columns should be part of INCLUDED list. Shall I create a multiple indexes with single key, or a composite index ? How many indexes to be created on a table as too many indexes has additional overhead in case of DML operations, backups, disk space, memory, index maintenance etc..?
Can anyone point me to some example scenarios/blogs for doing some exercise on index creation or proper approach while creating the indexes.
IS below statments is correct?
Do I need to create indexes on all columns referenced in WHERE clause?
Do I need to create indexes on all columns referenced in the join condition? example i have a 3 table join, A inner join B inner join C on A.c1 =B.c2 and B.c4 = C.c4
Thanks,
Sam
August 28, 2017 at 4:12 am
http://www.sqlservercentral.com/stairway/72399/
http://www.sqlinthewild.co.za/index.php/category/sql-server/indexes/
And that's just the first two that came to mind. Spend some time with your favourite search engine and do some reading.
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
August 28, 2017 at 2:03 pm
Hi Gail, Any text books that you would suggest on Indexing with worked out examples. I want to keep 1 or 2 books for reading.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply