Optimize the SQL Database Table Performation by Rebuild Query - Dynamics NAV SQL Optimization

 Hello Experts,

Run the below query on SQL DB for optimization

Take the full backup of database before run this query

================================================

DECLARE @sql VARCHAR(MAX), @i INT, @init INT, @table VARCHAR(50), @indextype VARCHAR(50), @indexname VARCHAR(500), @fillfactor INT

--DROP TABLE ##t

IF OBJECT_ID('tempdb..##t') IS NOT NULL

      DROP TABLE ##t

 

SELECT

ROW_NUMBER() OVER(ORDER BY i.fill_factor DESC) as ID

, DB_NAME() AS Database_Name

, sc.name AS Schema_Name

, o.name AS Table_Name

, o.type_desc

, i.name AS Index_Name

, i.type_desc AS Index_Type

, i.fill_factor

, 0 AS [Status]

, CAST('' AS VARCHAR(1000)) AS ErrorMEssage

INTO ##t

FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id

WHERE i.name IS NOT NULL

AND o.type = 'U'

ORDER BY i.fill_factor DESC, o.name

SELECT @i = MAX(ID), @init = 1 FROM ##t 

WHILE @i >= @init

BEGIN

SELECT @table = Table_Name, @indexname = Index_Name, @indextype = Index_Type, @fillfactor = fill_factor FROM ##t WHERE ID = @init

IF @indextype = 'CLUSTERED' AND @fillfactor NOT IN (0,100)

SET @sql = 'ALTER INDEX ['+@indexname+'] ON [dbo].['+@table+'] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)'

ELSE IF @indextype = 'NONCLUSTERED' AND @fillfactor NOT IN (0)

SET @sql = 'ALTER INDEX ['+@indexname+'] ON [dbo].['+@table+'] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)'

BEGIN TRY

PRINT 'Starting index rebuild of number ' + CAST(@init AS VARCHAR(10)) + ' out of ' + CAST(@i AS VARCHAR(10)) + ' Table Name = ' + @table

PRINT @sql

EXEC (@sql)

UPDATE ##t SET [Status] = 1 WHERE ID = @init

END TRY

BEGIN CATCH

UPDATE ##t SET ErrorMEssage = ERROR_MESSAGE() WHERE ID = @init

END CATCH

SET @init += 1

END

=====================================================

This query will take some time depend on size of database

I hope it will help someone.

Keep Sharing...Keep Growing...

====================================================

Perform following Steps to Reduce Table Lock: (Dynamics NAV)

1. Run above query to rebuild table index (run in night time – we can setup this query to run at every weekend)
2. Increase the table lock time waiting period (default it is 10 second – we can changes to 30 second) From Database à Alter à Advance à Timeout Duration
3. Check our custom code – if it is running in long loop for some standard  calculation than commit in code to release table lock
4. Verify that there is no CONFIRM command used in- between long running code- it is cause the major issue in table lock
5. We need to identify the Table Name – for that user getting table lock issue (We can run SQL Query  EXEC sp_who2  identify the running sql command and check the query executing cause the table lock)
6. Re-transfer the full SQL Database to fresh DB – It will reduce database size to 40% and increase performance (this is optional steps we can do if relay require)
7. Check our long executive code and add command SetCurrentKey to optimize it