July 21, 2011 at 7:51 am
Hello,
I have a 310 GB in SQL Server 2000.(Prod DB)
I want have restored the DB in SQL Server 2008 in Compatibility Level 100.
The production Database is highly fragmented and I need to indentify the fragmented indexes and the fragmentation level.
Will the restored database in SQL 2008 will have same fragmentation level. Will it be possible to find the fragmented indexes based on analysis on the 2008 database?
July 21, 2011 at 7:54 am
A restore recreates the DB exactly as it was at time of backup. If an index is fragmented at time of backup, it'll be fragmented in the restored database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 7:57 am
And to fix defragmentation : http://sqlfool.com/2010/04/index-defrag-script-v4-0/
July 21, 2011 at 8:07 am
Thanks for your quick reply,
I have used the following query and pulled the result from SQL Server 2008 DB
select
--s.name AS [Database],
t.name AS [Table], i.name AS [Index],
c.name AS [Column], b.avg_fragmentation_in_percent,b.avg_fragment_size_in_pages,
Count (*)
FROM
sys.dm_db_index_physical_stats (10, NULL, NULL , NULL, 'Detailed') b
INNER JOIN sys.tables t
ON b.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = t.object_id inner join sys.columns c on c.object_id = t.object_id and ic.column_id = c.column_id where i.index_id > 0 and i.type in (1, 2)
and b.avg_fragmentation_in_percent > 5
When I check for avg_fragmentation_in_percent for 'Table1' I get teh results as:
90.5
97.5
93.5
97.3
and so on
here are outputs for same table from SQL 2000:
DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (571149080); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 260553
- Extents Scanned..............................: 32598
- Extent Switches..............................: 32597
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.91% [32570:32598]
- Extent Scan Fragmentation ...................: 3.00%
- Avg. Bytes Free per Page.....................: 1266.8
- Avg. Page Density (full).....................: 84.35%
DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (571149080); index ID: 2, database ID: 11
LEAF level scan performed.
- Pages Scanned................................: 10445
- Extents Scanned..............................: 1328
- Extent Switches..............................: 9549
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 13.68% [1306:9550]
- Logical Scan Fragmentation ..................: 45.96%
- Extent Scan Fragmentation ...................: 31.02%
- Avg. Bytes Free per Page.....................: 3070.6
- Avg. Page Density (full).....................: 62.06%
DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (571149080); index ID: 3, database ID: 11
LEAF level scan performed.
- Pages Scanned................................: 10689
- Extents Scanned..............................: 1355
- Extent Switches..............................: 10128
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 13.20% [1337:10129]
- Logical Scan Fragmentation ..................: 47.29%
- Extent Scan Fragmentation ...................: 26.13%
- Avg. Bytes Free per Page.....................: 3185.3
- Avg. Page Density (full).....................: 60.65%
DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (571149080); index ID: 4, database ID: 11
LEAF level scan performed.
- Pages Scanned................................: 9895
- Extents Scanned..............................: 1256
- Extent Switches..............................: 9093
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 13.60% [1237:9094]
- Logical Scan Fragmentation ..................: 45.19%
- Extent Scan Fragmentation ...................: 31.37%
- Avg. Bytes Free per Page.....................: 2791.3
- Avg. Page Density (full).....................: 65.51%
Can you please let me know why is there so much of difference in data?
Statistics for Prod has not been updated for quite a long time.
Can this be a reason for such enormous difference?
July 21, 2011 at 8:12 am
No idea.
Either way you need to fix it and the script I posted will do just that, and very well.
I usually don't bother with previous versions info like that when it'll never be usefull again for that DB. You might want to choose to do the same. 😉
Reindex with the script and then update all stats will fullscan. CHECKDB is also a very good idea.
July 21, 2011 at 8:21 am
Thanks Ninja.
But the stakeholders are only interested about the only the fragmented tables.
So I was trying to find out a shortcut of identifying the tables using DMVs since I have already restored the DB recently in test environment.
😉
I also thought the DB will behave in the same manner as Gail said.
Thanks all for your inputs.
Please post if you have any further information.
July 21, 2011 at 8:29 am
The restored DB is exactly the same as the old DB. Restore does not change table fragmentation in any way
What's changed is how fragmentation is calculated. SQL 2000 and SQL 2008 use slightly different definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 8:43 am
Knowledge Hunter (7/21/2011)
Thanks Ninja.But the stakeholders are only interested about the only the fragmented tables.
So I was trying to find out a shortcut of identifying the tables using DMVs since I have already restored the DB recently in test environment.
😉
I also thought the DB will behave in the same manner as Gail said.
Thanks all for your inputs.
Please post if you have any further information.
Please read the article & script I posted. This is the best tool for that job.
July 21, 2011 at 11:26 am
Thanks Gail and Ninja.
The information is really helpful.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply