Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Adding proccesor cores

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-07-29 : 12:22:59
Hi,

I have a windows 2008 DataCender R2 with 4 CPU processors that runs SQL server 2008 R2.

To increase the performance, we want to add more cores to it.

It is in a VMware setup.

What do I need to tell the network guys? When they can add the proccessors without restart the windows, how will sql server take advantage of the new settings? Will a simple restart of sqlserver engine works? (current settings is Automatically set processor affinity mask and I/O affinity mask on).

Along the same line, if I create couple more tempdb(currently has 1, and plan to make it 4 for 8 cores), will a simple restart take care of everything?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-29 : 12:28:18
Adding more cores are seldom the solution to better performance.
I have a customer which listened to another consultant and doubled the amount of cores for a sum of $40,000 and of course the query were processed in half the time. From 140 seconds to 70 seconds.
I rewrote the original query and on the old machine with half the number of cores, the query that previously took 140 seconds now take 3 seconds to run.

It's almost always better to look at the code first.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-07-29 : 12:29:57
I understand that. That sad reality is that we do not own the code.


quote:
Originally posted by SwePeso

Adding more cores are seldom the solution to better performance.
I have a customer which listened to another consultant and doubled the amount of cores for a sum of $40,000 and of course the query were processed in half the time. From 140 seconds to 70 seconds.
I rewrote the original query and on the old machine with half the number of cores, the query that previously took 140 seconds now take 3 seconds to run.

It's almost always better to look at the code first.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-07-29 : 15:55:34
Have you done any tests to confirm that the performance is CPU bound? Adding cores won't help if the disk is the current bottleneck. This is probably even more true in a virtual environment, as I assume the storage is a SAN.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-07-29 : 16:19:36
Changing to a faster disk is coming up this weekend (from NetApp to EMC?).

By the way, now it is running on 12 processors.

quote:
Originally posted by robvolk

Have you done any tests to confirm that the performance is CPU bound? Adding cores won't help if the disk is the current bottleneck. This is probably even more true in a virtual environment, as I assume the storage is a SAN.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 17:35:41
Faster disks and more CPU might help, but as Swepeso pointed out, only so much.

Couple of things you can do easily:

Run Paul Randal's script in this article to find out "where it hurts" as he puts it. http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ He also gives descriptions of what each wait type may indicate. If you find that it is memory pressure that is causing poor performance (perhaps you have only 2 GB of total memory on your system? - in that case adding CPU's and having a faster disk may do very little).

Also, look at perfmon. There are a number of counters related to SQL Server performance. You can find which once would be most useful for identifying certain kinds of bottlenecks if you google.

Didn't quite follow what you meant by adding more tempdbs.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-07-30 : 09:04:20
The instance has 1 tempdb file. I know people are still debating 1 tempdb file per processor, as some microsoft site has put out as best practice is true or not.

quote:
Originally posted by James K

Faster disks and more CPU might help, but as Swepeso pointed out, only so much.

Couple of things you can do easily:

Run Paul Randal's script in this article to find out "where it hurts" as he puts it. http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ He also gives descriptions of what each wait type may indicate. If you find that it is memory pressure that is causing poor performance (perhaps you have only 2 GB of total memory on your system? - in that case adding CPU's and having a faster disk may do very little).

Also, look at perfmon. There are a number of counters related to SQL Server performance. You can find which once would be most useful for identifying certain kinds of bottlenecks if you google.

Didn't quite follow what you meant by adding more tempdbs.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-30 : 09:17:41
Ah got it - you meant tempdb data files. Although Microsoft recommendation is to create multiple files depending on the number of CPUs, I have never investigated this or experimented with this. So I will defer to experts to comment/make suggestions.

http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
Go to Top of Page

xiaomingzc
Starting Member

3 Posts

Posted - 2013-08-03 : 03:17:48
unspammed
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-08-04 : 04:34:37
quote:
Originally posted by Hommer

The instance has 1 tempdb file. I know people are still debating 1 tempdb file per processor, as some microsoft site has put out as best practice is true or not.

quote:
Originally posted by James K

Faster disks and more CPU might help, but as Swepeso pointed out, only so much.

Couple of things you can do easily:

Run Paul Randal's script in this article to find out "where it hurts" as he puts it. http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ He also gives descriptions of what each wait type may indicate. If you find that it is memory pressure that is causing poor performance (perhaps you have only 2 GB of total memory on your system? - in that case adding CPU's and having a faster disk may do very little).

Also, look at perfmon. There are a number of counters related to SQL Server performance. You can find which once would be most useful for identifying certain kinds of bottlenecks if you google.

Didn't quite follow what you meant by adding more tempdbs.





If the tempdb files are all on the same underlying SAN storage LUNs, you probably won't get much improvement.

Some of the tempdb suggestions were based on cases where there is contention on tempdb in-memory allocation bitmaps that are used when temp tables are created. Under conditions where temp tables are created at an extremely high rate, you might see some performance improvement from multiple tempdb files. I have only seen one case where this was really needed on hundreds of servers I supported, and we later eliminated the need by re-writting a single stored procedure.

Paul Randal has written about this in his blog.
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/



CODO ERGO SUM
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-04 : 05:45:31
Very interesting article Michael, thanks for the link!

I think we need to change the "Don't believe everything you read on the internet" to "Don't believe everything you read on MSDN".
Go to Top of Page
   

- Advertisement -