Assigning Processors
-
Hi All - I'm looking for a clarification. I have a vendor proposing that we upgrade our existing 2 processor box to a 4 processor box. No issue with that, we are processor bound. They also propose setting it up so that batch processing (SQL Agent jobs) can only run on certain processors and that on-line user (end users through the application) can only use other processors. I didn't know that this could be accomplished but haven't questioned them on it yet. Can someone confirm that this can or cannot be done? And how to do it if it is possible? Thanks!
-
Yeah. Under SQL Server Properties in EM, there's a processor tab where you can select which processors SQL Server will use. Not really sure about the Agent.
Also, I've never played with this setting. I just remember seeing it there when going to do something else in the properties window.
-
Thanks Tim, I know the settings you are talking about and they will set which processors sqlservr.exe can run on. I guess I really need a way to assign processors to sqlagent.exe as well in order to separate users and Agent jobs.
-
You can't assign sqlagent to separate processors from sqlservr without using 3rd party, very techncial utilites (such as from sysinternals)
All you can do is play with "affinity mask" to limit what CPUs the SQL engine (sqlservr.exe) runs on.
Now, sql agent does not execute jobs as such, it is a scheduler.
If these run a dts job or BULK INSERT job or a DBCC job or a backup job, then these are all submitted to the database engine so why not let the db engine use all 4 CPUs to improve parallel processing.
Also, watch your licensing.
It you are "per processor" then you still need to have 4 licenses because the OS can see 4 CPUs.
Just let SQL do it's own thing rather than try to second guess it without understanding what is going on.
Are you sure you are CPU bound? Are you causing CPU activity by having not enough RAM so you have excess paging? Are you on enterprise edition to make best use of multiple CPUs and RAM? Do you have separate arrays with RAID 1+0 and lots of spindles to improve IO? IMHO, vendors ask for more CPUs when their code is rubbish...
-
Thanks Shawn, this was exactly the info I was looking for. I agree, let the db engine run on all processors. The licensing is ok, we are per seat.
I haven't dug very deep into the problem yet. At first glance the CPU's are the problem, they are hitting 80-90% (sometimes 100%) and staying there for more than 20 minutes of each hour. And this happens every hour when their jobs run.
I'll be looking at paging but I don't expect memory to be a problem. The performance statistics I've seen so far show about 1.5 GB of available memory. Granted, it may not have been allocated properly.
"Are you on enterprise edition to make best use of multiple CPUs and RAM?" I understand why Ent. Edt. for the RAM, but why for multiple CPUs? Does anyone have any articles comparing the two versions beyond the basics of what features are supported in one vs. the other?
We have the data and transaction logs on separate arrays, each having 7 disks in a RAID 5 configuration (I know, not optimal for trx logs). I don't have any more information on their IO performance, but I'll be looking at it too.
It is vendor code, and I haven't looked at it yet, but my guess is that it is inefficient. I know they are doing a lot of calculations but the server they are on isn't that old and should be able to handle the load fine. We'll see, thanks!
-
Sorry for the useless information earlier
The funny thing is I'm currently fighting with crappy vendor code right now, as well. The thing that sucks about vendors is there are some that are damned good at what they do (code as well as the focus of said code), and some that throw code together with no care for how well it runs, so long as it eventually gets the right answer. It would seem to me that if I'm a vendor writing code to sell, I'm going to hire the most talented people I can find and enforce certain coding standards (like not crapping out twice a day...). I guess some vendors (esp the kind I deal with ... industrial process optimization kinds of things) don't know shit about the benefits of code optimization and wind up with half baked developers the get on the cheap.
Maybe I should hire myself out as a contractor...
-
Enterprise has better IO algorithms with read-ahead. On some benchmarking on a troubelsome app I had a while ago, I found that Enterprise was more resilient to loads and was quicker in normal queries and I coudl see read ahead in IO stats. It's does parallel DBCC so index rebuilds are quicker etc.
Check these for some background:
Read this for some basic performance stuff:
http://www.sql-server-performance.com/sql_server_performance_audit2.asp
This will help you find any other bottlenecks that may be masked or causing high CPU
Also this for SQL hardware. If you go for another 2 whizzy Xeons, you may have memory bus contention, this is why Intel do huge L2 and L3 cached because of teh shared bus:
http://www.webpronews.com/it/database/wpn-20-20041210SQLServerHardwarePerformanceChecklist.html
And this for some backup:
Other than that:
Do you reindex regularly? You could have fragmented data causing overhead.
Do you get things like CXPACKET waits?
How much RAM and I guess you use standard?
Is it worth multiple having more than oen filegroup (one for indexes, one for data)?
If you are CPU bound, can you offload some stuff onto a separate box somehow?
-
I don't have access to the servers, I provide trouble shooting help to the DBA group. Kinda like L4 support, but with other responsibilities as well. I'll definately be checking on their index rebuilds, I know they get a lot of new data each day. Their fill factor may not be appropriate either.
I'll have to check on what wait types they are getting too.
The server has 4GB of RAM, NT 4 Enterprise (SP6) and SQL 7. Not sure if SQL is Std or Ent, if I were to guess it would be Std, which means they can't take advantage of the extra memory.
If we find an IO problem I'd consider multiple filegroups, but it'd also mean adding another PowerVault (Dell hardware) and another controller card to get the IO channels we need. Our Server Ops folks frown on having two PowerVaults attached, but will do it if needed.
They could offload some work to another server but I doubt the business will want that. If we determine it is a hardware problem they'll just buy a bigger server, migrate to Windows 2003, and SQL 2000.
-
NT4 and SQL 7? Wow. I assumed SQL 2000 and Win2000 at least.
Simply new OS and new SQL versions could fix this, but it's not simple to do...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply