Processor Affinity - Why does this happen?
-
I've been battling away with a very badly designed application which is causing some issues on one of my SQL Servers.
Server is: Windows Server 2008 R2 running SQL Server Standard 2008 R2. Quad core, Virtual machine (VMware), 32GB RAM.
The application is badly written and when it starts it connects to SQL, runs a load of queries and extracts all the databases data into the memory on the application server! (Don't ask! Developers are working to improve this!). This works fine on a test database server, low load UAT server and even a local database server with one cpu, 2 GB total memory and not much else!
However, when this was released to production, the application started up, started loading its data then timed out and died! I have eventually found the query that the app runs before the failure and found this takes 33 seconds to execute. Longer than the default 30 second SQL client timeout. Running this query on the other machines, (even the really slow piece of rubbish machine with only 1 cpu and 2 GB of RAM), the query takes just 15 seconds to complete. The only reason for this is that the production database is a shared system with lots of other applications and even though I cannot see any problems with CPU or memory issues, the query still takes double the time to run comprared to a single use system.
Anyway, to cut a long story short, I manually set the CPU affinity on the sql server to all CPU's rather than just setting to automatic. Re-running the query now, it completes in 15 seconds. That is nearly a 50% increase in performance just from changing this setting.
Can anyone explain why this happens and is there anything wrong with having the CPU affinity setting set?
-
My first thought is that you had a different plan in the cache and making the affinity change causes a recompile which helped the issue.
But... I'm not sure about that one
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi Grant,
Thanks for your reply.
I've checked this morning and running the query now takes roughly 20 seconds, compared the 15 yesterday. A little longer than yesterday, so I thought maybe you are correct and the recompile improved it. However, I set the CPU affinity back to automatic, ran the query again and it took 38 seconds to execute. Setting the CPU affinity back to manual, on all CPU's, and it takes 16 seconds again, so to me (correct me if I am wrong!), this doesn't look to be just a recompile issue. I would have thought the query would be quick each time the cpu affinity is changed, whether to auto or manual.
Can I confirm; The settings I am changing are from this - (Slow 30 second query)
To this: (query completes in 15 to 20 seconds
Hopefully these screenshots show up.
My thinking is that with the first setting, SQL Server is putting the inefficient query on a number of schedulers. Due to the fact that this Server is a VM, even though SQL may see a scheduler as being free from an OS level, the underlying host may be adding a delay waiting to free up a cpu to do the work and adding a delay.
When setting the affinity to manual, even though I am saying use all available cores, SQL will only put the workload on one scheduler and not share it around. This doesn't add a delay as the VM host will most likely have a cpu free and the work completes in a shorter duration.
This is just a guess, but the only thing I can come up with to explain this!
-
Oh, we've got a VM on top of this... yikes. That does complicate things. Now I'm really unsure. Your explanation may be accurate, but... let's see if I can get someone smarter to look at this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I would look at what else is running on the VM.
Are they reserving resources for your instance?
Also reading from disk to memory.
You are in a shared environment, so you need to start with the whole server.
We would use a SAN for storing our data.
I resisted VM until I had an admin who understood you had to manage more for the peaks for our BI stack.
-
You also need to account for the application server, similar to looking at SQL Server and the resource needs.
If both are on the same VM, you are putting all the load on the same host.
-
Thanks all.
Yes this is complicated setup with the different layers of host-vm-instance etc.
Just to add to the complication, the VM has a SQL 2008 instance and a SQL 2012 instance running on it, both of which will be using the same resources. When this application runs, the app server (running on a different VM host), is not busy or running under any load. The SQL server in question also doesn't show any indication of resource pressure, with temp db hardly used, memory is available and CPU is not maxed out (on the os that is).
I really think it's just a case of the query being executed does take effort to run, which although doesn't stress the other databases on the SQL server or the SQL server itself, but does request slightly more resource than the underlying hardware can handle.
It's a tricky one, but quite interesting.
-
You might want to read this.
Might be helpful just to compare what the vendor says vs. your setup.
-
Greg Edwards-268690 (2/4/2014)
You might want to read this.Might be helpful just to compare what the vendor says vs. your setup.
Thanks for this. We have done a lot of what VM have suggested as best practice for the SQL Server, with the exception of dedicating a set amount of CPU resources for the VM. We currently have the host CPU shared amongst all VM's running on it, which isn't showing signs of stress at all. I'll keep investigating.
Thanks all.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply