| Author |
 Topic  |
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
 Posted - 07/18/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/18/2012 : 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. |
Edited by - nigelrivett on 07/18/2012 09:35:09 |
 |
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
Posted - 07/18/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/18/2012 : 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
Yak Posting Veteran
88 Posts |
Posted - 07/18/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/18/2012 : 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. |
Edited by - nigelrivett on 07/18/2012 10:15:11 |
|
|
|
russell
Pyro-ma-ni-yak
USA
4981 Posts |
Posted - 07/18/2012 : 10:51:18
|
| Might also add WHERE index_depth > 2 |
|
|
|
jeffw8713
Aged Yak Warrior
USA
696 Posts |
Posted - 07/18/2012 : 15:13:53
|
| How many pages do those indexes contain? |
|
|
|
russell
Pyro-ma-ni-yak
USA
4981 Posts |
Posted - 07/18/2012 : 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
Yak Posting Veteran
88 Posts |
Posted - 07/19/2012 : 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
USA
4981 Posts |
Posted - 07/19/2012 : 12:21:25
|
| Yeah, don't worry about indexes that small. |
|
|
|
jeffw8713
Aged Yak Warrior
USA
696 Posts |
Posted - 07/19/2012 : 14:27:08
|
| Yes - Russel and I were on the same path... |
|
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
Posted - 07/20/2012 : 06:10:06
|
Thanks Russell and Jeff.
|
|
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
Posted - 07/20/2012 : 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
Pakistan
864 Posts |
Posted - 07/20/2012 : 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/ |
Edited by - lionofdezert on 07/20/2012 08:18:39 |
|
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
Posted - 07/23/2012 : 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 |
|
|
| |
Topic |
|
Index rebuild again
