index and key: fragmentation

Showing posts with label fragmentation. Show all posts

Monday, March 26, 2012

Index REBUILD question

Hey all,
One of the guys here (Tibor Karaszi) kindly pointed me to a script in
BOL that checks the fragmentation of indexes in a db, then depending on
the amount of fragmentation it REBUILDS or REORGANIZES the index. The
script I want to use is as it appears in BOL, but I've tweaked it to
specify a FILLFACTOR of 95 for the REBUILDS.
So my question is, if I have this script say running in a maintenance
plan at a 24/7 site, and it decides to REBUILD an index, if someone
indirectly accesses the table via our application would the application
receive an error as the table has been locked by the REBUILD?
Or will the query from the user wait until the lock on the table is
returned by the REBUILD process? (this is probably the ideal situation
for us)

>From BOL I understand it may have a shared lock (ie. readonly) at some
times.
But I'm also concerned about writes to the table.
We would prefer that the access is slow at that point rather than
returning an error message.
Can anyone clear this up for me?
Cheers!!
PS:
We can't use REBUILD ONLINE as
1) We use large object data types and
2) The client does not have Enterprise versionA REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the duration. So if the user tries to access the table it
will be blocked. How long is determined by the amount of time it takes to
rebuild the index or the timeout setting which ever comes first. If you need
it to be available all the time then you should look into the REORGANIZE
option instead. And in either case I would not recommend specifying the fill
factor in that script. By doing so you loose the ability to tune the fill
factors on individual indexes. If you do not specify it then it will use the
last setting. So if you initially want 95 then you should have built them
with 95 or manually issue a REBUILD with 95 as the setting. From then on
don't specify it again in the general rebuild script.
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
> Hey all,
> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
> BOL that checks the fragmentation of indexes in a db, then depending on
> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
> script I want to use is as it appears in BOL, but I've tweaked it to
> specify a FILLFACTOR of 95 for the REBUILDS.
> So my question is, if I have this script say running in a maintenance
> plan at a 24/7 site, and it decides to REBUILD an index, if someone
> indirectly accesses the table via our application would the application
> receive an error as the table has been locked by the REBUILD?
> Or will the query from the user wait until the lock on the table is
> returned by the REBUILD process? (this is probably the ideal situation
> for us)
>
> times.
> But I'm also concerned about writes to the table.
> We would prefer that the access is slow at that point rather than
> returning an error message.
> Can anyone clear this up for me?
> Cheers!!
> PS:
> We can't use REBUILD ONLINE as
> 1) We use large object data types and
> 2) The client does not have Enterprise version
>|||In addition, if you have Enterprise Edition, you can do the rebuild with the
ONLINE option.
REORGANIZE might be better for you than REBUILD with ONLINE, you have to tes
t to see what suits you
best.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e5Uduv9nGHA.4848@.TK2MSFTNGP03.phx.gbl...
>A REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the
>duration. So if the user tries to access the table it will be blocked. How
long is determined by
>the amount of time it takes to rebuild the index or the timeout setting whi
ch ever comes first. If
>you need it to be available all the time then you should look into the REOR
GANIZE option instead.
>And in either case I would not recommend specifying the fill factor in that
script. By doing so you
>loose the ability to tune the fill factors on individual indexes. If you do
not specify it then it
>will use the last setting. So if you initially want 95 then you should have
built them with 95 or
>manually issue a REBUILD with 95 as the setting. From then on don't specify
it again in the general
>rebuild script.
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message news:1152064393.005694.10670@.j8g2000
cwa.googlegroups.com...
>

Index REBUILD question

Hey all,
One of the guys here (Tibor Karaszi) kindly pointed me to a script in
BOL that checks the fragmentation of indexes in a db, then depending on
the amount of fragmentation it REBUILDS or REORGANIZES the index. The
script I want to use is as it appears in BOL, but I've tweaked it to
specify a FILLFACTOR of 95 for the REBUILDS.
So my question is, if I have this script say running in a maintenance
plan at a 24/7 site, and it decides to REBUILD an index, if someone
indirectly accesses the table via our application would the application
receive an error as the table has been locked by the REBUILD?
Or will the query from the user wait until the lock on the table is
returned by the REBUILD process? (this is probably the ideal situation
for us)
>From BOL I understand it may have a shared lock (ie. readonly) at some
times.
But I'm also concerned about writes to the table.
We would prefer that the access is slow at that point rather than
returning an error message.
Can anyone clear this up for me?
Cheers!!
PS:
We can't use REBUILD ONLINE as
1) We use large object data types and
2) The client does not have Enterprise versionA REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the duration. So if the user tries to access the table it
will be blocked. How long is determined by the amount of time it takes to
rebuild the index or the timeout setting which ever comes first. If you need
it to be available all the time then you should look into the REORGANIZE
option instead. And in either case I would not recommend specifying the fill
factor in that script. By doing so you loose the ability to tune the fill
factors on individual indexes. If you do not specify it then it will use the
last setting. So if you initially want 95 then you should have built them
with 95 or manually issue a REBUILD with 95 as the setting. From then on
don't specify it again in the general rebuild script.
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
> Hey all,
> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
> BOL that checks the fragmentation of indexes in a db, then depending on
> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
> script I want to use is as it appears in BOL, but I've tweaked it to
> specify a FILLFACTOR of 95 for the REBUILDS.
> So my question is, if I have this script say running in a maintenance
> plan at a 24/7 site, and it decides to REBUILD an index, if someone
> indirectly accesses the table via our application would the application
> receive an error as the table has been locked by the REBUILD?
> Or will the query from the user wait until the lock on the table is
> returned by the REBUILD process? (this is probably the ideal situation
> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
> times.
> But I'm also concerned about writes to the table.
> We would prefer that the access is slow at that point rather than
> returning an error message.
> Can anyone clear this up for me?
> Cheers!!
> PS:
> We can't use REBUILD ONLINE as
> 1) We use large object data types and
> 2) The client does not have Enterprise version
>|||In addition, if you have Enterprise Edition, you can do the rebuild with the ONLINE option.
REORGANIZE might be better for you than REBUILD with ONLINE, you have to test to see what suits you
best.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e5Uduv9nGHA.4848@.TK2MSFTNGP03.phx.gbl...
>A REBUILD by default is a totally off-line process in that the table has an Exclusive lock for the
>duration. So if the user tries to access the table it will be blocked. How long is determined by
>the amount of time it takes to rebuild the index or the timeout setting which ever comes first. If
>you need it to be available all the time then you should look into the REORGANIZE option instead.
>And in either case I would not recommend specifying the fill factor in that script. By doing so you
>loose the ability to tune the fill factors on individual indexes. If you do not specify it then it
>will use the last setting. So if you initially want 95 then you should have built them with 95 or
>manually issue a REBUILD with 95 as the setting. From then on don't specify it again in the general
>rebuild script.
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
>> Hey all,
>> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
>> BOL that checks the fragmentation of indexes in a db, then depending on
>> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
>> script I want to use is as it appears in BOL, but I've tweaked it to
>> specify a FILLFACTOR of 95 for the REBUILDS.
>> So my question is, if I have this script say running in a maintenance
>> plan at a 24/7 site, and it decides to REBUILD an index, if someone
>> indirectly accesses the table via our application would the application
>> receive an error as the table has been locked by the REBUILD?
>> Or will the query from the user wait until the lock on the table is
>> returned by the REBUILD process? (this is probably the ideal situation
>> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
>> times.
>> But I'm also concerned about writes to the table.
>> We would prefer that the access is slow at that point rather than
>> returning an error message.
>> Can anyone clear this up for me?
>> Cheers!!
>> PS:
>> We can't use REBUILD ONLINE as
>> 1) We use large object data types and
>> 2) The client does not have Enterprise version
>

