Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 9, 2000

Using SQL Server Profiler - Page 6

By Andrew Wiegand

Is my new sproc causing blocking on the server?

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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM