Introduction

This document describes the database optimization approach in order to avoid deadlocks over the SQL Server in the current (Ephesoft 4.0.6.0) and older releases.

Solution

The solution for deadlocks observed in Ephesoft were fixed in the Ephesoft Version 4.0.5.0, which includes application and database optimization. But the database maintenance activity was manual which is now automated in the current release of Ephesoft Version 4.0.6.0.

Database optimization handles the deadlocks observed in the Activity Tables only. The deadlocks observed in the Key Value Service, Regex Automated Validation and Batch Instance Service were handled through the application code updated in the Ephesoft Version 4.0.5.0. Only database optimization could reduce the number of occurrences but will not eradicate the problem. Therefore, it is recommended to upgrade to the latest Ephesoft Version 4.0.6.0 for complete solution.

Ephesoft Version 4.0.6.0 and above (New Installation)

One-time optimization

All the database optimization that are to be done only once are handled automatically by the installer. Thus, no manual steps are required in this case.

Maintenance optimization

The maintenance activity for SQL Server database was earlier a manual effort in the Ephesoft Version 4.0.5.0. It was required to defragment the indexes w.r.t the increasing data load which also helps in avoiding deadlock scenarios.

In Ephesoft Version 4.0.6.0, this activity has been automated with the help of SQL procedure. The DBA has to create a one-time Job to execute the procedure for defragmenting the indexes. The indexes are now reorganized rather than rebuild avoiding locks on tables.

Procedure to re-organize Indexes

USE [DB-Name]

GO
IF OBJECT_ID('[dbo].[sp_ephesoft_performance]') IS NOT NULL
BEGIN 
 DROP PROC [dbo].[sp_ephesoft_performance] 
END 

GO
CREATE PROC [dbo].[sp_ephesoft_performance] 
@Threshold FLOAT=60.00
AS 
 DECLARE @Fragmentation FLOAT, @QueryToRebuild nvarchar(max) 
-- Script to generate rebuild query

 DECLARE INDEX_CURSOR CURSOR 
 FOR
 SELECT 
 'ALTER INDEX ' + dbindexes.[name]+ ' ON ' + dbschemas.[name] + '.' +dbtables.[name] +' REORGANIZE' as QUERY,
 indexstats.avg_fragmentation_in_percent
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
 INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
 INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
 INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
 AND indexstats.index_id = dbindexes.index_id
 WHERE indexstats.database_id = DB_ID()
 AND indexstats.avg_fragmentation_in_percent > @Threshold
 ORDER BY indexstats.avg_fragmentation_in_percent desc 

 OPEN INDEX_CURSOR -- This charges the results to memory

 FETCH NEXT FROM INDEX_CURSOR INTO @QueryToRebuild,@Fragmentation 

-- fetch the first result

 WHILE @@FETCH_STATUS = 0 
 BEGIN
 BEGIN TRAN
 PRINT CAST(@Fragmentation AS VARCHAR) +' ' + @QueryToRebuild
 EXEC sp_executesql @QueryToRebuild
 FETCH NEXT FROM INDEX_CURSOR INTO @QueryToRebuild, @Fragmentation
 COMMIT
 END

 CLOSE INDEX_CURSOR 
 DEALLOCATE INDEX_CURSOR

GO

 

In case of SQL Server Express Edition, this procedure can be run manually. It takes only one optional parameter i.e. the fragmentation threshold value. The default value is set as 60 which can be changed as per the requirement.

For example: 
exec sp_ephesoft_performance 40

How to create the Automation Job?

Creating the SQL Job using Management Studio

Following are the steps for creating the job:

  1. In the Object Explorer, expand the server wherein you want to create a SQL Server Agent Job.
  2. Expand the SQL Server Agent.
  3. Right-click on the Jobs folder and select New Job.
  4. The New Job dialog box opens. In the General Page, modify the general properties of the job. For more information on the available options on this page, see Job Properties – New Job (General Page)
  5. In the Steps page, organize the job steps. For more information on the available options on this page, see Job Properties – New Job (Steps Page)
  6. In the Schedules page, organize the schedules for the job. For more information on the available options on this page, see Job Properties – New Job (Schedules Page)
  7. In the Alerts page, organize the alerts for the job. For more information on the available options on this page, see Job Properties – New Job (Alerts Page)
  8. In the Notifications page, set the actions for Microsoft SQL Server Agent to perform when the job is complete. For more information on the available options on this page, see Job Properties – New Job (Notifications Page).
  9. In the Targets page, manage the target servers for the job. For more information on the available options on this page, see Job Properties – New Job (Targets Page).
  10. When finished, click on OK.

Creating the SQL Job via Query

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'EphesoftMaintenanceJob', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Defragment Indexes Daily', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT  
--Database login name who has access to the database on which Job needs to be executed.  

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DefragmentIndexes', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 	
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC sp_ephesoft_performance 40', 
		@database_name=N'ephe4050', 
		@flags=0
--command=N’EXEC sp_ephesoft_performance 40’is the fragmentation threshold of value 40. This value can be changed as per the requirement.
--database_name is the name of database on which the Job needs to be executed.

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunDaily', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20160523, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'bc8c1d17-d883-4a26-8272-406dfe311799'
--freq_type is the frequency on which this Job will run. (freq_type=1, once) (freq_type=4, daily) (freq_type=8, weakly) 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

 

Ephesoft version 4.0.5.0

 

One time optimization

