1

I am currently experiencing a situation where an application is sending several identical queries at once (I found this in SQL Profiler). This application is buggy, it is probably in loop somewhere and the responsible team is working to solve this problem. But the same application also provides other services that are essential to the company, so I can't just disable it. I have to live with it, until the team solves the problem.

So far so good. But this SQL Server instance also maintains other applications, whose performance are being penalized due this occurrence, some of them timeout their operation which makes this situation a DoS, in practice.

My question is: what can I do, in the DB side, to minimize the impact to the other applications?

I don't care if the buggy app gets penalized, but I would not like to penalize the other ones. (I can't just disable it, but I can slow down their queries results, if possible.)

Diego Queiroz
  • 247
  • 3
  • 8

1 Answers1

4

If you have SQL Enterprise edition, you could use Resource governor to limit resources consumed by the problemmatic application. You will need to create a classifier function that will "detect" the application e.g. based on user login or application name and assign application sessions to separate Workload group, while keeping all other users and apps in Default group. Then define resource pool for the application and allocate resources limits for CPU, Memory and/or IOPS. Map the Workload group to the resource pool. If done correctly, the application will be restricted to the defined CPU, memory and IO limits.

Suggested reading: https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

Edit: Based Diego's question I came up with possible workarounds without SQL enterprise edition. It is slightly rough and involves migration of the troublesome databases, but it's the only way I can think of:

Option 1) Install a new SQL server instance on the current server, migrate the database(s) there, edit connection string in the application etc. Limit assigned CPU cores and RAM on instance level (Processors affinity and Max Server Memory in server options). Secondary SQL instances on already licensed server do not require more licenses.

Option 2) In a virtualized environment you might create a whole new virtual machine, limit its resources on hypervisor level and then install a new SQL instance into that VM. Note that depending on your current licensing setup you might need additional Windows and/or SQL server licenses for the new VM.

Personally I'd go with 2) because it is easier to clean up when no longer needed. (And in my case I know that I have licensed entire virtualization hosts so I can create new VMs with SQL freely.)