SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Adding proccesor cores
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

789 Posts

Posted - 07/29/2013 :  12:22:59  Show Profile  Reply with Quote
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!

Edited by - Hommer on 07/29/2013 12:24:56

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/29/2013 :  12:28:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

789 Posts

Posted - 07/29/2013 :  12:29:57  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 07/29/2013 :  15:55:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

789 Posts

Posted - 07/29/2013 :  16:19:36  Show Profile  Reply with Quote
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.


Edited by - Hommer on 07/29/2013 16:46:55
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 07/29/2013 :  17:35:41  Show Profile  Reply with Quote
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

789 Posts

Posted - 07/30/2013 :  09:04:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 07/30/2013 :  09:17:41  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 08/03/2013 :  03:17:48  Show Profile  Reply with Quote
unspammed
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/04/2013 :  04:34:37  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 08/04/2013 04:39:44
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 08/04/2013 :  05:45:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000