2nd SQLPass Meetup at XXXLdigital
SQL Server experts exchanging experience
Author: Wolfgang Hesse, Head of Data Warehouse
As reported in the first post on the SQL PASS regional meeting, a series of talks on the topic of SQL Server at the XXXLdigital premises in Koblenz-Görgeshausen started on 12 November 2019.
On 27 February 2020, SQL Server experts were able to exchange views at XXXLdigital in Koblenz-Görgeshausen.
There were no presentations this time — instead there were plenty of discussions and experiences to be shared on the multitude of settings for SQL Server.
Speaker: Maic Beher
With his extensive hands-on knowledge on most of SQL Server settings, Maic Beher hosted the event and provided helpful hints.
Mr Beher has gained more than 20 years of experience in the field of SQL Server and works both on a national and an international level. Currently, he works as Principal Consultant for Trivadis AG in Switzerland. He advises companies from small start-ups up to large-scale pharmaceutical corporations on issues in terms of security, performance and high availability solutions. He also gives talks at Microsoft Switzerland on data management and works for the Swiss PASS.
Five key takeaways: SQL Server settings
We’ve summed up the most important points of discussion:
1. SQL-Server 2016-2019
Cardinality Estimator is applied based on database compatibility level.
MaxDoP can now be set for each database
Starting with SQL 2016, the following Trace Flags are no longer relevant:
T1117 (grow all files equally)
T1118 (activation Uniform Extents) all data pages of an Extent belong to one object. It is expected that this reduces the need to scan the SGAM page(s). Explanation on GAM and SGAM.
2. Best practices — database setup
Creating a database
The required objects for operating the database will be in the primary filegroup. After that, you need to create another filegroup and make that the default one. ← If needed, add eight files to that filegroup (depending on the server’s size).
TempDB
In case of Azure Managed instance, Microsoft pre-configures TemDB with 12 data files. Does this mean this is our new default value?
3. Physical properties
Disabling C-states and other BIOS power-saving settings
Hyperthreading yes/no
Normally, hyperthreading should be deactivated for OLTP (even if the server runs on a VM).
However, there might be instances where it makes sense to enable hyperthreading. If hyperthreads are enabled, the performance should be monitored by taking a look at the context switches.
Disk alignment
As a rule of thumb: NTFS volumes are to be formatted with an allocation size set to 64 KB (completely — not doing a quick format).
Usually, for each of the bigger SAN vendors and for the respective SAN model, there is a best practice setup of the SQL LUNs/ datastores etc.
4. Virtualisation
VMware — volume provisioning: For quite a while, the best practice has been thick provisioned, eager zeroed. If the VMware admins fail to do so, there is no other choice than to format the total volume in Windows (not doing a quick format). Accordingly, the first write performance takes place already when formatting... ← it just takes way longer in Windows compared to VMware.
5. Microsoft Azure – Managed instance
You are granted next to all freedoms an on-premise instance offers.
The participants' conclusion was quite positive. They considered it a successful PASS meeting with talks on SQL Server and best practices, VMware/ virtualisation, storage, settings and many other topics. Exchanging each other’s experience sure came as a breath of fresh air.
Participating companies
We’re quite pleased that the event included a diversity of companies: We were able to welcome employees from Landkreis Offenbach, Sozialverband VdK Hessen-Thüringen, to Hebel IT Consulting and Evalueserve GmbH.
We are very much looking forward to the next meeting at XXXLdigital and would like to wish you all the best, and especially good health.
*German version to be found here