November | 2017 | SQL with Manoj

SQL Server 2017 RTM, download, and cool “New Features” !!!

November 16, 2017 1 comment

On 2nd October 2017 Microsoft released the full & final General Available (GA) version of SQL Server 2017.

As announced earlier with the first CTP release, the new SQL Server 2017 will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.

–> Downloads:

– SQL Server 2017 RTM bits: To download the SQL Server 2017 RTM you can Register and Download the Full version or Free evaluation version (180 days).

– SSMS 17.x bits: download here

– Reporting Services (SSRS) 2017 bits: Starting SQL Server 2017 is no longer bundled with the ISO or CAB files, and will need to be installed separately: download here

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 16299: ) (Hypervisor)

–> Here are few few “Top Features” added in SQL Server 2017:

1. SQL Server on Linux: Now run SQL Server on all platforms, Windows, Linux, Mac, docker.

2. New Graph Database: for modeling many-to-many relationships.

3. Resumable online index rebuild: Resumes an online index rebuild operation from where it stopped after a failure or a pause.

4. Adaptive query processing: feature optimizes strategies to your application workload in runtime condition.

5. IDENTITY_CACHE option: to avoid gaps in identity values in case of server restarts or failover.

6. SQL Server Machine Learning Services: which added Python support, with existing R language.

7. SSIS “Scale Out” and HA (High Availability): execute SSIS packages across multiple servers.

–> For a full list of various features already rolled out with previous CTP versions, please check my previous posts below:

– All new features added in SQL Server 2016 SP1, [link].

– New features added in:

– SQL Server 2017 CTP 1.x.

– SQL Server 2017 CTP 2.x.

– SQL Server 2017 RC1

–> Videos on SQL Server 2017

– Download & Install SQL Server 2017 & SSMS on Windows

– Install SQL Server on Linux

–> References:

>> SQL Server 2017 official Page

>> Docs for SQL Server 2017

SQL DBA – Get top Tables with Stale Statistics

November 10, 2017 Leave a comment

SQL Server Query Optimizer (QO) uses Statistics to create query plans that improve query performance. The QO uses these statistics to estimate the cardinality (number of rows) in the query result.

There are times when these STATISTICS may go stale after certain INSERT, UPDATE and DELETE operations which involves large amount of rows, not qualifying for Auto-Stats Threshold.

For more about Statistics and Threshold check on MSDN Docs link

Identify Stale Stats:

Here is a simple query to identify TOP tables that have stale statistics, so that you can create UPDATE statistics for them explicitly:

SELECT TOP 50
	 CONCAT(sch.name, '.', obj.name) AS 'Table Name'
	,MAX(sp.last_updated)			AS 'Stats Last Updated'
	,MAX(sp.rows)					AS 'Rows'
	,MAX(sp.modification_counter)	AS 'Modification Counter'
FROM sys.stats st
JOIN sys.objects obj ON st.object_id = obj.object_id
JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
CROSS APPLY sys.dm_db_stats_properties(obj.object_id, st.stats_id) AS sp
WHERE obj.is_ms_shipped = 0 
GROUP BY CONCAT(sch.name, '.', obj.name)
ORDER BY MAX(sp.modification_counter) DESC;