Solved

Checkpoint time and periodicity

Posted on 2004-04-21
5
462 Views
Last Modified: 2011-09-20
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
Comment
Question by:authentify
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10882086
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
 
LVL 1

Author Comment

by:authentify
ID: 10882734
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
 
LVL 34

Expert Comment

by:arbert
ID: 10883026
"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
 
LVL 1

Author Comment

by:authentify
ID: 10883064
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
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 10883145
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question