Is my new sproc causing blocking on the server?
(enlarge)
Figure 6. Output from sp_who2 shows blocking on Server |
The above
screenshot shows us that the new sproc will cause some blocking on the server.
Whether or not this blocking is a problem depends on its duration. If we block
other processes for say 5 seconds that's not so bad. But if we were blocking
other processes for 3 minutes that could cause trouble. After monitoring the
server for the duration of the trace replay you should now have a good idea
of how the production server will react to your change. Did your change block
user processes for long periods of time? If so consider modifying the code to
reduce the chance of blocking. In our case the blocking lasted only for a few
seconds at a time. Therefore this new sproc can be installed into production.
Currently
the ability to replay SQL traces provides a powerful tool to help the developer
optimize his code. There are all sorts of neat things you can do when replaying
traces (i.e. breakpoints, pausing and starting the trace, stepping through the
trace, etc.). Although Profiler has its weaknesses the replay feature does a
great job of simulating a production environment on your Development server.
» 
IE 7
Firefox 2.0
Comment and Contribute
