Questions tagged [sql-server-2019]
SQL Server 2019 (major build version 15.00.xxxx). Please also tag sql-server.
1,094 questions
0
votes
1
answer
64
views
Any downsides of using UTF8 collation in SQL Server 2019?
We have some columns in the table that could possibly get some non-ANSI characters and we must store them. Before the SQL Server 2019 there was the only 1 option - NVARCHAR data type, however starting ...
- 181
5
votes
1
answer
459
views
Are there best practices for SQL Server blue/green deployment?
Is it possible to spin up the new instance with the same structure and replicate all the data there, then perform some long running blocking operations that actually do not perform incompatible ...
- 181
11
votes
2
answers
287
views
Huge log backups due to enabling querystore
We have a SQL Server 2019 CU18 where we discovered a strange issue with querystore.
Normally the average size of the hourly logbackup is 40MB but as soon as we enable querystore the average size of ...
- 2,114
1
vote
2
answers
82
views
SQL Server Browser service starts up and then immediately turns itself off
I have two servers on SQL Server 2019 that randomly decided to stop the SQL Server Browser service. When attempting to restart it, it turns itself back off immediately. Using the command prompt below ...
- 33
-1
votes
0
answers
49
views
Event ID 33205 Filling Windows Application Log
I was investigating an issue that occurred on sql server last night and began by checking the Application log in Event Viewer. The log is full of entries for Event ID 33205. I think it may be related ...
- 333
0
votes
1
answer
35
views
What is 1433U used for in MSSQL?
Investigating a freak interface flood hindering other traffic on 1433U inbound I found that legitimate traffic is on 1433T for DB traffic, 1434U for instance browsing, 1434T for admin access.
But: ...
0
votes
2
answers
101
views
Are these transactions normal?
When I run this query
SELECT transaction_id, name, transaction_begin_time, transaction_state
FROM sys.dm_tran_active_transactions
I always have these 6 rows in the result set
transaction_id
name
...
- 139
0
votes
1
answer
58
views
Long running waits- how to check/prove if the root case is at the SQL Side
Some of my users complain about the poor performance of the application developed/supported by an external company. The app uses SQL Server 2019 Enterprise Edition for a database engine.
I can see in ...
- 192
1
vote
1
answer
114
views
Daily deltas implementation
We have a feed of json lines files from a vendor. The whole corpus is about 45-50M records. They send us daily updates.
The old implementation has a heap with a couple of indexes to hold the corpus, ...
- 255
0
votes
0
answers
38
views
Is it possible to change the ERRORLOG files encoding in SQL Server 2019?
We have a security vendor in our company that needs to access the ERRORLOG files in some SQL Server 2019 instances. They say the encoding on our files is unusual [UTF-16 LE BOM] as the standard is [...
- 11
0
votes
1
answer
126
views
Why can I not capture dynamic SQL called with EXEC using the sqlserver.sql_batch_completed Extended Event?
It is my understanding that dynamic SQL is part of a batch. Yet, when I listed for sqlserver.sql_batch_completed as follows
CREATE EVENT SESSION [CaptureBatch] ON SERVER
ADD EVENT sqlserver....
- 823
0
votes
1
answer
69
views
Is there any disadvantage to moving a small and frequently read read-only table to in-memory OLTP?
Suppose that you have a table which is:
Read from so frequently that it is almost always in RAM.
Very small.
Almost never changed.
Is there any disadvantage moving such a table to in-memory OLTP? My ...
- 823
4
votes
1
answer
154
views
SQL Server Management Studio (SSMS) Table Properties Timeout Issue
I'm encountering an issue in SQL Server 2019 when using SSMS (versions 20.2 and 21 preview). Navigating to a table and opening the "Properties" dialog consistently results in a timeout.
...
- 2,482
3
votes
1
answer
84
views
How does name resolution work with temporary stored procedures?
Say I have the following two databases
USE [master]
CREATE DATABASE Jack
GO
CREATE DATABASE Jill
GO
USE Jack
GO
CREATE TABLE JacksTable
(
Id INT
)
INSERT INTO JacksTable VALUES (1)
If I create ...
- 2,083
0
votes
2
answers
129
views
What wait types would terrible RCSI performance cause?
Read Committed Snapshot Isolation (RCSI) is well-understood. The key way that it can cause performance bottlenecks is if the version chains get too long. Are there any wait type that indicate this ...
- 823
1
vote
1
answer
95
views
Inconsistent execution times for stored procedure
I have a stored procedure that runs daily. Most days it completes in about 20 minutes, some days it takes an hour. I'm trying to find the cause so I can get it to be more consistent. I looked for ...
- 333
0
votes
1
answer
169
views
SQL Server 2019 Hard Dependency on ODBC Drivers
When installing SQL Server 2019, ODBC drivers are also installed at the same time that MSSQL seems to have a hard dependency on. Can MSSQL be reconfigured to use newer versions of ODBC drivers and the ...
- 48
1
vote
1
answer
53
views
Why I don't see the OptimizerStatsUsage in the execution plan
SQL Server 2017 introduces a very helpful enhancement to the showplan to see which statistics were used to generate a plan: https://learn.microsoft.com/en-nz/archive/blogs/sql_server_team/sql-server-...
- 319
-1
votes
1
answer
63
views
SQL Server slows every night [closed]
The last couple of nights i woke up to websites with very slow loads due to SQL Server issues. The issue resolves on server restart
I tried sp_BlitzFirst and got "High Percentage Of Runnable ...
2
votes
3
answers
598
views
Why is my Nested Loops join showing inaccurate row estimates in SQL Server?
I have the following execution plan:
As you can see, the row estimates for the Clustered Index Scan and Index Seek operators are accurate. However, the Nested Loops join has a significant discrepancy:...
- 319
0
votes
0
answers
32
views
Log reader agent fails when replication filter is created using table from another database
I have a transactional replication setup on SQL Server 2019. I want to create a replication filter on one of the articles in which the filter clause needs to be specified using a table from another ...
- 163
0
votes
1
answer
26
views
how can I list all views and functions that are replicated?
I have a script to show me the replication articles of a publication:
declare @publication_name sysname = 'my_pub'
declare @database_name sysname = 'my_db'
use [my_db]
select ServerName = @@...
- 16.6k
1
vote
1
answer
102
views
Index on column with "datetime2" data type
I'd like to cut down on the scope of a query towards a table that has a timestamp column on the format: 2024-12-01 12:34:56.1234567. (A "datetime2" data type)
Assuming 10 million rows and ...
- 113
0
votes
1
answer
61
views
When should sp_spaceused be used to get the size of a database, instead of querying sys.database_files?
I frequently find the output of sp_spaceused misleading. What appears to be the size of all data in a database is actually the combined size of both the data file and the log file. I have recently ...
- 823
0
votes
1
answer
87
views
SSMS: Is there a faster way to SELECT TOP (1000) in the GUI?
Today, I'm checking every table in a massive database and writing a summary of what each is. This means that I'm right-clicking and pressing "Select Top 1000 Rows" a great many times. Is ...
- 823
-2
votes
1
answer
38
views
Getting Sql Server minidumps every few min [closed]
Is there a site where I can upload a Sql Server minidump to get more information about what caused it?
8
votes
6
answers
3k
views
How can we prevent Agent Jobs running twice when the clocks change?
Context
I have no idea where this is documented and was shocked that I could not find a duplicate. I only know it from seeing it go wrong several years in a row.
The Problem
On the day when the clocks ...
- 823
0
votes
1
answer
51
views
SQL Server instance upgrade impacting on other instance maintenance plan jobs
I have two instances on a single server. Instances are named TEST and LIVE for simplicity. Both are running SQL Server 2019. Only the LIVE instance has SQL Agent jobs running (Basic backup jobs via ...
- 101
1
vote
2
answers
109
views
MSSQL single user mode
I am encountering issues logging into SQL Server in single-user mode. Here’s the situation:
Single-user mode login issue: When I attempt to log in as the system user, I receive the following message: &...
- 11
0
votes
0
answers
48
views
using WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue) caused service broker keep calling activation function even there is no msg on the target queue
I have the following SQL service broker set up:
USE master
-- Create database
ALTER DATABASE ServiceBrokerDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ServiceBrokerDemo
CREATE DATABASE ...
- 319
0
votes
1
answer
69
views
sql server backups - what is physical_block_size?
we have recently changed the file server that is the repository (the place where the backups are stored) server .
I noticed the hardware specially IO is much better backup take shorter to finish, ...
- 16.6k
1
vote
2
answers
254
views
Do parallel SELECTs make a transaction?
While running under READ COMMITTED (the pessimistic type, not RCSI), I've just seen an UPDATE's IX lock deadlock with a SELECT's shared lock on the PAGE-level. The SELECT ran with a parallel execution ...
- 823
4
votes
1
answer
577
views
Does compressing indexes reduce the size of already compressed backups?
I use backup compression everywhere. Recently, I discovered that my backups were getting a little too big. I decided to fix this by compressing the indexes in the database (typically PAGE compression)....
- 823
2
votes
1
answer
114
views
How to properly create a Spatial index and write a SARGable query that leverages that Spatial index?
I can't seem to get SQL Server to leverage a basic Spatial index on a GEOGRAPHY object for even the simplest of queries.
Here is a dbfiddle.uk repro which demonstrates creating a simple table with a ...
- 39.5k
1
vote
3
answers
210
views
What is the relative performance trade off between having covered queries vs. index size?
I inherited a SQL Server table with a field that is a very large JSON blob (NVARCHAR(MAX)). The average size of this field is ~8k. The architect wants us to include all fields in the indexes so that ...
- 11
0
votes
1
answer
53
views
Are joins to heaps a performance concern if we can always force a hash join?
I clearly have a misconception, but no amount of consulting documentation has solved it. Consider the following argument:
We generally worry about joining to heaps, because sorting them is often more ...
- 823
-1
votes
1
answer
86
views
SQL Server 2019 tempdb files won't shrink
I have a tempdb configured with 8 datafiles and 1 log file. Originally the datafiles were all set to the same size, however, they have grown over time and are now all different sizes. I was able to ...
- 1
0
votes
0
answers
53
views
How to change the default SQL Server instance
How to change the sql server default instance which was installed before?
I have two versions of SQL Server installed SQL Server 2014 and SQL Server 2019.
My SQL Server 2014 is the default instance. ...
- 1
0
votes
1
answer
75
views
SSMS's GUI and dbatools produce different scripts for restoring databases in full recovery mode, what practical differences do they have?
I have a database in full recovery mode without log backups. Don't say it: I know. It fortunately isn't in an environment that I care about.
Today, disaster struck and I tried to use dbatools to ...
- 823
0
votes
1
answer
57
views
Can an index be redefined prior to a rebuild?
Suppose that my server has a job that sometimes rebuilds indexes based on factors that I don't control (e.g. fragmentation). Suppose also that I am sure that it will one day rebuild an index that I'm ...
- 823
3
votes
1
answer
176
views
Does rebuilding a clustered index offline require extra space for each non-clustered index?
I have an uncompressed clustered primary key. It consumes 63.6 GB in the main part of the clustered index with 17.9 GB LOB. The table's only non-clustered index is 57.3 GB also with 17.9 GB LOB. I ...
- 823
0
votes
1
answer
85
views
How can high severity alerts be configured in Amazon RDS SQL Server?
Per the RDS documentation,
SQL Server Agent alerts and operators aren't supported.
But on any sensible SQL Server, you really want these for the high severity alerts.
So, I'm here to ask: How can ...
- 823
1
vote
1
answer
202
views
Query has been cancelled because the estimated cost of this query exceeds the configured threshold
Whenever I run this query:
SELECT trade_detail_id,trade_side_id,id,is_last,is_xml_detail_filled,save_date_time,trade_id,trade_ligne_id,type,xml_detailled_info,xml_state
FROM some_table
WHERE (NOT (...
- 9,682
5
votes
3
answers
1k
views
Is SQL .bak file compressed without explicitly stating to compress?
I use the following script to make a .bak of a production database and save it to a test server where it can be restored if needed. The script does not state to compress the file. Running SQL Server ...
- 333
0
votes
0
answers
74
views
Is Automatic Plan Correction the only Query Store feature that is in SQL Server 2019 Enterprise Edition but not Standard Edition?
Hunting through the documentation for what is locked behind Enterprise Edition is not easy, but I gave it a good try. My conclusion was that Automatic Plan Correction is the only feature using Query ...
- 823
3
votes
1
answer
196
views
Should tiny dimension tables be considered for row or page compression on servers with ample CPU room?
An old Microsoft paper says to consider using ROW compression by default if you have lots of CPU room (emphasis mine).
If row compression results in space savings and the system can accommodate a 10 ...
- 823
3
votes
2
answers
470
views
Is there any benefit to declaring an index that contains a primary key as unique?
Suppose that I have a table with many columns that I don't care about, but two that I do: Primary and Secondary. There is a clustered primary key on Primary.
CREATE TABLE [dbo].[...
- 823
1
vote
2
answers
98
views
How can I do arithmetic in the ORDER BY clause with a column aliased in my SELECT clause?
The following is illegal.
SELECT
schema_id + 2 AS evil_column
FROM
sys.tables
ORDER BY
evil_column + 3;
If you remove the + 3, then it is legal.
Assuming that I want to do arithmetic with ...
- 823
0
votes
1
answer
76
views
Accessing encrypted MS SQL Database using the decryption key
I am migrating an old ASP application (written in the 2000s) to a new platform and am struggling with the best way to export the data in an unencrypted form. I have access to the current working site ...
- 111
2
votes
4
answers
357
views
How does SMO make it easy to grab all code on a Microsoft SQL Server?
There has recently been a lot of talk about putting database code in source control. I have read widely on this topic and there appears to be an unstated assumption that using SMO through PowerShell ...
- 823