|
|
|
SSChasing Mays
 
Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 695,
Visits: 454
|
|
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
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 4,434,
Visits: 3,018
|
|
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)
 Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere 
Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 12,616,
Visits: 8,644
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 2:42 PM
Points: 1,260,
Visits: 1,872
|
|
What change to the database ?? That might help direct the responses.
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 695,
Visits: 454
|
|
>> 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
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 4,434,
Visits: 3,018
|
|
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. 
As you will have seen, in the script, I check sqlversion and only install 1204 or 1222.
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 695,
Visits: 454
|
|
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
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 4,434,
Visits: 3,018
|
|
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.
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 12,616,
Visits: 8,644
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
