April 21, 2014 at 5:00 am
This question is about a dedicated SQL Server instance with the main reason being performance issues related to tempdb.
Our company sells a range of products all using SQL Server. The database is crucial as it contains all data plus settings. The workload of the products is OLTP plus small datawarehouse load being small reports run in snapshot isolation.
Our requirements state a dedicated database server or a dedicated instance. Of course dedicated hardware is the best option leave aside discussions on CPU, disks etc. There have been issues in the past with Tempdb that have lead to the requirement of a dedicated instance. Unfortunately the details are not longer known but it’s summarized with ‘locking issues on tempdb’. An actual example where the discussion gets hot is a customer that purchases a second of our products. The existing database server is dedicated for our software. Adding an extra instance on this server seem pointless to me. I use the metaphore of creating an extra frontdoor to create moor room in your house. I have looked in detail at the existing server and there seems to be room for more workload so I would add the database to the existing instance.
Only recently we apply or advise best practices for tempdb (dedicated drives, multiple datafiles, tuned growthrates and initial sizes).
Would there be any reason to have a second instance on the same server to prevent performance issues related to tempdb? I’m thinking of issues like Bitmap Allocation Page contention, row versioning load, query spills, sorts/hashes, CHECKDB, rebuild indexes with sort in tempdb etc. I know all these can be monitored in detail (and I will) but I have done a bit of research and have never come across any suggestion or evidence that an extra instance on the same server would help for tempdb performance issues.
April 21, 2014 at 5:13 am
It would be good to have one instance instead of multiple instance as allocation of the resource might be challange. You can think of seperate instance if you would like to run one application - one instance; second application - second instance. i.e. if you stop services of one instance will not affect another application instance. Also if you do have different Database instance settings than think of different instance.
How is perfromance after the implimentation of best practice for tempdb?
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 21, 2014 at 5:18 am
I can't speak to *why* your application may be having issues with TEMPDB, but I can make a few comments about why having a second instance would avoid the problems.
Essentially, when you install a second instance of SQL, it's (almost) completely stand-alone. Both instances will have their own TEMPDB, Master, MSDB, and Model databases, each instance will be running as separate Windows services, etc. So, no contention between them for TEMPDB resources (except possibly for disk, if both instances TEMPDB are on the same disks) The reason for the "almost" is, both instances will share some things, if they're installed, such as SSMS.
Using your metaphor:
I use the metaphore of creating an extra frontdoor to create moor room in your house.
Isn't quite accurate. Perhaps it would be better to describe it as putting a wall down the middle of your house, and making each side a fully functioning, livable house.
But, as I'm sure someone will point out, the bigger issue is *why* the application hits TEMPDB that hard. Long term a better solution might be to find that out, and if possible, resolve it so that multiple copies of the application *can* share one instance.
April 21, 2014 at 5:22 am
The problem is that the Tempdb incident has happened a while ago and no one knows any details and I wasn't involved. It has lead to the dedicated instance req and recently we apply and advise other best practices because we know more and more about SQL Server. So there is no before after situation. What I am looking for is arguments against extra instance for performance reasons which sort of is reverse burden of evidence as in my opinion there should be very good and tested reasons for it. I am the one having to apply and advise the requirements but I can not think of any reasons and feel a bit embarrassed even to have to ask for a dedicated instance for 'locking issues with the tempdb'.
April 21, 2014 at 5:28 am
Essentially, when you install a second instance of SQL, it's (almost) completely stand-alone. Both instances will have their own TEMPDB, Master, MSDB, and Model databases, each instance will be running as separate Windows services, etc. So, no contention between them for TEMPDB resources (except possibly for disk, if both instances TEMPDB are on the same disks) The reason for the "almost" is, both instances will share some things, if they're installed, such as SSMS.
I realize this. But then I fail to understand how these dedicated databases on the same hardware could relieve performance issues. If e.g. we have nr of tempdb datafiles at the nr of physical cores, how would an extra instance help even more?
I grant that an extra instance would help if also dedicated disks for this instance were available.
April 21, 2014 at 6:03 am
Bouke Bruinsma (4/21/2014)
Essentially, when you install a second instance of SQL, it's (almost) completely stand-alone. Both instances will have their own TEMPDB, Master, MSDB, and Model databases, each instance will be running as separate Windows services, etc. So, no contention between them for TEMPDB resources (except possibly for disk, if both instances TEMPDB are on the same disks) The reason for the "almost" is, both instances will share some things, if they're installed, such as SSMS.
I realize this. But then I fail to understand how these dedicated databases on the same hardware could relieve performance issues. If e.g. we have nr of tempdb datafiles at the nr of physical cores, how would an extra instance help even more?
I grant that an extra instance would help if also dedicated disks for this instance were available.
Quite simply, really, they don't "relieve performance issues," they work around the performance issues. If I read what you posted correctly, if you have multiple copies of your application being hosted by a single instance of SQL, there (may) be issues with contention over TEMPDB. All putting them on separate SQL instances does is work around the underlying problem, it doesn't resolve any performance issues.
From what you've posted so far, it sounds like you might be on the right track. Try to find out what's causing the problems when there's more than one install of the application sharing a SQL instance, then work on resolving that.
April 21, 2014 at 8:23 am
Bouke Bruinsma (4/21/2014)
This question is about a dedicated SQL Server instance with the main reason being performance issues related to tempdb.Our company sells a range of products all using SQL Server. The database is crucial as it contains all data plus settings. The workload of the products is OLTP plus small datawarehouse load being small reports run in snapshot isolation.
Our requirements state a dedicated database server or a dedicated instance. Of course dedicated hardware is the best option leave aside discussions on CPU, disks etc. There have been issues in the past with Tempdb that have lead to the requirement of a dedicated instance. Unfortunately the details are not longer known but it’s summarized with ‘locking issues on tempdb’. An actual example where the discussion gets hot is a customer that purchases a second of our products. The existing database server is dedicated for our software. Adding an extra instance on this server seem pointless to me. I use the metaphore of creating an extra frontdoor to create moor room in your house. I have looked in detail at the existing server and there seems to be room for more workload so I would add the database to the existing instance.
Only recently we apply or advise best practices for tempdb (dedicated drives, multiple datafiles, tuned growthrates and initial sizes).
Would there be any reason to have a second instance on the same server to prevent performance issues related to tempdb? I’m thinking of issues like Bitmap Allocation Page contention, row versioning load, query spills, sorts/hashes, CHECKDB, rebuild indexes with sort in tempdb etc. I know all these can be monitored in detail (and I will) but I have done a bit of research and have never come across any suggestion or evidence that an extra instance on the same server would help for tempdb performance issues.
To be honest, if the products you're selling have that much of a problem with TempDB, then something is VERY wrong with the code. Using a dedicated server is, at best, a temporary patch for a problem that will only get worse.
My recommendation is that the company should spend some time and money finding the code that causes such contention and fix it because if someone told me that a product needs a dedicated server, I probably wouldn't buy the product even if it were the only show in town.
Fixing such things isn't as big a bullet to bite as you would think IF you hire the right person for these problems. Someone like Kevin Boles could probably solve most of your issues in a week or two.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2014 at 9:20 am
I tend to agree with jasona.work here. You may or may not mask or relieve some tempdb issues with a second instance. There isn't a hard and set rule here that can guide you. Apart from separating out a tempdb, which could potentially alleviate some performance from separate file threads, there is also the issue of memory. It's entirely possible one of your databases dominates the buffer pool and memory and causes issues with the other requiring more spill work into tempdb. Separating to instances could allow you to tune memory, or it could result in the first instance being more starved of memory it needs.
Ultimately if you have two instances, you need to do some investigation into the resources each needs and then make a determination of whether or not you can consolidate them into one instance.
While you're doing this, I might look for bad code, as Jeff noted, and see if you can send hints or ideas to the developers/vendor and ask them to try and change their code to perform better.
April 21, 2014 at 9:51 am
Steve Jones - SSC Editor (4/21/2014)
I tend to agree with jasona.work here. You may or may not mask or relieve some tempdb issues with a second instance. There isn't a hard and set rule here that can guide you. Apart from separating out a tempdb, which could potentially alleviate some performance from separate file threads, there is also the issue of memory. It's entirely possible one of your databases dominates the buffer pool and memory and causes issues with the other requiring more spill work into tempdb. Separating to instances could allow you to tune memory, or it could result in the first instance being more starved of memory it needs.Ultimately if you have two instances, you need to do some investigation into the resources each needs and then make a determination of whether or not you can consolidate them into one instance.
While you're doing this, I might look for bad code, as Jeff noted, and see if you can send hints or ideas to the developers/vendor and ask them to try and change their code to perform better.
We are continuously looking for improvements in the code and getting better in tuning queries and settings. The issue from the past occured where we did combine 2 of our products. It was deemed to be locking in tempdb and has given rise to the conundrum of a dedicated server/instance.
I'm learning more and more on our software and workload on SQL Server and I'm convinced that our requirements are irrealistic and over the top. Many of our customers do not even follow our reqs and hence we have many examples of shared instances and mixed workload without issues.
To me it seems that an exception has become the standard and that we need to set this straight. My question here was to find out if an extra instance would make sense in any way but I am convinced that there is more to be gotten from tuning and monitoring with common sense.
Thanks for your answers. They really help me to get a better understanding.
April 21, 2014 at 10:38 am
It has been my experience, when dealing with third-party vendors to have them tidy up their bad, inefficient code comes nothing short of a minor miracle, especially when the third-party solution has been around for a while. I'll often hear from a vendor "well we haven't seen any issues you describe", where by trying to push back the problem back in my lap.
However, since this is "your" solution you may want to take a good hard look (or at least have some highly skilled performance tuning consultant) tidy up things so it doesn't put pressure on the SQL Server box.
Unless you are short on hardware resources I see very little advantage in adding SQL Server instances to a single box. Each instance is going to share the same CPU, Memory and disk storage (physical attributes of the box).
You are probably going to gain a bigger bang for your buck if you provision your box, (ie CPU, Disk, Memory) to accommodate the work load of the application.
A question was brought up about TempDB best practices. There is a lot to be said about this. Not knowing your entire physical hardware platform, keep in mind if you are using a RAID configuration, consider RAID 2 instead of 5 for Tempdb. Also isolation the data & logs onto separate drives as well. You mentioned something about pre-allocating the tempdb file sizes, this pays off very big. How many files have you created for your tempdb data files? Depending on the size of the application I've gone ahead and have created 16 or more files. There is a best practice on this topic that is worth reading through.
I will say this, since I took over the SQL Server enterprise server farm I can honestly say I've seen little to no issues with tempdb. The MS Best Practice is the best place to start!
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
April 21, 2014 at 12:27 pm
A question was brought up about TempDB best practices. There is a lot to be said about this. Not knowing your entire physical hardware platform, keep in mind if you are using a RAID configuration, consider RAID 2 instead of 5 for Tempdb. Also isolation the data & logs onto separate drives as well. You mentioned something about pre-allocating the tempdb file sizes, this pays off very big. How many files have you created for your tempdb data files? Depending on the size of the application I've gone ahead and have created 16 or more files. There is a best practice on this topic that is worth reading through.
We advise the lesser of 4 or the nr of physical cores for the nr of tempdb datafiles. Almost all discussions on any tempdb issue has this advice. Dedicated disks with gooed read/ write performance are also good but I think the multiple datafiles puts our perfomance issues to bed for quite some time.
April 21, 2014 at 1:31 pm
Bouke Bruinsma (4/21/2014)
We advise the lesser of 4 or the nr of physical cores for the nr of tempdb datafiles. Almost all discussions on any tempdb issue has this advice. Dedicated disks with gooed read/ write performance are also good but I think the multiple datafiles puts our perfomance issues to bed for quite some time.
What's "nr"?
April 21, 2014 at 2:57 pm
Bouke Bruinsma (4/21/2014)
A question was brought up about TempDB best practices. There is a lot to be said about this. Not knowing your entire physical hardware platform, keep in mind if you are using a RAID configuration, consider RAID 2 instead of 5 for Tempdb. Also isolation the data & logs onto separate drives as well. You mentioned something about pre-allocating the tempdb file sizes, this pays off very big. How many files have you created for your tempdb data files? Depending on the size of the application I've gone ahead and have created 16 or more files. There is a best practice on this topic that is worth reading through.
We advise the lesser of 4 or the nr of physical cores for the nr of tempdb datafiles. Almost all discussions on any tempdb issue has this advice. Dedicated disks with gooed read/ write performance are also good but I think the multiple datafiles puts our perfomance issues to bed for quite some time.
I don't disagree with the number ("nr") recommendations necessarily, but "RAID 2" is a rather bizarre suggestion. Use RAID 1 or, if short on drives and allowed by your company, you can even consider RAID 0 for tempdb, assuming the server has some planned down time, since tempdb never needs to be recovered.
Start with 4 exactly-equal-sized tempdb data files. You need only one log file, though, as with all databases.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 21, 2014 at 6:05 pm
In my original post I referenced RAID 2 which was incorrect. I meant to say RAID 0. Sorry for the confusion.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply