On github you may find pre-release version of sample database Wide World Importers which is going to replace well known Adventures Works.
Developer edition of SQL Server 2016 (and 2014) is now available for free download from here. BUT as we all good know there ain’t free lunch so instead of paying 50 € (or so) for Developer Edition we’ll pay for it with letting Microsoft know how we are using SQL Server features . I saw this in CTP3 installation but I’ve been expecting that this feature will be removed in RTM. It seemed ok that MSFT wants to know our experience with the product and capture potential problems to fix them before releasing final version.
Good news Everyone! SQL Server 2016 is here! RTM build with number 13.00.1605.1
I did install SQL Server 2016 RC3 recently and after answering all questions in the wizard and ticking all of the boxes I did check ConfigurationFile.ini to see what parameters installer uses.
Few parameters that caught my eye were related to telemetry:
Service Pack 2 for SQL Server 2014 will be released closed to date of release of SQL Server 2016. Reason why this is interesting for DBA’s is the fact that just right next to several fixes to database engine, some of the new features introduced in SQL Server 2016 will be also available in this build.
- Information displayed in SYS.DM_DB_INDEX_USAGE_STATS will be reset only when database would go offline or server is restarted.
- Other administrator friendly feature would be possibility to see progress of database recovery process with new xEvents with new actions
- New spills warnings during sort operations in tempdb
- Memory grant Showplan warning which shows in one of the 3 conditions:
- Under used – when the max used size is to small compared to the grant size. It is causing blocking and less efficient usage
- Over used – when the used size exceed the grant size. May cause out of memory on the server
- Excessive growth – when the dynamic grant increases too much. this may cause server instability and unpredicted query performance
Trace flags in SQL Server modify how database engine works. Those changes can affect SQL Server globally, making impact on performance of whole workload, or be limited to only one active session where the flag was set.
!!!Please always be careful when using trace flags in production and test extensively before setting them on production server!!!
You may and should check if there are any trace flags set when troubleshooting server issues.
more info at source: DBCC Tracestatus
we can see here trace flags set and what is their scope.
Few useful Trace flags in daily DBA life:
- 3226 – suppress information about successful backup, quite handy if you are running transaction log backup frequently on many databases.
- 3042 – SQL Server skips “pre-allocation” of of space for compressed backup and writes backup as it goes. May have negative impact on performance of backup operation. Useful when backup storage is used almost completely.
More trace flags will be added as I will have possibility to use them and time to write about it 🙂