[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

Checkpoint time and periodicity

Checkpoints on one of the DBs in our system happen about every 150 minutes and take up to 30 sec and plenty resources with them. Also the checkpoint time seem to grow with time. What can I do from the software perspective to make checkpoints faster and less resource-intensive (please - no hardware solutions, this is beyond my reach)? Any maintenance procedures like defragmenting DB or drives?
This is the transactional database where only "recent" (newer then several days) records are kept, the older records are deleted daily so I can imagine that the database itself may become pretty fragmented.

Generally - are there any benchmarks on how often the checkpoints should happen and how long they should take depending on the system load and hardware configuration?
0
authentify
Asked:
authentify
  • 3
  • 2
1 Solution
 
arbertCommented:
If you go into Enterprise Manger, server properties,  on the database settings tab the "Recovery Interval (min)" affects how often SQL Server takes a checkpoint--you can decrease this value.  Don't make it too low, or your server will be busy all the time.  Also, if it's just one particular database, you could also create a scheduled job that issues a CHECKPOINT every now and then.

You can defrag the drives, defrag the indexes if you have lots of updates, inserts, and deletes.  make sure the log file is on a different set of drives than the data files....

Are you running any hardware RAID?
0
 
authentifyAuthor Commented:
The value is 0 currently which means "automatic" as far I can understand. Then - the dependancy between the "Recovery Interval (min)" and an actual time the checkpoint takes (and interval between checkpoints) is all hidden.

And yes - this is RAID 5 (I know, this is bad) and this is cluster environment so write buffering is off.
0
 
arbertCommented:
"Then - the dependancy between the "Recovery Interval (min)" and an actual time the checkpoint takes (and interval between checkpoints) is all hidden."

Right, the lower the "Minutes to recovery", the quicker each checkpoint will be....

What about the location of the log and data files?  Are they on the saim raid group?
0
 
authentifyAuthor Commented:
No - they are separated. Does index defrag help in this situation? And - is there such a thing as data tables fragmentation and if so - how can I defrag them?
0
 
arbertCommented:
So are the logs also raid 5.....Index Fragmentation (as well as disk) will affect DB performance quite a bit.  Tables do become fragmented, but you can defrag them by defragging the clustered index on the table (if you don't have a clustered index, you should).

This script will defrag all the indexes on a database (straight from books online):

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL
 
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 1.0
 
-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
 
-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT null,
   CountPages INT null,
   CountRows INT null,
   MinRecSize INT null,
   MaxRecSize INT null,
   AvgRecSize INT null,
   ForRecCount INT null,
   Extents INT null,
   ExtentSwitches INT null,
   AvgFreeBytes INT null,
   AvgPageDensity INT null,
   ScanDensity DECIMAL null,
   BestCount INT null,
   ActualCount INT null,
   LogicalFrag DECIMAL null ,
   ExtentFrag DECIMAL null)
 
-- Open the cursor
OPEN tables
 
-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename
 
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END
 
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
 
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
 
-- Open the cursor
OPEN indexes
 
-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag
 
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)
 
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END
 
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
 
-- Delete the temporary table
DROP TABLE #fraglist
GO
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now