SQL Server - Performance Tuning

Hi,

 

Recently we have been facing the problem – Search Stored Procedures were taking more time than usual. Even some of them were taking more than 1 min to execute. We took the help of SQL Profiler to find out such statistics. After strong brain storming we found the simple solution – ReIndex all Tables

 

Here is the solution & script.

 

·          Set the compatibility mode on your database to 90.

·          ReIndex all Tables.

 

 

exec sp_dbcmptlevel 'DATABASE_NAME', 90;  -- Set the DATABASE_NAME to the target database

 

 

DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

 

 

SET @Database = 'DATABASE_NAME'   -- Set the DATABASE_NAME to the target database

 

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName 

               FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 

 

-- create table cursor

EXEC (@cmd)

OPEN TableCursor 

 

FETCH NEXT FROM TableCursor INTO @Table 

WHILE @@FETCH_STATUS = 0 

 

BEGIN

 

DBCC DBREINDEX(@Table,' ',90) 

FETCH NEXT FROM TableCursor INTO @Table 

 

END 

 

CLOSE TableCursor 

DEALLOCATE TableCursor

 

EXEC sp_updatestats

 

The above has reduced the time from more than 1 min to just 1 sec.

 

Happy Performance.

 

Thanks & Regards,

Arun Manglick || Senior Tech Lead || +91 20 30230500 Ext: 620 | +91 9850901262