Installing SQL Server 2016 CTP2
Installing SQL Server 2016 CTP2
First steps in testing SQL Server 2016 CTP2
Finally I managed to download and install the new CTP2. You need to have at least Windows Server 2012 or Windows 8 OS in order for the installation process to complete. Those are minimal OS requirements. You can find more info at https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx#top_principal
There are some differences in the installation process, that I am going to talk about in this post. The new feature you can choose as part of features selection is PolyBase Query Service for External Data – the service that makes available using T-SQL for querying relational and non-relational data from Hadoop:
Surprisingly (or not) if you choose to install the PolyBase Service, you will need to have Oracle JRE 7 Update.
Actually even if you install the Oracle JRE the PolyBase installation failed again with the same message. Anyway, I couldn’t manage to install the PoyBase Service, and probably I am not going to try it soon, so I am happy that Oracle JRE is not required for other features. So my new installation succeeded at the end. But I was really surprised it required a computer restart:
There is another new option that they put as part of the SQL Server setup – the number of TempDB files. The default value is 8 or the number of cores whichever is lower. You can increase the number during the setup process up to the number of cores.
The option is available in command lien setup also as SQLTEMPDBFILECOUNT.
This is my virtual machine which has only 1 CPU and I am not able to change the number above 1 during the setup. It doesn’t mean that I couldn’t add more tempdb files after the setup completes. But it means that in case I want to add more files than the number of core that I have currently (why to do that is another discussion) I couldn’t , because the setup throws an error. ZThis is something that I do not entirely agree as an action. It could for example warn you, but why to throw an error?!
You can successfully change your tempdb configuration after the setup and add as many files as you want (because of adding virtual CPUs later, following the rule of minimum 4 files, or some other reasons).
There is another drawback that I have found with this setting – the setup process creates the respective number of files with the size of 8MB, which is I would say kind of pointless having this setting here. Because even after the setup the tempdb configuration is not completed. You could still have a lot of performance issues because generally those files will need to grow. This will generally generate low space in tempdb notifications during the growth process (errors 11XX). During the growth process files will have different sizes which will let sessions to go and work in particular file, which will increase the PAGELATCH contention, the main reason for having >1 tempdb files.
Generally we can have 3 main reasons of performance issues in tempdb:
- Contention problem due to PAGELATCH waits on allocation pages
- Slow tempdb response times
- Tempdb space issues
All 3 problems have the same root, at least you can actually try to avoid all those by starting with a good tempdb configuration. Which means:
Having more that 1 tempdb file in order to reduce the allocation contention (fixing problem 1 from configuration perspective)
- (I am not going to talk here about tempdb issues that you can still have after that because of specific coding practices like combining DDLs wirth temp tables, this is fixed from SQL Server 2014, so potentially the code that leads to dramatically increasing of the contention creates no more issues)
- Isolating tempdb files and log (not necessary to put them of different drives) from other databases and put them on fast drive (5-10ms latency max) (fixing problem 2)
- Configuring the size of the files to be exactly the same and large enough in order to avoid growth (fixing problem 3)
Neither of them alone could resolve your tempdb issues. Neither of them alone ends up with a good tempdb configuration. In order to properly configure the tempdb you have to do them all. The new option in the setup process do just the first one, giving you just hint on number of files if you have some doubts. At least this option could help some DBAs to start thinking before configuring tempdb with 32 files for example (I have seen that). And if it’s the only purpose of this option – to act like a hint, then I agree having it as part of the setup. But honesty I missed it very easy on my first setup of the product, I just didn’t notice its there. But one can easy take this new option as something that guarantees that the tempdb is confugured as part of the setup and he/she doesn’t need to do anything else. And this is NOT true! So my main message here: this option is not enough to have your tempdb properly configured, you still have to do step 2 and 3 that I mentioned above.
All those comments about Enhanced Database Caching (if this is what they mean as a new feature, I am not sure)
can be summarized in the following way: I doubt the usefulness of having this option in the setup. DBAs should take this just as a hint just for number of files that tempdb should eventually have on a particular box. But if they recognize it only as a hint and not as an a guarantee of having a good configuration at the end of the setup process, then they could probably configure their tempdb pretty well without hints.
OR the program team should possibly consider to put the overall configuration setting of the tempdb as part of the setup process. This could be the best solution I think instead of doing it partially and this could avoid the confusion.
Thanks for reading!