Index rebuild again - 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

General SQL Server Forums

New to SQL Server Programming

Index rebuild again

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-18 : 09:31:29

Hi All,
I found the indexes to be rebuild from the query
and rebuilded. around 133 indexes
So to verify I ran same query to see now the query should show no results as indexes been rebuilded.
But I got some few indexes (49 indexes out of 133 ) again to Rebuild.
Can any one explain why?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 09:34:53

Suspect it's due to the query you are running.
Difficult to say more than that with the information given.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-18 : 09:41:58

I used below query and ran it for each index in cursor

SELECT A.* FROM
(SELECT s.name as schemaname,t.name tablename,
ix.name as indexname,
CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'Rebuild' ELSE 'ReOrganize'
END as 'B-O',
ps.avg_fragmentation_in_percent
FROM sys.indexes AS ix INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL)A
WHERE [B-O] ='Rebuild'
Order by A.[B-O]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 09:49:11

It should only get indexes where fragmentation is greater than 10 - run it to see how many there are and what the value is.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-18 : 09:52:58

Hi Nigel,
I ran it and got 133 indexes from that query whose fragementation is > 10
and from those I picked whose fragmentation ? 40 ( as only those needs to be rebuild)

Now when i ran for those 133
and check again i got 49 to rebuild again.

I want to know why I am getting even after rebuild has done on those 49 index?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 10:13:55

Because the rebuild didn't reduce the fragmentation?
Is the database being used when you run this?
Try picking one and seeing if a rebuild reduces the fragmentation.

Actually the reorganise might well not bring the fragmentation down below 10%.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-07-18 : 10:51:18

Might also add WHERE index_depth > 2

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-07-18 : 15:13:53

How many pages do those indexes contain?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-07-18 : 18:17:44

I think Jeff and I are on the same path without explaining ourselves.

A small index won't necessarily show improvement by rebuilding. And we don't care. If there's only a few thousand pages, a scan is good enough and sometimes the index can't be optimized anyway.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-19 : 02:30:03

Yeah Russell ,
Those 49 indexes have very less page count ( less than 20 )
and others were more than 1000.
So I beleive those would not get defragmented.Is it?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-07-19 : 12:21:25

Yeah, don't worry about indexes that small.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-07-19 : 14:27:08

Yes - Russel and I were on the same path...

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-20 : 06:10:06

Thanks Russell and Jeff.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-20 : 06:22:46

Jeff and Russsel
I beleive you both are DBA so you could help me again,

I was tuning SQL Server DB
and hence i Reduilded the indexes.It helped me performance.

Now, I ran Actual Exceution PLan for a SP and found two queries
have Query Costs 38% and 21 % in it

1) Query whose cost is 21 % have Lookup cost 94 %
( It is insert Query )

2) Query whose cost is 38 % have Clustered Index Scan of 36 % on 2 different tables.
(It is update Query )

How can we analyse/read the plan and how can the cost be reduced?
Any suggestions would be helpful.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-20 : 07:47:59

To resolve bookmark lookup, use coverying index ( include columns which are used by query and are not a part of sort key)
http://connectsql.blogspot.com/2011/01/sql-server-bookmark-lookups-query.html

Create proper Non clustered index to avoid Clustered Index Scan. On Which columns NC should be created. Move your mouse to clustered index scan node and it will give you information regarding predicates and seeks. Create index on these columns and for include columns, Press F4 and in properties, check Output Columns, move these columns to include section.

--------------------------
http://connectsql.blogspot.com/

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-23 : 06:10:48

When I analysed the Execution plan
I found there is KeyLookup Cost (94%) on Time Table
so I beleive I need to create Covering Index on Time Table.
When I found the columns which should be in INCLUDE ( i found it form OUTPUT LIST) are,

[Time].TaskUID,
[Time].StatusCode,
[Time].NegotiatedChargeAmt,
[Time].HomeNegChargeRegExtAmt,
[Time].OperNegChargeRegExtAmt.

As there are clustered and non clustered Indexes on table "Time" ( already created), hence can one create covering index
________________________
Non Clustered indexes on table "Time" are :-
__________________________
ActivityCode
BatchID
BillingNum
ProjectSiteURN
ProjectCode, TimeEntryDate
ResourceID, ResourceSiteURN,StatusCode, TimeEntryDate
StatusCode
______________________________
clustered, unique ON TimeID
______________________________

How can be covering index be created
and which Non Clustered indexes to be dropped after creating covering index?

Can Any one suggest

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources