June 13, 2011 at 8:43 pm
Hi all,
we just upgraded to SQL 2005, and recently we noticed one query take longer time to executre in SQL 2005.
In SQL 2000, it takes less than 1 second to complete,
In SQL 2005 (compatibility mode=80), it takes 2 minus and 34 minutes to complete.
The query can be improved by declaring the variable for @newproductcode, but there are a lot of stored procedure to be changed, is there any way we force the optimizer to use back the plan generated in SQL 2000??
Please kindly advice, thanks.
declare
@productcode varchar(10)
set @productcode = '18346500'
select top 1 productcode, klot from dbo.bdvs_productmasterHeader
where productcode='18346500' or newproductcode = '18346500' or
productcode =(select distinct newproductcode from dbo.bdvs_productmasterheader where productcode = '18346500')
and action<>'DELETE'
and jobarea='btv'
order by priority
June 13, 2011 at 9:06 pm
Hi,
Jst a question,
when i rewrite the query as below...
the query seemed to return very fast result..
can anyone explain why like this?
declare
@productcode varchar(10)
set @productcode = '18346500'
select top 1 productcode, klot from dbo.bdvs_productmasterHeader
where productcode='18346500' or newproductcode = '18346500' or
productcode =('select distinct newproductcode from dbo.bdvs_productmasterheader where productcode = ''18346500''')
and action<>'DELETE'
and jobarea='btv'
order by priority
June 14, 2011 at 4:57 am
There are places where you will run into situations where the 2000 query can run faster than the 2005 query. In all the cases I've seen so far, there were tuning opportunities in the 2000 query that once they were applied in 2005 resulted in performance superior to that in 2000.
Not seeing the execution plans or structures that you're looking at, I can't make a specific recommendation, but that's where I'd focus.
Also, it's worth mentioning, you should do a full scan update of all statistics after migrating your database to 2005. Eventually, over time, that will happen anyway, but no reason not to speed the process.
"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
June 15, 2011 at 2:36 am
Hi,
Please kindly see the execution plan from the attachment.
Thanks for the advice again.
SQL 2000 (execution timing 1 sec)
SQL 2005 (execution timing 2min 34 seconds)
June 15, 2011 at 4:58 am
Looks like parallelism might be getting you. The cost for the plan is only 3. What is your cost threshold for parallelism? Default should be 5. Try setting it up to 15 or 20 and then rerunning 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
June 15, 2011 at 10:00 am
hi,
how can we determine the cost is 3?
Since the cost is 3, it is under the default value of 5, means the SQL Server should choose to execute the query in serial?
(please feel free to correct me if my understanding is wrong)
Thank you so much again for the advice.
On top of that,when we rewrite the query for the nested query as
..........
productcode =('select distinct newproductcode from dbo.bdvs_productmasterheader where productcode = ''18346500''')
instead of
..........
productcode =(select distinct newproductcode from dbo.bdvs_productmasterheader where productcode = '18346500'),
may i knw what is the reason it is running much faster?
June 15, 2011 at 10:06 am
<RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="33"
EstimatedTotalSubtreeCost="3.28441"
June 15, 2011 at 11:26 am
Cost is an estimate generated by the optimizer. You can't control except by making the plan simpler.
As to why the query would run faster because you removed quotes, I have to assume it's changing the ability of SQL Server to change the data type on that value, an implicit conversion. What data type is it supposed to be?
"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
June 15, 2011 at 11:35 am
Ah we forgot the simpleton explaination. When you added the local variable a new plan was generated (possibly better than the 2 minutes one).
And stopped the prameter sniffing from killing your server.
I'd do as Grant said, reindex, update all stats with fullscan.
If it doesn't help with recompile could help. If not then a quick search / replace can solve the proc's code.
But I wouldn't discount doing a real tuning of the proc since you most likely have room to improve there as Grant said.
June 15, 2011 at 7:17 pm
The column data type is varchar(15).
In fact, I have the index rebuild and statistics update just after the restoration from the backup.
FYI, after i change the cost of threshold of parellism to 15, the result as expected is 1 second.
I do a simple testing by restoring the DB into SQL 2000 in the same spec of server.
The result return is still less than 1 second.
Just a doubt, SQL Server 2000 has the same default option of (ctop=5) and MAXDOP=0,
why is it having different plan in processing?
Thanks again.
June 15, 2011 at 8:08 pm
golden-1131024 (6/15/2011)
The column data type is varchar(15).In fact, I have the index rebuild and statistics update just after the restoration from the backup.
FYI, after i change the cost of threshold of parellism to 15, the result as expected is 1 second.
I do a simple testing by restoring the DB into SQL 2000 in the same spec of server.
The result return is still less than 1 second.
Just a doubt, SQL Server 2000 has the same default option of (ctop=5) and MAXDOP=0,
why is it having different plan in processing?
Thanks again.
It just is. You gotta deal with it (sorry for the harsh words but too many people hope for a magic pill here). You want to use sql 2k5 then make it work there.
We can help if you post the query plan. Untill then, good luck.
June 15, 2011 at 8:46 pm
Hi all,
Thanks for all the replies,
I am not expecting any tuning on the script..
Just would like to get more info on the sql db engine which has all default setting same but having different processing outcome as as mentioned..
Hope you all can clear my doubt on this..
Thousand thanks again.
June 15, 2011 at 9:03 pm
The thing is, you're running 2005 and 2000. They are two totally different optimizers. There were a number of upgrades in the engine as well as changes, stuff was fixed, all kinds of things. You're comparing apples to hammers to a degree here. You can't. It runs one way in 2005. If you can, you tune that query. Worrying about the difference between 2000 and 2005 won't get you anywhere. I don't have access to the code in 2000 and 2005 in order to explain to you exactly why they're different. They are. Deal with that & move forward. In the vast majority of cases, 2005 is better than 2000. You may have one of the edge cases where it isn't. It's called a regression. They do happen.
"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
June 15, 2011 at 9:45 pm
Hi all,
Thanks a lot for the info, advice and kind assistace given to me so far.
Really appreacite it, thanks again.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply