May 7, 2009 at 1:56 pm
I'm working on optimizing some procedures, and my changes aren't having the desired effects on the execution plans.
The execution plan showed several table scans. I've added indexes to the tables, and now I'm seeing Index Scans, or Index Seeks with accompanying RID Lookups by using the new indexes. The Index Scans are showing a higher cost than I would expect with the use of the index. The Index Seeks are normally showing a cost of around 0, but the accompanying RID Lookups are 35+%.
One of the index scans is hitting 250k rows (out of 254k) with a cost of 10%. Another is hitting 3500 (out of 530k) with a cost of 47%.
I've made sure that the indexes are carrying the fields in them that are used in either the where or join clauses, and optionally have included other columns for a covering index.
So, :
1. why would the query be doing an index scan vs. index seeks?
2. what do you need to do to eliminate the RID Lookup?
Oh, this procedure is running for 7 seconds, to produce 5 rows. And it's been the cause of some deadlocks.
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 7, 2009 at 3:08 pm
Can you attach the .SQLPLAN in a zip?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2009 at 3:20 pm
Have you checked out Gila's sqlinthewild stuff on indexing?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 7, 2009 at 3:47 pm
RBarryYoung (5/7/2009)
Can you attach the .SQLPLAN in a zip?
How do I get it out of SSMS into a zip file?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 7, 2009 at 4:16 pm
Saving it as XML should make it a *.SQLPLAN. Then you will have to zip that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 7:37 am
RBarryYoung (5/7/2009)
Can you attach the .SQLPLAN in a zip?
Okay, here it is. I'm trying to optimize the last query in the plan.
I have added the following indexes already, which gets the plan where it is:
USE [west_ccims_prod]
GO
if exists (select 1 from sys.indexes where name = 'IX_SCHT_Disbursements_obl_rcn_user_id')
DROP INDEX [SCHT_disbursements].[IX_SCHT_Disbursements_obl_rcn_user_id]
CREATE NONCLUSTERED INDEX [IX_SCHT_Disbursements_obl_rcn_user_id]
ON [dbo].[SCHT_disbursements] ([obl_rcn_user_id]) -- query 48
if exists (select 1 from sys.indexes where name = 'IX_SCHT_Disbursements_rcn_id__rcn_serial_id')
DROP INDEX [SCHT_disbursements].[IX_SCHT_Disbursements_rcn_id__rcn_serial_id]
CREATE NONCLUSTERED INDEX [IX_SCHT_Disbursements_rcn_id__rcn_serial_id]
ON [dbo].[SCHT_disbursements] ([rcn_id],[rcn_serial_id])
INCLUDE ([disbursement_id]) -- query 49
GO
if exists (select 1 from sys.indexes where name = 'IX_Directory_dir_sch_cd_cover')
DROP INDEX [Directory].[IX_Directory_dir_sch_cd_cover]
CREATE NONCLUSTERED INDEX [IX_Directory_dir_sch_cd_cover]
ON [dbo].[Directory] ([dir_sch_cd])
INCLUDE ([ucc_type_cd], [sch_nm], [region_cd], [brigade_cd]) -- query 52
GO
if exists (select 1 from sys.indexes where name = 'IX_SCHT_obligations_dir_sch_cd_cover')
DROP INDEX [SCHT_obligations].[IX_SCHT_obligations_dir_sch_cd_cover]
CREATE NONCLUSTERED INDEX [IX_SCHT_obligations_dir_sch_cd_cover]
ON [dbo].[SCHT_Obligations] ([dir_sch_cd], [rotc_sch_cd], [school_yr_dt], [student_id], [disbursement_id])
INCLUDE ([tuition_qy], [obligation_cd], [fee_qy], [pay_option_cd], [obligation_id],
[term_nm], [rotc_sch_record_cd]) -- query 51/52
GO
if exists (select 1 from sys.indexes where name = 'IX_SDMT_Student_student_id_cover')
DROP INDEX [SDMT_Student].[IX_SDMT_Student_student_id_cover]
CREATE NONCLUSTERED INDEX [IX_SDMT_Student_student_id_cover]
ON [dbo].[SDMT_Student] ([student_id], [cadet_id], [acad_sch_cd], [rotc_sch_cd]) -- query 52
GO
if exists (select 1 from sys.indexes where name = 'IX_Cadet_cadet_id_cover')
DROP INDEX [Cadet].[IX_Cadet_cadet_id_cover]
CREATE NONCLUSTERED INDEX [IX_Cadet_cadet_id_cover]
ON [dbo].[Cadet] ([cadet_id], [last_nm], [first_nm], [mi_nm] )
INCLUDE ([ssn_id]) -- query 52
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2009 at 8:33 am
To eliminate the key lookups you need to make the non-clustered indexes into covering indexes by either adding columns needed to the key or as included columns. But you need to be careful that you're not copying the entire table.
Index scans are not really necessarily better than table scans. It really depends on the index & the table. Because of the size of this set of queries, I'd strongly recommend you not rely on the estimated costs, but instead gather the STATISTICS TIME & IO data. That will tell you when you're getting performance benefits & costs better than the execution plan estimated costs.
"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
May 8, 2009 at 9:11 am
Grant Fritchey (5/8/2009)
To eliminate the key lookups you need to make the non-clustered indexes into covering indexes by either adding columns needed to the key or as included columns. But you need to be careful that you're not copying the entire table.
Right. I'm being careful about it. Most of these tables are pretty wide, and I'm looking at just the columns used in queries in this proc.
Index scans are not really necessarily better than table scans. It really depends on the index & the table.
Which is why I'm striving towards Index Seeks, without RID Lookups.
Because of the size of this set of queries, I'd strongly recommend you not rely on the estimated costs, but instead gather the STATISTICS TIME & IO data. That will tell you when you're getting performance benefits & costs better than the execution plan estimated costs.
You mean these (from just the last query):
Table 'SCHT_disbursements'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SDMT_Room_Board'. Scan count 0, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SCHT_obligations'. Scan count 1, logical reads 4426, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SDMT_Student'. Scan count 1, logical reads 718, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Directory'. Scan count 3, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Cadet'. Scan count 1, logical reads 1485, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DRCT_Unit_Personnel'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#RCNList____________________________________________________________________________________________________________00000000000B'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2009 at 9:38 am
I've looked through the query, but I haven't read all of it (there's a lot going on there). In general, your queries seem to be structured generally OK (the one's I saw), but the approach is a bit questionable. It looks like you're loading data to temp tables and then updating them from other queries and then joining that to yet other tables. In general, that's going to be somewhat expensive. Where it makes sense, you should try to, at least, load the temporary tables in a single statement rather than load them and then modify them.
After that, to eliminate key lookups, you have to adjust an index, either the cluster so that you don't need to use a nonclustered index, or the nonclustered index so that it becomes a covering index (meaning it has all the columns it needs to satisfy the query).
"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
May 8, 2009 at 10:56 am
Grant Fritchey (5/8/2009)
I've looked through the query, but I haven't read all of it (there's a lot going on there). In general, your queries seem to be structured generally OK (the one's I saw), but the approach is a bit questionable.
Yeah, I know. Did I mention it's a new job, and I'm looking at the procedures causing performance / deadlock issues. I don't have a good enough handle on the process requirements to start making wholesale changes to procedures, so I'm trying to get what is there working as best as it can.
It looks like you're loading data to temp tables and then updating them from other queries and then joining that to yet other tables. In general, that's going to be somewhat expensive.
It's very kind of you to not mention the cursors going on doing this...
Where it makes sense, you should try to, at least, load the temporary tables in a single statement rather than load them and then modify them.
This gets into that whole thing about changing procedures...
After that, to eliminate key lookups, you have to adjust an index, either the cluster so that you don't need to use a nonclustered index, or the nonclustered index so that it becomes a covering index (meaning it has all the columns it needs to satisfy the query).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2009 at 10:59 am
Wayne, questions:
1) How many total rows are in the SCHT_obligations table?
2) Have you used the time statistics to confirm if the QP estimates per statement are accurate (i.e., is the last statement really 75% of the run-time).
3) Since you've already changed it some, where does it stand now and where are you looking to go with it (ex: current vs. target run-times).
4) This is pretty complicated so I think that we're going to need the code and probably the DDL too.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 12:51 pm
I didn't want bring up the cursor and add to your misery.
Have your core questions regarding the index scans & key lookups been answered for the moment?
"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
May 8, 2009 at 9:57 pm
RBarryYoung (5/8/2009)
Wayne, questions:1) How many total rows are in the SCHT_obligations table?
530,000
2) Have you used the time statistics to confirm if the QP estimates per statement are accurate (i.e., is the last statement really 75% of the run-time).
No... I guess with those d****d cursors, it could really throw that off. I'll check it on Monday.
3) Since you've already changed it some, where does it stand now and where are you looking to go with it (ex: current vs. target run-times).
The QP and stats are with all of the indexes.
The thing that is concerning me the most is that it's still doing CI/Index Scans w/ RID Lookups, vs. Index Seeks. This increases the potential for deadlocks, which is why I'm looking at this procedure in the first place.
4) This is pretty complicated so I think that we're going to need the code and probably the DDL too.
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2009 at 10:00 pm
Grant Fritchey (5/8/2009)
Have your core questions regarding the index scans & key lookups been answered for the moment?
Well, yes and no. I understand the basic, technical answer of why sql does it. But, the indexes I built either have the columns as part of the indexed columns, or included columns. So, if this is done, why is it still doing an index scan and rid lookup instead of an index seek? What am I missing?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 9, 2009 at 2:36 am
WayneS (5/8/2009)
So, if this is done, why is it still doing an index scan and rid lookup instead of an index seek? What am I missing?
Which operator in which query are you looking at?
I saw 1 RID lookup in Query 52, against the table SCHT_disbursements
The index that it's seeking on is the nonclustered primary key, which only has disbursement_id in it. Hence a lookup to the heap is necessary to get the rest of the columns (rcn_id, rcn_serial_id, record_dt)
There's an index scan on Directory followed by a merge join. I'm guessing that it's being done that way because there are so many rows that a nested loop join with a seek on the join criteria would be far too expensive (1280 rows on one input, 3422 on the other)
Same with the cadet table
Same with the directory table.
Same with student
I suspect that the scan on obligation (which is probably the root of the problem) is because of the query structure. It's a where clause predicate that doesn't optimise well
WHERE
((so.dir_sch_cd = @school) AND
(@school 'HA' OR @school ''))
OR
((so.rotc_sch_cd = @Fice) AND
(@school = 'HA' or @school = ''))
I refer to that as a 'catch-all query'. There's an ongoing discussion here on it - http://www.sqlservercentral.com/Forums/Topic712643-338-1.aspx
Bottom line. Because of that query construct (most likely), SQL can't eliminate rows early (there are over 3000 rows flowing through most of the query. It returns 5) and hence it's picking merge joins (more optimal than nested loops on larger row sets) and hence you're seeing index scans.
Suggestion:
Break it up with a temp table. Put the filtered results from scht_obligations into a temp table early on, create a couple indexes and then use that in the queries in this proc. See if that helps.
I'm also concerned about the distinct. It doesn't appear necessary (5 rows before, 5 rows after). Is there a chance of duplicate rows? If not, drop that distinct.
Index the #RCNList temp table. Even if there's only 5 rows in it. Index makes it easier for optimiser to use it decently.
Note, I didn't look much at the other 51 queries.
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 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply