SQL Remove Old Backups: rebuild

Showing posts with label rebuild. Show all posts

Tuesday, March 6, 2012

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 mess
age
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
> 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 mess
age
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
> 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.
>
> 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
>

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
>

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...
> 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...
> 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.
>
> 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
>

Thursday, February 16, 2012

2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>

Tuesday, March 6, 2012

2005 vs 2000 performance problem

2005 vs 2000 performance problem

2005 vs 2000 performance problem

Thursday, February 16, 2012

2005 -lock being held by an orphan transaction?

2005 -lock being held by an orphan transaction?

2005 -lock being held by an orphan transaction?

SQL Remove Old Backups

Blog Archive

About Me