Currently i look for SPID which are blocking other spid's,enable traceon(3604) to capture SQLTEXT, Showplan.
If blockages keep re-occuring over time especially when certain query is executed, what are the next steps forward?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
when i posted this question, i was quite not aware of what to do next after you have captured information related to blockages like, SPID, Showplan and SQLText.
answering this question, just in case someone comes across same doubt.
Once you have found the details during blockages, next steps is to find out what caused it in first place.
sp_sysmon is tool you need to look into. sp_sysmon provides a detailed report of the activity on your system, and provides you with a number of ways to specify the type of information you want to receive, the time interval during which data for the report will be collected, and other options that determine how the report is generated.
The sp_sysmon report consists of a number of separate sections. You can run sp_sysmon to generate the complete report or only one of the individual sections. You can also specify a time interval over which the report should run or execute the stored procedure yourself at the beginning and end of the desired time period.