Performance issue due to index fragmentation.

Question

Post reply

Performance issue due to index fragmentation.

striker-baba

SSCrazy

Points: 2170
More actions
April 23, 2010 at 7:58 am

#144252

Hi all,
I have a table 'A' that gets new data inserted daily from different tables.
Today, all the jobs that populate the table 'A' are running very slow.
and If I try to query the table using select top 1 * from table'A' is also taking to much time. And the table had 5 indexes on it.
I think the table had its indexes freagmented.
I want to know how to check the fragmentation of all the indexes on that specific table and the ways to solve this problem.
Thanks in advance.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

What have you tried so far ? Have you searched for any information on the subject ?

striker-baba SSCrazy Points: 2170 More actions · Answer 2

I tried to query the fragmentation stats on that table.

using

SELECT

a.index_id,

name,

avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(' DB '),OBJECT_ID(' Table'A' '),NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON

a.object_id = b.object_id AND

a.index_id = b.index_id;

but the query is taking tooo long.....

I checked activity monitor. and it has IX locks on that table and the wait time is 2955150046.

Is there any other place where I can look for some information.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 3

If the queries aren't completing I would check for blocking using Activity Monitor.

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

You might want try running that dmv with the 'LIMITED' option:

FROM sys.dm_db_index_physical_stats (DB_ID(' DB '),OBJECT_ID(' Table'A' '),NULL, NULL, 'LIMITED')

as it will have better performance, specially if it is a big table.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

striker-baba SSCrazy Points: 2170 More actions · Answer 5

I did check in activity monitor for locks by process.

I found one of the job1 which inserts the data into table'A' is holding a

IX lock on the table.

this job1 is causing all the jobs from job2 to job 10 to wait.

but job1 has grant status.

So, Do I kill this job1 ?

but still job2 to job10 are going to perform the same insertions on the tableA.

So, Is that problem due to job or due to table A?

Sanjay Rohra Ten Centuries Points: 1158 More actions · Answer 6

If you run all the jobs that are going to insert data into Table A in parallel, there will definitely be blocking and it might also lead to deadlock causing your jobs to be terminated by the SQL engine. If possible, execute those jobs sequentially. Once done, check for fragmentation and do a re-org or rebuild of indexes.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 7

If that process is blocking others you'll need to either find out why it's running so long or stop it.

homebrew01 SSC Guru Points: 55539 More actions · Answer 8

Richard M. (4/23/2010)
You might want try running that dmv with the 'LIMITED' option:
FROM sys.dm_db_index_physical_stats (DB_ID(' DB '),OBJECT_ID(' Table'A' '),NULL, NULL, 'LIMITED')
as it will have better performance, specially if it is a big table.

I was going to suggest that too, but I checked BOL and it says LIMITED is the defualt when NULL is specified, so it looks like that's already the case.

Richard M. SSCertifiable Points: 7401 More actions · Answer 9

... just realized that when I saw your post... 🙂

In any event, seems the OP has other issues at hand here....

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

homebrew01 SSC Guru Points: 55539 More actions · Answer 10

striker-baba (4/23/2010)
I tried to query the fragmentation stats on that table.
using
SELECT
a.index_id,
name,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(' DB '),OBJECT_ID(' Table'A' '),NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON
a.object_id = b.object_id AND
a.index_id = b.index_id;
but the query is taking tooo long.....
I checked activity monitor. and it has IX locks on that table and the wait time is 2955150046.
Is there any other place where I can look for some information.

Can you run that during off-hours ?

If you're doing a lot of inserts, maybe you should just rebuild indexes anyway ?

Sanjay Rohra Ten Centuries Points: 1158 More actions · Answer 11

Sanjay Rohra (4/23/2010)
If you run all the jobs that are going to insert data into Table A in parallel, there will definitely be blocking and it might also lead to deadlock causing your jobs to be terminated by the SQL engine. If possible, execute those jobs sequentially. Once done, check for fragmentation and do a re-org or rebuild of indexes.

Just to add to my point, since there are multiple inserts daily, your indexes will definitely fragment out. You will have to rebuild them anyway. You can still cross-check the level of fragmentation by querying the sys.dm_db_index_physical_stats DMV, but do that only when there are no Inserts currently happening in your table.

striker-baba SSCrazy Points: 2170 More actions · Answer 12

Thanks to all for the replies.

I ran the query

SELECT

a.index_id,

name,

avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(' dbname '),OBJECT_ID(' table a '),NULL, NULL, 'limited') AS a

JOIN sys.indexes AS b ON

a.object_id = b.object_id AND

a.index_id = b.index_id;

and I have 5 indexes on my table

and all 3 indexes are 96% fragmented and 2 of them are 66% fragmented.

I am planning to archive the data and rebuild the indexes on that table.

DO you guys think this will work out .... or Any suggestions.

homebrew01 SSC Guru Points: 55539 More actions · Answer 13

striker-baba (4/23/2010)
Thanks to all for the replies.
I ran the query
SELECT
a.index_id,
name,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(' dbname '),OBJECT_ID(' table a '),NULL, NULL, 'limited') AS a
JOIN sys.indexes AS b ON
a.object_id = b.object_id AND
a.index_id = b.index_id;
and I have 5 indexes on my table
and all 3 indexes are 96% fragmented and 2 of them are 66% fragmented.
I am planning to archive the data and rebuild the indexes on that table.
DO you guys think this will work out .... or Any suggestions.

That should be an improvement.