SQL Server Performance – Prevent Database Slowdown from Heavy Processes

performancesql server

I'm a developer not a dba, but this task was given to me…

One of our databases is hit hard by a process which reads and modifies hundreds of thousands records onece or twice a day, during the time this process is running our other services who rely on this database get very slow.

What are some "common" approaches to solve this kind of problems?

Things I have thought that could work:

  • Using "NOLOCK" in the read queries
  • Using a cache service in front of the database, like Redis
  • Using database redundancy

My questions are: Could any of these 3 options works? and if so, what kind of results can I expect. What other options do I have?

Best Answer

You have two options:

  1. redesign/tune the procedure that is doing too much disk IO (or may too much use of cpu or ram)
  2. increase hardware power (more MIOps, more ram, more logic cores)

But before doing so you have to deep dive into the issue to find what is the root cause of it.

Related Question