-2

There is an issue on my production SQL Server. There is not any blocking on the server but it doesn't respond in acceptable time. For example, I execute the sp_who3 procedure to watch what is going on the server, but the execution time goes to 4, 5 minutes.

I noticed the issue occured when my temdb disk drive was under 10%.

Server Configuration:
160 Gb RAM
18 Core CPU

What should I do? I don't know what to do now.

  • 2
    There's not enough information here to really tell you what's going on. That is an extremely abnormal situation. I'm running SQL Server in a VM on a small laptop and it's not that slow. Look at sys.dm_os_wait_stats. See what's slowing things down. Look to the OS. Look at the system settings. Something is seriously flawed. – Grant Fritchey Jun 25 at 11:16
  • Please, remove the tags of SQL Server versions that do not correspond to your version. Adding all the versions like you did won't help you get more help, instead it will mislead people from giving you accurate advice. Check this SQL Server Wait Statistics article. Also, have you tried using sp_whoisactive to get more information on what's running at the moment on your server? – Ronaldo Jun 25 at 11:57
  • I have done all of them but nothing happened :( I had to restarted the server unfortunately. I have lived the situation twice in the last four weeks. I realized a situation that the issue occured when my temdb disk driver was under %10. Does this situation make any sense? @GrantFritchey – DBA Jun 25 at 13:25
  • I have done all of them but nothing happened :( I had to restarted the server unfortunately. I have lived the situation twice in the last four weeks. I realized a situation that the issue occured when my temdb disk driver was under %10. Does this situation make any sense? @Ronaldo – DBA Jun 25 at 13:26
  • What do you mean by "I have done all of them but nothing happened"? Rebooting SQL Server is not a good practice to solve this kind of situation because everything SQL Server placed in memory for better performance must be reloaded from disk again when requested (and it doesn't improve performance). Please, paste the result of the SQL Server Wait Statistics query from the link I advised you to check as it might give some clue about the problem. Also, you mentioned there was no locking at the moment, but can you see some long running query with sp_whoisactive? – Ronaldo Jun 25 at 17:12