SQL server and multi core cpu utilization
-
I have encountered this with several server, the cpu utilization with SQL server on multi core systems is very low i.e. one core does all the work and the others are idle, what is worse is that the queries are queued serially. Anyone encountered this, is there a solution.
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
-
I have encountered this with several server, the cpu utilization with SQL server on multi core systems is very low i.e. one core does all the work and the others are idle, what is worse is that the queries are queued serially. Anyone encountered this, is there a solution.
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
Have you checked if processor affinity is defined? It could be that only one core is allowed. Also it could be that the disk subsystem is actually the bottleneck so CPU has to wait for the answers. And what comes to the serial execution, I thing there are at least few possibilities: - again waiting for disk subsystem - serial executions is forced because of locking issues - transactions are ran in serializable isolation level.
The need to optimize rises from a bad design.My articles[^]
-
Have you checked if processor affinity is defined? It could be that only one core is allowed. Also it could be that the disk subsystem is actually the bottleneck so CPU has to wait for the answers. And what comes to the serial execution, I thing there are at least few possibilities: - again waiting for disk subsystem - serial executions is forced because of locking issues - transactions are ran in serializable isolation level.
The need to optimize rises from a bad design.My articles[^]
Thanks Mika, I will look into it... In the meantime more information: - We have a windows application server service connecting to sql server via integrated security on an internal threadpool of 10 threads. Each thread creates a connection when needed, and closes when finished. - The server is an HP with 2 quad core xeon hyperthreaded (16 processors in windows) with 10k sata raid 5. - Clients connect to the application server via tcp (no direct connection to sql).
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
-
Thanks Mika, I will look into it... In the meantime more information: - We have a windows application server service connecting to sql server via integrated security on an internal threadpool of 10 threads. Each thread creates a connection when needed, and closes when finished. - The server is an HP with 2 quad core xeon hyperthreaded (16 processors in windows) with 10k sata raid 5. - Clients connect to the application server via tcp (no direct connection to sql).
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
-
I have encountered this with several server, the cpu utilization with SQL server on multi core systems is very low i.e. one core does all the work and the others are idle, what is worse is that the queries are queued serially. Anyone encountered this, is there a solution.
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
-
Fair question, I presume when you have parallel threads each with their own connection they query in parallel.
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
-
Fair question, I presume when you have parallel threads each with their own connection they query in parallel.
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.