February 26, 2009 at 10:10 am
Lynn Pettis (2/25/2009)
Someone want to explain what the statistics profile is saying?
Each line in the stats profile corresponds to an operator in the query. If it were a graphical plan, each be a separate icon. Most of the columns in the profile appear as properties in the graphical plan. The physical operator and logical operator columns correspond with the names of the operators in the graphical plan
The text is much harder to read than the graphical, but it's the only way on SQL 2000.
A few things I noticed reading through the plan:
- Clustered Index Scan on HOMEACCTHIST. You may benefit from an nonclustered index on EFFECTIVEDTM, EXPIRATIONDTM
- Clustered Index Scan on PERSONSTATUSMM. You may benefit from an nonclustered index on EFFECTIVEDTM, EXPIRATIONDTM. Also an index on EMPLOYMENTSTATID, PersonID, EXPIRATIONDTM
- Index Scan on PAYPERIOD. You may benefit from a NC index on PayruleID, StartDT, EndDT (not sure on this one)
- Bookmark lookups on the [XU1_PERSON] index. It may help to widen it with these columns - PERSONID, PERSONNUM, FULLNM
There probably are more indexing opportunities, but that plan is exceedingly complex and hard to read.
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
February 26, 2009 at 10:27 am
GilaMonster (2/26/2009)
Lynn Pettis (2/25/2009)
Someone want to explain what the statistics profile is saying?Each line in the stats profile corresponds to an operator in the query. If it were a graphical plan, each be a separate icon. Most of the columns in the profile appear as properties in the graphical plan. The physical operator and logical operator columns correspond with the names of the operators in the graphical plan
The text is much harder to read than the graphical, but it's the only way on SQL 2000.
A few things I noticed reading through the plan:
- Clustered Index Scan on HOMEACCTHIST. You may benefit from an nonclustered index on EFFECTIVEDTM, EXPIRATIONDTM
- Clustered Index Scan on PERSONSTATUSMM. You may benefit from an nonclustered index on EFFECTIVEDTM, EXPIRATIONDTM. Also an index on EMPLOYMENTSTATID, PersonID, EXPIRATIONDTM
- Index Scan on PAYPERIOD. You may benefit from a NC index on PayruleID, StartDT, EndDT (not sure on this one)
- Bookmark lookups on the [XU1_PERSON] index. It may help to widen it with these columns - PERSONID, PERSONNUM, FULLNM
There probably are more indexing opportunities, but that plan is exceedingly complex and hard to read.
Thank you Gail. With your explanation above I can go back and review the plan and see where you determined all this. It will be a learning experience.
Again, thank you.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply