Software

Ask something
Discussion Locked

dbcc logical scan

0 helpful
6 replies

by crossleyc

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a database on sql server 2000 sp4. Not long ago I had something wrong with my transaction log and the DB went into "suspect mode". Microsoft showed me basically how to destroy the transaction log and rebuild it. I didnt loose any data, everything had already been commited. During this process I installed all four service packs for sql, since this server had not been updated since it went live. I also moved the transaction log to a SANS drive for space. I've experianced query slow downs since all this was done and I've rebuilt my indexes using a third party program that uses this DB. The only thing I've noticed thats different for this sql server is the "dbcc logical scan" stays at 100% and drops for only a second. I've read where this is symbolic of the read/write activity for the HD. Can anyone help me get to the bottom of whats up here?

6 replies

by mirrormirror

You are correct that this is indicative of I/O. If I were you I would check my physical fragmentation of all drives, do a DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES to check for fragmentation there and do a DBCC CHECKDB. Separating the data and log file is generally a good thing but if the new drive is slow then a performance hit happens. You might run profiler and check to see what statements are being passed in. Statements like DBCC UPDATEUSEAGE can be very I/O intense. Queries that are doing full table scans or lots of inserts and updates will be very intense. Look for statements with high reads and writes. Let me know what you find.

by crossleyc

Thanks for responding. I've already run profiler and I could not find any dbcc statements. The database was on the sans drive with the log file. I recently moved it off back to a local drive when I thought that being on fiber might be a performance issue. What I dont understand is why this is an issue when we're running the same queries now that we were before destroying
the transaction log? Thanks again for the leads and I'll get back with the results.

by crossleyc

When I'm looking at the results of this command (logical fragmentation & extent fragmentation) what will idicate fragmantation. Also what would I do about it?

by mirrormirror

Scan Density - [Best Count: Actual Count] - Best count is the ideal number of extent changes if everything is contiguously linked. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists.

The Avg. Bytes free per page and Avg. Page density (full) statistic in the result set give an indication of the fullness of index pages. The Avg. Bytes free per page figure should be low and the Avg. Page density (full) figure should be high. Dropping and recreating a clustered index, with the FILLFACTOR option specified, can improve these statistics. Also, the DBCC INDEXDEFRAG command will compact an index, taking into account its FILLFACTOR, which will improve these statistics.

Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). The DBCC INDEXDEFRAG or DBCC DBREINDEX commands are methods of reducing fragmentation and can be used to reduce these values.

There is a script in BOL that will defrag indexes and can be run as a scheduled job. It is part of the DBCC SHOWCONTIG documentation.

I have special jobs that defrag large and heavily used tables nightly and another job that defrags the entire database every weekend. It all depends on how the tables are used and how frequently a table becomes fragmented. I also update statistics in a job several times a day.

by crossleyc

Well its clear that most of my heavily queried tables are fragmented. Its just funny to me as to why this wasnt an issue before my suspect mode incident. Tonight after everyone has left, i'll go into single user so I can focus more resources on this task. I've tried to run a defrag script a few times now, but it just run for a minute then I get a "resource low. some results were dropped". I may just have to drop and rebuild them. Thanks for your help, I'll try this tonight and let ya know.

This discussion is closed to new replies.