In this entry I show how you can very easily shoot yourself in the foot if you don’t understand how SHRINKFILE / SHRINKDATABASE and DBCC DBREINDEX or CREATE INDEX with DROP_EXISTING work. Running DBREINDEX to remove fragmentation from 99% down to 98% (not good eh!).
Let’s set out some of the tools I’m using here…
DBCC SHOWCONTIG
Look at books online, basically this shows us the state of the table in terms of space usage and fragmentation (extent fragmentation within the database and logical fragmentation within the table or index itself).
DBCC EXTENTINFO
Check out http://support.microsoft.com/kb/324432/.
This useful statement shows us extent usage information for the database, we can capture this information to a table for analysis.
DBCC DBREINDEX
Look at books online, basically this rebuilds the index which will effect the ‘extent fragmentation’ and ‘logical fragmentation’ reported by DBCC SHOWCONTIG.
DBCC INDEXDEFRAG
Look at books online, whereas DBCC DBREINDEX and CREATE INDEX with DROP_EXISTING physically create a new copy of the index or table and then make that live, INDEXDEFRAG just poodles away fixing the logical fragmentation, it doesn’t assign any new extents so no data is copied, its only moved and that move uses very small transactions hence it can be run throughout the online day.
DBCC SHRINKFILE
Look at books online, essentially this moves data from the end of the database filling up all the free extents at the beginning of the database so it can then physically reduce the size of the file, there are options to stop it doing the data move – check bol.
Ok, now that’s out of the way what do I want to get across?
Essentially I want people to be aware of what happens when you do a DBCC SHRINKFILE and DBCC SHRINKDATABASE, you’d be surprised how often the mistake is made where somebody creates a near contiguous table/index using DBREINDEX / CREATE INDEX with DROP_EXISTING only to undo it all by running SHRINKFILE / SHRINKDATABASE straight after.
Create out test database, notice I’m using a separate file for this test, it makes life so much easier on the demo; there are benefits for using multiple files and multiple file groups and this contiguous data maintenance is one of them!
USE [master]
GO
CREATE DATABASE [testfile] ON PRIMARY
( NAME = N'testfile', FILENAME = N'E:\SQL2005\testfile.mdf' , SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FileTest]
( NAME = N'testfile1', FILENAME = N'E:\SQL2005\testfile1.ndf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testfile_log', FILENAME = N'E:\SQL2005\testfile_log.LDF' , SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'testfile', @new_cmptlevel=90
go
ALTER DATABASE testfile
MODIFY FILEGROUP FileTest DEFAULT;
go
Let’s create and populate our initial sample data set; notice how I’m deliberately inserting into the tables together so as to demonstrate how things become fragmented.
use testfile
go
create table test_table_1 (
id int not null identity unique clustered,
somedata char(4000) not null
)
create table test_table_2 (
id int not null identity unique clustered,
somedata char(4000) not null
)
go
set nocount on
declare @i int
set @i = 1
while @i <= 10000
begin
insert test_table_1 ( somedata ) values( replicate( 'a', 4000 ) )
insert test_table_2 ( somedata ) values( replicate( 'a', 4000 ) )
set @i = @i + 1
end
go
Let’s create a temporary table to hold the results from running EXTENTINFO, the structure of the table depends on which version of SQL Server you are running.
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 9
begin
create table #extentinfo
(
[file_id] smallint,
page_id int,
pg_alloc int,
ext_size tinyint,
obj_id int,
index_id tinyint,
partition_number int,
partition_id bigint,
iam_chain_type varchar(100),
pfs_bytes varbinary(10)
)
end
go
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8
begin
create table #extentinfo
(
[file_id] smallint,
page_id int,
pg_alloc int,
ext_size tinyint,
obj_id int,
index_id tinyint,
pfs_bytes varbinary(10)
)
end
go
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7
begin
create table #extentinfo
(
[file_id] smallint,
page_id int,
pg_alloc int,
ext_size tinyint,
obj_id int,
index_id tinyint,
pfs_bytes varbinary(10),
avg_used tinyint
)
end
go
Now our environment is complete lets get on and see what’s happening.
First, lets look at the outcome of the INSERT’s, notice how the object’s are interleaved through the database thereby causing ‘Extent Scan Fragmentation’.
insert #extentinfo
exec( '
dbcc extentinfo ( 0 ) with tableresults
' )
go
-- Take a look at the object interleaving
select *
from #extentinfo
where file_id = 3
order by page_id
|
Start Page |
ObjectId |
|
8 |
2073058421 |
|
10 |
2105058535 |
|
12 |
2073058421 |
|
13 |
2073058421 |
|
14 |
2105058535 |
|
15 |
2105058535 |
|
16 |
2073058421 |
|
17 |
2105058535 |
|
18 |
2073058421 |
Let’s take a look at DBCC SHOWCONTIG to see what it reports.
-- Note extent scan fragmentation
dbcc showcontig( test_table_1 )
dbcc showcontig( test_table_2 )
go
DBCC SHOWCONTIG scanning 'test_table_1' table...
Table: 'test_table_1' (2073058421); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 5000
- Extents Scanned..............................: 630
- Extent Switches..............................: 629
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.21% [625:630]
- Logical Scan Fragmentation ..................: 0.48%
- Extent Scan Fragmentation ...................: 99.37%
- Avg. Bytes Free per Page.....................: 70.0
- Avg. Page Density (full).....................: 99.14%
DBCC SHOWCONTIG scanning 'test_table_2' table...
Table: 'test_table_2' (2105058535); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 5000
- Extents Scanned..............................: 630
- Extent Switches..............................: 629
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.21% [625:630]
- Logical Scan Fragmentation ..................: 0.48%
- Extent Scan Fragmentation ...................: 99.37%
- Avg. Bytes Free per Page.....................: 70.0
- Avg. Page Density (full).....................: 99.14%
Look at that ‘Extent Scan Fragmentation’ – yikes! That’s because the objects are really badly interleaved – remind you of a production system, yep – that’s how it happens!
Try and get rid of the fragmentation:
dbcc indexdefrag( 0, test_table_1 )
go
This does nothing, INDEXDEFRAG only tackles and fixes the ‘Logical Scan Fragmentation’ which for both objects is fine.
Let’s reindex the table to get it back contiguous..
dbcc dbreindex( test_table_1 )
go
DBCC SHOWCONTIG scanning 'test_table_1' table...
Table: 'test_table_1' (2073058421); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 5001
- Extents Scanned..............................: 626
- Extent Switches..............................: 625
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [626:626]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 0.64%
- Avg. Bytes Free per Page.....................: 71.6
- Avg. Page Density (full).....................: 99.12%
Notice how ‘Extent Scan Fragmentation’ is now fine, but what has happened in our database file?
truncate table #extentinfo
go
insert #extentinfo
exec( '
dbcc extentinfo ( 0 ) with tableresults
' )
select *
from #extentinfo
where file_id = 3
order by page_id
go
Lovely! The database size has been increased and the object created in the new space, well, it’s lovely for my sample but what if you are dealing with GBytes? Also, we have a ton of free extents at the beginning of our database where the object resided before being defragged (re-indexed).
What does SQL Server do with this freespace? Well, as you guessed it uses it! Let’s create another object and populate that.
create table test_table_3 (
id int not null identity unique clustered,
somedata char(4000) not null
)
go
set nocount on
declare @i int
set @i = 1
while @i <= 10000
begin
insert test_table_3 ( somedata ) values( replicate( 'a', 4000 ) )
set @i = @i + 1
end
go
Check where the data went…
truncate table #extentinfo
go
insert #extentinfo
exec( '
dbcc extentinfo ( 0 ) with tableresults
' )
select *
from #extentinfo
where file_id = 3
order by page_id
go
As you can see we have interleaved extents again; SQL Server has used those extents that we made available as part of the DBREINDEX, let’s drop the table and check what happens.
drop table test_table_3
go
truncate table #extentinfo
go
insert #extentinfo
exec( '
dbcc extentinfo ( 0 ) with tableresults
' )
select *
from #extentinfo
where file_id = 3
order by page_id
go
Back to normal – the freespace comes back, shall we try and re-index test_table_2 and see what happens, remember the current Extent Scan Fragmentation is 99%.
dbcc dbreindex( test_table_2 )
go
dbcc showcontig( test_table_2 )
Any joy? Oh dear, we haven’t don’t very well there – Extent Scan Fragmentation is 1% better, now at 98%!
Lets give our database some room, extend the database from 130Mbytes up to 500Mbytes…
ALTER DATABASE [testfile] MODIFY FILE ( NAME = N'testfile1', SIZE = 512000KB )
Now try the DBREINDEX again.
dbcc dbreindex( test_table_2 )