SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance issue due to index fragmentation.


Performance issue due to index fragmentation.

Author
Message
striker-baba
striker-baba
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 541
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.
homebrew01
homebrew01
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19884 Visits: 9222
What have you tried so far ? Have you searched for any information on the subject ?



striker-baba
striker-baba
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 541
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
chrisfradenburg
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 2079
If the queries aren't completing I would check for blocking using Activity Monitor.
Richard M.
Richard M.
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2934 Visits: 2688
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
striker-baba
striker-baba
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 541
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
Sanjay Rohra
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 307
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
chrisfradenburg
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 2079
If that process is blocking others you'll need to either find out why it's running so long or stop it.
homebrew01
homebrew01
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19884 Visits: 9222
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.
Richard M.
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2934 Visits: 2688
... just realized that when I saw your post... Smile
In any event, seems the OP has other issues at hand here....

_______________________________________________________________________
For better assistance in answering your questions, click here
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search