CODEWITHSUNDEEP
sql-server-2008
MasterMeNow
MasterMeNow

Reputation: 103

How to check performance of a SQL Server database?

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

enter image description here

Upvotes: 7

Views: 48276

Answers (3)

Robert Cutajar
Robert Cutajar

Reputation: 3701

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

Upvotes: 4

Ian P
Ian P

Reputation: 1724

Sounds like it may be disk thrashing that the problem. Make sure your SQL endine is configuered (max memory) to less than is (freely) available on your system.

Is it a VM? Is the database large - SQL will continue to grab memory if its there - even if its hard disk memory - which is inherently slow

Upvotes: 0

Siva
Siva

Reputation: 2811

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

enter image description here

  • 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 enter image description here
  • Use Database Tuning Advisor to see the recommendations (http://www.youtube.com/watch?v=gjT8wL92mqE)

Upvotes: 2

Related Questions