question

higdonjohnm avatar image
higdonjohnm asked

sqlsrvr 2005 sql update execution plan

I have two test databases on the same server. The sql update exec plan for one database shows index updates and the other does not. Both test databases were restored from same backups. Why aren't the plans the same for both databases? Both databases have been reindexed and stats updated.
sql-server-2005execution-plan
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Remember, the site works on voting. For all answers that are helpful, click on the thumbs up next to those answers. For any one answer that solves the problem, click on the check box next to that answer.
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Are these Actual plans, or Estimated plans?
0 Likes 0 ·
higdonjohnm avatar image higdonjohnm commented ·
all your help is appreciated..
0 Likes 0 ·
higdonjohnm avatar image higdonjohnm commented ·
these are all estimated plans.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
These are pictures of execution plans, not the .sqlplan files. So all I can tell you is the pictures are different. Did you look at the properties of the first operator in each plan as I suggested. That will show information about how the plans were compiled that can help you understand what is happening.
0 Likes 0 ·
Show more comments
Grant Fritchey avatar image
Grant Fritchey answered
Without seeing the queries or the plans, it's hard to say. There must be a different. In the plans themselves, look at the first operator, the SELECT/INSERT/UPDATE/DELETE operation. Check the properties. That will show you exactly the queries being passed, parameters if any, and whether or not a full optimization was run. You can try to spot differences there.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

higdonjohnm avatar image higdonjohnm commented ·
I looked at the properties for both update statements. Both have optimization level at full and the options are the same (ansi nulls,padding,warnings,arithabort,concat,numeric,quoted_id). Of course the cached plan size,compile cpu, compilememory, compiletime, est # of rows is different because the 2 plans are different. Any other ideas? And btw... I didn't write this query.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Without seeing the actual execution plans, it's difficult to say. Please export the .sqlplan files and upload them.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Estimated number of rows is a giveaway. I'd suggest that the statistics have been updated on one database and not the other. Has there been data changes since the restore operation?
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Thinking about it, @higdonjohnm, you've got that the wrong way round. The two plans are different *because* the estimated number of rows is different...
0 Likes 0 ·
higdonjohnm avatar image
higdonjohnm answered
Here is the query... I will add the plans next. UPDATE cd SET cd.STATUS_CODE = 'EDIT', cd.STATUS_DATE = getdate(), cd.UPDATE_USER_ID = 'TEST', cd.UPDATE_DATETIME = GETDATE() FROM CONTRIB_DETAIL_SUBMISSION_BATCH cdsb with (nolock) inner join CONTRIB_DETAIL cd with (nolock) on cd.CONTRIB_DETAIL_SUBMISSION_BATCH_ID = cdsb.CONTRIB_DETAIL_SUBMISSION_BATCH_ID and cd.CONTRIB_SUMMARY_ID = cdsb.CONTRIB_SUMMARY_ID inner join CONTRIB_DETAIL_SUBMISSION cds with (nolock) on cds.CONTRIB_DETAIL_SUBMISSION_ID = cdsb.CONTRIB_DETAIL_SUBMISSION_ID inner join AGENCY a with (nolock) on a.AGENCY_ID = cds.REPORTING_AGENCY_ID and a.AGENCY_CLASSIFICATION_CODE = 'TH' WHERE cdsb.STATUS_CODE = 'SUPP' and day(getdate()) not between 1 and (14 - 1) and 1 = case when day(getdate()) = 14 then case when datepart(hh,getdate()) >= 6 then 1 else 0 end else 1 end
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

higdonjohnm avatar image higdonjohnm commented ·
![alt text][1] [1]: /storage/temp/902-execplan.png
0 Likes 0 ·
execplan.png (22.6 KiB)
higdonjohnm avatar image higdonjohnm commented ·
![alt text][1] [1]: /storage/temp/903-execplan1.png
0 Likes 0 ·
execplan1.png (56.2 KiB)

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.