Monday, March 19, 2012

Index Monitoring

I monitor the index fragmentation through dbcc showcontig command , i get result as
Pages Scanned
Extents Scanned
Extent Switches
Avg. Pages per Extent
Scan Density [Best Count:Actual Count]
Logical Scan Fragmentation
Extent Scan Fragmentation
Avg. Bytes Free per Page
Avg. Page Density (full).

But i want to know can we get value of 'extent switches' and logical scan fragmentation' through system tables , because we know that all database details are updated in system tables.As you have posted a question in the article section it ts being moved to SQL Server Forum.

Friday, March 9, 2012

index fragmentation LogicalFragmentation -how bad is bad - silly question alter

I thought I would delve into index fragmentation and I found some
great sql from many posters (thanks Erland!).
My question is how bad is bad? I know this is very subjective.
Some scripts I found would reindex if the LogicalFragmenation is over
30%.
I have some tables that are 98% (I'm guessing really bad). I know it
all depends..

more as a learning point: I found a table that had over 30%
logicalfragmentation, I dropped the indexes, created then ran the
script that used type code segment:
'DBCC SHOWCONTIG(' + @.TableName + ') WITH TABLERESULTS, ALL_INDEXES,
NO_INFOMSGS')

In one case, the indexes for the table dropped below 30%, in another
case the index was still fragmented ever after I dropped and re-
created index.

SQL Server 2005 x64 SP2

This is the script I am running (I found this in another thread that
Erland posted):

SET NOCOUNT ON
USE ds_v6_source

DECLARE @.TableName VARCHAR(100)

-- Create a table to hold the results of DBCC SHOWCONTIG
IF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULL
DROP TABLE #Contig

CREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,
[IndexName]
VARCHAR(200),
[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,
[MinimumRecordSize] INT,
[MaximumRecordSize] INT , [AverageRecordSize] INT,
[ForwardedRecords] INT ,
[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]
NUMERIC(6,2)
,
[AveragePageDensity] NUMERIC(6,2), [ScanDensity]
NUMERIC(6,2) ,
[BestCount] INT ,
[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,
[ExtentFragmentation] NUMERIC(6,2) )

DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @.TableName + ') WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM curTables INTO @.TableName
END

CLOSE curTables
DEALLOCATE curTablesHi

"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173530238.674192.247030@.64g2000cwx.googlegro ups.com...

Quote:

Originally Posted by

>I thought I would delve into index fragmentation and I found some
great sql from many posters (thanks Erland!).
My question is how bad is bad? I know this is very subjective.
Some scripts I found would reindex if the LogicalFragmenation is over
30%.

In the "Reorganizing and Rebuilding Indexes" topic, BOL recommends to
REORGANIZE for 5-30% and REBUILD 30%

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm

Although the script on the "sys.dm_db_index_physical_stats" topic in BOL use
10-30% to REORGANIZE

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm

Quote:

Originally Posted by

I have some tables that are 98% (I'm guessing really bad). I know it
all depends..
>
more as a learning point: I found a table that had over 30%
logicalfragmentation, I dropped the indexes, created then ran the
script that used type code segment:
'DBCC SHOWCONTIG(' + @.TableName + ') WITH TABLERESULTS, ALL_INDEXES,
NO_INFOMSGS')

DBCC SHOWCONTIG does not support some new features in SQL Server 2005 see
the "DBCC SHOWCONTIG" topic in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1df2123a-1197-4fff-91a3-25e3d8848aaa.htm

Quote:

Originally Posted by

>
In one case, the indexes for the table dropped below 30%, in another
case the index was still fragmented ever after I dropped and re-
created index.

Posting the output from sys.dm_db_index_physical_stats would be useful.

Quote:

Originally Posted by

>
SQL Server 2005 x64 SP2

Check you are on the version of SP2 downloaded after 2007-03-05 or the
critical update http://support.microsoft.com/kb/933508 if you have
Maintenance Plans or SSIS packages.

Quote:

Originally Posted by

If you are using DBCC DBREINDEX or DBCC INDEXDEFRAG you should look to
change to ALTER INDEX...

John|||thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type_descindex_depthindex_level
avg_fragmentation_in_percentfragment_countavg_fragment_size_in_pages
page_count
IN_ROW_DATA1033.33333333333331211.1666666666667134
IN_ROW_DATA2088.2352941176471161.062517

Based on your points about BOL, I found a script the reindex, or reorg
the index, but I still have files upwards of 80% fragmentation.
This is an undiscovered country for me, thanks for the guidance!
Rob|||Hi Rob

"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173578291.621286.217970@.p10g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count
IN_ROW_DATA 1 0 33.3333333333333 12 11.1666666666667 134
IN_ROW_DATA 2 0 88.2352941176471 16 1.0625 17
>
Based on your points about BOL, I found a script the reindex, or reorg
the index, but I still have files upwards of 80% fragmentation.
This is an undiscovered country for me, thanks for the guidance!
Rob
>

There seems to be some columns missing! Can you also post the DDL for the
table and indexes.

John|||The rest of the columns where null, so I didnt include..
(I am wondering if I should create the indexes in a file space other
than [PRIMARY].)

File is INTSTD. this is the DLL that is maintained in my data
warehouse tool from cognos.
// Connection: 2-Source

CREATE TABLE "dbo"."INTSTD"
(
"STUDENT_ID" CHAR(20) NOT NULL,
"REINSTATE_DT" DATETIME NULL,
"VISA_MAILED_DT" DATETIME NULL,
"INITIALI20_DT" DATETIME NULL,
"FORMI20_DT" DATETIME NULL,
"I94CARD_DT" DATETIME NULL,
"SEVIS" CHAR(12) NULL,
"SEVIS_ISSUE_DT" DATETIME NULL,
"REINSTATE_APPROVED_DT" DATETIME NULL,
"VISA_APPROVED_DT" DATETIME NULL,
"PT_START_DT" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_DT" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);

CREATE INDEX STUDENT_ID ON "dbo"."INTSTD" ( "STUDENT_ID" );
CREATE INDEX LOAD_DT ON "dbo"."INTSTD" ( "LOAD_DT" );

The SQL snipit I copied from BOL

DECLARE @.db_id SMALLINT;
DECLARE @.object_id INT;

SET @.db_id = DB_ID(N'ds_v6_source');
SET @.object_id = OBJECT_ID(N'ds_v6_source.dbo.intstd');
SELECT * FROM sys.dm_db_index_physical_stats(@.db_id, @.object_id, NULL,
NULL , 'LIMITED');

tab delimited results for the table:
database_idobject_idindex_idpartition_numberindex_type_desc
alloc_unit_type_descindex_depthindex_level
avg_fragmentation_in_percentfragment_countavg_fragment_size_in_pages
page_countavg_page_space_used_in_percentrecord_count
ghost_record_countversion_ghost_record_countmin_record_size_in_bytes
max_record_size_in_bytesavg_record_size_in_bytes
forwarded_record_count
8110262697101HEAPIN_ROW_DATA1021.73913043478261012.4124
NULLNULLNULLNULLNULLNULLNULLNULL
8110262697131NONCLUSTERED INDEXIN_ROW_DATA2030.7692307692308
102.626NULLNULLNULLNULLNULLNULLNULLNULL
8110262697141NONCLUSTERED INDEXIN_ROW_DATA2033.33333333333339
218NULLNULLNULLNULLNULLNULLNULLNULL

On Mar 11, 6:36 am, "John Bell" <jbellnewspo...@.hotmail.comwrote:

Quote:

Originally Posted by

Hi Rob
>
"rcamarda" <robert.a.cama...@.gmail.comwrote in message
>
news:1173578291.621286.217970@.p10g2000cwp.googlegr oups.com...
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>
There seems to be some columns missing! Can you also post the DDL for the
table and indexes.
>
John

|||Hi

"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173613533.607839.275800@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

The rest of the columns where null, so I didnt include..
(I am wondering if I should create the indexes in a file space other
than [PRIMARY].)
>
File is INTSTD. this is the DLL that is maintained in my data
warehouse tool from cognos.
// Connection: 2-Source
>
CREATE TABLE "dbo"."INTSTD"
(
"STUDENT_ID" CHAR(20) NOT NULL,
"REINSTATE_DT" DATETIME NULL,
"VISA_MAILED_DT" DATETIME NULL,
"INITIALI20_DT" DATETIME NULL,
"FORMI20_DT" DATETIME NULL,
"I94CARD_DT" DATETIME NULL,
"SEVIS" CHAR(12) NULL,
"SEVIS_ISSUE_DT" DATETIME NULL,
"REINSTATE_APPROVED_DT" DATETIME NULL,
"VISA_APPROVED_DT" DATETIME NULL,
"PT_START_DT" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_DT" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);
>
>
>
CREATE INDEX STUDENT_ID ON "dbo"."INTSTD" ( "STUDENT_ID" );
CREATE INDEX LOAD_DT ON "dbo"."INTSTD" ( "LOAD_DT" );
>
>
The SQL snipit I copied from BOL
>
DECLARE @.db_id SMALLINT;
DECLARE @.object_id INT;
>
SET @.db_id = DB_ID(N'ds_v6_source');
SET @.object_id = OBJECT_ID(N'ds_v6_source.dbo.intstd');
SELECT * FROM sys.dm_db_index_physical_stats(@.db_id, @.object_id, NULL,
NULL , 'LIMITED');
>
tab delimited results for the table:
database_id object_id index_id partition_number index_type_desc
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count avg_page_space_used_in_percent record_count
ghost_record_count version_ghost_record_count min_record_size_in_bytes
max_record_size_in_bytes avg_record_size_in_bytes
forwarded_record_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.7391304347826 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.7692307692308
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 9
2 18 NULL NULL NULL NULL NULL NULL NULL NULL
>

This does not show the fragmentation you are talking about! You may want to
consider making student_id/load_dt a clustered index. If this data is bulk
loaded for unique load_dts then a clustered index on load_dt/student_id
would effectively append data with no fragmentation.

Do you have a sensible fill factor?

John|||John,
I've been running the scripts you've pointed out in BOL, so they are
changing. the amount and size of fragmentation is coming down, but I
have some smaller files with a lot.
fill Factor is default, which I think is 80.

On Mar 11, 9:15 am, "John Bell" <jbellnewspo...@.hotmail.comwrote:

Quote:

Originally Posted by

Hi
>
"rcamarda" <robert.a.cama...@.gmail.comwrote in message
>
news:1173613533.607839.275800@.t69g2000cwt.googlegr oups.com...
>
>
>

Quote:

Originally Posted by

The rest of the columns where null, so I didnt include..
(I am wondering if I should create the indexes in a file space other
than [PRIMARY].)

Quote:

Originally Posted by

File is INTSTD. this is the DLL that is maintained in my data
warehouse tool from cognos.
// Connection: 2-Source

Quote:

Originally Posted by

Quote:

Originally Posted by

CREATE INDEX STUDENT_ID ON "dbo"."INTSTD" ( "STUDENT_ID" );
CREATE INDEX LOAD_DT ON "dbo"."INTSTD" ( "LOAD_DT" );

Quote:

Originally Posted by

The SQL snipit I copied from BOL

Quote:

Originally Posted by

DECLARE @.db_id SMALLINT;
DECLARE @.object_id INT;

Quote:

Originally Posted by

SET @.db_id = DB_ID(N'ds_v6_source');
SET @.object_id = OBJECT_ID(N'ds_v6_source.dbo.intstd');
SELECT * FROM sys.dm_db_index_physical_stats(@.db_id, @.object_id, NULL,
NULL , 'LIMITED');

Quote:

Originally Posted by

tab delimited results for the table:
database_id object_id index_id partition_number index_type_desc
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count avg_page_space_used_in_percent record_count
ghost_record_count version_ghost_record_count min_record_size_in_bytes
max_record_size_in_bytes avg_record_size_in_bytes
forwarded_record_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.7391304347826 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.7692307692308
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 9
2 18 NULL NULL NULL NULL NULL NULL NULL NULL

>
This does not show the fragmentation you are talking about! You may want to
consider making student_id/load_dt a clustered index. If this data is bulk
loaded for unique load_dts then a clustered index on load_dt/student_id
would effectively append data with no fragmentation.
>
Do you have a sensible fill factor?
>
John- Hide quoted text -
>
- Show quoted text -

|||Hi Rob

On Mar 11, 4:47 pm, "rcamarda" <robert.a.cama...@.gmail.comwrote:

Quote:

Originally Posted by

John,
I've been running the scripts you've pointed out in BOL, so they are
changing. the amount and size of fragmentation is coming down, but I
have some smaller files with a lot.
fill Factor is default, which I think is 80.
>
On Mar 11, 9:15 am, "John Bell" <jbellnewspo...@.hotmail.comwrote:
>

If your tables are bulk loaded and never change a 100% fill factor
would be feasible. If the data pages are all in memory then the index
fragmentation is not going to be an issue, this may be the case with
small tables.
To reduce fragmentation of the heap tables you can try adding a
clustered index and then drop it, although you should look at possibly
having a permanent clustered indexes in some cases.

Check the indexes are being used by seeing if there is an entry in
sys.dm_db_index_usage_stats . If there is no entry the index has not
been used since the last reboot, so you would need to see if it will
ever be usfull.

John|||On Mar 12, 8:23 am, "John Bell" <jbellnewspo...@.hotmail.comwrote:

Quote:

Originally Posted by

Hi Rob
>
If there is no entry the index has not
been used since the last reboot, so you would need to see if it will
ever be usfull.
>

Just to correct myself! That is not quite right, look for entries with
values of 0
see https://www.microsoft.com/technet/t...ex/default.aspx
for more.

John

Index fragmentation issue - SQL2005

Hi,
We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
where our indexes are going from 100% unfragmented to 30% unfragmented
(According to dbcc showcontig) in the space of 10 minutes when we insert
data.
Now, obviously we'll get some fragmenation and we re-index overnight but the
speed the fragmentation occurs so quickly it makes our new server slower
than our old one.
How can I track down why these tables are fragmenting so quickly?Nick wrote:
> Hi,
> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
> where our indexes are going from 100% unfragmented to 30% unfragmented
> (According to dbcc showcontig) in the space of 10 minutes when we insert
> data.
> Now, obviously we'll get some fragmenation and we re-index overnight but the
> speed the fragmentation occurs so quickly it makes our new server slower
> than our old one.
> How can I track down why these tables are fragmenting so quickly?
>
How big (how many pages) are these indexes?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The biggest table (There are 3 with issues) is just over 8000 pages.
Our index proc has run overnight, and still one of the tables shows 30% scan
density - issuing a dbcc reindex then put it back to 99%. I don't understand
how the reindex doesn't affect it one time and then it does the next!
The tables are on a filegroup that is split over 3 files - this was to help
performance. It's a 64-bit AMD box with storage on a NetApp filer. SP1
applied with all hotfixes I could find!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:453F9977.5060504@.realsqlguy.com...
> Nick wrote:
>> Hi,
>> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
>> where our indexes are going from 100% unfragmented to 30% unfragmented
>> (According to dbcc showcontig) in the space of 10 minutes when we insert
>> data.
>> Now, obviously we'll get some fragmenation and we re-index overnight but
>> the speed the fragmentation occurs so quickly it makes our new server
>> slower than our old one.
>> How can I track down why these tables are fragmenting so quickly?
> How big (how many pages) are these indexes?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know whether it applies to this particular situation, but scan density doesn't give
meaningful information if the filegroup has several files. "Jumping back and forth between the
files" counts as fragmentation. This is why scan density isn't presented in the now recommended way
to check fragmentation sys.dm_db_index_physical_stats. Google for Paul Randall and suitable keywords
and you will probably find some more technicalities on the subject.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nick" <no.thanks@.nowhere.com> wrote in message news:eBWQPPM%23GHA.4388@.TK2MSFTNGP02.phx.gbl...
> The biggest table (There are 3 with issues) is just over 8000 pages.
> Our index proc has run overnight, and still one of the tables shows 30% scan density - issuing a
> dbcc reindex then put it back to 99%. I don't understand how the reindex doesn't affect it one
> time and then it does the next!
> The tables are on a filegroup that is split over 3 files - this was to help performance. It's a
> 64-bit AMD box with storage on a NetApp filer. SP1 applied with all hotfixes I could find!
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:453F9977.5060504@.realsqlguy.com...
>> Nick wrote:
>> Hi,
>> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem where our indexes are
>> going from 100% unfragmented to 30% unfragmented (According to dbcc showcontig) in the space of
>> 10 minutes when we insert data.
>> Now, obviously we'll get some fragmenation and we re-index overnight but the speed the
>> fragmentation occurs so quickly it makes our new server slower than our old one.
>> How can I track down why these tables are fragmenting so quickly?
>> How big (how many pages) are these indexes?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Thanks Tibor; I've started using the new function as you suggest.
Unfortunatley, the fragmentaion is still bad but at least this might give me
a clue and pointer in the right direction.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eMR%23wCN%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
>I don't know whether it applies to this particular situation, but scan
>density doesn't give meaningful information if the filegroup has several
>files. "Jumping back and forth between the files" counts as fragmentation.
>This is why scan density isn't presented in the now recommended way to
>check fragmentation sys.dm_db_index_physical_stats. Google for Paul Randall
>and suitable keywords and you will probably find some more technicalities
>on the subject.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nick" <no.thanks@.nowhere.com> wrote in message
> news:eBWQPPM%23GHA.4388@.TK2MSFTNGP02.phx.gbl...
>> The biggest table (There are 3 with issues) is just over 8000 pages.
>> Our index proc has run overnight, and still one of the tables shows 30%
>> scan density - issuing a dbcc reindex then put it back to 99%. I don't
>> understand how the reindex doesn't affect it one time and then it does
>> the next!
>> The tables are on a filegroup that is split over 3 files - this was to
>> help performance. It's a 64-bit AMD box with storage on a NetApp filer.
>> SP1 applied with all hotfixes I could find!
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:453F9977.5060504@.realsqlguy.com...
>> Nick wrote:
>> Hi,
>> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
>> where our indexes are going from 100% unfragmented to 30% unfragmented
>> (According to dbcc showcontig) in the space of 10 minutes when we
>> insert data.
>> Now, obviously we'll get some fragmenation and we re-index overnight
>> but the speed the fragmentation occurs so quickly it makes our new
>> server slower than our old one.
>> How can I track down why these tables are fragmenting so quickly?
>> How big (how many pages) are these indexes?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>

Index fragmentation issue - SQL2005

Index Fragmentation and Datatype Issue

I've got an issue where by certain types of records in a particular table are
becoming fragmented. The table is made up of an ID Column (identity), a
Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
[FK](varchar) and some other ones.
The none-clustered index comprises these four columns in ascending order.
For the most part this is fine, records are appended to the table and
fragmentation doesn't occur. However the indexes have started to become
fragmented for certain types of records.
The records which seem to be causing the fragmentation use predominantly
numeric account codes. Has anyone experienced a problem similar to this where
by the data type has caused index fragmentation?
Many thanks,
=============
VB .NET Developer
http://www.rocketscience.uk.com
Why would you have an index like that? Since the identity column is unique
the rest of the index is pretty much useless. And if this is a
Non-clustered index it has no bearing on the amount of fragmentation in the
table itself or the placement of new rows into the table. That is controlled
by the clustered index or if it is a Heap you have no control over where the
rows get placed. You might want to have a look at these:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.co...showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/colu...illfactors.asp
Fill Factors
http://www.sql-server-performance.co...ed_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:9A4EFB0E-0B3F-4024-AC6A-ECA0FCE52D4A@.microsoft.com...
> I've got an issue where by certain types of records in a particular table
> are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
> Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this
> where
> by the data type has caused index fragmentation?
> Many thanks,
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
|||JumpingMattFlash a écrit :
> I've got an issue where by certain types of records in a particular table are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this where
> by the data type has caused index fragmentation?
> Many thanks,
In fact in your case frag is probably due to UPDATE on Account ID.
When choosing VARCHAR the storage does store the data exactly at the
length of the data. Wich mean if you have choose VARCHAR(32) and
inserting a 8 char value, only 8 char will be use in the complete row.
After if you update this data to enlarge it, for instance by a data wich
is 12 char length, it is impossible to store the value of this column in
the original row. So a new storage emplacement is choose but the
complete row stay at the old place. Only the new value is store outside
and pointer are placed form the original row to say where this data has
been moved !
This is fragmentation.
So, in indexes, VARCHAR is not the good choice when updates can occur...
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Index Fragmentation and Datatype Issue

I've got an issue where by certain types of records in a particular table ar
e
becoming fragmented. The table is made up of an ID Column (identity), a
Reference Column[FK](int) , a Date Column (timedate) and an Account ID C
olumn
[FK](varchar) and some other ones.
The none-clustered index comprises these four columns in ascending order.
For the most part this is fine, records are appended to the table and
fragmentation doesn't occur. However the indexes have started to become
fragmented for certain types of records.
The records which seem to be causing the fragmentation use predominantly
numeric account codes. Has anyone experienced a problem similar to this wher
e
by the data type has caused index fragmentation?
Many thanks,
--
=============
VB .NET Developer
http://www.rocketscience.uk.comWhy would you have an index like that? Since the identity column is unique
the rest of the index is pretty much useless. And if this is a
Non-clustered index it has no bearing on the amount of fragmentation in the
table itself or the placement of new rows into the table. That is controlled
by the clustered index or if it is a Heap you have no control over where the
rows get placed. You might want to have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:9A4EFB0E-0B3F-4024-AC6A-ECA0FCE52D4A@.microsoft.com...
> I've got an issue where by certain types of records in a particular table
> are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
> Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this
> where
> by the data type has caused index fragmentation?
> Many thanks,
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com|||JumpingMattFlash a écrit :
> I've got an issue where by certain types of records in a particular table
are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this wh
ere
> by the data type has caused index fragmentation?
> Many thanks,
In fact in your case frag is probably due to UPDATE on Account ID.
When choosing VARCHAR the storage does store the data exactly at the
length of the data. Wich mean if you have choose VARCHAR(32) and
inserting a 8 char value, only 8 char will be use in the complete row.
After if you update this data to enlarge it, for instance by a data wich
is 12 char length, it is impossible to store the value of this column in
the original row. So a new storage emplacement is choose but the
complete row stay at the old place. Only the new value is store outside
and pointer are placed form the original row to say where this data has
been moved !
This is fragmentation.
So, in indexes, VARCHAR is not the good choice when updates can occur...
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Index Fragmentation and Datatype Issue

I've got an issue where by certain types of records in a particular table are
becoming fragmented. The table is made up of an ID Column (identity), a
Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
[FK](varchar) and some other ones.
The none-clustered index comprises these four columns in ascending order.
For the most part this is fine, records are appended to the table and
fragmentation doesn't occur. However the indexes have started to become
fragmented for certain types of records.
The records which seem to be causing the fragmentation use predominantly
numeric account codes. Has anyone experienced a problem similar to this where
by the data type has caused index fragmentation?
Many thanks,
--
============= VB .NET Developer
http://www.rocketscience.uk.comWhy would you have an index like that? Since the identity column is unique
the rest of the index is pretty much useless. And if this is a
Non-clustered index it has no bearing on the amount of fragmentation in the
table itself or the placement of new rows into the table. That is controlled
by the clustered index or if it is a Heap you have no control over where the
rows get placed. You might want to have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Clustered Indexes
--
Andrew J. Kelly SQL MVP
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:9A4EFB0E-0B3F-4024-AC6A-ECA0FCE52D4A@.microsoft.com...
> I've got an issue where by certain types of records in a particular table
> are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
> Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this
> where
> by the data type has caused index fragmentation?
> Many thanks,
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com|||JumpingMattFlash a Ã©crit :
> I've got an issue where by certain types of records in a particular table are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this where
> by the data type has caused index fragmentation?
> Many thanks,
In fact in your case frag is probably due to UPDATE on Account ID.
When choosing VARCHAR the storage does store the data exactly at the
length of the data. Wich mean if you have choose VARCHAR(32) and
inserting a 8 char value, only 8 char will be use in the complete row.
After if you update this data to enlarge it, for instance by a data wich
is 12 char length, it is impossible to store the value of this column in
the original row. So a new storage emplacement is choose but the
complete row stay at the old place. Only the new value is store outside
and pointer are placed form the original row to say where this data has
been moved !
This is fragmentation.
So, in indexes, VARCHAR is not the good choice when updates can occur...
A +
FrÃ©dÃ©ric BROUARD, MVP SQL Server, expert bases de donnÃ©es et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modÃ©lisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

index fragmentation

Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below. The second line is a valid index - cognos_1 - but the first line doesn't have an index name but it shows 90% fragmentation. Why does that line without an index name show up? Is there a way to get rid of it? Does it matter that it shows up?
d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4 129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33.333333333333329 1 3 0.0 NULL
Thanks,
--
Dan D.It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
null name and index id=0. It also explains that logical scan fragmentation
is not relevant for heaps.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH
FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't
have an index name but it shows 90% fragmentation. Why does that line
without an index name show up? Is there a way to get rid of it? Does it
matter that it shows up?
>
> d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4
129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2
NULL NULL 33.333333333333329 1 3 0.0 NULL
> Thanks,
> --
> Dan D.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.On Apr 26, 10:05 pm, phancey <d...@.2bytes.co.uk> wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
actually the database file was created without a size, then changed to
500MB, then changed again to 3.5GB, all before the majority of the
data was written to the tables (and certainly before any data was
written to this particular table).|||Can you give the exact statement you are executing to get this information,
and the exact, complete output you receive? Perhaps (hopefully) you are
misinterpreting the data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"phancey" <deja@.2bytes.co.uk> wrote in message
news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>|||Using SQL 2005, I view the properties of the index, then
"Fragmentation".
Also, my data load (which does a check on existing records before
inserting) grinds to a halt presumably because of this query. I have
managed to run the data load a few times without any problem but
unfortunately did not make enough notes on the exact setup because I
did not think there was going to be a problem (I'm pretty sure I did
check fragmentation of the indexes though and am pretty sure that
fragmentation was less than 10%). Now I cannot recreate that fast load
and am at my wits end to figure out what is different.
The database file sizes again are got from the properties page of the
database in Management Studio.
thanks
On Apr 26, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Can you give the exact statement you are executing to get this information,
> and the exact, complete output you receive? Perhaps (hopefully) you are
> misinterpreting the data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "phancey" <d...@.2bytes.co.uk> wrote in message
> news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> > I've got 99% fragmentation on a clustered index of a table with 29699
> > rows (there have been just 29699 inserts, no updates, no deletes).
> > There are 8 columns on the table with an int and a char(32) being the
> > unique clustered index - in that order - and I'm pretty sure that the
> > rows are inserted in int,char(32) order. Average row size is 852,
> > there are 3769 pages with average fullness being 83%.
> > The database file was created as 3.5GB and has 51% space free. The
> > data file itself is only fragmented into 3 pieces according to Disk
> > Fragmenter.
> > I don't really understand why it has fragmented so much. All index
> > rows should be appended because they are inserted in key order, there
> > is plenty of space in the file - would it have been better to have
> > made the file smaller and done AutoGrow by 1MB'
> > The table also has a non-clustered index, which is hardly fragmented
> > at all.
> > The insert transaction consisted of many inserts on many different
> > tables, all with similar indexes, all inserted in key value order.
> > There are maybe 100 tables - inserts take place across the 100 tables,
> > then another group of inserts take place against all the tables. But
> > for any specific table any insert will be in key value order and
> > should therefore be appended (which seems to be supported by the page
> > fullness being 83%). But how can I avoid the logical fragmentation? I
> > know I can use DBCC DBREINDEX but this is happening so quickly in the
> > system that it is not reasonable to have to do it this soon (and it's
> > actually happening three quarters of the way through a data load).
> > There would have been hundreds of thousands of records inserted during
> > the transaction.
> > Thanks for any help.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.Hi
"phancey" wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
>
Could you post DDL and the command(s) you have used to show the
fragmentation with their output?
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
Disc fragmentation may have an effect on performance, but is not really
related to index fragmentation which could have a more critical effect. To
remove disc fragmentation you would need to stop SQL Server or detatch the
database and run the defragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
No, incrementing with smaller amounts increases the risk of disc
fragmentation, and slows down the process of inserting rows as you will have
to wait while the file expands. Planned pre-allocation of the data files is
the best option. Do you have autoshrink on? Have you shrunk the files
manually?
Is the log file on the same drive?
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
You don't mention the value for extent fragmentation! What you could do is
load into a staging table, then do the inserts into each table in one go
rather than on a row per row basis. Could you drop the clustered index during
the upload?
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
What fragmentation value do you get after the DBREINDEX?
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>
John|||Hi
I have just seen the other post, and seen that you are using SQL 2005, look
at the function sys.dm_db_index_physical_stats, information can be obtained
from books online. You may also want to read about index fragmentation in
Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
0735621055
John|||Doh! The application (which I have no control over), despite asking
them to insert in int,char(32) asc order, was actually inserting in
int,char(32) desc order. So that would probably explain it.
Thanks anyway
On 27 Apr, 08:36, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I have just seen the other post, and seen that you are using SQL 2005, look
> at the function sys.dm_db_index_physical_stats, information can be obtained
> from books online. You may also want to read aboutindexfragmentationin
> Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
> 0735621055
> John|||Hi
"phancey" wrote:
> Doh! The application (which I have no control over), despite asking
> them to insert in int,char(32) asc order, was actually inserting in
> int,char(32) desc order. So that would probably explain it.
> Thanks anyway
>
In which case dropping the index may be your best option.
John|||Better may be to order the index columns in desc order instead. See BOL for
the CREATE INDEX statement.
--
TheSQLGuru
President
Indicium Resources, Inc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0EFD52C4-245E-4551-BD6E-87E9924C29B4@.microsoft.com...
> Hi
> "phancey" wrote:
>> Doh! The application (which I have no control over), despite asking
>> them to insert in int,char(32) asc order, was actually inserting in
>> int,char(32) desc order. So that would probably explain it.
>> Thanks anyway
> In which case dropping the index may be your best option.
> John|||On 27 Apr 2007 05:34:09 -0700, phancey <deja@.2bytes.co.uk> wrote:
>Doh! The application (which I have no control over), despite asking
>them to insert in int,char(32) asc order, was actually inserting in
>int,char(32) desc order. So that would probably explain it.
>Thanks anyway
:)
J.

Index Fragmentation

I have some very large transactional tables (20GB+). I am looking to optimize
the indexes. My question relates to fill factors.
In these tables (assuming they have a clustered index that is a sequential
value (or identity field)), if I have a covering index where the first key is
the clustered index, will fragmentation occur in this index as new data is
added since all the values will be added to the end? If fragmentation does
not occur in this situation, I would assume that specifying a non-default
fill factor on these types of indexes be a waste of disk space and resources.
Similarly, would another index, such as a transaction date (which generally
will only increase) peform similarly to the above example.Jason
Start with
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>|||I had already read that, however that article really focuses on when to
defragment the indexes, not necessarily what a proper fill factor is or with
what types of indexes a fill factor might be proper for.
"Uri Dimant" wrote:
> Jason
> Start with
> http://www.sql-server-performance.com/rd_index_fragmentation.asp
>
>
> "Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
> news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
> >I have some very large transactional tables (20GB+). I am looking to
> >optimize
> > the indexes. My question relates to fill factors.
> >
> > In these tables (assuming they have a clustered index that is a sequential
> > value (or identity field)), if I have a covering index where the first key
> > is
> > the clustered index, will fragmentation occur in this index as new data is
> > added since all the values will be added to the end? If fragmentation
> > does
> > not occur in this situation, I would assume that specifying a non-default
> > fill factor on these types of indexes be a waste of disk space and
> > resources.
> >
> >
> > Similarly, would another index, such as a transaction date (which
> > generally
> > will only increase) peform similarly to the above example.
> >
>
>|||Jason,
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
I am not quite sure what you mean by that. A clustered index (CI) is
essentially a covering index on all columns. If you have a CI on a
monotonically incrementing value such as Identity then newly inserted rows
will not cause fragmentation. But if you later update any rows on columns
that are not fixed in size with a larger value than the original you can get
page splits. A little bit of fragmentation is usually not a problem. If it
is an OLTP system fragmentation is not that much of an issue. See the
article below:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
To answer your question on non-clustered indexes they are just like
clustered indexes in structure except that the leaf level is only the
column(s) in the index expression. In a CI the leaf level is the whole row.
As with a CI the NCI will not fragment if the rows are inserted in column
order such as datetime etc.
Andrew J. Kelly SQL MVP
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>

index fragmentation

Hi,
I rebuilt index for my tables yersterday morning, today when I check using
SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read that
high logic fragmentaion is not good, why it happened?
ThanksIs this a Heap? How many pages in the index? Can you post the results of
DBCC SHOWCONTIG?
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> Hi,
> I rebuilt index for my tables yersterday morning, today when I check using
> SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read
> that
> high logic fragmentaion is not good, why it happened?
> Thanks|||here is the result:(I changed my table name and index name)
table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL NULL 95.079950799507998 773 813 99.579154968261719 NULL
table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL NULL 90.771812080536918 541 596 99.884445190429687 NULL
table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL NULL 97.414806110458287 829 851 99.864151000976563 NULL
table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL NULL 80.076142131979694 631 788 99.841461181640625 NULL
"Andrew J. Kelly" wrote:
> Is this a Heap? How many pages in the index? Can you post the results of
> DBCC SHOWCONTIG?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> > Hi,
> > I rebuilt index for my tables yersterday morning, today when I check using
> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read
> > that
> > high logic fragmentaion is not good, why it happened?
> > Thanks
>
>|||That is pretty hard to make out. It is better to show the regular output
and not with TABLERESULTS option. But it seems like you do have a clustered
index on this. What is the datatype of the Column(s) in the Clustered
index? Did you shrink the database or is the AutoShrink option turned on?
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> here is the result:(I changed my table name and index name)
> table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> NULL 95.079950799507998 773 813 99.579154968261719 NULL
> table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> NULL 90.771812080536918 541 596 99.884445190429687 NULL
> table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> NULL 97.414806110458287 829 851 99.864151000976563 NULL
> table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> NULL 80.076142131979694 631 788 99.841461181640625 NULL
>
> "Andrew J. Kelly" wrote:
>> Is this a Heap? How many pages in the index? Can you post the results
>> of
>> DBCC SHOWCONTIG?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>> > Hi,
>> > I rebuilt index for my tables yersterday morning, today when I check
>> > using
>> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
>> > read
>> > that
>> > high logic fragmentaion is not good, why it happened?
>> > Thanks
>>|||sorry, I do have a numerica field (primary key) clusterd index.
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 1, database ID: 3
TABLE level scan performed.
- Pages Scanned........................: 38433
- Extent Switches.......................: 5950
- Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
- Logical Scan Fragmentation ..............: 99.56%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 15, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6177
- Extent Switches.......................: 788
- Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
- Logical Scan Fragmentation ..............: 99.58%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 16, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 4329
- Extent Switches.......................: 548
- Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
- Logical Scan Fragmentation ..............: 99.88%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 25, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6629
- Extent Switches.......................: 840
- Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
- Logical Scan Fragmentation ..............: 99.86%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 32, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 5046
- Extent Switches.......................: 638
- Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
- Logical Scan Fragmentation ..............: 99.84%
"Andrew J. Kelly" wrote:
> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a clustered
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> > here is the result:(I changed my table name and index name)
> > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> > NULL 95.079950799507998 773 813 99.579154968261719 NULL
> > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> > NULL 90.771812080536918 541 596 99.884445190429687 NULL
> > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> > NULL 97.414806110458287 829 851 99.864151000976563 NULL
> > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> > NULL 80.076142131979694 631 788 99.841461181640625 NULL
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Is this a Heap? How many pages in the index? Can you post the results
> >> of
> >> DBCC SHOWCONTIG?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> >> > Hi,
> >> > I rebuilt index for my tables yersterday morning, today when I check
> >> > using
> >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
> >> > read
> >> > that
> >> > high logic fragmentaion is not good, why it happened?
> >> > Thanks
> >>
> >>
> >>
>
>|||And also the database has AutoShrink option on.
"Andrew J. Kelly" wrote:
> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a clustered
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> > here is the result:(I changed my table name and index name)
> > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> > NULL 95.079950799507998 773 813 99.579154968261719 NULL
> > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> > NULL 90.771812080536918 541 596 99.884445190429687 NULL
> > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> > NULL 97.414806110458287 829 851 99.864151000976563 NULL
> > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> > NULL 80.076142131979694 631 788 99.841461181640625 NULL
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Is this a Heap? How many pages in the index? Can you post the results
> >> of
> >> DBCC SHOWCONTIG?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> >> > Hi,
> >> > I rebuilt index for my tables yersterday morning, today when I check
> >> > using
> >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
> >> > read
> >> > that
> >> > high logic fragmentaion is not good, why it happened?
> >> > Thanks
> >>
> >>
> >>
>
>|||ok - here's what it looks like has happened:
1) you've rebuilt all your indexes and acheived near perfect fragmentation
2) shrink has run and totally reversed the fragmentation status - to be the
worst possible as far as DBCC SHOWCONTIG is concerned
Fragmentation is when the next logical page in an index is not the next
physical page in an index, moving from left to right logically (and from the
beginning of the file to the end, physically). Shrink starts at the end of
the file and works towards the front, moving single pages. Looks like its
made every page appear 'fragmented' to showcontig. Unfortunately, they are
also fragmented as the buffer pool sees them and so no readahead can occur
and your range scan performance will be terrible.
The simple lesson here is: don't ever run auto-shrink. The database needs
the extra space most of the time. Andrew - can you provide a link to Tibor's
'don't run shrink' webpage?
You should also read the whitepaper below which goes into detail on
fragmentation and when/how to get rid of it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...
> And also the database has AutoShrink option on.
> "Andrew J. Kelly" wrote:
> > That is pretty hard to make out. It is better to show the regular
output
> > and not with TABLERESULTS option. But it seems like you do have a
clustered
> > index on this. What is the datatype of the Column(s) in the Clustered
> > index? Did you shrink the database or is the AutoShrink option turned
on?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> > > here is the result:(I changed my table name and index name)
> > > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL
NULL 0
> > > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> > > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812
NULL
> > > NULL 95.079950799507998 773 813 99.579154968261719 NULL
> > > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595
NULL
> > > NULL 90.771812080536918 541 596 99.884445190429687 NULL
> > > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850
NULL
> > > NULL 97.414806110458287 829 851 99.864151000976563 NULL
> > > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787
NULL
> > > NULL 80.076142131979694 631 788 99.841461181640625 NULL
> > >
> > >
> > > "Andrew J. Kelly" wrote:
> > >
> > >> Is this a Heap? How many pages in the index? Can you post the
results
> > >> of
> > >> DBCC SHOWCONTIG?
> > >>
> > >> --
> > >> Andrew J. Kelly SQL MVP
> > >>
> > >>
> > >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> > >> > Hi,
> > >> > I rebuilt index for my tables yersterday morning, today when I
check
> > >> > using
> > >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
> > >> > read
> > >> > that
> > >> > high logic fragmentaion is not good, why it happened?
> > >> > Thanks
> > >>
> > >>
> > >>
> >
> >
> >|||Yes it was the shrinking that did it. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:A01DBDCB-EA16-4295-A69B-55A36DDE0412@.microsoft.com...
> sorry, I do have a numerica field (primary key) clusterd index.
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 1, database ID: 3
> TABLE level scan performed.
> - Pages Scanned........................: 38433
> - Extent Switches.......................: 5950
> - Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
> - Logical Scan Fragmentation ..............: 99.56%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 15, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6177
> - Extent Switches.......................: 788
> - Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
> - Logical Scan Fragmentation ..............: 99.58%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 16, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 4329
> - Extent Switches.......................: 548
> - Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
> - Logical Scan Fragmentation ..............: 99.88%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 25, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6629
> - Extent Switches.......................: 840
> - Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
> - Logical Scan Fragmentation ..............: 99.86%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 32, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 5046
> - Extent Switches.......................: 638
> - Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
> - Logical Scan Fragmentation ..............: 99.84%
> "Andrew J. Kelly" wrote:
>> That is pretty hard to make out. It is better to show the regular output
>> and not with TABLERESULTS option. But it seems like you do have a
>> clustered
>> index on this. What is the datatype of the Column(s) in the Clustered
>> index? Did you shrink the database or is the AutoShrink option turned
>> on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>> > here is the result:(I changed my table name and index name)
>> > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL
>> > NULL 0
>> > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
>> > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812
>> > NULL
>> > NULL 95.079950799507998 773 813 99.579154968261719 NULL
>> > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595
>> > NULL
>> > NULL 90.771812080536918 541 596 99.884445190429687 NULL
>> > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850
>> > NULL
>> > NULL 97.414806110458287 829 851 99.864151000976563 NULL
>> > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787
>> > NULL
>> > NULL 80.076142131979694 631 788 99.841461181640625 NULL
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Is this a Heap? How many pages in the index? Can you post the
>> >> results
>> >> of
>> >> DBCC SHOWCONTIG?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>> >> > Hi,
>> >> > I rebuilt index for my tables yersterday morning, today when I check
>> >> > using
>> >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
>> >> > read
>> >> > that
>> >> > high logic fragmentaion is not good, why it happened?
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||> Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
Here goes. I'd appreciate much if you let me know if you find anything incorrect or that should be
expanded upon.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eOgH$vnAFHA.1452@.TK2MSFTNGP11.phx.gbl...
> ok - here's what it looks like has happened:
> 1) you've rebuilt all your indexes and acheived near perfect fragmentation
> 2) shrink has run and totally reversed the fragmentation status - to be the
> worst possible as far as DBCC SHOWCONTIG is concerned
> Fragmentation is when the next logical page in an index is not the next
> physical page in an index, moving from left to right logically (and from the
> beginning of the file to the end, physically). Shrink starts at the end of
> the file and works towards the front, moving single pages. Looks like its
> made every page appear 'fragmented' to showcontig. Unfortunately, they are
> also fragmented as the buffer pool sees them and so no readahead can occur
> and your range scan performance will be terrible.
> The simple lesson here is: don't ever run auto-shrink. The database needs
> the extra space most of the time. Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
> You should also read the whitepaper below which goes into detail on
> fragmentation and when/how to get rid of it.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...
>> And also the database has AutoShrink option on.
>> "Andrew J. Kelly" wrote:
>> > That is pretty hard to make out. It is better to show the regular
> output
>> > and not with TABLERESULTS option. But it seems like you do have a
> clustered
>> > index on this. What is the datatype of the Column(s) in the Clustered
>> > index? Did you shrink the database or is the AutoShrink option turned
> on?
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> > news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>> > > here is the result:(I changed my table name and index name)
>> > > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL
> NULL 0
>> > > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
>> > > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812
> NULL
>> > > NULL 95.079950799507998 773 813 99.579154968261719 NULL
>> > > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595
> NULL
>> > > NULL 90.771812080536918 541 596 99.884445190429687 NULL
>> > > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850
> NULL
>> > > NULL 97.414806110458287 829 851 99.864151000976563 NULL
>> > > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787
> NULL
>> > > NULL 80.076142131979694 631 788 99.841461181640625 NULL
>> > >
>> > >
>> > > "Andrew J. Kelly" wrote:
>> > >
>> > >> Is this a Heap? How many pages in the index? Can you post the
> results
>> > >> of
>> > >> DBCC SHOWCONTIG?
>> > >>
>> > >> --
>> > >> Andrew J. Kelly SQL MVP
>> > >>
>> > >>
>> > >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> > >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>> > >> > Hi,
>> > >> > I rebuilt index for my tables yersterday morning, today when I
> check
>> > >> > using
>> > >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
>> > >> > read
>> > >> > that
>> > >> > high logic fragmentaion is not good, why it happened?
>> > >> > Thanks
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >
>

Monday, March 26, 2012

Index REBUILD question

Index REBUILD question

Monday, March 19, 2012

Index Monitoring

Friday, March 9, 2012

index fragmentation LogicalFragmentation -how bad is bad - silly question alter

Index fragmentation issue - SQL2005

Index fragmentation issue - SQL2005

Index Fragmentation and Datatype Issue

Index Fragmentation and Datatype Issue

Index Fragmentation and Datatype Issue

index fragmentation

Index fragmentation

Index fragmentation

Index Fragmentation

index fragmentation

index and key

Blog Archive

About Me