While working on optimizing performance of SQL Queries, its very important that the right numbers are compared.
Performance numbers might vary with each execution mostly due to SQL Server cache and buffer.
Below are the steps that can be taken to make sure that you are seeing right numbers.
– Clear the cache by running the above Script provided in this Link
– Run the query that you are trying to performance tune and note the Statistics. (a)
– Run the above script again to clear the cache.
– Now execute the tuned query and note down the Statistics. (b)
Query Statistics (a) and (b) would now be comparable as both of them are executed over the clean cache.
Below is the TSQL Script to fetch the list of tables in a database with identity column properties
SELECT SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME,
OBJECT_NAME(T.OBJECT_ID) AS TABLENAME,
TI.NAME AS COLUMN_NAME,
TI.LAST_VALUE AS LAST_VALUE
FROM SYS.TABLES AS T
SYS.IDENTITY_COLUMNS AS TI
ON T.OBJECT_ID = TI.OBJECT_ID
Below script that can be used to clear SQL Server Cache. Helpful while working on performance tuning of SQL Scripts.
DBCC FREESYSTEMCACHE ('ALL')
Note that this script is solely for running on Development environments, do not run in Production as it would cause issues to queries that depend on cached data.
SQL Server 2016 has multiple enhancements such as
Mission Critical Performance
Deeper Insights Across Data
Hyper scale Cloud
Below is the link to the Data sheet.
SQL Server 2016 PDF data Sheet
Stretch DB feature in SQL Server 2016 helps to move a part of a SQL Server table to the azure for Archival.
It would help in situations where there is a need to archive non transactional Rows from a table, but still need the ability to Query them when needed.
Watch this video for more details.
Query Store in SQL Server 2016 is a kind of a Flight data recorder for SQL Server.
Its a good feature to debug performance issues with Query plans.
It helps to Record, Analyze, Identify and Force good performing Query plans.
Watch this video to understand more about Query store.