Hi,
I have a database and application that are running slowly at the moment. My investigations so far have led me to HD issues. After an analysis of the data drives (RAID 5 on 5x36Gb drives) it shows that they are 99% fragmented.
Can i run a defrag on this drive?
Will i have to stop the SQL services?
tia
fatherjack
kalpesh.s...@gmail.com Feb 1, 6:50 am show optionsNewsgroups: comp.databases.informixFrom: kalpesh.s...@gmail.com - Find messages by this authorDate: 1 Feb 2005 06:50:21 -0800Local: Tues, Feb 1 2005 6:50 amSubject: how do you know indexes have been fragmentedReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Remove | Report Abusei ran dbcc showcontig on my sql server db and it returned fo*llowingTable: 'Table1' (1621580815); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 4982- Extents Scanned..............................: 628- Extent Switches..............................: 627- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 99.20% [623*:628]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 99.52%- Avg. Bytes Free per Page.....................: 38.3- Avg. Page Density (full).....................: 99.53%Based on searching for info on index defrag it seems my Exte*nt ScanFragmentation percentage is not what it should be (0%) . Is *it trueandif yes how can you be sure that your indexes have been fragm*ented.If indexes are really fragmented what is the best way withou*treindexing(or is that the best way) to defrag the indexes.Thank you Kalpesh
View 3 Replies View Related
Last week i found on my database that almost all indexes had more than 70% of fragmentation. I rebuilt the indexes to fix the "problem" but today i found out that in one particular table (one with little more than 1 million records") the indexes are again 99,80% fragmented.
Did I do somethin wrong? If you ask me if there are a lot of transactions running over that table, the answer is NO. There is one procces that could append (insert) between 500 and 1000 records but this happens just once a month.
Have a 1TB of heaped tables being used 24/7 and performance is degrading over time, and the vendors dynamic SQL won't include clustered indexes! (and they won't let me add them)
I can reorganise the heap with an ALTER TABLE statement, add a column,
clustered index, drop column etc. However this is intrusive and I would require an entire day to perform this piece-meal, and a blanket script for 2000+ tables would kill performance all together.
Would a back-up, then remove the 1TB DB followed by a restore placing the data back onto disk unfragmented. (with LS this would only require a 3 hour down-time window) In theory it should work?
I just did index defragmentation for some databases include MSDB . I notice there are 3 indexes from MSDB database that fragmented quickly ( I did rebuild last nite at 10 PM - > fragmentation level becomes zero but today at 9 am it become 80 % ).The indexes are backupsetuuid, backup media family uuid, backupmediasetuuid. I am thinking to set the fill factor for those indexes = 80 respectively.
View 6 Replies View RelatedHi everyone,
If I have a table with some indexes on the foriegn keys and these indexes are heavily fragmented (80%+), is it normal for queries to return incorrect results?
For example if I had a table called Customer( CustID, Name) and Orders (OrderID, CustID, Product, Date).
Lets say I have a non clustered index on CustID in Orders table, and the clustered indexes are Customer.CustID and Orders.OrderID
If the non clusterd index on Orders.CustID becomes heavily fragmented and I am querying the Orders table with TSQL "SELECT * FROM Orders where CustID = @CustID" I sometimes get missing data or incorrect results. In one case all orders for a particular year were missing, but if I queried using OderID they were returned. Rebuilding the index fixed the problem.
I know the index should be rebuilt or reorganized depending on the fragmentation but if one happened to become this fragmented should it start returning incorrect data?
- Using SQL Server Express 2005