Questions tagged [sql-server-2019]

SQL Server 2019 (major build version 15.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
34
votes
1answer
2k views

SQL Server 2019 executes unreachable code

Consider the following repro example (fiddle): CREATE FUNCTION dbo.Repro (@myYear int) RETURNS datetime AS BEGIN IF @myYear <> 1990 BEGIN RETURN NULL END DECLARE @...
22
votes
1answer
577 views

Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY

SQL Server 2019 CTP3.1 introduced an optimization to address last-page insert contention. This takes the form of an index option called OPTIMIZE_FOR_SEQUENTIAL_KEY. One imagines this could be an ...
20
votes
1answer
3k views

How do you enable SQL Server 2019's result set caching?

SQL Server 2019 CTP 2.3 added a few new columns to sys.databases, including is_result_set_caching_on: It's not documented yet in the What's New in SQL Server 2019, nor in the ALTER DATABASE page. I ...
11
votes
2answers
2k views

When `nvarchar/nchar` is going to be used with SQL Server 2019?

With SQL Server 2019 Microsoft introduces UTF-8 support for CHAR and VARCHAR data types and says: This feature may provide significant storage savings, depending on the character set in use. For ...
11
votes
1answer
692 views

After upgrading to SQL Server 2019, function throws “insufficient memory” error

I have moved an SQL Server 2012 Database (8GB in size) to a newly setup SQL Server 2019 virtual machine with the same memory and CPU configuration and changed the compatibility level to SQL Server ...
11
votes
1answer
155 views

Inline Scalar UDF Under RCSI - Can Results Differ

SQL Server 2019 introduces Scalar UDF inlining, aka "Froid." This ".. embeds [scalar UDFs] in the calling SQL query." Previously, scalar UDFs ran under their own execution context, separate from the ...
10
votes
3answers
2k views

SQL Server Agent fails after installing SQL Server 2019 CU2

After installing CU2 our SQL server agent randomly stops working. If you go to check on the status of the agent by opening Job Activity Monitor, it comes up blank for a while then throws an error: ...
10
votes
1answer
503 views

SQL 2019 install…what is AzureAttestService?

We started the installation and configuration of SQL Server 2019 on Windows 2019 and noticed after install a new service named 'AzureAttestService' was running and set to automatic. Services list the ...
8
votes
2answers
3k views

Dodgy T-SQL Query Execution driving me crazy

SQL Server 2019. Here's a link to a gist with the xml plan. Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of ...
8
votes
1answer
316 views

How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?

SQL Server 2019 introduced a new (and undocumented) system stored procedure: sys.xp_delete_files What, exactly, does this new extended stored procedure do? How does it differ from xp_delete_file ...
8
votes
1answer
148 views

initdata: No memory for kernel buffers

I recently upgraded my SQL Server 2017 Developer Edition on RedHat Enterprise Linux 7.6 to SQL Server 2019 CTP 3.0. The mssql-server service will no longer start. /var/opt/mssql/log/errorlog shows ...
7
votes
2answers
521 views

After SQL restart, CTE causes invalid object name error for a short period in SQL Server 2019

We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL ...
6
votes
1answer
549 views

Execution plan doesn't show memory grant

I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit CTRL+L in SQL Server Management Studio to ...
6
votes
2answers
668 views

SSRS error “something went wrong please try again later” in web portal

I have a brand new installation of SQL Server Reporting Services 2019 on top of MSSQL 2019 Enterprise CU1. SSRS has been installed and configured using default settings, apart from the service account ...
6
votes
0answers
186 views

Select permission denied when using linked server in a function unless inlining is off

We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production ...
5
votes
2answers
4k views

Where to download SQL Server 2019 Express LocalDB Installer MSI from

SQL Server 2019 is released but I there doesn't appear to be a LocalDB version available for download. Does anyone know if there will be a LocalDB release or if 2017 is the last version?
5
votes
3answers
229 views

“tempdb metadata memory-optimized” configuration option

Checking SQL Server 2019 CTP 2.5 (15.0.1500.28) I found this new configuration option and was wandering what is behind it. Couldn't find any information so far and was wondering if you know where to ...
5
votes
1answer
113 views

Is Scalar UDF inlining broken?

This SQL code returns "a" instead of "asdfg": CREATE FUNCTION [dbo].[GetPayload2] (@ID int) RETURNS VARCHAR(300) AS BEGIN IF @ID = 1 RETURN 'asdfg' RETURN '' END GO SELECT dbo.GetPayload2(1) ...
4
votes
2answers
3k views

Upgrading from SQL Server 2012 SP1 to SQL Server 2019

I'm upgrading an internal database at work, and we are creating a new VM to host it. I can't see an option to generate a script to export the SQL Server 2012 SP1 database for server version SQL ...
4
votes
4answers
162 views

SQL Server 2019 performance worse than 2012… am I missing something?

We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs....
4
votes
1answer
219 views

Changing to compatibility level 150 makes SQL Server consume all available memory

We're moving an existing DB to a new SQL Server 2019 instance. If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available ...
4
votes
1answer
322 views

SQL Server evaluation period has expired

OS: Windows 10 Enterprise 64bit SQL Server: Microsoft SQL Server 2019 (RC1) SQL Server Version: Developer Edition I'm unable to start the SQL Server service due to the following error, "SQL Server ...
4
votes
4answers
266 views

Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR

I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database: SELECT * FROM Users u LEFT JOIN Comments c ON u.Id = c....
4
votes
1answer
92 views

Heap table insert

I am trying to understand the behaviour of SQL Server when it INSERT record into a Heap table. According to about sql Server blog post, paragraph 2 "SQL Server would not put a new row to the page if ...
4
votes
1answer
506 views

Performance of Polybase

We've been experimenting with Polybase on a SQL Server 2019 (CU2) box, using SQL Server external data sources and the performance has not been good - in most cases a 1400% increase. In each case all ...
4
votes
0answers
92 views

Can backups done through external clients cause memory pressure on SQL Server?

Developer in DBA role here, we recently migrated from 2008 to 2019 and are having memory related problems on the new server. Every 3 to 6 days the OS will start paging and CPU usage would spike to 99%,...
4
votes
0answers
127 views

How can Cumulative Updates to SQL Server 2019 be Slipstreamed into a Single Installer?

I'd like to take the latest version of SQL Server (SQL Standard, 15.0.2000.5), and apply the latest CU to it so that I have a single installer, thus reducing the size of the resulting package. The ...
4
votes
0answers
122 views

SQL Server 2019: Memory performance with graph queries (possible memory leak)

I'm currently working on implementing an upgrade to SQL Server 2019 in order to make use of the graph features that are available in it. Our database stores records of files and their children, and ...
3
votes
2answers
490 views

When should the IGNORE_DUP_KEY option be used on an index?

Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index. My goal is to ...
3
votes
1answer
263 views

User-defined function returns first character only although RETURN VARCHAR(10) is defined?

Everyone, I cannot figure out what is missing in the below user-defined function: CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money) RETURNS VARCHAR(10) AS BEGIN IF (@Salary < 30000) ...
3
votes
3answers
156 views

Subscription Initialization throws Error “The option ”INLINE=ON“ is not valid for this function” which is incorrect

Scenario We are upgrading our SQL estate to SQL Server 2019 and have setup what will be the production environment. From: SQL2008R2 Standard/Windows Server 2008R2 - Primary Server/Secondary Server (...
3
votes
1answer
172 views

SQL Server AlwaysOn Availablity Group Automatic seeding

I created an AlwaysOn in SQL Server 2019 and I selected Automatic seeding, but it does not create my database in secondary nodes, On the other hand, if I create my database manually I give and error ...
3
votes
2answers
114 views

Restoring MDF without LDF file

I am trying to attach a database just from the MDF file, cause the server crashed and could never find the LDF. I am on a 2019 MS SQL Server. I am following the instructions from this page. I have ...
3
votes
1answer
55 views

Clustered index data access

I am trying to understand how SQL Server access data from the clustered index. My understanding is when the table has a clustered index SQL should be able to seek into the single page that holds the ...
3
votes
1answer
81 views

Getting all undeclared variables from query

Imagine you try to run the following query: SELECT * FROM dbo.Customers c WHERE id = @id AND dateTimeCreated = @date You'll get an error message along the lines of Must declare the scalar ...
3
votes
1answer
154 views

Allowing non-privileged users to cause privileged code to run

Context: Our SQL server is used for teaching, and we need to be able to automatically create databases for the students on demand and in real-time. We also need the students to be able to delete ...
3
votes
0answers
80 views

Turn off Error AND Usage Reporting SQL Server

Unfortunately, SQL Server usage internet bandwidth and us activity in SQL, for R&D. You can find in your server, this service is called: SQL Server error and usage. This service is active without ...
2
votes
3answers
141 views

Why can't SQL Server find a seek plan? Is this a bug?

create table Test1 (Id int not null, H char, primary key (Id), index i1 unique (H)) create table Test2 (Id int not null, H char, primary key (Id), index i2 unique (H)) insert into Test1 values (1, 'A')...
2
votes
1answer
40 views

How to control Segmentation min/max data_id on a non-clustered ColumnStore index

Given a simple row-based table without a PK but with a row-based clustered index like so: create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value]) Then I wish to ...
2
votes
5answers
266 views

