August 12, 2011 at 8:05 am
Hi,
I ran a blocked process report against a database i have. Its being used by call centre. Agents log transactions into this database. The report showed blocking but did not show which queries are blocking each other. There is a Universe program that is run against the transaction table which reads records and writes back to that table. This table is also used by agents to log notes or financial stuff at the same time. The program ran for a year without a problem.
I believe that a select query in the Universe program is clashing with an update query as sp_who2 indicates that. The report is not saying much for me.
Attached is the report i got in Notepad form. How can identify where the problem is? Is the select query drawing too much data that it is clashing with the update? The Universe program uses transaction syntax.
August 12, 2011 at 8:16 am
Have you checked SP_WHO2 while this is going on to see what the SPID of the blocking process is?
Here's the code I use to see what the details of a blocking SPID hold:
sp_who2 --Run first to see what / who is blocking. Then put SPID below where 133 is.
DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
FROM SysProcesses
WHERE SPID = 133 --Change this SPID as appropriate
SELECT *
FROM ::fn_get_sql(@handle)
And if you don't get to that in time, you might be able to set up a job that logs the SPID stuff to a table.
Since the code for monitoring and logging the processes is not mine, I can't post it (and can't ask permission at the moment). But the tables involved in monitoring for blocked processes are sys.dm_tran_locks, sys.dm_os_waiting_tasks, sys.partitions, sys.dm_exec_requests, and a CROSS APPLY to sys.dm_exec_sql_text(sql_handle). I hope that helps.
August 12, 2011 at 3:59 pm
I typically make my own version of sp_who2 that I can run and it gives me the handle stuff already with some code of what is being run, reads on session, writes, security details... though... looking at whatever it is you used for blocked process report (sorry not too familiar) I use something similar.
Perhaps your report can have some mods like the following (I use for over # blocked processes, alerts emailed to me):
set nocount on
declare @sql varchar(max);
set @sql = 'set nocount on
select
current_timestamp as [DateNOW],
blked.spid as [BlockedProcessID],
blked.waittime as [WaitTime],
dbblked.name as [DatabaseBlockedProcessDatabase],
blked.cpu as [BlockedSessionCPU],
blked.physical_io as [BlockedSessionPhysicalIO],
blked.memusage as [BlockedSessionMemoryUsage],
blked.login_time as [BlockedSessionStart],
blked.last_batch as [BlockedQueryStart],
rtrim(blked.hostname) as [BlockedQueryHostname],
rtrim(blked.program_name) as [BlockedQueryProgramName],
rtrim(blked.cmd) as [BlockedQueryCommandType],
rtrim(blked.LogiName) as [BlockedQueryUserLogin],
left(convert(varchar(max),blkedhan.text),200) as [BlockedQueryLEFT200Text],
blker.spid as [BlockingProcessID],
dbblker.name as [DatabaseBlockingProcessDatabase],
blker.cpu as [BlockingSessionCPU],
blker.physical_io as [BlockingSessionPhysicalIO],
blker.memusage as [BlockingSessionMemoryUsage],
blker.login_time as [BlockingSessionStart],
blker.last_batch as [BlockingQueryStart],
rtrim(blker.hostname) as [BlockingQueryHostname],
rtrim(blker.program_name) as [BlockingQueryProgramName],
rtrim(blker.cmd) as [BlockingQueryCommandType],
rtrim(blker.LogiName) as [BlockingQueryUserLogin],
left(convert(varchar(max),blkerhan.text),200) as [BlockingQueryLEFT200Text],
case when blker.blocked = 0 then ''!!!!!!!!!!!!*** MAIN BLOCKER ***!!!!!!!!!!!!!!''
else isnull(convert(varchar(100),blker.blocked),''null'')
end as [BlockingBlockedByProcessID]
--, (select * from sys.dm_tran_locks locks where locks.request_session_id = blked.spid for xml path(''Lock''), type) as ''Locks''
from sys.sysprocesses as blked
join sys.databases dbblked on dbblked.database_id = blked.dbid
cross apply sys.fn_get_sql(blked.sql_handle) as blkedhan
join sys.sysprocesses as blker on blked.blocked = blker.spid
join sys.databases dbblker on dbblker.database_id = blker.dbid
cross apply sys.fn_get_sql(blker.sql_handle) as blkerhan
for xml path(''Process''),type, root(''BlockedProcesses'')'
exec (@sql) -- instead of having it emailed, gets about the result that was in your text file but with more detail
--
--EXEC msdb.dbo.sp_send_dbmail
-- @recipients = 'your@email.com',
-- @query = @sql,
-- @subject = 'testing database mail',
--@attach_query_result_as_file=1,
--@query_attachment_filename='Results.xml',
--@query_result_width = '30000',
--@importance='high',
--@query_no_truncate = 1
August 15, 2011 at 2:18 am
Hi,
Thanks for all the responses. I ran the query below first:
---------------------
sp_who2 --Run first to see what / who is blocking. Then put SPID below where 133 is.
DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
FROM SysProcesses
WHERE SPID = 133 --Change this SPID as appropriate
SELECT *
FROM ::fn_get_sql(@handle)
-------------------------
It did not pick the query that was blocking when i used the blocking spid. But it did pick the blocked spid query.
The second post response query generated the file attached. I have also put an Excel sheet showing sp_who2 results at the time the block occurred.
The select query in the Universe program runs once at the beginning. Results are put into a temporary file. The update statement runs per record retrieved by the select and it uses the temporary file. The update statement writes to the postingtransaction table.The field ExportedToAjs in the posting transaction table does not have an index on it. Is the problem due to a missing index on the ExportedToAJs field? How can i resolve the problem as the program logic indicates that the select query should not be interfering with the update statement?
August 15, 2011 at 6:04 am
Knowing the blocked query doesn't help you. Any query could be blocked. What you need to know is the details of the blocking query (the one that is stopping the other queries from running).
Since SP_WHO2 isn't always accurate (as in you log in right after the block disappears from the query's abilities to detect it), the best way to do this is set up a polling process using a table and a query (see my earlier post about the tables to use) and run the job every 20 seconds to check for blocked processes.
This won't catch everything, but it will catch a whole bunch of stuff you don't realize is blocking. Then, you can re-optimize the blocking queries (or send them off to the vendor / developer to optimize) so they stop blocking.
August 16, 2011 at 8:54 am
tendayit (8/15/2011)
Hi,Thanks for all the responses. I ran the query below first:
---------------------
sp_who2 --Run first to see what / who is blocking. Then put SPID below where 133 is.
DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
FROM SysProcesses
WHERE SPID = 133 --Change this SPID as appropriate
SELECT *
FROM ::fn_get_sql(@handle)
-------------------------
It did not pick the query that was blocking when i used the blocking spid. But it did pick the blocked spid query.
The second post response query generated the file attached. I have also put an Excel sheet showing sp_who2 results at the time the block occurred.
The select query in the Universe program runs once at the beginning. Results are put into a temporary file. The update statement runs per record retrieved by the select and it uses the temporary file. The update statement writes to the postingtransaction table.The field ExportedToAjs in the posting transaction table does not have an index on it. Is the problem due to a missing index on the ExportedToAJs field? How can i resolve the problem as the program logic indicates that the select query should not be interfering with the update statement?
From your excel and txt files, it looks like the query below is the source of the blocking.
<BlockingQueryLEFT200Text>SELECT PostingTransactionID, AccountID, TransactionDate, PostingCodeID, Narration, Reference, ExportedToAJS, AJSTransactionId FROM PostingTransaction WHERE ExportedToAJS = '0'</BlockingQueryLEFT200Text>
How long does this query normally take? How many rows are in the PostingTransaction table? How "unique" are the values in ExportedToAJS?
Also, if the select query is run once and the results appended to a temp file, why are the update queries being run while the "SELECT" statement is still executing? Shouldn't the application wait for the select statement to finish writing to the temp file prior to updating the postingtransaction table?
August 16, 2011 at 9:01 am
For the answer to your question, yes, you want in index. If this table is causing blocks, most likely want to take it off the heap, especially if you re setting values via an ID (PKEY or identity column).
PostingTransactionID, if this column is a unique identifier, create a unique clustered index on the column so the update goes a lot faster and less likely to block. Still going to block, but will hasten the update seek to the index point, do the update faster instead of getting a table lock, and let the other select finish more quickly.
August 18, 2011 at 8:45 am
The Universe program was modified to read data into a temp file in Universe from sql and to close the connection to sql. Data gets processed in Universe and a new connection to sql gets opened. The results are written back to the postingtransaction table. No problems have been encountered since the change.
Thanks all for your responses.
August 18, 2011 at 5:41 pm
tendayit (8/18/2011)
The Universe program was modified to read data into a temp file in Universe from sql and to close the connection to sql. Data gets processed in Universe and a new connection to sql gets opened. The results are written back to the postingtransaction table. No problems have been encountered since the change.Thanks all for your responses.
Good to hear. Thanks for the update.
August 18, 2011 at 6:39 pm
For future reference, here is another query to help get to that head blocker
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0),
[Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
Also, for reference, you could use the query I wrote about here[/url]. Or use sp_whoisactive by Adam Machanic.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply