March 10, 2010 at 10:17 am
Hello guys,
I am pretty new to Database Administration...I have been working on Performance tuning from couple of days but couldn't solve the issues....During peak hours my CPU usage becomes 100%
Here are some of the steps which i took
1) executed the DBCC CHeckDB Commnd
2) executed the DBCC CheckConstraints Command
3) Executed DBCC CHeckCatalog
3)I wrote a script to rebuild all the indexes
4) Defragmented all the indexes
5) Updated Statistics using Update Statistics Command
Can u suggest me to look for any other bottlenecks and how to solve those bottlenecks???
Regards,
Sandeep
March 10, 2010 at 10:26 am
You might read this http://msdn.microsoft.com/en-us/library/cc966540.aspx
In terms of the CPU, 100% isn't bad. The server uses all resources to work on queries when it needs them. If it's sustained, then you have a bottleneck.
#s 1, 2, 3 don't help. They check for corruption. 4 might help, if indexes are the issue, but you need to figure out what the bottleneck is.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 10, 2010 at 11:03 am
I ran the following query
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
and the results are
free pages free space in MB
49792 389.0000
which means there is a free space of only 389.000 MB
I believe the bottleneck is in Temporary Database....I m not sure though......
Can you guys give any suggestions on this???
Your help is highly appreciated
Regards,
Sandeep
March 10, 2010 at 12:07 pm
Finally i figured out the problem...its because of Blocking and Locking issues.......But i don't have much idea how to solve those issues.....Can u guys throw some light on it???
Regards,
Sandeep
March 10, 2010 at 12:14 pm
Need more info Check what SPID is blocking, and see which query, see why it is taking lock on the objects.
March 10, 2010 at 1:04 pm
sandip.vs (3/10/2010)
Finally i figured out the problem...its because of Blocking and Locking issues.......But i don't have much idea how to solve those issues.....Can u guys throw some light on it???Regards,
Sandeep
The blocking and locking can be resolved by performance tuning your queries. Designing appropriate indexes will also help in this scenario. You need to know what query is causing the problem and then go from there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply