0

recently I had to run few heavy one-time queries on our MSSQL 2008 R2 64-bit server and faced a problem: executing them made SQL server consume 100% CPU which eventually (in about 20 seconds) made server absolutely unresponsive. Thus I was forced to reboot it or wait until execution completes which took a lot of time depending on a query.

What I noticed is that setting CPU Affinity for SQL server to 7 cores instead of 8 available in task manager would keep server responsive so I could cancel my query if it took too long (and proceed with query optimizations without having too reboot).

But is it a good idea to limit CPU Affinity of SQL server?

Please share your thoughts. Server is being used for web-applications.

user2042487
  • 11
  • 1
  • *"Server is being used for web-applications"* doesn't cut it to describe what the responsabilities of your server are. – TT. Jan 25 '16 at 14:28
  • Sorry, my point was that server is running queries from web application scripts (PHP, Coldfusion, Python) so there is always a chance that one of the queries will load CPU to 100% again because data and queries are being modified quite often. Also I would like to point out that the sql server process runs with normal CPU Priority. – user2042487 Jan 25 '16 at 14:50
  • I understand, but what other things are running on the server? It is impossible to give a good answer to your question without extensive detail on what is running on the server. – TT. Jan 25 '16 at 14:57
  • There is also a MySQL instance on it but it is not being used really. Please note that I am not looking for a way to optimize the server load. It runs great when Im not torturing it with these one-time report queries. What I am asking is if limiting CPU Affinity of SQL server process is an OK thing to do. I heard it can mess up with SQL server scheduler or something. – user2042487 Jan 25 '16 at 15:15

2 Answers2

0

It turns out to be a Bad Idea.

After few days with CPU affinity 7/8 I noticed that SQL server would continuously load 1-2 cores up to 100% while other cores were available.

It is probably true that SQL Scheduler cannot distribute workload correctly when CPU affinity is limited.

user2042487
  • 11
  • 1
0

Its years later but in case anyone finds this in search, your assumption is correct that work schedulers become locked to a core. However there is a trace flag to turn on in order to put this back: 8002.

Keyboardg
  • 1