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.
Author |
Topic |
QAZAFI
Yak Posting Veteran
50 Posts |
Posted - 2008-06-17 : 22:07:57
|
Hello AllI am facing a huge performance Issues with one of my database which is size about 60gb.This database has lot of inserts and delition and I am using Clustered indexes on my all table and when I Monitered that after every week the my indexes are fragmented up to 80% which in result has lot of performance impact on my database. One thing more when I watch the performnce moniter of my Server it is always using about 90% CPU. I tried a lot but could not figure out what to do. Any help be really be apprecited. My users are facing big delay in order to post their GL batches.Thanks in advanceQazafi |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-17 : 22:18:22
|
As your index are fragmented , you need to rebuild all indexes in offline hours. |
|
|
QAZAFI
Yak Posting Veteran
50 Posts |
Posted - 2008-06-18 : 00:30:31
|
I did that lot of time but after every single week it will again fragmented any work around that will be appreceited |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-06-18 : 01:46:01
|
Are you running any shrink db jobs , this can cause fragmentationJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-18 : 08:55:13
|
Then you have to rebuild highly fragmented index every night. |
|
|
CanadaDBA
583 Posts |
Posted - 2008-06-18 : 09:37:15
|
You say there are lots of inserst and deletions; then why you are using clustered indexes? "Avoid clustered indexes on frequently updated columns: Since nonclustered indexes depend upon clustered indexes, if the columns comprising the clustered index are frequently updated, this will cause the row locators stored in the nonclustered indexes to also have to be updated. This leads to increased performance costs for all queries associated with these columns as locking occurs."from: http://articles.techrepublic.com.com/5100-10878_11-5148062.htmlCanada DBA |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-18 : 11:57:48
|
If you have lots of inserts/deletes, you shouldn't have too many indexes in OLTP environent like CanadaDBA said it gets fragmented. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-18 : 22:53:36
|
You should rebuild index weekly in this case, and check cpu usage after reindex. |
|
|
pootle_flump
1064 Posts |
Posted - 2008-06-19 : 03:24:38
|
Please can you post the full DDL (incl all indexes) for the table and the fragmentation values you are using?Please can you be more specific about exactly which queries are affected? I imagine these will be range\ CI scans but just to be sure....quote: Originally posted by QAZAFI which in result has lot of performance impact on my database......My users are facing big delay in order to post their GL batches.
|
|
|
|
|
|