This article provide a summary on how to improve performance and avoid Deadlocks.
This article applies to 5.6.30 and later.
Error MSG:
GetFromDB ': Query: SP_EM_GetResourceQuantityAllowed N'516', N'12 ', N'1', N'16 ', N'285'. Key Field Name: TotalQuota. Details: A fatal error occurred for the current command. Clear any results. The current command encountered a fatal error. Clear any results.
Error MSG:
"The
Server request timed out." when subscribing to a service.
To improve SQL performance, avoid deadlocks following change is required on MachPanel Database:
Login as SA, Open MachPanel_DB properties, click Options, change “Is Read Committed Snapshot On” to “True” as shown below:
-If deadlock errors are seen, do check Is Read Committed Snapshot On is true or not on database, using query below:
--Query must be executed using SA or Admin account
Query:
SELECT is_read_committed_snapshot_on
FROM sys.databases WHERE name = 'MachPanel_Db';
GO
--To set Is Read Committed Snapshot On = 1, execute below script using SA or Admin Account
USE master;
GO
ALTER DATABASE MachPanel_Db
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE MachPanel_Db
SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE MachPanel_Db
SET MULTI_USER;
GO
<
Additional Steps
Recovery Modes and the Performance Matrix for SQL Database (Optimization of Database):
https://kb.machsol.com/Knowledgebase/55658/Recovery-Modes-and-the-Performance-Matrix-for-SQL-Database