Troubleshooting SQL Locks

SQL Locking is one of the most frustrating and misunderstood issues you will encounter when dealing with SQL Server.  One good tool for getting a handle on this is SQL Profiler.  There is a blocked process event that it can monitor but it must first be enabled.  To enable SQL blocked process monitoring, use the sp_reconfigure command:

sp_configure 'show advanced options', 1  
RECONFIGURE
GO
sp_configure 'blocked process threshold', 10
GO
RECONFIGURE
GO

The blocked process threshold needs to be set because blocking happens quite frequently and it's up to you to determine when it becomes a problem.  This is typically going to be right around the time the application times out and the user gets an error message.  Once this is enabled, you can use the SQL Profiler tool to collect locking events.



I will typically take out the SQL statement and procedure events, since these tend to bloat the file and generate a lot of static during long traces.  If you have a situation where you can re-create a lock and need to find detailed information about that specific instance, you will want the SQL statements and procedures.

After a trace has been running for a few hours, you can start to see where your problems are coming from.  At that point, you can begin to focus on the code that might produce the queries that are causing locks and create indexes or optimize the code.


Comments

Post a Comment

Popular posts from this blog

Accessing Dynamics NAV OData with Postman

Image
1/13/20 - This apparently only applies to NAV.  See note at the end for connecting to Business Central It took me a little while to nail down authenticating to a Dynamics NAV OData web service with Postman.  Fiddler does it quite easily but I suspect it is a little more intelligent while Postman requires the correct settings for everything. First, in NAV you need to have NTLM authentication enabled.  I have found this is required any time you want to use the web services with anything other than .NET (and even then...). Now, with NTLM enabled, you might think the authentication is NTLM but you'd be wrong.  It's Digest.  There are two flavors of Digest: MD5 and MD5-sess.  By default, Postman will come up with MD5.  I discovered it was supposed to be MD5-sess by using Chrome's developer console.  With this console open, make the GET request and you can see all of the data being exchanged. Within the Authorization header, toward the end, you wi...
Read more

When you are falsely accused of not having SQL Server Report Builder installed

Today I was updating some report objects from a text file for an older BC13 upgrade.  Yes, even at this late date BC13 can be an "upgrade".  Long story.  Anyhow, in the process of pulling in reports I got an error message To upgrade reports, you must have Microsoft SQL Server 2016 Report Builder installed. The problem is, I did have SQL Server 2016 Report Builder installed.  Some cursory Googling revealed that someone else had run across this and their solution was to install an older version of the report builder.  Not satisfied with this option, I ran the import under Process Monitor .  Process Monitor showed that BC was looking for Microsoft.ReportingServices.RdlObjectModel.dll and not finding it.  Sure enough, that file was nowhere to be found.  I did find an article on that indicating that it was a part of older report builder installations and I managed to find it on my development system under some Docker containers (of all places)....
Read more

Error with Zetadocs on Sharepoint Online

Image
I was recently setting up Zetadocs on a new server (existing database) and I got this message when closing the General Settings screen. Exception of type 'Microsoft.Dynamics.Nav.Types.Exceptions.Encryption.NavEncryptionKeyNotFoundException' was thrown. I couldn't find the error anywhere in Google, hence this post.  The problem is that on a new server, the key needed to decrypt Sharepoint Online's login information will not have been installed.  To fix this, first connect to the service where Zetadocs was first installed (or is working) and open the Enrcryption Management screen. Click the "Export Encryption Key" button and save the key to a file.  You will be prompted to secure the file with a password. Once you have the file saved, copy it to the new server then go into the Encryption Management screen on its service and click "Import Encryption Key".  Enter the password for your file and the new key will be imported.
Read more