Server for SQL Server 2008 R2
-
Hi
We have a ML350 G5 with dual cpu and 8GB RAM for a Dynamics NAV 5 SP1 ERP installation. There are around 100 users accessing 4 databases. Two of the dbs is around 4 years old (and around 100GB in size each, split into different disks with RAID0+1) and most users complain that it takes longer time to post transactions.
So, I am about to purchase a new server (Dell Power Edge R 910 with 96GB RAM and dual E7-8837 2.66 GHz processors and lot of storage. Only SQL2K8R2 will be installed on Win 2008 R2 standard other than anti virus.
Now, my worry is whether this will be used by SQL2K8R2 Standard to improve the speed considerably or the power will be wasted.
I understand the performance depends on many factors but, here, kindly expecting your advice on maximizing the use of RAM and CPU on the server.
TIA
-
Depending on the SQL Server version you're planning to use, not all of the RAM can be used (e.g. Standard edition is limited to 64GB RAM as per Microsoft).
The next question would be: what is the setup of your "lot of storage"? Do you have multiple (physical) spindles available to separate tempdb, log and user DB and what speed are those running at? What RAID level do you have in place per spindle setup?
Another question: What is the current bottleneck? CPU? IO? Network?
And finally: Do you have efficient programming in place (e.g. set based queries, appropriate indexing, efficient maintenance)? Sometimes the plan to use bigger hardware to increase performance will fail due to inefficient code.... 😉
-
LutzM (1/21/2012)
Depending on the SQL Server version you're planning to use, not all of the RAM can be used (e.g. Standard edition is limited to 64GB RAM as per Microsoft).So, 96GB is going to be just luxury and not used by Sql. Since the price difference between 64Gb and 96GB is not much here, i will choose 96GB and leave for OS (and any other comes later).
The 64GB limit is for each instance or for sql server as whole?
The next question would be: what is the setup of your "lot of storage"? Do you have multiple (physical) spindles available to separate tempdb, log and user DB and what speed are those running at? What RAID level do you have in place per spindle setup?
This comes with 8x600GB drives (more can be added) and my plan is to use RAID10. Since this server is for lot of transactions, I decided not to go for RAID5. Yes, my plan is mirror two drives, to have tempDB, log, userDB on separate drives, probably userDB split on more than one volume.
Another question: What is the current bottleneck? CPU? IO? Network?
And finally: Do you have efficient programming in place (e.g. set based queries, appropriate indexing, efficient maintenance)? Sometimes the plan to use bigger hardware to increase performance will fail due to inefficient code.... 😉
Since the ERP is Microsoft Dynamics NAV (or NAVISION) from Microsoft®, I hope their queries are efficient and I will be fine-tuning the indexes and any other customized areas.
How to make SQL server to use maximum (64GB) memory ALL THE TIME ?
TIA
-
meelan (1/21/2012)
So, 96GB is going to be just luxury and not used by Sql. Since the price difference between 64Gb and 96GB is not much here, i will choose 96GB and leave for OS (and any other comes later).
The 64GB limit is for each instance or for sql server as whole?
Yes, 64GB per instance (not per database). A second instance would not be recommended from my point of view since at least one of the two instance would then need to have lower values for the upper memory limit.
This comes with 8x600GB drives (more can be added) and my plan is to use RAID10. Since this server is for lot of transactions, I decided not to go for RAID5. Yes, my plan is mirror two drives, to have tempDB, log, userDB on separate drives, probably userDB split on more than one volume.
Make ure you have some space available for the backups, too (both, log and db). If you don't have any maintenance window, you might at least put those backups on a separate drive.
Since the ERP is Microsoft Dynamics NAV (or NAVISION) from Microsoft®, I hope their queries are efficient and I will be fine-tuning the indexes and any other customized areas.
Just because it's a Microsoft product, it doesn't mean it's well-tuned by default. But I don't know anything at all about NAVISION to make any judgement. Goggel or a NAV related forum should help to clarify...
How to make SQL server to use maximum (64GB) memory ALL THE TIME ?
That's almost impossible to achieve. For example: if you just start SQL Server and do nothing at all (no queries, no maintenance, just nothing) SQL Server won't require that much memory. But as the system gets busy, it will automatically request as much memory as needed until it hit the upper limit. When there's less CPU power required, it'll free some memory if it's requested to by the OS (until the min memory value specified for that instance is reached). Otherwise it'll cache the data in RAM to avoid another path to the drive in case the data are requested again. The concept of when data are reased or cached is beyond me. But SQL Server tend to keep data in memory rather than freeing the memory.
One solution to make it use the max 64GB is to write some horrible queries. 😉
But I don't understand the reason behind this question: if SQL Server is "allowed" to use the 64GB in case it's needed, why do you want to force to use it all the time, even when there's no need?:ermm:
-
I assume you want to dedicate 64GB only for SQL Server operations & even if OS / other application needs, it shouldn’t release. Am I guessing it right?
Per Lutz explanation, it’s very much clear that SQL Server won’t consume all of the memory unless required for its operation. Similarly won’t release the memory to OS unless there are memory crunches. In either case, SQL Server would have sufficient memory for its operation.
-
meelan (1/21/2012)
This comes with 8x600GB drives (more can be added) and my plan is to use RAID10. Since this server is for lot of transactions, I decided not to go for RAID5. Yes, my plan is mirror two drives, to have tempDB, log, userDB on separate drives, probably userDB split on more than one volume.So 8 spindles in total for OS, tempDB, user databases and log. Let's see...
1 mirrored set for TempDB - 2 drives
1 mirrored set for User DB log - 2 drives
That leaves 4 drives, either 1 RAID 10 array or 2 RAID 1 arrays, and the OS has to go somewhere, preferably separate, as do the backups
There's a fair bit of storage space here (well, 2.4 TB usable space if everything is RAID 1 or 10), but there's not many spindles. I highly doubt this will handle huge numbers of transactions with this storage setup. The memory will help, but you could still run into IO bottlenecks
Have you estimated the required IOPS for the required transactions? Does the drive setup handle that number?
Since the ERP is Microsoft Dynamics NAV (or NAVISION) from Microsoft®, I hope their queries are efficient and I will be fine-tuning the indexes and any other customized areas.
Dynamics is notorious for been a terrible performance hog, and you can't just change indexes or queries without violating your support agreement (have to get permission from MS first)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you for the very helpful response.
I am going to talk to the server vendor on Monday about the storage again. Also, going to run the performance monitor on the current server to see the bottlenecks and I/O, cpu usage etc again for a whole week.
Please suggest some performance monitor counters to monitor in this case.
TIA
-
Chapter 1 (and 2, 3 and 4) http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi Gila
That was really helpful and I ran one script from the book and result for one DB is attached herewith as an excel book. Please check the values and give your valuable comments.
io_stall is 1,976,957,214
the rest is in the file.
-
And?
There's info in chapter 1 on interpreting the perfmon and other stats and it points out that no single statistic alone is useful in diagnosis. Also, a single query of that DMV is of little use alone, it's cumulative data since SQL started. Without knowing if your SQL box has been running a day or a year, there's little that can be concluded from a single view of the data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
and dont run your anti-virus on you'r prod server.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Antivirus on prod server is fine, just set the exclusions properly for the SQL files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
600 GB drives must mean you're going with 10k RPM SAS drives. You didn't say how many drives that ML350 G5 has or what type but I'm guessing they are also 10k RPM SAS drives. Even with a newer storage controller and a couple more drives you won't see a big jump in performance from the disk subsystem. So if your are experiencing a bottleneck currently from disk IO you should look at getting 15k RPM drives and more of them. I think Dell is now selling or soon will have 300 GB 15K drives in their servers. You'll have to buy more drives to make up for the lost space of course, but that will help, too.
As others have said before, though, you really need to identify where your current bottlenecks are so you know where to spend your money on the new system. To that end, in addition to setting up all of your own perfmon counters, you can download SQL Nexus and PAL from codeplex to help with diagnosing bottlenecks on your current server. They work with SQLDiag to give you some well laid out performance analysis including guidance on thresholds/acceptable values.
Good luck
Steve
SQL Managed
-
Spend some time on "www.mibuso.com". You will find lots of NAV performance related information there. Along with access to people with lots of experience in this area. You should also involve your NSC.
While hardware may be part of the ultimate solution, it likely is not your only issue. Also important that it be the right hardware.
BTW - Running 4 databases violates the license.
-
Thanks for the guidance.
One week monitoring of current server (using more than one tool) showed us not much IOPS but more than 95% CPU and Physical RAM usage most of the time.
So, we decided to go with the new server. Once, it is installed, we will be fine tuning.
Thank you all again
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply