I have been testing methods to maintain indexes in a SQL Server 2005 database which has been migrated from SQL Server 2000. The compatibility level is still set to 80. I used the query below to inspect the degree of fragmentation amongst other things.
SELECT a.index_id
, name
, database_id
, avg_fragmentation_in_percent
,index_type_desc
,fragment_count
,page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Some of the indexes in the database had a high degree of fragmentation based on the avg_fragmentation_in_percent value. I tried drop+create, rebuild and reorganise commands on those indexes. Predictably, drop + create was the most effective, but even that did not always reduce fragmentation much. Sometimes the fragmentation was the same no matter what method I used. Other times drop+create helped, rebuild made it worse.
Would someone please let me know which is the better way to reduce fragmentation on Windows:NT drive? dbcc indexdefrag or dbcc reindex or dropping and recreating indexes through SQL?
i have inherited a database running on sql7 sp2. a lot of the tables do not have a clustered index. some of these tables are highly fragmented. below is a sample of the dbcc showcontig output
this table has 5 non-null char columns whose total length is 43 chars and the table occupies about 900 mb for only about 670,000 rows! select on the table sucks!
since there is no clustered index i have to rebuild the table to remove the fragmentation. is there a way to reorganize the table without recreating it?
I have a problem with defragmenting the tables. When i ran dbcc showcontig for the tables, it shows 94%, 50%, 33% fragmentations . I have rebuild indexes on all those tables and ran dbcc showcontig again but still it shows the same % of fragmentations. Is there any way to remove fragmentation on all the tables completely. Any help would be appreciated.
What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?
If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?
How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.
What functions of tools do you use for managing index fragmentation?
DBCC?
I am working through MS Press SQL 2005 book and it mentions the sys.dm_db_index_physical_stats function? It then give an example of code which is very involved.
before rebuild index --------------------- tablenameIndexname avg_fragmentation_in_percent Payoff_QuotePK_Payoff_QuoteDetail 83.3333333333333 ALTER INDEX ALL ON Payoff_Quote REBUILD after issue the above statement result remain same.
I have a nice script that will look at the index fragmentation by using the DMV (sys.dm_db_index_physical_stats) and if its above a specificed threshhold, it will rebuild or reorg the index.
What I have noticed is that even after reorging and/or rebuilding the index, the fragmentation percent in the DMV does not change for some indexes. Other indexes are updated just fine.
Is this a result of updating statistics? Why would the fragmentation change for some indexes and not others? Why does it seem no matter how much rebuilding or reorging is done, the fragmentation percent for some indexes does not change?
We backup a lot of SQL databases. The db admin uses SQL to dump the databases to a *.bak file on a network share, then we pick them up to tape.
The problem is that for some reason, the backup files are MASSIVELY fragmented, which kills our backup speed to tape (via Netbackup Enterprise). If I defragment the drive (via the built-in tool on Win2K3 or PerfectDisk 8) our speed to tape more than doubles (can go from 16MB/sec to 36MB/sec). However, after the next SQL backup, the drive is completley fragmented once again (around 70%).
Is there any way to improve how these files are written to disk (to keep them relatively in one piece)? For some reason it appears this is a bigger issue with the SQL backup files than any other file type. Thanks.
How do you defragement SQL database data files? Running the DBCC DBREINDEX , DBCC INDEXDEFRAG, does that minimize fragmentations of the tables and the indexes files or is it just the index files? Thanks in advance
We have nightly job running to reorg all in one of our prod database. But the index on one of the table fragmenting quickly by the morning showing 90% fragmentation.
Abe writes "i am using SQL server 2000, when i tried to back up the database in SQL server, the backup began failing coz SQL Server could no longer allocate 964kb of contiguous blocks. (the min needed is 1MB)
What causes the memory or blockes to be fragmented?
How do you solve this issue other than rebooting the server?
How do you prevent this from happening or monitor this issue?
I have a table with 50% Logical Scan Fragmentation. [ according to Dbcc Showcontig (myTable) ] Why after running DBCC INDEXDEFRAG (myDB,myTable) does it still sit at 50%. Why isn't it lower?
I've inherited a SQL 2005 Server with a major problem. It's been badly admin'd for ages.
It's got a 250gb disk with 3% space free. The disk is 93% fragmented. There is a sql data file of 231gb on that volume that i'm trying to shrink as it has 92gb of free space on it apparently. It won't shrink because the fragmentation is too bad - it just hangs for 2 days then times out. I've tried shrinking the files and database to no avail. I can't defrag as it just says it's finished after 2 minutes and doesn't do anything.
can you defrag system tables. They appear in my dbcc showcontigreport. Some are worse than other but if it where a user table Iwould defrag it. However the reindex commands doesnt work on systemtables.... Any ideas.Thanks Matt.
Hi, It is natural that index gets fragment overtime. But I would like to know how you do the reindexing or defragment when your database table is big and you cannot afford the time to rebuild them. Thanks.
Hi All, I want to check the fragmentation for a table. But I do not have permissions to run DBCC commands on that server. Do we have any other method to identify the health of the table?
I have several databases in which our indexes have not been rebuilt/reorganized. I have worked primarily on SQL 2005 but with 2000 I am not familiar with Logical vs Extent fragmentation. And on the 2000 server there is high Logical Fragmentation (1000+%). With me rebuilding the indexes on the 2000 server databases, will this reduce performance or halt functionality of the servers while this process is running???
I try to find the percentage of fragmentation in the perticular table using the following table. For some tables it is getting the results but for some database tables it is saying the error in qurey even if I use same query. Is there any reason for that. Or is there any other way to find out the fragmentation of table. any help would be fine. Thanks!
I have a process(Peoplesoft) in SQL Server which takes long time to finish. I am looking for is there any way to find out fragmentation of the table to defagment it or is there any way to allocate the size for particular table. Couple of users are running the process at same time but as SQL Server has table/page locks it locks and releases after the job is done. Can i make it row level lock by executing sp_tableoption procedure. I would appreciate for all your suggeations.
Can anyone let me now why the extent scan fragmentation is very high. I do have a clustered index on this table . The fill factor is 0 and this table has high inserts as it is used to maintain history. Rebuilding the Indexes did not help.
DBCC SHOWCONTIG scanning 'ACCOUNTS' table... Table: 'ACCOUNTS'(1061578820); index ID: 1, database ID: 5 TABLE level scan performed. - Pages Scanned................................: 728157 - Extents Scanned..............................: 91759 - Extent Switches..............................: 93305 - Avg. Pages per Extent........................: 7.9 - Scan Density .......: 97.55% [91020:93306] - Logical Scan Fragmentation ..................: 0.33% - Extent Scan Fragmentation ...................: 99.99% - Avg. Bytes Free per Page.....................: 76.6 - Avg. Page Density (full).....................: 99.05% DBCC execution completed. If DBCC printed error messages, contact your system administrator
Thanks in advance, Shades[B]Extent Scan Fragmentation
I have rebuilded indexes and some of the indexes still showing fragmentation percentage as 99%. I have cross checked page count for these indexes and they are more than 10000 pages.As i am aware if the page count is less we can ignore this percentage. But in my case the page count is 10000.
Currently managing a VLDB which is heavily fragmented. The applications characteristics are heavy INSERTS, UPDATES and DELETES and against my recommendations the majority of the large tables are HEAPS or have inadequate non-clustered indexes. The code is dynamic SQL, the tables dynamic and the vendor's understanding of SQL is poor which hampers my progress here.
Both the OS defrag GUI and sys.dm_db_index_physical_stats show huge amounts of fragmentation, some tables are around 80%. Note: (Indexreorg jobs are in place where indexes are present)
The problem I have is convincing the windows engineers whom contol the budget that deploying a defrag tool such as Idera's Defrag Manager will actually be of any use. They keep asking whether the app will actually will get inside the DB and I'm curious whether there will be any benefit on the HEAP tables should the vendor not make changes to their code.
Is there any conclusive evidence I can provide these Window guys to quantify my defrag expenditure request? Any help would be appreciated
I need to manage the problem of negative performance implications when I fragment a 1TB+ DB. I want to perform Index Reorganization if fragmentation is no higher than 30%, and Index Rebuild if the fragmentation exceeds 30%.
Firstly can anyone recommend a script which uses sys.dm_db_index_physical_stats system to ascertain the fragmentation level. Secondly, is there a technique I can employ to prevent the ONLINE operation completely killing performance on 27/4 production system?
