Database Sql Server 2017

Key Features

SQL Server on Linux (New in 2017)

SQL Server runs natively on Linux via a platform abstraction layer (SQLPAL) built on top of drawbridge/library OS. Supported on:

• Red Hat Enterprise Linux 7.3+
• SUSE Linux Enterprise Server v12 SP2+
• Ubuntu 16.04+
• Docker containers

Key differences from Windows:

• Configuration via mssql-conf instead of SQL Server Configuration Manager
• Service managed via systemctl instead of Windows services
• No SQL Server Agent GUI -- use mssql-server-agent package + T-SQL
• No SSMS on Linux -- use Azure Data Studio or remote SSMS
• No Windows Authentication natively -- requires Active Directory integration via Kerberos
• File paths: /var/opt/mssql/data/ (default data), /var/opt/mssql/log/ (default log)

# Configure with mssql-conf
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 8192
sudo systemctl restart mssql-server

Features NOT available on Linux (2017):

• Replication (publisher/distributor -- subscriber works)
• Stretch Database
• PolyBase
• Distributed queries (linked servers to non-SQL sources)
• Machine Learning Services (added on Linux in 2019)
• FILESTREAM
• CLR assemblies with EXTERNAL_ACCESS or UNSAFE

Adaptive Query Processing

Three features that allow the optimizer to adapt based on actual runtime conditions. Requires compat level 140.

1. Batch Mode Adaptive Joins: The optimizer defers the join algorithm choice (hash join vs. nested loops) until runtime based on actual row counts at the adaptive threshold.

-- Look for AdaptiveJoin operator in execution plans
-- Works only with batch mode (requires at least one columnstore index involvement)
-- The plan shows an AdaptiveJoin with an AdaptiveThresholdRows property

2. Interleaved Execution for Multi-Statement TVFs (MSTVFs): Instead of a fixed 1-row estimate for MSTVFs, the optimizer pauses, executes the MSTVF, counts actual rows, then resumes optimization with the real cardinality.

-- Before 2017: MSTVFs always estimated at 1 row (or 100 with TF 2453)
-- After 2017 (compat 140): Actual row count used for downstream operators
-- No action needed -- it just works. Check plans for accurate cardinality on MSTVF operators.

3. Batch Mode Memory Grant Feedback: If a query spills to tempdb or wastes memory grant, the feedback adjusts the grant for the next execution. Applies to batch mode operators only in 2017.

-- Monitor memory grant feedback
SELECT * FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NOT NULL;

-- Check if feedback is adjusting grants via execution plan XML:
-- Look for IsMemoryGrantFeedbackAdjusted attribute

2017 limitation: Memory grant feedback is batch mode only. Row mode memory grant feedback added in 2019.

Graph Database Support

Model many-to-many relationships with NODE and EDGE tables:

-- Create node tables
CREATE TABLE dbo.Person (
    PersonID INT PRIMARY KEY,
    Name NVARCHAR(100)
) AS NODE;

CREATE TABLE dbo.City (
    CityID INT PRIMARY KEY,
    Name NVARCHAR(100)
) AS NODE;

-- Create edge table
CREATE TABLE dbo.LivesIn AS EDGE;

-- Insert data
INSERT INTO dbo.Person VALUES (1, 'Alice');
INSERT INTO dbo.City VALUES (1, 'Seattle');
INSERT INTO dbo.LivesIn ($from_id, $to_id)
VALUES ((SELECT $node_id FROM dbo.Person WHERE PersonID = 1),
        (SELECT $node_id FROM dbo.City WHERE CityID = 1));

-- Query with MATCH
SELECT p.Name, c.Name AS City
FROM dbo.Person p, dbo.LivesIn l, dbo.City c
WHERE MATCH(p-(l)->c);

2017 limitations:

• No SHORTEST_PATH (added in 2019)
• No edge constraints (added in 2019)
• No derived table or view support in MATCH
• No polymorphic queries across node types

Automatic Tuning

Automatic plan correction detects plan regressions and forces the last known good plan:

-- Enable automatic tuning
ALTER DATABASE [MyDB] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

-- Monitor automatic tuning recommendations
SELECT * FROM sys.dm_db_tuning_recommendations;

-- Check what plans have been automatically forced
SELECT reason, score, state_transition_reason,
       JSON_VALUE(details, '$.implementationDetails.script') AS force_script
FROM sys.dm_db_tuning_recommendations
WHERE state_transition_reason = 'AutomaticTuningOptionEnabled';

Requires Query Store to be enabled and in READ_WRITE mode.

Python in ML Services

SQL Server 2017 adds Python alongside R in Machine Learning Services:

EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
import pandas as pd
from sklearn.linear_model import LinearRegression
model = LinearRegression()
model.fit(InputDataSet[["feature1","feature2"]], InputDataSet["target"])
OutputDataSet = pd.DataFrame({"prediction": model.predict(InputDataSet[["feature1","feature2"]])})
',
    @input_data_1 = N'SELECT feature1, feature2, target FROM dbo.TrainingData';

Resumable Online Index Rebuild

Pause and resume index rebuild operations. Useful for maintenance windows:

ALTER INDEX IX_MyIndex ON dbo.MyTable REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

-- Pause
ALTER INDEX IX_MyIndex ON dbo.MyTable PAUSE;

-- Resume
ALTER INDEX IX_MyIndex ON dbo.MyTable RESUME;

-- Check status
SELECT * FROM sys.index_resumable_operations;

2017 scope: Resumable REBUILD only. Resumable CREATE INDEX added in 2019.

Query Store Enhancements

New in 2017:

• Wait stats capture -- Query Store now captures per-query wait statistics:

ALTER DATABASE [MyDB] SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);

-- Query wait stats
SELECT ws.wait_category_desc, ws.avg_query_wait_time_ms,
       qt.query_sql_text
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
ORDER BY ws.avg_query_wait_time_ms DESC;

CLR Strict Security

Breaking change in 2017: CLR assemblies default to requiring UNSAFE assemblies to be signed and the corresponding certificate/asymmetric key must have a login with UNSAFE ASSEMBLY permission.

-- To restore 2016 behavior (NOT recommended for production):
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

For assemblies that cannot be signed, use trusted assemblies:

EXEC sp_add_trusted_assembly @hash = 0x...; -- SHA-512 hash of the assembly

Version Boundaries

• This agent covers SQL Server 2017 (compat level 140) specifically
• Features NOT available in 2017 (introduced later):
  • Intelligent Query Processing full suite (2019) -- only batch mode AQP subset available
  • Row mode memory grant feedback (2019)
  • Batch mode on rowstore (2019)
  • Scalar UDF inlining (2019)
  • Table variable deferred compilation (2019)
  • Accelerated Database Recovery (2019)
  • Resumable CREATE INDEX (2019)
  • APPROX_COUNT_DISTINCT (2019)
  • Big Data Clusters (2019)
  • Parameter Sensitive Plan optimization (2022)
  • Query Store hints (2022)
  • Ledger tables (2022)
  • Contained Availability Groups (2022)

Common Pitfalls

1. Adaptive joins only in batch mode -- Requires at least one columnstore index reference or compat level 150+ for batch mode on rowstore. Pure rowstore workloads on compat 140 do not get adaptive joins.
2. CLR strict security breaks upgrades -- Unsigned CLR assemblies that worked in 2016 will fail in 2017. Prepare migration by signing assemblies or using trusted assemblies.
3. Linux feature gaps -- Several features are unavailable on Linux in 2017 (see above). Validate feature requirements before choosing Linux.
4. Memory grant feedback instability -- In some workloads, memory grant feedback can oscillate. Monitor via plan cache for IsMemoryGrantFeedbackAdjusted and disable per-query with DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK hint if needed.
5. Automatic tuning aggressive plan forcing -- May force plans that were coincidentally fast. Monitor sys.dm_db_tuning_recommendations for false positives.
6. Graph MATCH limitations -- Cannot use MATCH in subqueries, CTEs, or with derived tables in 2017. Workaround with temp tables.

Migration from SQL Server 2016

When upgrading from SQL Server 2016 (compat level 130) to 2017 (compat level 140):

1. Enable Query Store first (if not already) -- Capture baseline at compat 130
2. Upgrade engine -- Keep compat level at 130 initially
3. Test CLR assemblies -- CLR strict security is the biggest breaking change. Sign unsigned assemblies.
4. Change compat level to 140 -- Unlocks adaptive query processing features
5. Monitor for regressions -- Use Query Store to compare performance before/after compat level change
6. Enable automatic tuning -- FORCE_LAST_GOOD_PLAN = ON provides a safety net for plan regressions
7. Enable wait stats capture in Query Store
8. Linux consideration -- 2017 is the first version where Linux is an option. Evaluate if Linux hosting meets your requirements.

Known Behavioral Changes at Compat 140

• Batch mode adaptive joins change join strategies at runtime
• Interleaved execution changes MSTVF cardinality estimates (can change downstream plan choices)
• STRING_AGG function available (replaces FOR XML PATH concatenation pattern)
• TRIM, TRANSLATE, CONCAT_WS functions added

Reference Files

Load these for deep knowledge:

• ../references/architecture.md -- Storage engine, buffer pool, query processing
• ../references/diagnostics.md -- Wait stats, DMVs, Query Store usage, Extended Events
• ../references/best-practices.md -- Instance configuration, backup strategy, security