July 30, 2009 at 7:59 am
Are these the basic rules to follow when building indexes:
Field in the join clause should be the first item in the index,
followed by any field that shows up in the where clause,
include fields should be everything else that is in the "select" portion that isn't already included.
I realize this is very generic, and the include part probably wouldn't be correct for most indexes, but for my example, only one other field in the table shows up in the select statement, so I thought to add it in the include statement...
Does this sound logical?
Thanks
July 30, 2009 at 8:21 am
Order of the columns in the key may or may not break down the way you're talking about. It depends on what the optimizer does. As a starting point, yeah, it sounds good. You should get a covering index from what you describe.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2009 at 8:23 am
However you wouldn't build indexes for every query. I would think you would start with the columns in PKs and FKs first, if you haven't explicitly declared those or set indexes. Those often cover the join conditions, then look at common WHERE clauses amongst most of your queries.
July 30, 2009 at 8:26 am
Yep, there are already a few indexes on this table that cover the PK and other "ID's" in the table, but one specific SP always comes back via Quest Perfomance Analysis as the "heavy hitter" that I would like to optimize.
July 30, 2009 at 8:59 am
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
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
July 30, 2009 at 9:04 am
gregory.anderson (7/30/2009)
Yep, there are already a few indexes on this table that cover the PK and other "ID's" in the table, but one specific SP always comes back via Quest Perfomance Analysis as the "heavy hitter" that I would like to optimize.
Post it here, we can give you index recommendations and reasons why we're making those recommendations
Have a look at this to see the best way to post a performance question. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
July 30, 2009 at 9:47 am
Here is the proc:
SELECTCSH.Non_Cash_Act_IDAS [NonCashActivityID],
Pmt.Pmt_IDAS [PmtID],
Pmt.Statement_IDAS [StatementID],
CSH.AmountAS [Amount]
FROMNon_Cash_Act CSH
INNER JOINCD_Session SES ON SES.CD_Session_ID = CSH.CD_Session_ID
INNER JOINPmt ON CSH.Pmt_ID = Pmt.Pmt_ID
INNER JOINStatement ST ON ST.Statement_ID = Pmt.Statement_ID
WHERESES.Cash_Drawer_ID = @Cash_Drawer_ID
ANDST.StatementVoidIndicator = @False
ANDCSH.Active = @True
Want the index on Non_Cash_Act. Primary keys for tables are [TableName_ID] so it's not really utilized on the Non_Cash_Act table
As I stated above, I was going to write something like this:
Create NonClusteredIndex [IndexName] On Non_Cash_Act
(
[CD_Session_ID] Asc,
[Pmt_ID] Asc,
[Active] Asc
) Include ([Amount])
July 30, 2009 at 10:16 am
could you upload the actual execution plan for us as well?
edit:
Please also give us the definitions of the current indexes on those tables.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply