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?



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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.