May 31, 2007 at 2:34 pm
The users are complaining of general slowdowns in my environment. I enocuntered database locks, timesouts, and connection errors regarding these slowdowns, but sometimes the system works normally. I want to make my application work faster, not sure where to start the process to figure out what excatly the problem is causing. I tried looking at the long runnig queries throug profiler assuming to find something wrong in coding.
Please guide me how to start my job here to discover where exactly the problem is? Its urgent
June 1, 2007 at 4:27 am
Profiler is a very good place to start. If you run it during the slow downs you should be able to identify the source of the long duration locks
Also make sure that you indexes are not fragmented and that your statistics are up to date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2007 at 2:57 pm
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 4, 2007 at 5:12 am
Rule one of support...find out what has changed. Has someone just updated some of the SQL?
Also, check for any errors in the SQL server logs. If you see any errors for 'not enough contiguous memory available' it could explain your problem.
Regards Peter.
June 5, 2007 at 7:33 am
1) Search these forum archives, as well as the web and the msdn sql forums. There is a wealth of information on performance analysis and tuning.
2) If it is really important, hire a professional to give you a quick performance review. That is MUCH better than trying to figure out something you were not trained for for days/weeks trying to get a handle on it yourself - while your system continues to be non-performant. There are a BUNCH of things a pro can do in the first few hours he/she is monitoring your system.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2007 at 9:29 am
Great advice !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply