Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
40
votes
2answers
19k views

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific ...
39
votes
3answers
5k views

SQL Server's “Total Server Memory” consumption stagnant for months with 64GB+ more available

I have run into an odd issue where SQL Server 2016 Standard Edition 64-bit has seemed to have capped itself off at precisely half of the total memory allocated towards it (64GB of 128GB). The output ...
34
votes
7answers
8k views

What's a good use case for SELECT * in production code?

Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object). But I ran across a case now where I'm tempted to use ...
34
votes
3answers
8k views

Why would a SELECT query cause writes?

I've noticed that on a server running SQL Server 2016 SP1 CU6 sometimes an Extended Events session shows a SELECT query causing writes. For example: The execution plan shows no obvious cause for the ...
34
votes
2answers
4k views

Is it generally faster to select into a temp table than selecting into an actual table?

I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity? I thought I recall it saying something special about tempdb and ...
31
votes
4answers
1k views

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
29
votes
1answer
3k views

What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions?

As Microsoft makes SQL Server version upgrades more frequently since the SQL Server 2005 or 2008 days, a lot of companies find it difficult to determine when an upgrade is a "must have!" and when an ...
28
votes
5answers
6k views

Why does my SELECT DISTINCT TOP N query scan the entire table?

I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two ...
28
votes
1answer
684 views

Is the eager spool operator useful for this delete from a clustered columnstore?

I'm testing deleting data from a clustered columnstore index. I noticed that there is a large eager spool operator in the execution plan: This completes with the following characteristics: 60 ...
27
votes
4answers
37k views

Blue icon with question mark - what does it mean?

The SQL Server instance is accessible and seems to be fine. Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Mar 6 2017 14:18:16 Copyright (c) Microsoft Corporation ...
27
votes
2answers
1k views

What is a “Partial Matching Index”?

I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here ...
26
votes
2answers
1k views

Why does a subquery reduce the row estimate to 1?

Consider the following contrived but simple query: SELECT ID , CASE WHEN ID <> 0 THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE) ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2) END AS ...
25
votes
3answers
10k views

SQL Server clears plan cache and execution stats periodically

After upgrading SQL Server 2014 to 2016, the server keeps resetting cached execution plans and dm* views (like dm_exec_query_stats) etc. every couple of hours As if someone executes DBCC ...
25
votes
1answer
9k views

Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many?

DECLARE @T TABLE( Col NCHAR(1)); INSERT INTO @T VALUES (N'A'), (N'B'), (N'C'), (N'Ƕ'), (N'Ƿ'), (N'Ǹ'); SELECT * FROM @T WHERE ...
23
votes
1answer
2k views

Why doesn't this query use an index spool?

I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap: CREATE TABLE ...
23
votes
1answer
914 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...
22
votes
3answers
25k views

varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata. Does it matter anymore at this point? I saw some posts on ...
22
votes
3answers
5k views

How to disable “Completion time:…” in SQL Server Messages window

Every query I run in SSMS append the annoying message: "Completion time:...". How can I disable that text?
20
votes
3answers
628 views

Why can it take up to 30 seconds to create a simple CCI rowgroup?

I was working on a demo involving CCIs when I noticed that some of my inserts were taking longer than expected. Table definitions to reproduce: DROP TABLE IF EXISTS dbo.STG_1048576; CREATE TABLE dbo....
18
votes
2answers
2k views

Disk space full during insert, what happens?

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb ...
18
votes
2answers
1k views

Why is AT TIME ZONE nondeterministic?

SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it. Why is ...
18
votes
1answer
1k views

Nonclustered index storage on clustered columnstore

In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is ...
18
votes
1answer
2k views

Why does this derived table improve performance?

I have a query which takes a json string as a parameter. The json is an array of latitude,longitude pairs. An example input might be the following. declare @json nvarchar(max)= N'[[40.7592024,-73....
17
votes
5answers
48k views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...
17
votes
1answer
11k views

datetime2(0) vs datetime2(2)

According to the documentation datetime2 (Transact-SQL): Storage size 6 bytes for precisions less than 3. 7 bytes for precisions 3 and 4. All other precisions require 8 bytes. The size of ...
17
votes
2answers
61k views

The certificate chain was issued by an authority that is not trusted

some time ago I installed SQL server 2016 developer edition on a windows 10 home edition environment (a laptop to be precise) and everything was fine. then someone - an administrator on the box - ...
17
votes
1answer
8k views

SQL Server 2016 Bad Query Plan locks up DB once a week

Once a week, for the past 5 weeks, around the same time of day (early morning, may be based on user activity when people are starting to use it), SQL Server 2016 (AWS RDS, mirrored) starts timing out ...
17
votes
1answer
1k views

Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a ...
16
votes
2answers
3k views

SQL estimates are way off on DELETE statement with Triggers on huge tables

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
16
votes
3answers
12k views

