Webcast: Operational Excellence for DBAs, Part 1: Setup and Configuration
This session covers key setup and configuration questions faced when setting up a new SQL Server, from the bare metal configuration on up. What difference does the CPU make? How important is 32-bit versus 64-bit? Should I virtualize? What kind of disk subsystem do I need? What edition of SQL Server is best for this application and how should it be licensed? What’s a good "starter" configuration compared to a "high-end" configuration? What difference do important SP_CONFIGURE settings make to system performance?
> Register Now
> Live date: 11/10/2010 at 12:00 Pacific
Featured White Paper(s)
Automating DBA Processes for Microsoft SQL Server
Want to save time, reduce errors, get things done without you doing them and standardize preventative maintenance of your SQL… (read more)
Featured Script
dba3_sp_HelpReNameIndicies
Bonus Proc dbo.sp_HelpReNameIndiciesForEachTable Bonus Script – applies to all DBs on DBMS… (read more)
Locking and Blocking
While discussing some of the issues you will experience with Self-Help Business Intelligence we talked about Locking and Blocking. When you define your own reports you have the ability to optimize for those situations. When you have views made available to end users for which you do not yet have usage patterns the situation is more complicated.
In response to that editorial I received a comment from Paul. He experiences something of a similar nature where a number of systems interact in ways that are difficult to trace, and cause some locking and blocking grief. Many of you can say, "Me Too".
Paul Says:
I’m sorry to say that we face some of the same challenges so I don’t really have a lot of solutions to share. We have quite a few "batch" style applications that run in our environment and locking (and the dreaded "timeout") does become an issue. Even in one of our desktop applications there appears to be an issue with locking of the table (this then of course prevents other users from saving data to the same table). There doesn’t "appear" to be anything wrong with the code (I wrote it myself and have had others review it) but there is obviously something wrong with either keeping a connection open or not closing one properly.
It appears to be similar to the case you presented – when the user closes the program (once you identify who it is via Activity Monitor) the "Suspended" process goes away. We sometimes have to forcibly close the connection via Activity Monitor if there are multiple Suspended processes. The Suspended process is shown with a CXPACKET-type description and finding the root cause of this has been difficult at best (some research has indicated this may be a network-type error).
The question I face is how to deal with all of these applications and have them "play nice" with each other? I’ve thought of many things including separating the "batch" tables into their own databases (split by department function) but it does take time to set that up (identifying all the tables, changing the applications to point to a different data source, etc). Different schemas wouldn’t necessarily solve the locking issue but at least with different databases when the locks do occur they limit the "damage" to a specific area instead of the whole office. Obviously dealing with the locking is the way to go but taking the time to track down those issues while also working on many other projects is very difficult. Some could justify that the cost of tracking down the issue is outweighed by it’s occurrence.
My first thought would be, "Are you using Transactions?" Even if the connection is not closed, if you use a transaction and the process ends with a COMMIT then all of the locks should be released for other processes. Most likely, the connections still appear due to Connection Pooling. Unless the user actually closes the client application, then any connection that is closed will not be physically closed, but returned to the connection pool for use in the future,.When you forcibly close the connection, or the user exits the application, then any locks will be released. At that point, you have no assurance what the state of the data is.
Send your tips for Paul to btaylor@sswug.org.
Cheers,
Ben