Solved

Checkpoint time and periodicity

Posted on 2004-04-21
5
453 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
  • 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is format f12.8 for a CSV file 6 41
SQL Server - Slabs 9 38
TSQL - IF ELSE? 3 29
SQL Query with WHERE clause for an entire day 5 20
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now