3

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
  • This question has an excellent answer, and contains links to many other good answers to the server memory configuration debate. dba.stackexchange.com/questions/47205/… – Max Vernon Jun 18 '14 at 19:44
  • @MaxVernon I actually read that because it appeared on the sidebar while creating this question! :) I even linked the Glenn Berry article on how to choose the maximum memory configuration based on available memory in a comment to mrdenny's answer. – Zack Jun 18 '14 at 19:47
  • Excellent. Just making sure! Have a +1 for a great, detailed question. – Max Vernon Jun 18 '14 at 19:54
1

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.

  • Our clients machines vary anywhere from 8-32 GB of RAM, so to set the appropriate amount of maximum server memory, I will probably go with the suggestion found here sqlservercentral.com/blogs/glennberry/2009/10/29/… and leave 1500 - 2000 MB for the OS and other applications. – Zack Jun 18 '14 at 18:55
  • 1
    Leaving 2 Gigs of RAM is usually fine, assuming that there's nothing else running on the server. If you are running a website, applications, etc. you'll need to reduce max server memory so that those other applications have memory available without battling SQL Server for it. – mrdenny Jun 18 '14 at 18:57
  • On some of the machines, they are hosting a web application in IIS, but during the backup process, we disable access to it, since it uses the database we are working on. I think we might try to set the maximum memory close to the total available while we run the backup, and then adjust it according to whether or not the server is also used for hosting a web application. – Zack Jun 18 '14 at 18:59
  • Also, while running the backup procedure on the 2.5GB test database, I saw that there were PAGEIOLATCH_EX waits while shrinking the database, even when I had the memory set to max, with 16 GB available. I didn't see any during the delete on my dev machine, but on the clients machines, there are latch waits during the delete, and during the shrink. The waits on my dev machine were not very long, maybe 3-5 seconds at most, is this normal to see? I'm using activity monitor to see the waits on the dev machine and client machines. – Zack Jun 18 '14 at 19:09
  • Seeing waits on disk when shrinking is normal as there's a massive amount of data movement that needs to happen when the shrink runs. Activity monitor is probably fine, though I would recommend looking at sp_whoisactive (you'll need to download it). It makes looking at waits for processes a lot easier. – mrdenny Jun 18 '14 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.