Solved

Checkpoint time and periodicity

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

12 Experts available now in Live!

Get 1:1 Help Now