Skip to main content

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
33 votes
1 answer
2k views

SQL Server 2019 executes unreachable code

[Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.] Consider the following repro example (fiddle): CREATE FUNCTION dbo.Repro (@myYear int) RETURNS ...
Heinzi's user avatar
  • 3,155
23 votes
1 answer
906 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 ...
Michael Green's user avatar
20 votes
2 answers
3k views

SQL Server in-memory optmized tempdb metadata memory usage continually growing

Problem We have enabled sp_configure 'tempdb metadata memory-optimized' = 1, and now the tempdb meta data is taking over 400 GB on one of our servers and continues to grow. There are some drops in ...
SQLing4ever's user avatar
20 votes
2 answers
6k 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 ...
Brent Ozar's user avatar
17 votes
1 answer
113k views

How do I download SQL Server 2019 developer edition?

I've been looking around the web on how to download SQL serer 2019 Developer edition. Currently, on the Microsoft site they only have the 2022 version, so I was wondering how one can download older ...
Mike's user avatar
  • 171
16 votes
3 answers
34k 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?
Simon's user avatar
  • 263
16 votes
1 answer
10k views

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 ...
SQLDBAND's user avatar
  • 355
16 votes
1 answer
501 views

Does SQL Server 2019 have task scheduling differences between enterprise and standard editions?

In How It Works: SQL Server 2012 Database Engine Task Scheduling, Bob Dorr explains some of the changes for worker scheduler assignment in SQL Server 2012. He mentions that some of the improvements ...
Joe Obbish's user avatar
  • 32.5k
14 votes
1 answer
21k views

Clone a database from SQL Server 2019 to 2017 [duplicate]

I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says: System.Data....
serge's user avatar
  • 421
13 votes
7 answers
2k views

Improve The Performance Of Multiple Date Range Predicates

let's say You have a stored procedure that accepts arrays of datetimes, which are loaded into a temp table, and used to filter on a datetime column in a table. There can be any number of values ...
Erik Darling's user avatar
  • 41.9k
12 votes
2 answers
1k views

Unable to execute query and not even able to generate Estimated execution plan

I am working on SQL Server 2019. I have a table dbo.AllDates where I have all dates from 1990 to 2050. I have another table dbo.ActualExchangeRates where I have actual exchange rates for certain ...
saadkaul's user avatar
  • 113
12 votes
2 answers
3k 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 ...
gotqn's user avatar
  • 4,026
12 votes
1 answer
721 views

Is there a reliable way to check if a trigger being fired was the result of a DML action from another *specific* trigger?

Is there any way to tell from within a trigger, when it gets fired, that it is being fired as the result of a DML action that occurred within another specific trigger? Any chance any information about ...
J.D.'s user avatar
  • 38.1k
12 votes
1 answer
928 views

Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?

I'm testing against SQL Server 2019 CU14. I have a pure row-mode query that selects the top 50 rows from a complicated view. The full query takes 25426 ms of CPU time at MAXDOP 1 and 19068 ms of CPU ...
Joe Obbish's user avatar
  • 32.5k
12 votes
1 answer
7k 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 ...
Solomon Rutzky's user avatar
12 votes
1 answer
758 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 ...
Alan Deutscher's user avatar
11 votes
3 answers
4k views

Does killing an application kill the SQL connection?

Suppose an application has established two connections to SQL Server. A session is actively running a query A session that is sleeping, waiting for the next instruction Does killing this application ...
variable's user avatar
  • 3,566
11 votes
2 answers
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 ...
ZCT's user avatar
  • 211
11 votes
3 answers
2k views

MERGE into a view with INSTEAD OF triggers

I have a view with instead of triggers and I'm trying to use it with EF Core, which tries to batch inserts together in a form of merge statement. Here's my table and view: create table tbl (id ...
torvin's user avatar
  • 213
11 votes
4 answers
17k 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....
blobbles's user avatar
  • 1,621
11 votes
1 answer
1k 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 ...
dsungaro's user avatar
  • 113
11 votes
1 answer
185 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 ...
Michael Green's user avatar
11 votes
3 answers
3k 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: ...
Adam Kavan's user avatar
10 votes
1 answer
3k views

Is it possible for SQL Server to grant more memory to a query than is available to the instance

I was asked the other day what would happen if SQL Server wanted to run a single query that was granted more memory than is available to the instance. My initial thoughts were that I may see ...
SE1986's user avatar
  • 1,990
10 votes
4 answers
20k 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 ...
Bartosz X's user avatar
  • 503
9 votes
2 answers
500 views

SQL Server sample database WideWorldImporters fails dbcc checkdb

Problem Statement I just ran dbcc checkdb on the sample WideWorldImporters database. I received (4) errors about statistics corruption that I do not remember having seen in the past when I ran this ...
Tom Wickerath's user avatar
9 votes
1 answer
536 views

WITH EXECUTE AS SELF returning an error when running sys.fn_dump_dblog

I'm attempting to configure a stored procedure to EXECUTE AS a user with sysadmin privileges to allow a non-privileged user to obtain results from the undocumented TVF sys.fn_dump_dblog. I'm creating ...
Hannah Vernon's user avatar
  • 70.2k
9 votes
2 answers
259 views

Why does this TVF throw error 9820 with GETDATE() as an input parameter?

I am testing on SQL Server 2019 CU14. Consider the following table-valued function created against a SQL Server database with compatibility level 130 or 140: -- requires database compat 130 or 140 to ...
Joe Obbish's user avatar
  • 32.5k
9 votes
1 answer
791 views

2019 Enterprise - NOLOCK without NOLOCK, page latch time-out on tempdb and finally a dump [closed]

We have a very strange problem with a new SQL Server 2019 on 2 new physical machines: Infrastructure: Starting a new installation of SQL Server 2019 Enterprise (15.0.2000.5 / X64 on Windows Server ...
SQLpro's user avatar
  • 509
8 votes
2 answers
2k views

How can I achieve predicate pushdown in my view

I have a reporting table (about 1bn rows), and a tiny dimension table: CREATE TABLE dbo.Sales_unpartitioned ( BusinessUnit int NOT NULL, [Date] date NOT NULL, SKU ...
Daniel Hutmacher's user avatar
8 votes
1 answer
1k views

Database-Instance using half of available CPU cores

I'm looking at the following scenario: MSSQL Server 2019 Standard running on Windows Server 2016. sys.dm_os_schedulers shows 12 available logical CPU cores. Those 12 cores are split between 2 sockets....
hcm's user avatar
  • 183
8 votes
2 answers
1k views

Self-deadlocked process on a table variable

We are running SQL Server 2019 CU12 for one of our customers. Some time ago we started getting a wired deadlocks whereby a single process deadlocks itself on access to a table variable. Example ...
Martin Karouš's user avatar
8 votes
3 answers
2k 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 ...
PhilipD's user avatar
  • 189
8 votes
2 answers
952 views

SQL Server not Using Index

I am absolutely stumped as to why my query is not using what I think is a selective index. My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many ...
Joshua Grippo's user avatar
8 votes
1 answer
387 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 ...
Hannah Vernon's user avatar
  • 70.2k
8 votes
0 answers
143 views

SQL Server: how Row Goal affects the same index operation?

I am tuning some SQL performance in the SQL Server 2019, and I ran into issues with one query that involves the issue with the row goals. The simplified query is: select TOP 1 ISNULL(RUNID,-1) from ...
Jan Drozen's user avatar
7 votes
2 answers
1k views

ReadOnly queries on secondary server do not return correct values

I have two servers A and B. Both are in an Always On Group. The B server is configured for ReadOnly requests. This works fine so far, but recently there is a problem for queries for a service. The ...
SettusBlake's user avatar
7 votes
2 answers
1k views

Which execution plan is stored in the plan cache?

When query is executed, SQL server will produce a list of query plans and heuristically choose the plan with lower cost. The chosen plan will be stored in the plan cache for subsequent use when it ...
variable's user avatar
  • 3,566
7 votes
3 answers
4k 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 ...
Triynko's user avatar
  • 615
7 votes
1 answer
1k views

SQL Server: Max memory same as Min memory?

I am currently skimming through Pro SQL Server 2019 Administration: A Guide for the Modern DBA and I have found one thing that somewhat confuses me. In Chapter 5: CONFIGURING THE INSTANCE, the section ...
Yano_of_Queenscastle's user avatar
7 votes
3 answers
2k views

ReFS Vs NTFS for SQL Server 2019, server 2019

Recently we've been running into errors when snapshotting DBs (operation error 665) One of the solutions of this error is to change the underlying disk type to ReFS. However I can't find much out ...
Cheerful DBA's user avatar
7 votes
1 answer
469 views

In a multi-database AG, what decides which databases will have more than 1 redo thread?

We have a large physical server with more than 50 dbs on it; some are quite busy, others very quiet. When having multiple DBs in an AG, only some of them multiple redo threads. We wanted the databases ...
user3593990's user avatar
7 votes
1 answer
241 views

Filter by partition number when the table is partitioned by computed column

I've created 2 identical tables partitioned by an integer column. In one of the tables the column is computed, in the other table the column is not computed. When I query the table with computed ...
Artashes  Khachatryan's user avatar
7 votes
4 answers
955 views

Is my queue table implementation race condition safe?

Hello people smarter than me! I've created a sort-of-a-queue table system, but it seems too simple to be safe from race conditions. Am I missing something or is the following race condition safe? The ...
J.D.'s user avatar
  • 38.1k
7 votes
2 answers
397 views

Intellisense automatically disabling with "WITH Inline = OFF"?

In my SSMS (I'm using 18.10) project, I've been frustrated by the fact that Intellisense is not functioning in my files. I had conjectured that it was due to the size of the codebase, but in fact, I ...
Rushabh Mehta's user avatar
7 votes
5 answers
4k views

Microsoft SQL Server 2019 Developer Edition suddenly throwing Internal Connection Fatal Errors

I have Microsoft SQL Server 2019 Developer edition installed on my computer for local development work with .NET. Everything was working well as of two months ago, when I was last working on a ...
Andy S.'s user avatar
  • 73
7 votes
2 answers
1k views

Is the Query Store's Overall Resource Consumption report telling me my database is doing terribly or are the numbers just whacked?

Recently we upgraded one of our production servers from SQL Server 2016 to SQL Server 2019 (CU 15). This was a great opportunity for me to enable Query Store on our main application database. It's ...
J.D.'s user avatar
  • 38.1k
7 votes
1 answer
419 views

Why does an inequality search on an indexed column give constant scans

Using the StackOverflow2010 database, I can create an index on the users table as follows: CREATE INDEX IX_DisplayName ON dbo.Users ( DisplayName, UpVotes ) And then run an inequality search ...
SE1986's user avatar
  • 1,990
7 votes
3 answers
861 views

Huge disk space for data file and log file executing alter table

I have a big problem with the files size on my SQL Server database. Let me put everything into context: On an empty database I'm creating a new table: CREATE TABLE mytable ( id int identity(1,1) ...
Interel's user avatar
  • 71
7 votes
1 answer
194 views

SQL Server Primary Key Column Statistics Histogram Suggests Duplicate Values

I have a statistic on a Primary Key column in a table. When I update the statistic with the default options: UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350 I get a histogram as follows (...
SE1986's user avatar
  • 1,990

1
2 3 4 5
21