All the database optimization that are to be done only once are handled automatically by installer. Thus no manual steps are required in this case.

Maintenance optimization

Following optimization should be part of maintenance activity of database server.

These queries needs to be executed daily or weekly or monthly, depending on the number of pages executed over a period of time. For example, if we are running 12000 pages on daily basis, these queries should be executed daily.

Also, shutdown all the Ephesoft servers before starting the maintenance activity.

Update Statistics

While executing this query no batches should be in RUNNING state. Please replace ‘<DB-Name>’ with the name of respective database name.

USE <DB_NAME>

GO

EXEC sp_MSForEachTable 

'UPDATE STATISTICS ? WITH FULLSCAN;'

GO

Rebuild Indexes

Following query will help us determine which indexes needs to re build. Execute the following query and then for indexes if fragmentation is above 60, please copy the query from column ‘QUERY’ and execute the same.

USE <DB-Name>
SELECT dbschemas.[name] as 'Schema', 

dbtables.[name] as 'Table', 

-- Script to generate rebuild query

'ALTER INDEX ' + dbindexes.[name]+ ' ON ' + dbschemas.[name] + '.' +dbtables.[name]   +' REBUILD' as QUERY,

dbindexes.[name] as 'Index',

indexstats.index_type_desc AS IndexType, 

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

--AND indexstats.avg_fragmentation_in_percent > 0

ORDER BY indexstats.avg_fragmentation_in_percent desc

 

 

Ephesoft version older than 4.0.5.0 and above 3.x

 

One time optimization

These queries needs to execute once in a life time of database configured for Ephesoft.

Prerequisite

  1. All the servers connected to the database should be down.
  2. Ephesoft database should contain all the tables and sample data.

Query

Enable read committed snapshot.

Please replace ‘<DB-Name>’ with the name of respective database name.

USE master;
 

/* Set Database to Single User Mode */

GO

ALTER DATABASE <DB_NAME>

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

 

/* Set SNAPSHOT ON */

GO

ALTER DATABASE <DB_NAME>

SET ALLOW_SNAPSHOT_ISOLATION ON

GO

 

GO

ALTER DATABASE <DB_NAME>

SET READ_COMMITTED_SNAPSHOT ON

GO

 

/* Set Database back to Multi-User */

GO

ALTER DATABASE <DB_NAME>

SET MULTI_USER;

GO

To check whether snapshot have been enabled, please execute the following query. It should result in ‘1’ for all the columns. Please replace ‘<DB-Name>’ with the name of respective database name.

SELECT is_read_committed_snapshot_on, snapshot_isolation_state FROM

sys.databases WHERE name= ‘<DB_NAME>’;

 

Removal of Page Locks

Following procedure needs to be created which on execution will remove page locks from all the tables within a database. Please replace ‘<DB-Name>’ with the name of respective database name.

USE <DB_NAME>

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[allPageLockRemoval] @schemaName nvarchar(30), @dbName nvarchar(30)

AS

               BEGIN

                              DECLARE @indexName nvarchar(60)

                              DECLARE @indexSql nvarchar(4000) 

                              DECLARE @qTableName varchar(100)

                              DECLARE @alterSql nvarchar(4000)

               

                              CREATE TABLE #index_names (name varchar(100), table_name varchar(100))

                              

                              SET @indexSql = 'INSERT INTO #index_names(name, table_name) SELECT i.name, t.name FROM ' +

                                                                                          @dbName + '.sys.indexes i INNER JOIN ' + @dbName +

                                                                                          '.sys.tables t ON t.object_id = i.object_id'

                              PRINT 'SQL query to find indexes for all tables'

                              PRINT @indexSql              

                              EXEC(@indexSql)               

                                                                                          

                              DECLARE indexes CURSOR FOR

                                             SELECT name, table_name

                                             FROM #index_names

                                                            

                              PRINT 'Setting page locks OFF for all the indexes'                 

                              OPEN indexes

                                             FETCH NEXT FROM indexes INTO @indexName, @qTableName

                                             WHILE @@FETCH_STATUS = 0

                                             BEGIN

                                                            PRINT 'Alter query to set page lock OFF'

                                                            SET @alterSql = 'ALTER INDEX ' + QUOTENAME(@indexName) + ' ON ' + QUOTENAME(@schemaName) + '.'

                                                                                                                                       + QUOTENAME(@qTableName) + ' SET ( ALLOW_PAGE_LOCKS  = OFF )'

                                                            PRINT @alterSql

                                                            EXEC(@alterSql)

                                                            PRINT 'Page lock removed from index: ' + @indexName

                                                            FETCH NEXT FROM indexes INTO @indexName, @qTableName

                                             END

                              CLOSE indexes

                              DEALLOCATE indexes

                              DROP TABLE #index_names

               END

GO

Execute the following query to execute the above procedure, please replace ‘<DB-Name>’ with the name of respective database name and <SCHEMA-NAME> with schema:-

USE <DB-name>

GO

DECLARE              @return_value int

EXEC      @return_value = [<SCHEMA-NAME>].[allPageLockRemoval]

                              @schemaName = N'<SCHEMA-NAME>',

                              @dbName = N'<DB-Name>'

SELECT  'Return Value' = @return_value

GO

 

Maintenance optimization

These optimization should be part of maintenance activity of database server. This is similar to what has been defined for Ephesoft version 4.0.5.0 and above.

 

Documentation Main Page | How To Articles | Downloads and Updates |

Was this article helpful to you?

Kip Path

Comments are closed.