Database fragmentation

Hello,
How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.
Thanks for helpYour database is 60% fragmented. What does it say? What do you do?|||I just found my answer:

USE pubs
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES|||Fragmentation may be on the file system level, which may be corrected by detaching the database, defragging the disk, reattaching the db, and doing the db level defrag which you just discovered.|||The file fragmentation on the disk is invisible to SQL Server... It sees a file as a contiguous stream of 8 Kb pages, but it is quite ignorant of how those pages are actually laid out on the disk(s).

rdjabarov brings up a good point though, in that even a table or database that is contiguous from SQL Server's perspective might be on a Windoze file that has grown many times, so the head still has to hop all over creation to retrieve data. It is a good idea to periodically detach the database and defrag the Windoze disk after the database file(s) grow.

-PatP|||So what defragmentation tool is available that is able to defrag a database file considering it's internal structure?

I've seen several implementations of defragtools; one would only move about the occupied sectors, making it easier for the OS the find free space. Others would do a file approach, selecting the sectors involved and move those around. I've had a defrag tool that did a file approach and moved the first sectors of a file to the beginning, working to the end. It took ages, _and_ the tool somehow mixed up sectors, rendering some files useless.

With database files it's tricky: indexes, tables, procedures, views.. where to store them? Would a tool consider? Does it speed up performance or does it go down the drain?

I have to admit it's been a long time since I last looked at defragmentation tools; so I don't know how advanced they are these days and how well they work. SQLServer already claims a contiguos block of data when a db is created, I think adding new parts works the same. If a growth of a db is the only change to the disk, and sqlserver claims the new block right after the db-block, what are the odds a defrag tool doesn't leave it alone and make it worse?

I'm not an expert on defragmentation, I'm also not an expert on sqlserver, I know from experience a defrag helps when a lot of small files are scattered around the disk, but I wonder the effect on db-files.|||There are two kinds of fragmentation that apply here.

For internal fragementation, the only fix that I know of is to rebuild the index or indicies affected. DBCC REINDEX works well for this.

For file fragementation, you can either detach the database or stop the SQL Server and use the build in defragmentation tool, or you can buy Diskeeper.

-PatP|||But does the command

DBCC SHRINKDATABASE (pubs,NOTRUNCATE)

move pages in the beginning of the database file ? Can this contribute to decrease internal fragmentation ?|||No, DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp) doesn't move any pages. Even if it did move them, it would change the fragmentation, but rarely decrease it.

-PatP|||So, I don't understand the aim of this command (with NOTRUNCATE option).|||NOTRUNCATE will not release freed space back to OS.|||I'm sorry, I didn't catch Kuzco's reply earlier. I was thinking in terms of the TRUNCATEONLY option when I wrote that the DBCC SHRINKDATABASE didn't move pages. The NOTRUNCATE option can move pages, but it doesn't always do what you'd expect it to... See BOL on DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp) or better yet Inside SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=Xz6NhUAA2Z&isbn=0735609985&itm=1) for more detailed descriptions.

-PatP|||Ok, thanks for your informations