Recently on one of our staging servers had a memory consumption issue and even 32 GB of RAM was looking insufficient and application performance was being impaired. Initially I thought the cause would be poorly designed queries, but after a thorough investigation this turned out not to be the case. I noticed that whenever the Re-indexing and Update Statistics jobs executed against the database the memory consumption increased considerably. The size of the database was 155 GB, the server had 32 GB of RAM as well as 8 CPU’s on it.
While performing the investigation of the system, I decided to test the performance of the Re-indexing and Update Statistics tasks separately.
The T-SQL used for performing the Re-indexing of the database is as shown below:
DECLARE
@dbname varchar(1000),
@parentname varchar(255),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2),
@command varchar(1000)
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select [name]
from sysdatabases where name not in
(
'master',
'model',
'msdb',
'tempdb'
)
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @command =
'
use ['+@dbname+']
Exec sp_MSForEachtable ''DBCC DBREINDEX ("?")''
'
exec (@command)
FETCH NEXT FROM DBCUR INTO @dbname
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO
Note from the above T-SQL, the task uses a system stored procedure named Exec sp_MSForEachtable along with a DBCC command named ”DBCC DBREINDEX ("?"). This was taking around 54 minutes to perform the Re-indexing of the entire database. As soon as the above T-SQL was executed the Memory Utilization on the server rapidly shot up. Since DBCC DBREINDEX is going to be removed in future releases of SQL Server and sp_msforeachtable is an undocumented stored procedure I decided to change the logic involved in performing the Re-indexing task as follows:
-- Ensure a USEstatement has been executed first. SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); --PRINT N'Executed: ' + @command; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do; GO
As seen from the above T-SQL, only those indexes whose fragmentation level is less than 30% will be re-organized whereas the one whose fragmentation level is greater than 30% will be rebuilt. The script has also eliminated the use of sp_msforeachtable undocumented stored procedure as well as the DBCC DBREINDEX command.
Once this logic was implemented, the Re-indexing task completed in just 18 minutes and the memory consumption on the server was mere 5.29 GB.
The Logic initially used for performing the Update Statistics is as follows:
DECLARE @dbname varchar(1000), @parentname varchar(255), @SQLSTR VARCHAR (1000), @ctrl CHAR (2), @command varchar(1000) SET @ctrl = CHAR (13) + CHAR (10) DECLARE DBCUR CURSOR FOR select [name] from sysdatabases where name not in ( 'master', 'model', 'msdb', 'tempdb' ) order by 1 OPEN DBCUR FETCH NEXT FROM DBCUR INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN select @command = ' use ['+@dbname+'] Exec sp_MSForEachtable ''update statistics ? with fullscan'' ' exec (@command) FETCH NEXT FROM DBCUR INTO @dbname END CLOSE DBCUR DEALLOCATE DBCUR GO
The majority of the tables in the database had columns of NTEXT and Image Data Types.
Note from the above T-SQL that the Update STATISTICS with FULLSCAN is going to be performed for each and every table in the database even for those tables where there are columns having data type such as NTEXT, IMAGE,VARBINARY which is very inefficient and can kill the performance of your server.
If a column has a NTEXT data type then it means that the data is stored in the form of LOB structure. For a LOB column, when the statistics is created, the first 100 bytes and last 100 bytes will be read to generate statistics. If we do not specify the sample rate for the UPDATE STATISTICS command, SQL Server will calculate the sample rate based on the number of rows and number of pages used by that table. In addition, the number of BLOB pages will not be considered so it is possible that when we run UPDATE STATISTIC a large volume of data is sampled and 200 bytes of every BLOB row is read which generates a large I/O request and increasing memory consumption. Therefore it is always best that if you have a table in which there are columns of data types TEXT,NTEXT,IMAGE,VARBINARY then always perform the UPDATE STATISTICS operation without a FULLSCAN. I changed the T-SQL involved in performing the UPDATE STATISTICS operation as follows:
SET NOCOUNT ON
DECLARE @columnname VARCHAR(MAX)
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE @NAME VARCHAR(MAX)
declare @i INT
declare @j INT
create table #temp
(
tablename varchar(1000),
statsname varchar(1000),
columnname varchar(1000)
)
insert #temp(tablename,statsname,columnname)
SELECT DISTINCT
OBJECT_NAME(s.[object_id]),
s.name AS StatName,
COALESCE(@NAME+ ', ', '')+c.name
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN INFORMATION_SCHEMA.COLUMNS D ON D.[COLUMN_NAME]= C.[NAME]
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
create table #temp1
(
id int identity(1,1),
tablename varchar(8000),
statsname varchar(8000),
columnname varchar(8000)
)
insert #temp1(tablename,statsname,columnname)
select tablename,statsname,stuff(
(
select ','+ [columnname] from #temp where
statsname = t.statsname for XML path('')
),1,1,'')
from (select distinct tablename,statsname from #temp )t
SELECT @i=1
SELECT @j=MAX(ID) FROM #temp1
WHILE(@I<=@J)
BEGIN
SELECT @statsname = statsname from #temp1 where id = @i
SELECT @tablename = tablename from #temp1 where id = @i
SELECT @columnname = columnname from #temp1 where id = @i
SET @sql = N'UPDATE STATISTICS '+QUOTENAME(@tablename)+QUOTENAME(@statsname)
PRINT @sql
EXEC sp_executesql @sql
SET @i = @i+1
END
DROP TABLE #temp
DROP TABLE #temp1
Once the above logic was implemented, the Update Statistics command completed within 6 minutes and without a strain on memory resources:
My suggestion to the database developers would be to avoid NTEXT data type as much as they can in their development work.
This was one such approach which I used to fine tune the performance of the system which was badly impacted due to the re-indexing and Update Statistics task. If you have any suggestions then please do let us know, alternatively you can write me on singhsatnam84@yahoo.com
]]>