SQL Server - Select most recent record from each group when performance is critical

I run a SQL Server 2016 database where I have the following table with 100+ millions rows: StationId | ParameterId | DateTime | Value 1 | 2 | 2020-02-04 15:00:000 | 5....
16
votes
1answer
659 views

Why is CHECKDB reading the transaction log file on a database with a memory optimized table?

tl;dr: why is CHECKDB reading the transaction log for a user database with memory optimized tables? It appears that CHECKDB is reading the transaction log file of the user database when it's checking ...
15
votes
1answer
4k views

SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?...
15
votes
1answer
494 views

Sort spills to tempdb but estimated rows equals to actual rows

On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of ...
14
votes
1answer
4k views

How did my colleague get his own schema?

I have a stored procedure that: checks if a table exists and if so, drops it. creates that table again then fills that table with about 30 queries. When I (DB Owner) run this proc, everything works ...
14
votes
2answers
6k views

Strange performance problem with SQL Server 2016

We have a single instance of SQL Server 2016 SP1 running in a VMware virtual machine. It contains 4 databases, each for a different application. Those applications are all on separate virtual servers. ...
14
votes
1answer
569 views

Incorrect behaviour of SQL Server 2016 with memory-optimized tables

Please have a look at the following SQL query: CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE ( source_col INT NULL, target_col INT not NULL INDEX ix_InMemoryTable NONCLUSTERED (target_col) ) ...
14
votes
1answer
784 views

What is the maximum number of allowable bound actions for an extended event?

If you add "too many" actions to an event in an event session, you will receive this error: Msg 25639, Level 16, State 23, Line 1 The event, "[event name]", exceeds the number of allowable bound ...
14
votes
2answers
2k views

Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory

CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days. We are already running with "optimize for ad hoc workloads" option on. (Entity Framework and custom reporting creates a lot of ad hocs!) ...
14
votes
2answers
2k views

SQL Server 2016 vs 2012 insert performance

I have two SQL Server instances on the same server: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64) ...
13
votes
3answers
25k views

How to add a Default constraint while creating a table? SQL Server [closed]

I am trying to create a new table with columns followed by their constraint as shown below. Create tblTest( columns.. .. .. Gender int, Constraint DF_tblTest_Gender Default 3 For Gender, .. .. .. ) ...
13
votes
6answers
33k views

The operating system returned error 21 (The device is not ready.)

Every time I reboot Windows, for some databases I get this error: The operating system returned error 21(The device is not ready.) I checked the disk with chkdsk /r - no bad sectors. I executed ...
13
votes
2answers
733 views

Unclear update conflict

I have two questions: 1. Why do I get update conflict in this situation instead of just blocking: -- prepare drop database if exists [TestSI]; go create database [TestSI]; go alter database [TestSI] ...
13
votes
1answer
2k views

Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?

If I take a backup of a database from a SQL Server 2016 instance and then restore it to a 2017 instance to do some work on it. Can I then turn around and back up that database from the 2017 instance ...
13
votes
2answers
723 views

Displaying an Estimated Execution Plan generates CXPACKET, PAGELATCH_SH, and LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits

I'm running Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) on a 4 vCPU VM with max degree of parallelism set to 2 and cost threshold for parallelism set to 50. In the mornings, when trying to ...
13
votes
2answers
1k views

Cardinality estimation problem on inner join

I'm struggling to understand why row estimation is so terribly wrong, here is my case: Simple join - using SQL Server 2016 sp2 (same issue on sp1), dbcompatiblity=130. select ...
13
votes
1answer
1k views

SQL Server Spill level 0 - what is it?

I was asked to look into performance of a query in SQL Server 2016 SP2, and I found something that I have not seen before: <Warnings> <SpillToTempDb SpillLevel="0" SpilledThreadCount="1" /&...
13
votes
2answers
5k views

Why does dropping foreign keys take long?

I have made a script that, one at a time, deletes all the foreign keys from a database, just like this: ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1 ALTER TABLE MyTable2 DROP CONSTRAINT ...
13
votes
2answers
3k views

Alternative to MakeValid() for spatial data in SQL Server 2016

I have a very large table of geography LINESTRING data that I'm moving from Oracle to SQL Server. There are a number of evaluations that are executed against this data in Oracle, and they will need ...
13
votes
1answer
474 views

Can a un-used NONCLUSTERED INDEX still enhance query speed?

This is a strange situation, but I'm hoping someone has an answer. During some performance troubleshooting, we added a NONCLUSTERED INDEX to a table, as was requested by sp_BlitzIndex. We checked its ...
13
votes
3answers
837 views

Overcome LIKE character length limitation

By reading this LIKE character length limitation here, it looks like I can't send a text longer than ~4000 characters in a LIKE clause. I'm trying to fetch the query plan from query plan cache for a ...

1
2 3 4 5
46