February 28, 2014 at 8:19 am
Does anyone know if there is any noticeable performance impact by turning on trace flag 1222?
February 28, 2014 at 8:35 am
I have never noticed any performance issues with that TF. I guess you if you have a ton of deadlocks it may impact your error log, but it is a safe trace flag to have on.
February 28, 2014 at 9:11 am
I have seen an issue with it, but only on a system that was getting amazingly huge amounts of deadlocks. For the average system, even the average problematic system, I don't think you'll see any issues.
However, in this day & age, SQL Server 2008 or better, you have an alternative to get the entire deadlock graph without touching traceflags or trying to read that mess within the error log. The system_health extended event session is automatically running on every 2008+ machine. It captures deadlocks. You can pull the deadlock graph straight from there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 28, 2014 at 1:11 pm
profiler as a server side trace is another option. I presume would be more intrusive than the trace flag though, but gives a graphical representation.
Grant, is system_health extended event available in standard editions of 2008\2008R2?
---------------------------------------------------------------------
February 28, 2014 at 1:31 pm
Yes, the system_health extended event is available in 2008 r2. I'm looking at the results from it now. It's not easy to follow what'ss going on. I did find that there are almost constant deadlock on this server. It's almost every minute.
February 28, 2014 at 3:46 pm
That's a lot of deadlocks. You might actually have impacted performance using the traceflag.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 28, 2014 at 3:47 pm
george sibbald (2/28/2014)
profiler as a server side trace is another option. I presume would be more intrusive than the trace flag though, but gives a graphical representation.Grant, is system_health extended event available in standard editions of 2008\2008R2?
Mike already answered, but I'll follow-up. Yes. But, you can only open them using XQuery because the output is XML.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 28, 2014 at 3:53 pm
thats me out then, xquery gives me a headache. 🙂
---------------------------------------------------------------------
February 28, 2014 at 4:00 pm
george sibbald (2/28/2014)
thats me out then, xquery gives me a headache. 🙂
Ha! It gives everyone headaches. But you can search out scripts online to pull the information out. It just makes sense to start using info that's already there, rather than modify the server with traceflags when you don't have to. Plus, I'm lazy. 😉
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply