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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL Server Performance Issues

Author  Topic 

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-06-17 : 22:07:57
Hello All

I 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 advance

Qazafi

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.
Go to Top of Page

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
Go to Top of Page

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 fragmentation

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Go to Top of Page

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.
Go to Top of Page

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.html



Canada DBA
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.

Go to Top of Page
   

- Advertisement -