so that means all these three factors should be true together i mean :-

avg_fragment_size_in_pages >= 8 AND fragmentation > 10 AND page_count >=1000

please correct me If i am wrong?

Hi Dave,

Can you tell me how do you get the information of data inserted/updated/deleted since last update statistics...is there any DMV or table to look out..

Hi Gail,

quick question - I have the doubt.. so you said indexes having 1000 pages or more than that should only be considered..

along with this, dont we have to take...

if you want you can try using Common Table Expression (CTE) method.. ans see how long it will take

WITH CITY_INFO(stateid ,cityid,districtid,distpt,citypt

(

select stateid ,

...

if that index is using for read (I mean select statement) then by defining the filfactor to 100% would be he best in terms of performance as far as I...

can you create a nonclustered index on temp table

like

CREATE NONCLUSTERED INDEX NCIDX_KW_ID

ON #temp_6675(KW_ID)

drop the index at the end of the statement. and try whether it works...

also refer...

One more thing I missed...if the index page size is too small, even if we alter the index with REBUILD or REORGANIZE, it will neglected, right?

if then, how big the...

Thanks you so much Matt...now I got things little clearer...

Just my curious to know:- as we know that if the avg_fragmentation_in_percent is between 10% and 30% Reorganize will be the...

Have you ever tried using INNER JOIN..if possible can you try it whether it works.

l

for example :-

Insert into

select distinct kw_id

from StageDB.dbo.tt_wf_49b955a9_gtsang_2BC a -- 10000o rows...

just a curious to know. is your table contains any indexes clustered or nonclustered?

there is an article in SQL 2005 BOL...

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2625ba79-2966-4df8-9cf8-185beead5c83.htm..

I am not sure how to use it becuase I...

hi Mohit - in your previous reply you mentioned index_depth some part of your previous reply:-

I execute:

SELECT * FROM sys.dm_db_index_physical_stats(db_id, NULL, NULL,NULL,'LIMITED')

This gives me information for a Single Database, all...

thanks Mohit...thanks much for sharing thoughts..

one final little question....you mentioned index_depth...what will be be decision point whther index is fragmented in terms of index_depth...

thanks

Joseph

ok ...but can you please specify the term 'avg index fragmentation percent'..let say how do we find when we run as follows:

select * FROM sys.dm_db_index_physical_stats(db_id(),object_id,index_id,NULL,NULL)....

can you please clarify regarding table...

ok Mohit, i think I got it what you mean...the column 'avg_fragmentation_in_percent' right?....but other than this and index page number, is there any third factor to determine fragment level of...

Hi Mohit,

Can you please mention from which column in sys.dm_db_index_physical_stats functions we get that information the one you mentioned (avg fragmentation level...)