November 6, 2008 at 1:39 pm
We made a change to a database and users are reporting that "locking" now happens 3 -5 times daily. I'm new to this game.
1. What tools would you suggest I use to monitor the situation to see what is happening and what is the source of the issue?
2. If you think PerfMon is the way to go, what counters would you use?
3. Does anyone have experience with Redgate SQL Response as it relates to lock determinaiton?
TIA,
barkingdog
November 6, 2008 at 1:53 pm
add the following startup parameters to your sql instance.
This way you'll see in the sqlserver errorlog what statements are suffering
the conflict(s).
--dynamic activaton
dbcc traceon(1204, 1222, 3605, -1)
declare @MaxSQLArg varchar(50)
declare @NewSQLArg varchar(50)
declare @NewSeq int
set @NewSeq = 0
Declare @TraceFlag Nvarchar(50)
create table #tmpRegValues (Value varchar(50), Data varchar(1000))
create table #tmpNewRegValues (Value varchar(50), Data varchar(1000))
insert into #tmpRegValues
exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
-- Bepalen volgende SQLArg.
select @MaxSQLArg = max(Value) from #tmpRegValues
set @NewSeq = convert(integer, substring(@MaxSQLArg,7,len(@MaxSQLArg)))
if @@version like '%SQL Server 2005%'
begin
set @TraceFlag = '-T1222' -- More detailed deadlock info
if not exists (select * from #tmpRegValues where Data =@TraceFlag)
begin
print @TraceFlag + ' toevoegen'
--Bepalen volgende SQLArg.
select @MaxSQLArg = max(Value) from #tmpRegValues
-- set @NewSeq = convert(integer, substring(@MaxSQLArg,7,len(@MaxSQLArg)))
set @NewSeq = @NewSeq + 1
set @NewSQLArg = substring(@MaxSQLArg,1,6) + convert(varchar(5),@NewSeq)
print @NewSQLArg
exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', @NewSQLArg, N'REG_SZ', @TraceFlag
-- voorbeeldje exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SQLArg3', N'REG_SZ', N'-T1222'
end
else
begin
print @TraceFlag + ' Already Installed'
end
end
else
begin
Set @TraceFlag = '-T1204' -- print deadlock info
if not exists (select * from #tmpRegValues where Data =@TraceFlag)
begin
print @TraceFlag + ' toevoegen'
set @NewSeq = @NewSeq + 1
set @NewSQLArg = substring(@MaxSQLArg,1,6) + convert(varchar(5),@NewSeq)
print @NewSQLArg
exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', @NewSQLArg, N'REG_SZ', @TraceFlag
-- voorbeeldje exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SQLArg3', N'REG_SZ', N'-T1204'
end
else
begin
print @TraceFlag + ' Already Installed'
end
end
set @TraceFlag = '-T3605' -- write deadlock info to SQLServerSystemLog
if not exists (select * from #tmpRegValues where Data =@TraceFlag )
begin
print @TraceFlag + ' toevoegen'
-- if @NewSeq = 0
-- begin
-- --Bepalen volgende SQLArg.
-- --select @MaxSQLArg = max(Value) from #tmpRegValues
-- set @NewSeq = convert(integer, substring(@MaxSQLArg,7,len(@MaxSQLArg)))
-- end
set @NewSeq = @NewSeq + 1
set @NewSQLArg = substring(@MaxSQLArg,1,6) + convert(varchar(5),@NewSeq)
print @NewSQLArg
exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', @NewSQLArg, N'REG_SZ', @TraceFlag
-- voorbeeldje exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SQLArg4', N'REG_SZ', N'-T3605'
end
else
begin
print @TraceFlag + ' Already Installed'
end
if @NewSeq > 0
begin
Print 'Oude waarden'
select * from #tmpRegValues
-- selecteren en tonen nieuwe waarden
insert into #tmpNewRegValues
exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
Print 'Nieuwe waarden'
select * from #tmpNewRegValues
end
drop table #tmpRegValues
drop table #tmpNewRegValues
--dynamic activation
-- dbcc traceon(1204, 1222, 3605, -1)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2008 at 2:06 pm
Can you ask the users what exactly they mean by 'locking'?
dbcc traceon(1204, 1222, 3605, -1)
3605 isn't required for deadlock graphs. Personally I find 1204 to be unnecessary if 1222 is on. 1222 contains all the info that 1204 does and a lot more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2008 at 3:05 pm
What change to the database ?? That might help direct the responses.
November 6, 2008 at 7:36 pm
>> Can you ask the users what exactly they mean by 'locking'?
From the user's perspective their application appears to stop functioning until we use sp_who2 to find the user causing the block and ask that user to close the open app.
Barkingdog
November 6, 2008 at 11:48 pm
GilaMonster (11/6/2008)
Can you ask the users what exactly they mean by 'locking'?dbcc traceon(1204, 1222, 3605, -1)
3605 isn't required for deadlock graphs. Personally I find 1204 to be unnecessary if 1222 is on. 1222 contains all the info that 1204 does and a lot more.
Oh indeed, copy / paste has its down sides. :blush:
As you will have seen, in the script, I check sqlversion and only install 1204 or 1222.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 7, 2008 at 12:21 am
ALZDBA,
That's one heck of a script. Can you give me an idea of what it is doing? When I run it from Query Editor it shows the sql startup parameters and, I gather, populates the sql server log file with info about any locking happening on the server at thetime the script is run. Is that correct?
TIA,
Barkingdog
November 7, 2008 at 12:41 am
the scripts just checks if these startup parameters exist for your sqlserver instance,
and adds them if needed.
This way, the deadlock notifications in the sqlserver errorlog are always logged, even if the instance has been restarted.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2008 at 4:54 am
Barkingdog (11/6/2008)
From the user's perspective their application appears to stop functioning until we use sp_who2 to find the user causing the block and ask that user to close the open app.
Sounds like you may have transactions remaining open. To resolve this, you need to find out what the blocking transactions are running and see what they're doing. See if you can spot long running or uncommitted transactions.
To see what the blocking and blocked transactions are running you can use this.
SELECT er.session_id, wait_type, wait_time, host_name, program_name, original_login_name, er.reads, er.writes, er.cpu_time, blocking_session_id, st.text
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es on er.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
where blocking_session_id > 0
UNION
SELECT er.session_id, wait_type, wait_time, host_name, program_name, original_login_name, er.reads, er.writes, er.cpu_time, blocking_session_id, st.text
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es on er.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
where er.session_id in (select blocking_session_id from sys.dm_exec_requests where blocking_session_id > 0)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply