September 23, 2009 at 11:37 pm
Hi,
We have 260MB database. We keep issuing one or two update statements for every one day or two days.
I want to know if there is
any fragmentation or other issues so that i can shrink the database.
Because our application is working slow sometimes and its taking time to
open next page. Can you let me know how to proceed.
Regards
Meher
September 24, 2009 at 4:26 am
One of the commands that you can run is
dbcc showcontig
this reports on fragmentation at table level
you ideally want
Scan Density [Best Count:Actual Count] to be closer to 100% as possible
Scan Fragmentation, closer to 0% the better.
you can also use the system view
sys.dm_db_index_physical_stats
look it up in BOL on how to use it.
Once you have identified indexes that are fragmented.
you can either drop and recreate them or do
ALTER INDEX REORGANIZE - replacement for DBCC INDEXDEFRAG
or
ALTER INDEX REBUILD - replacement for DBCC DBREINDEX
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 24, 2009 at 6:41 am
I'd strongly suggest taking SilverFox's second suggestion and use sys.dm_index_physical_stats. There's more information to be had in a clearer fashion than from the older showcontig statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2009 at 1:23 pm
chances are you have diff problem, if you DB is only 260 mb then fragmentation is more ten likely not your problem. Sql you should be able to handle that with out a problem....fragment or not.....What kind of hardware are running
October 1, 2009 at 5:45 am
Hi,
Hardware Configuration:
Intel(R) Xeon(TM) CPU 3.00GHz
2.99GHz, 4GB of Ram
E drive: 232 GB free:101 GB
F drive : 220 GB free:98 GB
Regards
Meher
October 1, 2009 at 6:18 am
chiru_meher (10/1/2009)
Hi,Hardware Configuration:
Intel(R) Xeon(TM) CPU 3.00GHz
2.99GHz, 4GB of Ram
E drive: 232 GB free:101 GB
F drive : 220 GB free:98 GB
Regards
Meher
I would take a look at the application that is running, considering the amount of free space. shrinking would serve no purpose. performance slowdown can be down to a number of different issues.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 9:47 am
Hi,
Uploading the Report containing Information About fragmentation.
Attached the file
October 1, 2009 at 3:02 pm
looks like a number of issues going on...by guess,
1.st you have many heap tables.these are fragmented, you need add a clustered index in order to fix.
2. do an order by page count anything less then say 100 pages dont even bother.
3. your biggest problem at the end of the day is you don't have enough hardware.
4. if you are running the application on this box you really have a problem...
5. the only suggestion i can give you is set min and max memory.. with the amount of ram you have I would set it around 2 gig min and 3 gig max....cahnces are this will help but not fix your problems....you really need more RAM and more CPU
good luck
October 7, 2009 at 8:57 am
...so that i can shrink the database. ...
Shrinking the database will lead to fragmentation.
Wilfred
The best things in life are the simple things
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply