June 5, 2009 at 7:36 am
Hi all,
I have a database that archives lots of data from external reporting programs in the form of Detailed -> Hourly -> Daily -> Monthly -> Yearly. Every few days, the lower tables get summarized and placed in the table above, while the lower tables are purged. Also, there is a continuous, out of my control, input of data into the detailed tables.
As this is a third party application, I'm trying to streamline the database by adding clustered indexes (yes, there were no clustered indexes OR primary keys at all...was a separate issue I had posted about earlier). Now that I have my indexes created in a way I'm happy with, the next step is to rebuild them.
I have an automated script that will run ALTER INDEX ALL ON TARGET_TABLE REBUILD WITH(ONLINE = ON) on all the tables once a week. Obviously, with so much data being uploaded, blocking will occur.
I know that if I check sysprocesses, and the blocked value equals the spid value, I can ignore that as the process is not really blocking itself. What I'd like to do is catch the true blocking that occurs and kill that rebuild index step. I plan to do this with a parallel block killer that runs every minute in the same time frame as the rebuild job.
Is there a away to link the blocked object with the blocked spid in so I known I'm not killing some other process?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 5, 2009 at 8:36 am
To start with, stop looking at sysprocesses and start using the DMV's to look at your problems.
The first one to look at is sys.dm_os_waiting_tasks which will hold all the waiting task information. You can do a join to sys.dm_exec_requests by blocking session_id to link a waiting task to its blocking task and then use the sys.dm_exec_sql_text DMF to get the executing statement for the blocking task. Possibly something like:
select
blocked.session_id as blocked_sessionid,
blocked_text.text as blocked_statement,
blocking.session_id as blocking_sessionid,
blocking_text.text as blocking_statement, *
from sys.dm_os_waiting_tasks as tasks
join sys.dm_exec_requests as blocking on tasks.blocking_session_id = blocking.session_id
join sys.dm_exec_requests as blocked on tasks.session_id = blocked.session_id
cross apply sys.dm_exec_sql_text(blocking.sql_handle) as blocking_text
cross apply sys.dm_exec_sql_text(blocked.sql_handle) as blocked_text
where tasks.session_id tasks.blocking_session_id
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
June 5, 2009 at 10:13 am
Jonathan Kehayias (6/5/2009)
To start with, stop looking at sysprocesses and start using the DMV's to look at your problems....
Hmmm...thanks for the tips. I really need to get a good reference on DMV's (I had a full page pullout that they sent with my subscription of SQL Server Magazine, but lost it...grrr). The script is definitely pointing me out in the right direction.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply