reorganize index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

reorganize index

Author

Topic

tboonleong
Starting Member

24 Posts

Posted - 2008-01-18 : 01:03:54

DECLARE @strIndex_Name varchar(50)
DECLARE @strTable_Name varchar(50)

SET @strTable_Name = 'DIM_AR_CLASS'
SET @strIndex_Name = 'IX_DIM_AR_CLASS_1'

PRINT @strTable_Name
PRINT @strIndex_Name

ALTER INDEX @strTable_Name ON @strIndex_Name REORGANIZE

Result msg:

DIM_AR_CLASS
IX_DIM_AR_CLASS_1
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@strTable_Name'.

If i change the statement as below then it work fine.
ALTER INDEX IX_DIM_AR_CLASS_1 ON DIM_AR_CLASS
REORGANIZE

Anyone know why ?Does this alter index statement not support variable during execution time?
Appreciate any help.
Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-18 : 12:17:09

quote:
Originally posted by tboonleong

Does this alter index statement not support variable during execution time?

Thats correct. You cannot use variables with ALTER INDEX. You can build a dynamic sql and EXEC it.

EXEC('ALTER INDEX ' + @strTable_Name + ' ON ' + @strIndex_Name + ' REORGANIZE')

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tboonleong
Starting Member

24 Posts

Posted - 2008-01-21 : 02:31:37

I had tried your suggestion and it work fine.
But when I check the related index been reorganize , and found that the related index still have defragmentation where the avg_fragmentation_in_percent still more then zero.

Thus, I rebuild the related index but the avg_fragmentation_in_percent still more zero. I even tried it for rebuild a few times but the avg_fragmentation_in_percent still remain unchange.

May I know why the rebuild index and reorganize index not able to minimize the avg_fragmentation_in_percent until zero?

Any others way to help to minimize the avg_fragmentation_in_percent to zero?

Thanks again for your helping.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-21 : 03:30:14

Is the fragmentation percent you've posted for index level 0 ?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

tboonleong
Starting Member

24 Posts

Posted - 2008-01-21 : 04:38:12

Hi jackv,
Did index level affect the rebuild and reoganize index?
May I know how to determine the index level?
Thanks in advance.

ns_nataly
Starting Member

13 Posts

Posted - 2008-01-21 : 07:52:07

-- ALTER INDEX @strTable_Name ON @strIndex_Name REORGANIZE
Syntax of ALTER INDEX :
ALTER INDEX { index_name | ALL }
ON <object> ...

In your statement swap variables position :
ALTER INDEX @strIndex_Name ON @strTable_Name REORGANIZE

Natalia

tboonleong
Starting Member

24 Posts

Posted - 2008-01-21 : 20:33:13

I had swapped the variables position as per suggested before that.
The issue still same as I had menation at the 3rd posting above.
Anyone have any idea.
Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-21 : 20:42:51

What did 'dbcc showcontig(tab_name) with all_indexes' say?

tboonleong
Starting Member

24 Posts

Posted - 2008-01-22 : 06:06:22

DBCC SHOWCONTIG scanning 'DIM_TIME' table...
Table: 'DIM_TIME' (614345303); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 88
- Extents Scanned..............................: 18
- Extent Switches..............................: 17
- Avg. Pages per Extent........................: 4.9
- Scan Density [Best Count:Actual Count].......: 61.11% [11:18]
- Logical Scan Fragmentation ..................: 71.59%
- Extent Scan Fragmentation ...................: 38.89%
- Avg. Bytes Free per Page.....................: 80.4
- Avg. Page Density (full).....................: 99.01%
DBCC SHOWCONTIG scanning 'DIM_TIME' table...
Table: 'DIM_TIME' (614345303); index ID: 2, database ID: 6
LEAF level scan performed.
- Pages Scanned................................: 37
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 7.4
- Scan Density [Best Count:Actual Count].......: 100.00% [5:5]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 60.00%
- Avg. Bytes Free per Page.....................: 153.1
- Avg. Page Density (full).....................: 98.11%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-22 : 09:42:54

The obejct(s) have less than 100 pages in them, I wouldn't worry about them. Most defragmentation scripts will skip objects with less than 1000 pages.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources