All geek questions in one place

How to check SQL Server database performance?

I restarted the dedicated server this morning. as a result, my SQL Server was very responsive. I could query 1,500,000 table entries, the results are returned within a few seconds.

after a while I see that my processor began to grow, and it is not so fast for 30-50 seconds for the same requests.

I checked sp_who2, no lock transactions. I restored the index. The changes have not slowed down yet.

What is the best way to eliminate this kind of behavior? How to increase productivity?

Here is my task manager

enter image description here

+6
source share
3 answers

I sniff out the memory shortage problem. The page buffer may be full or some other memory limitation may be corrupted. Start by monitoring the main system performance counters, for example:

  • Download Cpu
  • Page Error
  • R / w drive queue
  • Drive time / time

Grab them from the moment you restart the machine, and then draw it using excel or another graphic tool. Add some SQL counters to the mix. There are many resources on the Internet if you are looking for "sql server performance coutners". Try the permanent counters mentioned here: http://www.sql-server-performance.com/2007/performance-monitor-general/

It is important to monitor the progress of time and compare it with other indicators and taking into account productivity.

Another thing to look for is recompiling the plan. Check for SP:Recompile in the Profiler trace and see if they correlate with performance degradation. - ref https://support.microsoft.com/en-us/kb/243586

+4
source
  • In the server properties, you can manually assign how many processors you want to use sql

enter image description here

  • Make sure you have all the Windows updates, applicable service packs
  • Without starting the database application, only when starting SQL Server check how much memory is used
  • The best approach is to run the SQL profiler for the performance template. enter image description here
  • Use the Database Advisor to see recommendations ( http://www.youtube.com/watch?v=gjT8wL92mqE )
+2
source

This seems to be a disk issue. Make sure your SQL end is configured (maximum memory) less than there is (free) on your system.

Is this a virtual machine? Whether the database is large - SQL will continue to grab memory if it is there, even if its memory is on the hard drive, which is inherently slow

0
source

Source: https://habr.com/ru/post/897061/

More articles:


All Articles