December 13, 2012 at 10:11 pm
Hi All,
Today i was working on index
i created on table
CREATE TABLE EMPLOYEE
(
E_ID INT ,
D_ID INT NOT NULL,
DES_ID INT NOT NULL,
NAME VARCHAR(100) NOT NULL,
SALERY INT NOT NULL
)
and i created
one clustered and two on -cluster index on employee using GOI.
then i inserted 6000 record using
INSERT INTO EMPLOYEE VALUES (1,11123,7,'ddddddddddddddd',20)
go 6000
then t checked using below script that what is the average index fragmentation levels using below script i found that there are three indes % of fragmentation
SELECT DB_NAME(PS.database_id) AS dbName,
S.name AS SchemaName,
O.name AS TableName,
b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
INNER JOIN sys.objects O ON PS.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND b.is_hypothetical = 0 -- Only real indexes
AND O.type_desc = 'USER_TABLE' -- Restrict to user tables
AND PS.page_count > 8 --- ignore tables less tha 64K
ORDER BY ps.avg_fragmentation_in_percent DESC
Then it was LESS THEN 30% so i did
ALTER INDEX nc_index
ON EMPLOYEE REBUILD
then when i again execute the above script for average index fragmentation levels for finding the |%
it was showing only two index ---- What it did and where is my threed index which i created ?
Rajesh_only_TestdboEMPLOYEEnc193.3333333333333
Rajesh_only_TestdboEMPLOYEEclu66.6666666666667
Next i executed
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS TableIndexName
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat
INNER JOIN sys.indexes i
ON i.OBJECT_ID = phystat.OBJECT_ID AND i.index_id = phystat.index_id
WHERE
phystat.avg_fragmentation_in_percent > 20
AND OBJECT_NAME(i.OBJECT_ID) IS NOT NULL
ORDER BY phystat.avg_fragmentation_in_percent DESC
it is showing three index
EMPLOYEEnc_index
EMPLOYEEnc1
EMPLOYEEclu
EMPLOYEENULL
December 13, 2012 at 11:25 pm
rajeshjaiswalraj (12/13/2012)
Then it was LESS THEN 30% so i didALTER INDEX nc_index
ON EMPLOYEE REBUILD
then when i again execute the above script for average index fragmentation levels for finding the |%
it was showing only two index ---- What it did and where is my threed index which i created ?
Probably filtered out by the filters on the query. Specifically
AND PS.page_count > 8 --- ignore tables less tha 64K
That doesn't ignore tables below 8 pages, it ignores indexes with less than 8 pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2012 at 12:25 am
Hi Gila,
Thanks for the reply.
I removed the below line but i got the same result.
AND PS.page_count > 8 --- ignore tables less tha 64K
Then i make some changes in my script
WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
i removed 20 and i make it 0 - after this i got correct result .....
Thanks for your help.....
December 14, 2012 at 3:11 am
Bear in mind, if you're writing a script to identify indexes that need rebuilding, you want those filters in. You may well want the size filter to be larger than 8 pages too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply