locked
What to change to take advantage of SQL Server 2016 upgrade RRS feed

  • Question

  • We are migrating from SQL Server 2008 R2 Enterprise Edition  on Windows Server 2008 R2 to SQL Server  2016 Enterprise Edition on Windows Server 2106. We are also upgrading our hardware from a basic 50 gb, 3.4 GHz 2 core to  150 gb, 3.6 GHz 8 core.

    We use the RDBMS for data warehousing, meaning we run SSIS to import data from various files, and other databases. Then we transform the imported data into dimensional data model (facts and dimensions) and run reports. We also run Index Optimization Procs, Database Integrity Checks and Database Backups as maintenance tasks.

    What should we do on our ETLs, database objects, maintenance tasks to take advantage of SQL 2016. One thing I know is to start using column store indexes for improved performance.

    Thanks in advance.

    Tuesday, May 7, 2019 12:57 PM

All replies

  • The Query Store is a completely new feature which will help DBA’s to troubleshoot performance issues resulting from changes in the query plan. Historical data is captured to allow for comparisons to be made between queries, plans and statistics.  They Query Store is not enabled by default, so in order to use it you can enable it with this command:

    ALTER DATABASE AdventureWorks SET QUERY_STORE = ON; 


    Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.

    Tuesday, May 7, 2019 1:10 PM