How to Improve SQL Server Database Performance Globally

Microsoft’s SQL Server is a DBA friendly product. Nevertheless, at certain times it might not be fast enough which will result in customer escalations. In such situations “Performance Tuning” is the only solution but many DBA’s are neither aware of it nor know where to start. In this article, we will learn how to improve database performance at a global level.

Improve SQL Server Database PerformancePerformance improvement

Any experienced DBA would have the ability to solve an issue and identify the root cause that affected the production SQL Server. Instead of being reactive, wait for issues to occur and face customer escalations; we can be proactive and improve database performance.

What’s in the script?

Update StatisticsThese two scripts identify databases on which auto update statistics and auto create statistics were disabled. It then creates dynamic TSQL statements to turn on those two features.  After making these changes, your databases would run extremely fast and your customers would send appreciation emails. This script does not have error handling. When trying to run the script with a alter database statement on a corrupted SQL Server database, it would fail.

Script:

CREATE TABLE TBL1(cservername VARCHAR(200));
CREATE TABLE TBL2(cscript VARCHAR(2000));
INSERT INTO TBL1
VALUES('SERVER1');
DECLARE @cmd NVARCHAR(4000);
DECLARE @cmd2 NVARCHAR(4000);
DECLARE @vsvrname VARCHAR(200);
DECLARE cur1 CURSOR
FOR SELECT cservername
    FROM TBL1;
OPEN cur1;
FETCH NEXT FROM cur1 INTO @vsvrname;
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @vsvrname;
        SET @cmd2 = 'insert into TBL2 SELECT ''ALTER DATABASE ['' + name + ''] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT'' FROM ['+@vsvrname+'].master.sys.databases WHERE is_auto_update_stats_on = 0';
        PRINT @cmd2;
        DELETE FROM TBL2;
        EXEC (@cmd2);
        DECLARE @cscript VARCHAR(2000);
        DECLARE cur2 CURSOR
        FOR SELECT cscript
            FROM TBL2;
        OPEN cur2;
        FETCH NEXT FROM cur2 INTO @cscript;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @cmd = 'EXEC ('''+@cscript+''') at ['+@vsvrname+']';
                PRINT @cmd;
                EXEC (@cmd);
                FETCH NEXT FROM cur2 INTO @cscript;
            END;
        CLOSE cur2;
        DEALLOCATE cur2;
        FETCH NEXT FROM cur1 INTO @vsvrname;
    END;
CLOSE cur1;
DEALLOCATE cur1;
GO
CREATE TABLE TBL1(cservername VARCHAR(200));
CREATE TABLE TBL2(cscript VARCHAR(2000));
INSERT INTO TBL1
VALUES('SERVER1');
DECLARE @cmd NVARCHAR(4000);
DECLARE @cmd2 NVARCHAR(4000);
DECLARE @vsvrname VARCHAR(200);
DECLARE cur1 CURSOR
FOR SELECT cservername
    FROM TBL1;
OPEN cur1;
FETCH NEXT FROM cur1 INTO @vsvrname;
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @vsvrname;
        SET @cmd2 = 'insert into TBL2 SELECT ''ALTER DATABASE ['' + name + ''] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT'' FROM ['+@vsvrname+'].master.sys.databases WHERE is_auto_update_stats_on = 0';
        PRINT @cmd2;
        DELETE FROM TBL2;
        EXEC (@cmd2);
        DECLARE @cscript VARCHAR(2000);
        DECLARE cur2 CURSOR
        FOR SELECT cscript
            FROM TBL2;
        OPEN cur2;
        FETCH NEXT FROM cur2 INTO @cscript;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @cmd = 'EXEC ('''+@cscript+''') at ['+@vsvrname+']';
                PRINT @cmd;
                EXEC (@cmd);
                FETCH NEXT FROM cur2 INTO @cscript;
            END;
        CLOSE cur2;
        DEALLOCATE cur2;
        FETCH NEXT FROM cur1 INTO @vsvrname;
    END;
CLOSE cur1;
DEALLOCATE cur1;
GO

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover Outlook and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.