How to check performance of a SQL Server database?

Question

I restarted my dedicated server this morning. as a result my SQL Server was very responsive. I could query 1.500.000 records table, results returns within couple secs.

after a while I see my CPU started growing, and it's not so fast anymore 30-50 secs for the same queries.

I checked sp_who2, no blocking transactions. I rebuilt the index. No changes still slow

What is the best way to troubleshoot this kind of behaviour? How to improve performance?

Here is my task manager

Answer 1

I'm sniffing a memory pressure problem. The page buffer may be full or some other memory constraint get's hit. Start with monitoring essential system performance counters like:

• Cpu load
• Page fault
• Disk r/w queue
• Disk r/w time

Capture them from the moment you restart the machine and then plot it with excel or some other graphing tool. Add some SQL counters to the mix. There's many resources online if you search for "sql server performance monitor coutners". Try the perf counters mentioned here: http://www.sql-server-performance.com/2007/performance-monitor-general/

It is essential to observe the progress in time and correlate it with other indicators and the percieved performance.

Another thing to look for would be plan recompilations. Check for SP:Recompile events in Profiler trace and see if any correlate with performance degradation. - ref https://support.microsoft.com/en-us/kb/243586

Answer 2

• Under Server properties, You can manually assign how many cpu's you want sql to use

• Ensure you have all windows updates, service packs applied
• Without running your database application, Only with SQL Server running check how much memory is being used
• Best Approach is to run SQL profiler for performance template
• Use Database Tuning Advisor to see the recommendations (http://www.youtube.com/watch?v=gjT8wL92mqE)