July 27, 2011 at 2:57 pm
In the SQL2005 Activity Monitor I was able to set up a custom filter that only showed SPIDs that were either blocking or blocked but I don't see how to do this with SQL2008. All I see is the predefined filter of "All" which isn't what I'm looking for. Am I unable to do this in this version?
Thanks
July 28, 2011 at 2:38 am
Gregory Brown (7/27/2011)
In the SQL2005 Activity Monitor I was able to set up a custom filter that only showed SPIDs that were either blocking or blocked but I don't see how to do this with SQL2008. All I see is the predefined filter of "All" which isn't what I'm looking for. Am I unable to do this in this version?Thanks
wehn you open activity monitor in 2008, you will see a column as Blocked. You go to its drop down and you will find offending processes. Just click on one of them and it will filter the information. Other than this, there is just T-SQL results that i am aware of. not sure if there is an option in GUI.
July 28, 2011 at 3:26 am
Try the following T-SQL
select * from sys.sysprocesses where blocked > 0
July 28, 2011 at 6:03 am
I like to keep the Activity Monitor up all day and ONLY like to see SPIDs related to blocking so that I don't have to see any of the non-blocking SPIDs. So I guess this is a step backwards in the GUI? It doesn't look like Denali has added this back either. What third party tools can do this?
July 28, 2011 at 6:05 am
Gregory Brown (7/28/2011)
I like to keep the Activity Monitor up all day and ONLY like to see SPIDs related to blocking so that I don't have to see any of the non-blocking SPIDs. So I guess this is a step backwards in the GUI? It doesn't look like Denali has added this back either. What third party tools can do this?
Denalii??? Are you already using that version?
If you intend to keep the activity monitor running throught the day, I would rather avoid it as it becomes an intensive application sometimes and rather you can capture such transactions using T-SQL.
July 28, 2011 at 6:07 am
I just installed the latest Denali on a test machine to take a look.
How do I get the T-SQL to continuously monitor on a specified interval?
July 28, 2011 at 6:10 am
Gregory Brown (7/28/2011)
I just installed the latest Denali on a test machine to take a look.How do I get the T-SQL to continuously monitor on a specified interval?
May be using some stored procedure and running it through some job frequently. I am not concerned with processes blocking some other for a couple of seconds but what matters is if the blocked processes stay the same way for longer duration. I ain't an expert on it so wait for some big daddy to respond to your queries.
July 28, 2011 at 8:48 am
You can schedule a stored proc that will run at certain interval and load the data in another table.
Keep monitoring the table for the largest wait time.
It its too long and you see a block then there is some problem.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply