SQL Server / ADO.NET: Blocking Issues
I realize this is a VERY vague question, so forgive me.
The company I worked with contacted me saying that since they had upgraded to SQL Server 2008 they were experiencing a "blocking" issue.
I really don't know what that means. I don't even know how to see these "blocking" errors in SQL Server. This is not my area of expertise.
The no longer running DBA is processing SQL Server stuff anymore, and my program has probably written about 100,000 records per day to its database.
Just before I left, I changed all of our data-layer code from ADO.NET SqlCommand stored procedures to LINQ to SQL.
Does anyone know where I start trying to help them? I don't get paid just trying to help with a confusing startup.
How can I even BEGIN fix this problem?
a source to share
You can only start by getting a more accurate description of the problem they are seeing - and whether they are being told the "blockage" they are reporting or what they think they have a problem.
Until it becomes clear at what level in the system the problem occurs, it is impossible to even try to see the answer.
In terms of "fixing" blocking issues, the activity manager in SQL Server Management Studio (or any of them in 2k8) will show blocking to a certain extent, but in fact, that's one step ahead of where you are at the moment.
At the moment, the key question is what exactly is the error they see and where it is happening.
a source to share
How to track deadlocks using SQL Server 2005 Profiler , Should work with SQL 2008
I know StackOverflow Author had some similar issues, http://www.codinghorror.com/blog/archives/001166.html
I would just tell them to install MS SQL 2008 SP1 and most of the fixes.
a source to share
There is always a situation where we have to use a SELECT statement with WITH (NO LOCK), but this is not recommended in a sensitive environment as it allows dirty reads (uncommitted dependencies). But most of the blocking problems occurred during Update operations, so you should better focus on the update statements on your system. I suggest you use UPDATE with the ROWLOCK command so that SQL Server will only lock one row of the table, not the entire column or Pagelock.
The fact that you are having performance issues and deadlocks could be an indication of a more serious problem. For example, if SQL Server does not find the correct index for a query, it will perform a full table or index scan instead of a range scan; forcing it to use Tablock instead of Paglock (s).
a source to share