Knowledge Base

SQL Backup and CPU affinity

Category: How do I?
Date: 30 Jun 2009
Product: SQL Backup
I use CPU affinity on SQL Server, there are remaining "unused" cores. I''d like to set the affinity on these, so that backup compression and encryption are separate from the SQL Service. Is this possible?

You can set processor affinity on a per-process-basis by right clicking a process in Task Manager and choosing "Set Affinity". You can then choose which CPUs you want that process to run on. To set this permanently every time SQL Backup runs, you would require the use of a third-party tool that can manipulate Windows process scheduling, but if the backup job is already running, you could certainly set the affinity for SQBCoreService.exe, which is the process that if compressing, encrypting, and writing the backup data stream to disk.

Getting the data out of SQL Server will use the SQL Server process and you can tell SQL Server to use a different CPU for just that part of its function. However as soon as SQL Server hands the data to SQBCoreService that would use its cores for the encryption/compression/disk writes/etc.