SQL Server Performance Forum – Threads Archive

Best way to be proactive

Hello, I learned an excellent lesson yesterday about the capabilities of the UPDATE STATISTICS sproc (with the help of you guys obviously). What I would like to do is let you know all of the utilities that I am running and get your input, as expert DBA’s, as to what I may be missing. I am trying to be practive and hope to prevent an issue from coming up and then having to solve it after the fact. Right now, I am routinely (automated jobs) doing the following: DB backup
Transaction Log Backup
Optimization Job
Integrity Check
Shrink DB file
Shrink Log file
Update Statistics Does anyone have any input as to what else I can schedule or automate to make sure I prevent some specific future issues? Or, are there other utilities/SP’s/SQL, that I should be running every so often to keep my DB in check? Thanks. Rodney

When you say integrity check I assume you mean DBCC CHECKDB – if not take a good look at it. You may also want to look at DBCC DBREINDEX/DEFRAGINDEX and DBCC SHOWCONTIG – very useful. On large tables you will see a performace if you do not reindex. We reindex all tables at least once a week. If your running Win2003 take a look at performance monitor as it can log records to an sql database. Counters to look at would be Hit Cache Ratio, Disk I/O, paging and CPU. Again we log these details so we can go back and graph them to see overall server performace historically. Very handy for predicting future capacity. If your using Win2k you can still use Performace Counters but you will have to log them to text files and then import them. Hope this helps!

If you run RBCC DBREINDEX and after shink DB all reindex is lost.
So I suggest shrink DB (do you really need?) before DBREINDEX.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

As Luis suggests, shrinking a database on a regular basis is not required. The only exception I can think of this are cases where you run regular jobs to delete data from the database. In addition, shrinking a database may cause your auto growth feature to work unnecessarily, wasting server resources. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Shrinking can also slow down other processes where you need to add data. You can also look at dbcc indexdefrag if you’ve done lots of major data modifications (deletes) and your data is badly fragmented. Indexdefrag is an online operation, so if dbreindex takes too long and keeps your tables locked, its another option. This is a logged task so can fill up your transaction log.

To automate the "DBCC DBREINDEX", how can I do this for all tables in my DB? I just ran the command in quotes against my prod DB and it gave me the following: "An incorrect number of parameters was given to the DBCC statement" I assume this means I did not specify a table or set of tables. I am running all of these processes overnight during slow times, so I don’t think they are affecting performance too much. I will keep the Shrink DB timing in mind if I add the Reindex. THanks. Rodney

Here is using INDEXDEFRAG USE YourDatabase
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ‘Derfagmenting index_id = ‘ + convert(char(3), @indid) + ‘of the ‘
+ rtrim(@TableName) + ‘ table’
IF @indid <> 255 DBCC INDEXDEFRAG (YourDatabase, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

And here’s how to automate it, only reindexing tables which are fragmented… http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=444 Tom Pullen
DBA, Oxfam GB

For some good utility procs and thoughts, you might also want to check out Tara’s blog: http://weblogs.sqlteam.com/tarad MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

If you can schedule the DBCC DBREINDEX job regularly then no need to look for DBCC INDEXDEFRAG, BOL explains Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command’s online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index. And occassionally you can run UPDATE STATS for generic performance on slow running queries. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>