Granting a user a permission to restore a database

In a database I created a user with the following roles: Server Roles public Database roles db_backupoperator db_ddladmin db_datareader db_datawriter The problem is I don't want to change the ...
2
votes
2answers
847 views

Replication problem in SQL Server 2019

I have installed SQL Server 2019 RC1 and cannot create a snapshot. I keep getting the same message when I run the snapshot agent: 2019-10-30 12:32:41.59 Microsoft (R) SQL Server Snapshot Agent 2019-...
2
votes
1answer
776 views

SQL Server 2019 CTP 2.1 on Ubuntu fails after powering off

I've installed Microsoft SQL Server 2019 (CTP2.1) - 15.0.1100.94 (X64) on my VirtualBox Ubuntu 16.04.5 LTS. Everything seemed to work properly until I've powered off the machine. Since then SQL Server ...
2
votes
1answer
31 views

Cannot find DReplay.exe for SQL Server 2019

I have been working on SQL Server 2019 version and trying to setup a load test environment using SQL Server Distributed Reply Controller - Client, however I cannot find the DReplay.exe. I thought the ...
2
votes
1answer
189 views

Sql Server - Build a json array of integer from an aggregate

I want to use an aggregate to build a simple JSON array like this LuckyNumbers array : [{ "id": 1, "name": "Josian", "LuckyNumbers": [581, 777] }, { "id": 2, "name": "Paul", "...
2
votes
1answer
57 views

How to migrate SQL Server 2014 to SQL Server 2019 on Linux using AG [closed]

I would like to migrate SQL Server 2014 on Windows to SQL Server 2019 on Linux using while running AG. Is there an option to Add the SQL Server 2019 to the same AG and once the time comes gracefully ...
2
votes
1answer
76 views

Different execution plans in SQL Server for same query but different offset

I am doing performance experiments regarding the fulltext search feature of Microsoft SQL Server using the stackoverflow database. I have a (seemingly simple) query that results in different ...
2
votes
1answer
149 views

SQL Server 2019: InitPersistentVersionStore failed after installing CU2

I have installed SQL Server 2019 CU 2 over a vanilla install of SQL Server 2019 RTM. Now I am puzzled by the following messages in the error log at startup: RecoveryUnit::...
2
votes
1answer
261 views

Limit TLS and Cipher Suites on SQL Server 2016-2019

I'm wanting to configure some SQL 2016+ servers to use only TLS 1.2+ and a subset of Cipher suite options (no ciphers considered weak or compromized). I'm having trouble tracking down how to ...
2
votes
1answer
140 views

SQL Server Demo Environment on Docker Desktop (Win 10): DB storage und upgrade paths

I would like to run SQL Server 2019 on my work as well as my private machine (both running Windows 10 Pro). Thus I thought Docker Desktop would be a nice option. However I would like to keep the ...
2
votes
2answers
54 views

dbcc checkdb runs for interminably long time, 50% of the time

I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database. The integrity check part ...