MSSQL Locks Average Wait Time is too high

Last days SCOM reported “SQL DB Average Wait Time is too high” monitoring tickets where the limit set to 250ms.

When I tried to check “SQL Server Locks counters” via PowerShell, I found extremely high value in “AverageWaitTimems” property.

How to find the “_PerfRawData_MSSQL_Locks” object:

Get-WmiObject -List | ?{$_.Name -like "*PerfRawData_MSSQL*Locks"} | Select Name

 

Get actual “AverageWaitTimems” property:

Get-WmiObject Win32_PerfRawData_MSSQLInstanceNameLocks | ?{$_.Name -like "_Total"} | Select Name, AverageWaitTimems | Format-Table -AutoSize

AverageWaitTimems

If you check the community addition below the page you’ll see a post how you should calculate the the real value from raw data. Please check “PERF_AVERAGE_BULK” section in the “Interpreting the counter values from sys.dm_os_performance_counters” to see how it works really. You can find here others as “Buffer cache hit ratio, Transactions, Total pages”.

Example to calculate the real value:

Get-WmiObject Win32_PerfRawData_MSSQLInstanceNameLocks | ?{$_.Name -like "_Total
"} | Select Name, AverageWaitTimems, AverageWaitTimems_Base | Format-Table -AutoSize

Sample 1

Sample1

Sample 2

Sample2

Calculated data:

(Sample2.AverageWaitTimems-Sample1.AverageWaitTimems)/(Sample2.AverageWaitTimems_Base-Sample1.AverageWaitTimems_Base)

(6452617-6452602)/(15901-15900)=15

You can check it via “CookedValue” based on the MSSQL$INSTANCE:Locks(_Total)\Average Wait Time (ms) counter.

Get-Counter -Counter '\MSSQL$INSTANCE:Locks(_Total)\Average Wait Time (ms)'

How to find counter to the “_PerfRawData_MSSQL_Loks” object:

Get-Counter -ListSet * | ?{$_.CountersetName -like "*MSSQL*Locks*"} | Select -expand Paths

 

PWA error: An entry with the same key already exists

Full story started with a different issue see below.

We had an issue regarding “Create Project Web App Instance” where the first given administrator account display name showed as <i:0#.w|firstname%2c lastname>.

Root cause analysis showed “Failure trying to Sync Web Application – events ID: 5555” in the application log.

Project farm contains only a proxy “User Profile Service Application” which is connect to a different SharePoint farm. I realized from the ULS log there is a simple access denied issue where the SharePoint Project Farm account missed “Full Control” permission on the “User Profile Service Application” on the secondary SharePoint farm.

ULS log entry: Category: User Profiles, Message: Exception occured while connecting to WCF endpoint: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied.

Issue with the “PWA error” in the title started during my investigation previous problem, when I tried to create Project web app instances to test account display name issue. First I created a test project site as “Test-PWA” and when I finished the test I deleted it. Later I wanted to test it again and created the same site as “Test-PWA”, but at this time I got the error: “Sorry, something went wrong. An entry with the same key already exists.” and a correlation ID instead of the list about Projects on the “Manage Project Web Apps”.

ULS log also contained the error: Application error when access /_admin/pwa/managepwa.aspx, Error=An entry with the same key already exists.

The issue answered here by Michael Wharton, MVP.

“This error is due to orphan PWA instances.”

The solution is to check orphan entries in the service application and remove them by PowerShell.

$PWA = Get-SPServiceApplication | ?{$_.DisplayName -like "Project Server Service Application"}
$PWA.SiteCollection | Select Name, ID, @{N='DatabaseName';E={$_.ProjectServiceDatabase.Name}}
$RemoveEntry = $PWA.SiteCollection | ?{$_.Id -eq ""}
$RemoveEntry.Delete()