Our sqlserver's performance is from time to time very poor (queries that should execute under a second sometimes take 30 seconds and more which makes the client to time out). It happens more frequently between 9:00 and 10:00 AM, but it also can happen at other moments (eg yesterday around 3:00 PM)

• Processor load in the taskmanager is only a couple of percents
• The activity monitor doesn't show any locks
• No backup or other 'heavyload' processes are running when this occurs
• The profiler doesn't seem to make me any wiser

My question is : which tools do I have available, other than those I mentioned above, to track down this problem and/or when using these tools, what specific parameters need my special attention ?

 

Welcome to the forum!! 1) Don't use taskmanager. Use Performance Monitor. 2) Use Profiler. Find long queries. Copy and paste into Management Studio. Take a look of execution plan. If you can't read a execution plan, run DTA to find if any indexes are necessary. Do this in testing database. 3) Test the query with and without new indexes. If duration is significant less than before, you can apply those indexes in production. 4) Repeat 2) and 3) for each long queries. Remember this: if you apply new indexes, to improve one query, may be the new indexes improve also others queries. So, smalls moves and test again.

Hi Pascal,

The attached query will show you all statements that are currently running so you can run that while you are in the middle of a "rough period" but I STRONGLY recommend you to install the Performance Dashboard. It's absolutely excellent for real-time troubleshooting and works in SQL Server 2005 SP2 and upwards. You can download it here: http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en.

Everything that you say - except the part about seeing no locks in Activity Monitor - seem to imply that you have some blocking problems: the problem is intermittent AND typically occurs during an hour when, in many scenarios, most applications have a peak of users coming in (9-10 o'clock in the morning), very low CPU utilization etc.

You might want to enabled "Blocked Process Threshold" (see Books Online) and then do some Profiler tracing again - if for no other reason than to rule out blocking.

HTH!

/Elisabeth