2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
--
Aaron Bertrand
SQL Server MVP|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
>> faster hardware, same proc takes about 2 minutes to run.
>> Can anyone suggest things to look at?
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.
> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
>> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
>> syntax again to get the query plan as text, with stats? I am trying :
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>> The proc does *lots* of things, so eyeballing a compare between plans is
>> next to impossible.
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>