DBfile usage: tablename

Showing posts with label tablename. Show all posts

Sunday, March 11, 2012

DBCC INDEXDEFRAG script not working

From the script found here
(http://msdn2.microsoft.com/en-us/library/ms175008.aspx):
USE db
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
I get the following errors:
Server: Msg 170, Level 15, State 1, Line 52
Line 52: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'END'.
Server: Msg 170, Level 15, State 1, Line 82
Line 82: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 93
Incorrect syntax near the keyword 'END'.
I thought I had completed this successfully before but obviously I hadn't.
Does anyone have any tips?If this is run on sql server 2000, "Begin" cannot be terminated with a
semi-colon.

Wednesday, March 7, 2012

DBCC DBREINDEX not changing anything!

This is quite weird...
Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
rebuild anything in the indexes, and fragmentation remains (67%).
Am I doing something incredibly stupid here or is there an issue I need
to be aware of?
Table has non-clustered indexes on it only.
Any suggestions? Has anyone seen this behaviour before?
Thx
How big is the table? Do you have a clustered index on the table? If not
then you can rebuild til you are blue in the face and it will still stay the
same. One of the reasons why most every table should have a clustered
index.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:ez$QWeOGFHA.444@.TK2MSFTNGP15.phx.gbl...
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need to
> be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
|||Paul
You really need a clustered index for reindex or rebuild to be effective in
removing fragmentation. Create a clustered index and then drop it. This will
reduce your fragmentation, and due to not having a clustered index your
tables will not fragment as quickly afterwards.
Hope this helps
John
"Paul Buxton" wrote:

> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need
> to be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
>
|||Andrew J. Kelly wrote:
> How big is the table? Do you have a clustered index on the table? If not
> then you can rebuild til you are blue in the face and it will still stay the
> same. One of the reasons why most every table should have a clustered
> index.
>
I thought it may be index related (hence my mentioning the indexes).
The table itself is 1Gb in size. Unfortunately, I can't change the
indexing (MSCRM - and not a clustered index in sight!) as support will
be removed if we do.
Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE
STATISTICS only. Strangely, The MS CRM Performance Tuning Whitepaper
suggests running the DBREINDEX when they have no clustered indexes in
the DB.
|||John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
>
Sadly, I can't change the schema for the DB or create indexes as far as
I can tell. My understanding is that we lose our support if we do
(system is MS CRM).
|||Sadly, I can't change the database in anyway or we lose support (system
is MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I
can do.
John Bandettini wrote:[vbcol=seagreen]
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
> "Paul Buxton" wrote:
>
|||Is it an index or the datapages you want to defrag? There is no way to defrag the data pages for a
table without a clustered index (heap table) with less than:
1 Export, truncate, import (or similar)
or
2 Create a clustered index and then drop it.
Also, if this is indeed a heap table, you need to think about that fragmentation really is for the
data pages. There is no linked list for the data pages in a heap table, so there's no jumping back
and forth. So the type of fragmentation we can talk about is if you either have non-consecutive
extents or less than full pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul Buxton" <spireite@.hotmail.com> wrote in message news:Oi4RdgQGFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
> I thought it may be index related (hence my mentioning the indexes). The table itself is 1Gb in
> size. Unfortunately, I can't change the indexing (MSCRM - and not a clustered index in sight!) as
> support will be removed if we do.
> Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE STATISTICS only. Strangely,
> The MS CRM Performance Tuning Whitepaper suggests running the DBREINDEX when they have no
> clustered indexes in the DB.
>
>
|||You should contact the vendor and tell them to get their act together and
add a proper clustered index. Why do you say that Indexdefrag is the best
option? As we all have pointed out there is nothing you can do to
defragment the tables without a CI. This is true regardless of using
DBREINDEX or INDEXEDEFRAG. As for the non-Clustered indexes (NCI's) you are
still free to use DBREINDEX on them. If you have the maintenance window to
run this you will mostly find it will be faster and cleaner than
Indexdefrag.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:eKau2hQGFHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Sadly, I can't change the database in anyway or we lose support (system is
> MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I can do.
>
> John Bandettini wrote:

DBCC DBREINDEX not changing anything!

This is quite weird...
Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
rebuild anything in the indexes, and fragmentation remains (67%).
Am I doing something incredibly stupid here or is there an issue I need
to be aware of?
Table has non-clustered indexes on it only.
Any suggestions? Has anyone seen this behaviour before?
ThxHow big is the table? Do you have a clustered index on the table? If not
then you can rebuild til you are blue in the face and it will still stay the
same. One of the reasons why most every table should have a clustered
index.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:ez$QWeOGFHA.444@.TK2MSFTNGP15.phx.gbl...
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need to
> be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx|||Paul
You really need a clustered index for reindex or rebuild to be effective in
removing fragmentation. Create a clustered index and then drop it. This will
reduce your fragmentation, and due to not having a clustered index your
tables will not fragment as quickly afterwards.
Hope this helps
John
"Paul Buxton" wrote:

> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need
> to be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
>|||Andrew J. Kelly wrote:
> How big is the table? Do you have a clustered index on the table? If no
t
> then you can rebuild til you are blue in the face and it will still stay t
he
> same. One of the reasons why most every table should have a clustered
> index.
>
I thought it may be index related (hence my mentioning the indexes).
The table itself is 1Gb in size. Unfortunately, I can't change the
indexing (MSCRM - and not a clustered index in sight!) as support will
be removed if we do.
Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE
STATISTICS only. Strangely, The MS CRM Performance Tuning Whitepaper
suggests running the DBREINDEX when they have no clustered indexes in
the DB.|||John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective i
n
> removing fragmentation. Create a clustered index and then drop it. This wi
ll
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
>
Sadly, I can't change the schema for the DB or create indexes as far as
I can tell. My understanding is that we lose our support if we do
(system is MS CRM).|||Sadly, I can't change the database in anyway or we lose support (system
is MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I
can do.
John Bandettini wrote:[vbcol=seagreen]
> Paul
> You really need a clustered index for reindex or rebuild to be effective i
n
> removing fragmentation. Create a clustered index and then drop it. This wi
ll
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
> "Paul Buxton" wrote:
>|||Is it an index or the datapages you want to defrag? There is no way to defra
g the data pages for a
table without a clustered index (heap table) with less than:
1 Export, truncate, import (or similar)
or
2 Create a clustered index and then drop it.
Also, if this is indeed a heap table, you need to think about that fragmenta
tion really is for the
data pages. There is no linked list for the data pages in a heap table, so t
here's no jumping back
and forth. So the type of fragmentation we can talk about is if you either h
ave non-consecutive
extents or less than full pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul Buxton" <spireite@.hotmail.com> wrote in message news:Oi4RdgQGFHA.2876@.TK2MSFTNGP12.phx
.gbl...
> Andrew J. Kelly wrote:
> I thought it may be index related (hence my mentioning the indexes). The t
able itself is 1Gb in
> size. Unfortunately, I can't change the indexing (MSCRM - and not a clust
ered index in sight!) as
> support will be removed if we do.
> Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE STAT
ISTICS only. Strangely,
> The MS CRM Performance Tuning Whitepaper suggests running the DBREINDEX w
hen they have no
> clustered indexes in the DB.
>
>|||You should contact the vendor and tell them to get their act together and
add a proper clustered index. Why do you say that Indexdefrag is the best
option? As we all have pointed out there is nothing you can do to
defragment the tables without a CI. This is true regardless of using
DBREINDEX or INDEXEDEFRAG. As for the non-Clustered indexes (NCI's) you are
still free to use DBREINDEX on them. If you have the maintenance window to
run this you will mostly find it will be faster and cleaner than
Indexdefrag.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:eKau2hQGFHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Sadly, I can't change the database in anyway or we lose support (system is
> MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I can do.
>
> John Bandettini wrote:

DBCC DBREINDEX not changing anything!

This is quite weird...
Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
rebuild anything in the indexes, and fragmentation remains (67%).
Am I doing something incredibly stupid here or is there an issue I need
to be aware of?
Table has non-clustered indexes on it only.
Any suggestions? Has anyone seen this behaviour before?
ThxHow big is the table? Do you have a clustered index on the table? If not
then you can rebuild til you are blue in the face and it will still stay the
same. One of the reasons why most every table should have a clustered
index.
--
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:ez$QWeOGFHA.444@.TK2MSFTNGP15.phx.gbl...
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need to
> be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx|||Paul
You really need a clustered index for reindex or rebuild to be effective in
removing fragmentation. Create a clustered index and then drop it. This will
reduce your fragmentation, and due to not having a clustered index your
tables will not fragment as quickly afterwards.
Hope this helps
John
"Paul Buxton" wrote:
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need
> to be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
>|||Andrew J. Kelly wrote:
> How big is the table? Do you have a clustered index on the table? If not
> then you can rebuild til you are blue in the face and it will still stay the
> same. One of the reasons why most every table should have a clustered
> index.
>
I thought it may be index related (hence my mentioning the indexes).
The table itself is 1Gb in size. Unfortunately, I can't change the
indexing (MSCRM - and not a clustered index in sight!) as support will
be removed if we do.
Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE
STATISTICS only. Strangely, The MS CRM Performance Tuning Whitepaper
suggests running the DBREINDEX when they have no clustered indexes in
the DB.|||John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
>
Sadly, I can't change the schema for the DB or create indexes as far as
I can tell. My understanding is that we lose our support if we do
(system is MS CRM).|||Sadly, I can't change the database in anyway or we lose support (system
is MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I
can do.
John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
> "Paul Buxton" wrote:
>
>>This is quite weird...
>>Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
>>rebuild anything in the indexes, and fragmentation remains (67%).
>>Am I doing something incredibly stupid here or is there an issue I need
>>to be aware of?
>>Table has non-clustered indexes on it only.
>>Any suggestions? Has anyone seen this behaviour before?
>>Thx|||Is it an index or the datapages you want to defrag? There is no way to defrag the data pages for a
table without a clustered index (heap table) with less than:
1 Export, truncate, import (or similar)
or
2 Create a clustered index and then drop it.
Also, if this is indeed a heap table, you need to think about that fragmentation really is for the
data pages. There is no linked list for the data pages in a heap table, so there's no jumping back
and forth. So the type of fragmentation we can talk about is if you either have non-consecutive
extents or less than full pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul Buxton" <spireite@.hotmail.com> wrote in message news:Oi4RdgQGFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
>> How big is the table? Do you have a clustered index on the table? If not then you can rebuild
>> til you are blue in the face and it will still stay the same. One of the reasons why most every
>> table should have a clustered index.
> I thought it may be index related (hence my mentioning the indexes). The table itself is 1Gb in
> size. Unfortunately, I can't change the indexing (MSCRM - and not a clustered index in sight!) as
> support will be removed if we do.
> Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE STATISTICS only. Strangely,
> The MS CRM Performance Tuning Whitepaper suggests running the DBREINDEX when they have no
> clustered indexes in the DB.
>
>|||You should contact the vendor and tell them to get their act together and
add a proper clustered index. Why do you say that Indexdefrag is the best
option? As we all have pointed out there is nothing you can do to
defragment the tables without a CI. This is true regardless of using
DBREINDEX or INDEXEDEFRAG. As for the non-Clustered indexes (NCI's) you are
still free to use DBREINDEX on them. If you have the maintenance window to
run this you will mostly find it will be faster and cleaner than
Indexdefrag.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:eKau2hQGFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Sadly, I can't change the database in anyway or we lose support (system is
> MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I can do.
>
> John Bandettini wrote:
>> Paul
>> You really need a clustered index for reindex or rebuild to be effective
>> in removing fragmentation. Create a clustered index and then drop it.
>> This will reduce your fragmentation, and due to not having a clustered
>> index your tables will not fragment as quickly afterwards.
>> Hope this helps
>> John
>> "Paul Buxton" wrote:
>>
>>This is quite weird...
>>Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
>>rebuild anything in the indexes, and fragmentation remains (67%).
>>Am I doing something incredibly stupid here or is there an issue I need
>>to be aware of?
>>Table has non-clustered indexes on it only.
>>Any suggestions? Has anyone seen this behaviour before?
>>Thx

Saturday, February 25, 2012

dbcc dbreindex

Hi All,

I have a following script:

DECLARE @.TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Rebuilding indexes ' + @.TableName + ' table'
DBCC DBREINDEX (@.TableName, ' ', 10)
FETCH NEXT FROM cur_reindex INTO @.TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO

I added as a step in the job. However, when I run it I get an error message:

Msg 2501, Sev 16: Could not find a table or object named 'table_1'. Check sysobjects. [SQLSTATE 42S02]

When I run
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

table_1 is the first on list. Any idea why I am getting this error message?Hi Inka

I almost replied to your previous post with this but didn't bother. I doubt you'll produce something as good as Tara's (http://weblogs.sqlteam.com/tarad/) script (scroll down and you'll see her sproc - there are more db maintenance ones too). As a minimum you should get some good pointers.

HTH|||I would bet that dbo does not own table_1.|||MCrowley,

You are right. It is user1 that owns that table. How can I fix the script so I can prefix the table name with user1?|||Concatenate with TABLE_SCHEMA + '.' field.|||Thank you very much for your help. It worked.

DBCC DBREINDEX

When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index shows
Table: 'tablename' (437576597); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 15721
- Extents Scanned.......................: 2027
- Extent Switches.......................: 2026
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
- Extent Scan Fragmentation ...............: 43.96%
- Avg. Bytes Free per Page................: 202.3
- Avg. Page Density (full)................: 97.
When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
but the first on remains unchanged. Any ideas? TIA PS: All the indexes are
nonclusteredThat is because index 0 is not an index at all. Index ID of 0 indicates
this is a HEAP. Heaps can not be reindexed. You should add a clustered
index to the table and that will change the ID from 0 to 1 and allow you to
defragment and reindex the table.
--
Andrew J. Kelly SQL MVP
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> shows
> Table: 'tablename' (437576597); index ID: 0, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 15721
> - Extents Scanned.......................: 2027
> - Extent Switches.......................: 2026
> - Avg. Pages per Extent..................: 7.8
> - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> - Extent Scan Fragmentation ...............: 43.96%
> - Avg. Bytes Free per Page................: 202.3
> - Avg. Page Density (full)................: 97.
> When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
> but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> are
> nonclustered|||Thanks for your response. A follow up question. Does a heap eventually get so
fragmented that it gets corrupt? Is there a way to defrag or reindex a heap
or do we need to go to clustered indexes. TIA
"Andrew J. Kelly" wrote:
> That is because index 0 is not an index at all. Index ID of 0 indicates
> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> index to the table and that will change the ID from 0 to 1 and allow you to
> defragment and reindex the table.
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> > shows
> >
> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> > TABLE level scan performed.
> > - Pages Scanned........................: 15721
> > - Extents Scanned.......................: 2027
> > - Extent Switches.......................: 2026
> > - Avg. Pages per Extent..................: 7.8
> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> > - Extent Scan Fragmentation ...............: 43.96%
> > - Avg. Bytes Free per Page................: 202.3
> > - Avg. Page Density (full)................: 97.
> >
> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> > are
> > nonclustered
>
>|||Fragmentation does not cause corruption but it can affect performance. The
only way to defrag a HEAP is to export all the data, truncate the table and
import it back in again. Or you can create a clustered index and then drop
it. But it will immediately start to get fragmented again with more
activity. Bottom line is that the recommendation is to have a clustered
index on each and every table. Have a look at this:
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Andrew J. Kelly SQL MVP
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> Thanks for your response. A follow up question. Does a heap eventually get
> so
> fragmented that it gets corrupt? Is there a way to defrag or reindex a
> heap
> or do we need to go to clustered indexes. TIA
> "Andrew J. Kelly" wrote:
>> That is because index 0 is not an index at all. Index ID of 0 indicates
>> this is a HEAP. Heaps can not be reindexed. You should add a clustered
>> index to the table and that will change the ID from 0 to 1 and allow you
>> to
>> defragment and reindex the table.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
>> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
>> > shows
>> >
>> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 15721
>> > - Extents Scanned.......................: 2027
>> > - Extent Switches.......................: 2026
>> > - Avg. Pages per Extent..................: 7.8
>> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
>> > - Extent Scan Fragmentation ...............: 43.96%
>> > - Avg. Bytes Free per Page................: 202.3
>> > - Avg. Page Density (full)................: 97.
>> >
>> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
>> > indexes
>> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
>> > are
>> > nonclustered
>>|||Also check out the whitepaper below which explains the options for removing
fragmentation and when it actually makes sense to remove it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u5KKBAYEGHA.524@.TK2MSFTNGP09.phx.gbl...
> Fragmentation does not cause corruption but it can affect performance. The
> only way to defrag a HEAP is to export all the data, truncate the table
> and import it back in again. Or you can create a clustered index and then
> drop it. But it will immediately start to get fragmented again with more
> activity. Bottom line is that the recommendation is to have a clustered
> index on each and every table. Have a look at this:
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
>> Thanks for your response. A follow up question. Does a heap eventually
>> get so
>> fragmented that it gets corrupt? Is there a way to defrag or reindex a
>> heap
>> or do we need to go to clustered indexes. TIA
>> "Andrew J. Kelly" wrote:
>> That is because index 0 is not an index at all. Index ID of 0 indicates
>> this is a HEAP. Heaps can not be reindexed. You should add a clustered
>> index to the table and that will change the ID from 0 to 1 and allow you
>> to
>> defragment and reindex the table.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
>> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first
>> > index
>> > shows
>> >
>> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 15721
>> > - Extents Scanned.......................: 2027
>> > - Extent Switches.......................: 2026
>> > - Avg. Pages per Extent..................: 7.8
>> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
>> > - Extent Scan Fragmentation ...............: 43.96%
>> > - Avg. Bytes Free per Page................: 202.3
>> > - Avg. Page Density (full)................: 97.
>> >
>> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
>> > indexes
>> > but the first on remains unchanged. Any ideas? TIA PS: All the
>> > indexes
>> > are
>> > nonclustered
>>
>|||Thanks for your response and I will check out the article.
"Paul S Randal [MS]" wrote:
> Also check out the whitepaper below which explains the options for removing
> fragmentation and when it actually makes sense to remove it.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u5KKBAYEGHA.524@.TK2MSFTNGP09.phx.gbl...
> > Fragmentation does not cause corruption but it can affect performance. The
> > only way to defrag a HEAP is to export all the data, truncate the table
> > and import it back in again. Or you can create a clustered index and then
> > drop it. But it will immediately start to get fragmented again with more
> > activity. Bottom line is that the recommendation is to have a clustered
> > index on each and every table. Have a look at this:
> >
> > http://www.sql-server-performance.com/gv_clustered_indexes.asp
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Greg" <Greg@.discussions.microsoft.com> wrote in message
> > news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> >> Thanks for your response. A follow up question. Does a heap eventually
> >> get so
> >> fragmented that it gets corrupt? Is there a way to defrag or reindex a
> >> heap
> >> or do we need to go to clustered indexes. TIA
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> That is because index 0 is not an index at all. Index ID of 0 indicates
> >> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> >> index to the table and that will change the ID from 0 to 1 and allow you
> >> to
> >> defragment and reindex the table.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> >> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> >> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first
> >> > index
> >> > shows
> >> >
> >> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> >> > TABLE level scan performed.
> >> > - Pages Scanned........................: 15721
> >> > - Extents Scanned.......................: 2027
> >> > - Extent Switches.......................: 2026
> >> > - Avg. Pages per Extent..................: 7.8
> >> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> >> > - Extent Scan Fragmentation ...............: 43.96%
> >> > - Avg. Bytes Free per Page................: 202.3
> >> > - Avg. Page Density (full)................: 97.
> >> >
> >> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
> >> > indexes
> >> > but the first on remains unchanged. Any ideas? TIA PS: All the
> >> > indexes
> >> > are
> >> > nonclustered
> >>
> >>
> >>
> >
> >
>
>|||Thanks for your response and I will check out the article.
"Andrew J. Kelly" wrote:
> Fragmentation does not cause corruption but it can affect performance. The
> only way to defrag a HEAP is to export all the data, truncate the table and
> import it back in again. Or you can create a clustered index and then drop
> it. But it will immediately start to get fragmented again with more
> activity. Bottom line is that the recommendation is to have a clustered
> index on each and every table. Have a look at this:
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> > Thanks for your response. A follow up question. Does a heap eventually get
> > so
> > fragmented that it gets corrupt? Is there a way to defrag or reindex a
> > heap
> > or do we need to go to clustered indexes. TIA
> >
> > "Andrew J. Kelly" wrote:
> >
> >> That is because index 0 is not an index at all. Index ID of 0 indicates
> >> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> >> index to the table and that will change the ID from 0 to 1 and allow you
> >> to
> >> defragment and reindex the table.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> >> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> >> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> >> > shows
> >> >
> >> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> >> > TABLE level scan performed.
> >> > - Pages Scanned........................: 15721
> >> > - Extents Scanned.......................: 2027
> >> > - Extent Switches.......................: 2026
> >> > - Avg. Pages per Extent..................: 7.8
> >> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> >> > - Extent Scan Fragmentation ...............: 43.96%
> >> > - Avg. Bytes Free per Page................: 202.3
> >> > - Avg. Page Density (full)................: 97.
> >> >
> >> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
> >> > indexes
> >> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> >> > are
> >> > nonclustered
> >>
> >>
> >>
>
>

Sunday, March 11, 2012

DBCC INDEXDEFRAG script not working

Wednesday, March 7, 2012

DBCC DBREINDEX not changing anything!

DBCC DBREINDEX not changing anything!

DBCC DBREINDEX not changing anything!

Saturday, February 25, 2012

dbcc dbreindex

DBCC DBREINDEX

DBCC DBREINDEX

Friday, February 24, 2012

DBCC DBREINDEX

DBfile usage

Blog Archive

About Me