| 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 |
 |
|
|
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
|
|
|
|
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. |
|
|
|
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.
|
|
|
|
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. |
|
|
|
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.
|
|
|
|
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).aspxCreate 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. |
|
|
|
xiaomingzc
Starting Member
3 Posts |
Posted - 2013-08-03 : 03:17:48
|
| unspammed |
|
|
|
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 corehttp://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 |
|
|
|
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". |
|
|
|
|
Adding proccesor cores