SQL Server – Could ‘Maximum Server Memory’ Be Too Low for Large Batched Delete?

alter-databasedeletememoryoutput-clausesql server

I'm trying to improve the performance of a SQL server database backup program that will create a new database to hold one years worth of report records, delete report records out of the main database, and capture the delete output and use it to insert the deleted records into the new 'year named' database. Essentially I am doing a move of the records from one main database, to separate databases for each year.

On my test database, that is about 2.5 GB worth of report records, this process is taking around 3:00-3:40 minutes to complete. I have the 'Maximum Server Memory' set to the max, which is 2147483647 MB. As I understand it, SQL server uses whatever memory is available up to that maximum limit, and will release any memory that it does not need if there is memory pressure from other applications that require more memory.

On the client SQL servers, we have the 'Maximum Server Memory' set to 2048 MB, because we thought that since SQL server was holding on to too much memory when it was set to the max value. The client databases are usually much larger than my test database, ranging anywhere from 30 GB to over 60 GB in a couple cases. Running the backup utility on the databases on these servers is taking at least 10 hours, and we usually tell the client that it could take anywhere from 18-24 hours for the utility to run to completion and create the 'year named' backup databases.

We constantly see PAGEIOLATCH_EX and PAGEIOLATCH_SH waits, which I think means that either the disk subsystem is having problems with the IO required, or there is not enough memory available for the operation.

Would limiting the 'Maximum Server Memory' like we have cause excessive latch wait times like we are seeing?

We don't currently disable the indexes on the Reports table, which I'm sure is causing some slowness, but I'm not sure how much. We actually create an additional index over the date field, because that is the used in the where clause of our delete, in an attempt to speed up finding the appropriate records to delete. Which I now think might not be necessary, because the records should already be in date order, because that is how they are created daily.

The process is like this:

  1. Drop the full text index on the report table.
  2. Create a non-clustered index on the report table over the date field and ID.

  3. For each year of records selected

    1. Create a new database to move the records to.
    2. Create the reports table that has the same fields as the base database.
    3. Perform the delete of that years records in 500 record batches, capturing the output and inserting it in the 'year named' database.
    4. Create the full text index on the new database.
    5. Shrink the main database. (This is because of disk space issues. Most of the clients disks can't finish the operation without running out of space unless we shrink the main database to release the space)
  4. Re-enable the full text index on the main reports database.

I'll try to include some samples of the script that I think might be relevant to the question.

Delete with output clause

DECLARE @Rows INT
SET @Rows = 1

WHILE (@Rows > 0)
BEGIN
    DELETE TOP (500) FROM [" + opDbName + @"].[dbo].[Reports]
    OUTPUT DELETED.[ID]
        ,DELETED.[datafile]
        ,DELETED.[report]
        ,DELETED.[date]
        ,DELETED.[istext]
        ,DELETED.[version]
        ,DELETED.[dateimported]
        ,DELETED.[archivelocation]
    INTO [" + newDbName + @"].[dbo].[Reports]([ID],[datafile],[report],[date],[istext],[version],[dateimported],[archivelocation])
    WHERE [Date] BETWEEN '" + i + @"-01-01' AND '" + i + @"-12-31'

    SET @Rows = @@ROWCOUNT
END
GO

Create year database

    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'" + newDbName + @"')
    BEGIN
    PRINT('Creating database " + newDbName + @" ...')
    CREATE DATABASE [" + newDbName + @"] ON PRIMARY 
    ( NAME = N'" + newDbName + @"', FILENAME = N'" + dataDir + newDbName + @".mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'" + newDbName + @"_log', FILENAME = N'" + logsDir + newDbName + @"_log.ldf', MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
    GO

Alter year database

I don't even really know what all these settings are, but they come from generating the script to create the base database from above, and I just apply it to the newly created database to keep them the same.

PRINT('Altering database " + newDbName + @" ...')
GO
ALTER DATABASE [" + newDbName + @"] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [" + newDbName + @"].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [" + newDbName + @"] SET ANSI_NULL_DEFAULT OFF 
ALTER DATABASE [" + newDbName + @"] SET ANSI_NULLS OFF 
ALTER DATABASE [" + newDbName + @"] SET ANSI_PADDING OFF 
ALTER DATABASE [" + newDbName + @"] SET ANSI_WARNINGS OFF 
ALTER DATABASE [" + newDbName + @"] SET ARITHABORT OFF 
ALTER DATABASE [" + newDbName + @"] SET AUTO_CLOSE OFF 
ALTER DATABASE [" + newDbName + @"] SET AUTO_CREATE_STATISTICS ON 
ALTER DATABASE [" + newDbName + @"] SET AUTO_SHRINK ON 
ALTER DATABASE [" + newDbName + @"] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [" + newDbName + @"] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [" + newDbName + @"] SET CURSOR_DEFAULT GLOBAL 
ALTER DATABASE [" + newDbName + @"] SET CONCAT_NULL_YIELDS_NULL OFF 
ALTER DATABASE [" + newDbName + @"] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [" + newDbName + @"] SET QUOTED_IDENTIFIER OFF 
ALTER DATABASE [" + newDbName + @"] SET RECURSIVE_TRIGGERS OFF 
ALTER DATABASE [" + newDbName + @"] SET DISABLE_BROKER 
ALTER DATABASE [" + newDbName + @"] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE [" + newDbName + @"] SET DATE_CORRELATION_OPTIMIZATION OFF 
ALTER DATABASE [" + newDbName + @"] SET TRUSTWORTHY OFF 
ALTER DATABASE [" + newDbName + @"] SET ALLOW_SNAPSHOT_ISOLATION OFF 
ALTER DATABASE [" + newDbName + @"] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [" + newDbName + @"] SET READ_COMMITTED_SNAPSHOT OFF 
ALTER DATABASE [" + newDbName + @"] SET HONOR_BROKER_PRIORITY OFF 
ALTER DATABASE [" + newDbName + @"] SET RECOVERY SIMPLE 
ALTER DATABASE [" + newDbName + @"] SET MULTI_USER 
ALTER DATABASE [" + newDbName + @"] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [" + newDbName + @"] SET DB_CHAINING OFF 
ALTER DATABASE [" + newDbName + @"] SET READ_WRITE 
GO

Best Answer

Your process is sound. Putting the index on the date column will make it much faster for SQL Server to find the rows that it is looking for. Without the nonclustered index SQL Server will need to scan the production table every time you go to delete the rows. This means that SQL will need to load the entire table from disk each time the DELETE TOP 500 runs. Having (and using) the nonclustered index will be essential for getting this done quickly.

As for the memory setting, you'll want to set that to give SQL Server access to as much RAM as possible. You are correct, SQL Server will release RAM if other applications need it. The fact that you are seeing PAGEIOLATCH_EX and PAGEIOLATCH_SH waits tells me that you don't have enough RAM to keep the nonclustered index which you created in memory when combined with the other data which the system is using. Increasing the memory settings on the SQL Server will help this, provided that you have more memory in the server.

Those two wait types (PAGEIOLATCH_EX and PAGEIOLATCH_SH) tell us that you are waiting for the disk to respond to your request for more data to be loaded. The more data you can keep in memory the less data you'll need to read from the disk.

Related Question