Does SQL degrade when DBCC Traceon 1222 is switched on?

Hi,

I'm seeing a few deadlocks on my SQL 2005 production database and wondered what i could expect if i switch on the trace flag 1222. Can this cause more deadlocks to occur? Will the performance of SQL degrade? Is it safe to have this flag set on a production server or is there another method you would recommend?

Thanks

Martin

In general every extra logging causes a little performance impact but it will not cause extra deadlocks. The performance impact is more acceptable than having deadlocks so until you have found the root cause of these deadlocks I would not hesitate to use the trace flag.

Do not bother too much about the performance impact however, it is very little and definitely less than the cost of having deadlocks.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Does SQL degrade when DBCC Traceon 1222 is switched on?

Hi,

I'm seeing a few deadlocks on my SQL 2005 production database and wondered what i could expect if i switch on the trace flag 1222. Can this cause more deadlocks to occur? Will the performance of SQL degrade? Is it safe to have this flag set on a production server or is there another method you would recommend?

Thanks

Martin

In general every extra logging causes a little performance impact but it will not cause extra deadlocks. The performance impact is more acceptable than having deadlocks so until you have found the root cause of these deadlocks I would not hesitate to use the trace flag.

Do not bother too much about the performance impact however, it is very little and definitely less than the cost of having deadlocks.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

sql

does importing a trace file via FN_TRACE_GETTABLE use the tempDB?

i need to look at some large trace files (3 - 8 Gig). The drive that
has the tempDB on is is small..currently has < 2 G of free space on
it.
does the FN_TRACE_GETTABLE() function route the import through the
tempDB?
if so is there a way to avoid this?
I don't want to choke the root drive of the server importing this...I have not seen it being spooled on a simple select into from a trace file
but you might just want to keep an eye on it.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<kent@.kaeservices.com> wrote in message
news:29307ecd-75d3-42e1-ba4a-06d67f0c5c14@.d4g2000prg.googlegroups.com...
>i need to look at some large trace files (3 - 8 Gig). The drive that
> has the tempDB on is is small..currently has < 2 G of free space on
> it.
> does the FN_TRACE_GETTABLE() function route the import through the
> tempDB?
> if so is there a way to avoid this?
> I don't want to choke the root drive of the server importing this...

does importing a trace file via FN_TRACE_GETTABLE use the tempDB?

i need to look at some large trace files (3 - 8 Gig). The drive that
has the tempDB on is is small..currently has < 2 G of free space on
it.
does the FN_TRACE_GETTABLE() function route the import through the
tempDB?
if so is there a way to avoid this?
I don't want to choke the root drive of the server importing this...
I have not seen it being spooled on a simple select into from a trace file
but you might just want to keep an eye on it.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<kent@.kaeservices.com> wrote in message
news:29307ecd-75d3-42e1-ba4a-06d67f0c5c14@.d4g2000prg.googlegroups.com...
>i need to look at some large trace files (3 - 8 Gig). The drive that
> has the tempDB on is is small..currently has < 2 G of free space on
> it.
> does the FN_TRACE_GETTABLE() function route the import through the
> tempDB?
> if so is there a way to avoid this?
> I don't want to choke the root drive of the server importing this...