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.
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.