September 23, 2016 at 9:00 am
Hi,
I have a ~75 GB database that's running on SQL Server 2014 Standard SP1 on a VM with 1 socket and 8 vCPUs, with 32GB RAM. I am following some of the basic recommendations for general database setup (max memory, maxdop, cost threshold for parallelism, etc.), but there are a few areas where I have questions and I'm hoping some of you might be able to help.
1) I know the recommendation for tempdb is to have it live on a different volume than the user databases. Should all of the system databases reside together on a separate volume or just tempdb?
Also, I believe the rationale for this is that if either tempdb or a user database fills up too much space on the drive and something causes the SQL Server service to stop, it can be difficult to restart SQL Server, right? What if the drive had terabytes of storage and wouldn't come close to filling up. Are there other reasons to separate the databases?
2) I am planning to split tempdb into 8 files instead of the 1 that it is now, and then enable trace flags 1117 and 1118 to ensure the engine always uses full extents and so that all of the tempdb files grow equally. Is there a good reason NOT to take these steps?
On a side note, and I'm not sure how this plays into it, but regardless of how many volumes we create, they all share the same (redundant) storage behind the scenes--so does that affect anything regarding placement of the database files, logs, backups, etc.?
Thank you,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 23, 2016 at 9:05 am
Mike Scalise (9/23/2016)
1) I know the recommendation for tempdb is to have it live on a different volume than the user databases. Should all of the system databases reside together on a separate volume or just tempdb?
Just TempDB, and it's not about space, it's about the IO load that TempDB will get (and if it's one drive behind the scenes, multiple logical drives is mostly pointless)
2) I am planning to split tempdb into 8 files instead of the 1 that it is now, and then enable trace flags 1117 and 1118 to ensure the engine always uses full extents and so that all of the tempdb files grow equally. Is there a good reason NOT to take these steps?
Why 8 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 recoverability
September 23, 2016 at 9:18 am
Gail,
I was following Paul Randal's advice in his article about TempDB here:
http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
Based on your response, now I'm starting to doubt that it makes sense to do that. What is your recommendation.
Also, it was described to me that all of our VMs go through a SAN, which has multiple, fast, redundant SSD storage, so I wasn't sure if we should still try to separate TempDB, logs, backups, etc.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 23, 2016 at 9:49 am
If everything's going to the same SAN array, then you only separate for easier space management, so that a runaway TempDB won't also make the user DBs run out of space, or run C drive out of space and destabilise Windows.
Paul's recommendation is fine, personally I start at 4 TempDB data files unless there's evidence of allocation contention. If there is, then I go for 8 or more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2016 at 4:38 am
1) Why limit yourself to 32GB RAM? SQL 2014 Standard Edition can hold 128GB
2) Why mess with tempdb at all? Have you identified tempdb allocation issues as a cause of performance problems?
3) use sp_whoisactive to see right-now performance issues, with an incredible wealth of information at your fingertips
4) If you do reconfigure tempdb for allocation issues, I would start with 2 or maybe 4 files, not 8.
5) In most cases there is no need to configure a bunch of stuff and volumes if all same shared storage.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 24, 2016 at 5:42 am
TheSQLGuru (9/24/2016)
1) Why limit yourself to 32GB RAM? SQL 2014 Standard Edition can hold 128GB2) Why mess with tempdb at all? Have you identified tempdb allocation issues as a cause of performance problems?
3) use sp_whoisactive to see right-now performance issues, with an incredible wealth of information at your fingertips
4) If you do reconfigure tempdb for allocation issues, I would start with 2 or maybe 4 files, not 8.
5) In most cases there is no need to configure a bunch of stuff and volumes if all same shared storage.
Hi Kevin,
Thanks for the input.
1) I'm not experiencing memory contention, so I haven't requested more because there's not an issue there. Would you proactively increase it?
2) Good point. I haven't identified an allocation issue with tempdb. I simply thought it was standard practice for a database setup like increasing the cost threshold for parallelism, setting a cap on memory, etc.
3) I do use sp_whoisactive, which is great! I tend to use is with its default settings. Do you find that you use it a different (and more beneficial) way. If so, would you mind sharing how?
4) Ok, if I decide to go down that path, I'll make sure to start small.
5) Ok. If it's all essentially the same, then the only reason I can think of is so that I don't have to periodically explain why TempDB is on the same drive as the other databases and that it doesn't matter.
Thank you!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 24, 2016 at 12:25 pm
You CAN certainly run a 75GB database just fine on 32GB. But often you CAN NOT. If you know what you are doing and don't observe any issues that are related to not having sufficient memory (and note there are a wide array of things that can crop up from that) then move on - I'm sure you have other issues to address. 🙂
As for sp_whoisactive, just read Adam's 30-day blog post series and you will be able to figure things out for yourself I am sure!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 26, 2016 at 4:09 am
Is there any particular reason you're on that specific patch level (rather than SP2 CU1)? It might not matter, I'm just interested.
September 26, 2016 at 7:34 am
Beatrix Kiddo (9/26/2016)
Is there any particular reason you're on that specific patch level (rather than SP2 CU1)? It might not matter, I'm just interested.
Very good question. Yes. The vendor has only certified through SQL Server 2014 SP1. I have a message to the company to see when they plan to certify SP2, at which time I'll definitely look to upgrade (and include the cumulative updates).
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 26, 2016 at 7:48 am
Ah, thanks for answering. I assumed it must be a vendor thing.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply