Scenario: A single version 12 server is running 100 separate databases, all started at the same time (or as fast as 100 START DATABASE statements can run), and all lightly loaded (let's say "completely idle").

Idle databases take checkpoints every 20 minutes or so, and when the 20 minutes are up all heck breaks loose, performance-wise, as all 100 databases take their checkpoints one after the other.

If something ELSE is running, say on database 101, or even some other non-database process, you can just forget latency, throughput, or whatever other measure of performance you're interested in... if the computer isn't a super-high-performer, it will be crushed for maybe a minute or two or more.

So... all I want is some method to get those 100 databases on separate checkpoint schedules, something cheaper than "upgrade the hardware".

Any ideas?

asked 03 Jul '12, 05:31

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%


And I guess the idea should be a better approach than

  1. setting the checkpoint_time option to (group-wise) different values for each database or
  2. running explicit CHECKPOINT statements within each database at different times (under the expectation that this will have an influence on the next "automatic" checkpoint), say by an event with a schedule with a start time (or frequency) calculated by the database start time and the database number?
(03 Jul '12, 05:41) Volker Barth
Replies hidden

BTW, the forum database server seems to checkpoint intensively, too - or why is the forum site slow again?

(03 Jul '12, 05:42) Volker Barth

Wide variations in response time for ALL processes is a way of life... what, you're not used to it yet?

As far as this forum is concerned, if you want to take a break from productive work, but you like gambling, try the "Search" button: sometimes it takes a few seconds (too long but not stupid long) to over 30 seconds (that's just craaaazy)... with the two experiences back to back, like a slot machine.

Anyway, based on years of experience, performance is not going to become stably adequate any time soon. Folks do respond if you complain loudly enough, but the fixes never stick for very long, and you just end up getting a reputation as a whiner... don't let that happen to you!

(03 Jul '12, 07:38) Breck Carter

Volker alluded to a solution in his comment to the question.... but here is the idea in more detail:

Create a database start event in each of the databases that delays a variable amount of time and then executes an explicit checkpoint. The idea is to spread the checkpoints out evenly across the ~20 minute regular checkpointing interval.

CREATE EVENT randomize_first_checkpoint
   TYPE DatabaseStart
      declare @num  int;    -- total number of databases running on server
      declare @ord  int;    -- ordinal of this database within list of databases
      declare @sec  int;
      declare @min  int;

      -- wait some time to let all databases to start (from command line!)
      WAITFOR DELAY '0:0:10';

      -- figure out how many databases are running on the server?
      SELECT number as num, db_name(number) as name, number(*) as ord
        into #temp
        from sa_db_list()
       order by num;
      SET @num = ( select count(*) from #temp );

      -- ... and what position is this database on the server?
      SET @ord = ( select ord from #temp where name = db_name() );

      -- assume checkpoints occur at 20 minute regular intervals (i.e. 20*60 seconds)
      -- figure out how long to wait before we force a checkpoint on this database
      set @sec  = ( 20*60 )*( @ord - 1 ) / @num;
      set @min  = @sec / 60;
      set @sec  = @sec - 60*@min;

      execute immediate 'WAITFOR DELAY ''0:' || @min || ':' || @sec || '''';

You may want to change the initial delay in the above procedure and/or change the '20' minutes in the above computation (if you change the checkpoint interval from the default).

permanent link

answered 03 Jul '12, 08:40

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

edited 03 Jul '12, 09:24

Yes, that's the detailed solution I had been too lazy to work out:)

(03 Jul '12, 09:21) Volker Barth

In case all those databases have one common DBA account (or you know all of their DBA accounts), you could also use one particular database as "checkpoint controller" and use remote database access to FORWARD CHECKPOINT statements to each database in a round-robin fashion.

That would prevent the need to alter each and every would just need a remote server object - and possibly you could use one (or a few) remote servers and adapt them via ALTER SERVER USING ... on the fly:)

permanent link

answered 03 Jul '12, 09:20

Volker%20Barth's gravatar image

Volker Barth
accept rate: 33%

Your answer
toggle preview

Follow this question

By Email:




Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • numbered list: 1. Foo 2. Bar
  • basic HTML tags are also supported

Question tags:


question asked: 03 Jul '12, 05:31

question was seen: 1,550 times

last updated: 03 Jul '12, 09:24