SQL Server/ADO.NET: Locking issues

Question

I understand this is a VERY vague question, so forgive me.

A company I used to work for has contacted me saying that since they've upgraded to SQL Server 2008, they've been encountering a ton of "locking" issues.

I really don't know what this means. I don't even know how to see these "locking" errors in SQL Server. This was not my area of expertise.

A DBA that doesn't work there anymore handled SQL Server stuff, and my program probably wrote about 100,000 records/day to their database.

Just before I left, I changed all our data layer code from ADO.NET SqlCommand stored procedures to LINQ to SQL.

Anyone have any idea where I start to try helping them out? I'm not getting paid, just trying to help out a struggling start-up.

How do I even BEGIN troubleshooting this?

Answer 1

You can only start by getting a more accurate description of the problem they're seeing - and whether the "locking" they're reporting is what is being reported to them or what they think the problem they have is.

Until its clear at what level in the system the problem is being generated its not possible to even begin to attempt to see an answer.

In terms of "seeing" locking issues, the activity manager in SQL Server Management Studio (or whatever either of those has become in 2k8) will show locking to a certain extent, but in truth that's a step ahead of where you are at the moment.

Right now the key question is what exactly is the error they are seeing is and where is it occurring.

Answer 2

How to Track Down Deadlocks Using SQL Server 2005 Profiler, Should work with SQL 2008

I know StackOverflow Author had some similar problems, http://www.codinghorror.com/blog/archives/001166.html

I would just tell them to install MS SQL 2008 SP1, and most hotfixes.

Answer 3

There is always a situation that we have to use SELECT statement with WITH (NO LOCK) but it is not advisable in sensitive environment because it enables dirty reads (uncommitted dependencies). But most of the deadlocks problems occurred during Update operations so you should better focus on update statements in your system. I suggest you to use UPDATE with ROWLOCK command so that SQL Server will lock only single row of table instead of whole tablelock or Pagelock.

The fact that you have performance problems and deadlocks might be an indicator of a more serious problem. For example, if SQL-Server doesn't find a proper index for a query, it will make a full table or index scans instead of a range scan; forcing it to use a Tablock instead of Paglock(s).