42
This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for that function.
Using a variable instead of a constant shows a bit more detail
declare @myYear int = 0
SELECT dbo.Repro(@myYear);
Node 5 defines Expr1000 = CASE ...
20
If somebody stumbles upon this, here is the full download URL for SQL Server 2019 LocalDB
13
UTF-8 support gives you a new set of options. Potential space savings (without row or page compression) is one consideration, but the choice of type and encoding should probably be primarily made on the basis of actual requirements for comparison, sorting, data import, and export.
You may need to change more than you think, since e.g. an nchar(1) type ...
12
My first question is, will these update statements work as intended?
Very likely, but not certain.
SQL Server guarantees it will honour the semantics of the query, and the level of ACID compliance determined by the effective isolation level. Beyond that, all is implementation detail (including what type(s) of locks are taken, when, and for how long they ...
12
is there any way to get that TOP N to not be so heavy?
I think there's a little bit of a misunderstanding here about how execution plans work.
That number is just the estimated cost, which is a model SQL Server uses to determine what the most efficient execution plan will be. It's not updated at runtime, so even if the operators used very little resources,...
12
To start with, the older xp_delete_file (also undocumented) is quite limited:
It can only delete either backup (.bak / .trn) or report (??) files (I believe it actually scans the first part of them to verify the file "type"
It only deletes by file extension (i.e. not specific files, or whatever matches based on wildcards) and based on being older ...
11
Cause
SQL Server is trying to inline the function but failing due to the complexity.
Using so much memory while doing so is unexpected and almost certainly a bug.
A definition for the nested function dbo.IstFeiertag would be needed for a full repro.
Workaround
Add WITH INLINE = OFF to the function(s) definition. Once this issue is resolved, you should ...
11
I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level.
Change the Database Compatibility Level and use the Query Store
If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher ...
answered May 29 '20 at 23:33
David Browne - Microsoft
31.5k2 gold badges32 silver badges69 bronze badges
10
Currently, in CTP 2.3, in so far as I can tell - you cannot enable this, even under DAC.
answered Mar 3 '19 at 17:09
Sean Gallardy - Retired User
20.8k2 gold badges33 silver badges64 bronze badges
10
Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.
If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.
The ...
10
This handy page from Microsoft should be very useful for you as it explains in detail how to carry out a migration and the tools and methods available:
Supported Version and Edition Upgrades for SQL Server 2019
Use the Data Migration Assistant (DMA) to scan your databases to make sure they can be migrated to the 2019 server. It will highlight any potential ...
10
Thank you for reporting this. There is a feedback item in https://feedback.azure.com/forums/908035-sql-server/suggestions/39754837-ssms-18-4-sqlserver-2019-cu2-object-explorer-fails. All updates will be provided there as we investigate.
FYI, the bug introduced in CU2 with xp_sqlagent_enum_jobs is now understood and a fix will ship in CU3. Meanwhile, if you'...
10
This is a bug with SQL Server inline functions in 2019 RTM.
DECLARE @Salary MONEY = 100440;
SELECT dbo.ufn_GetSalaryLevel(@Salary)
The expression in the constant scan (with CONVERT_IMPLICIT changed to CONVERT to make it runnable) is
SELECT CONVERT(VARCHAR(10), CASE
WHEN CASE
WHEN CASE
...
9
It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it.
Of course, there are a couple helpful posts on the subject by Paul White:
IGNORE_DUP_KEY slower on clustered indexes
A creative use of IGNORE_DUP_KEY
If your concern is around upsert patterns (or something similar),...
9
After some digging I found plenty of unconnected issues, but finally managed to solve them and wanted to share them all in one place so nobody else waste that much time if hits the same issue (or some of them).
First you want to access is the Reporting Services Error Catalog, depending on your installation directory, it will be something like this:
?:\...
9
You're encountering a known bug with scalar UDF inlining. You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself):
Disabling Scalar UDF Inlining without changing the compatibility level
Or install CU2 for the permanent fix.
...even running a simple scalar function (which internally uses ...
8
Since we've established that no other transaction holds any of the
locks the current one holds, it logically follows that no other
transaction would be attempting to update or lock any of the records
with those same object ids in the personal inventory table.
You should know that this:
no other transaction holds any of the locks the current one ...
8
We normally just ...
Perform a simple SQL Server Database Backup on the old SQL Server instance (or with Transact-SQL command BACKUP)
Copy the database backup (*.BAK file) to the new SQL Server instance
Restore the database using either SQL Server Management Studio (SSMS) or using the Transact-SQL command RESTORE DATABASE ....
During the restore of the ...
8
Try creating the following indexes (I'm using what I think is something similar to your naming convention for index names):
CREATE INDEX DomainTable$DomainIP$IDNum
ON dbo.DomainTable (DomainIP, IDNum DESC);
CREATE INDEX theDB$idx001
ON dbo.theDB (IDNum DESC)
INCLUDE (theDate, [Status], Rate);
The first index allows SQL Server to do a seek on DomainIP, ...
8
Why is it omitted?
Because the replication NOT considered to be HA and DR solution, it is considered to be Migrate & Load Data feature within SQL Server, which mainly cover the requirement of distributing subset of data into different locations wherein HA and DR solution mainly focuses on whole database/SQL-Instance level Failover capabilities.
Will ...
7
Every feature has a use case, and IGNORE_DUP_KEY is no exception.
As I explain in IGNORE_DUP_KEY slower on clustered indexes, this option only potentially benefits performance (on a clustered index) when the number of duplicate key exceptions is sufficiently small.
The break-even point is system-dependant, and requires realistic testing to properly ...
7
this can reduce the size of tables and indexes (emphasis added)
Reduction in size is only possible if most of the characters are essentially [space], 0 - 9, A - Z, a - z, and some basic punctuation. Outside of that specific set of characters (in practical usage terms, standard ASCII values 32 - 126), you will be at best equal in size to NVARCHAR / UTF-16, ...
7
You should be able to see the reason automatic seeding didn't complete by looking in the SQL Server error log from the time period where you created the AG (on both the primary and secondary replicas).
One common cause for this failure is that the Availability Group doesn't have the "CREATE ANY DATABASE" permission on the secondary. To correct that, you ...
7
Release candidates and community technology previews (CTPs) are considered evals and they expire 180 days after installation. You may not be able to upgrade the instance as upgrades are not supported (see Upgrading SQL Server 2019 from CTP to RTM version: Is it even possible?)
Backup up your user database files (you'll have to copy the actual .mdf, .ldf, ...
7
The DBA is correct - if the database is part of an Availability Group (AG), it has to be in the FULL recovery model. This is because of the way AGs work - they ship transaction log blocks from the primary server to the secondary server(s). So all the detailed log records provided by the FULL recovery model are needed.
You could remove the database from the ...
7
There are a bunch of different reasons why a statement might be recompiled, including both performance and correctness-related reasons.
For instance, an index used by the query may have been dropped, in which case the plan must be recompiled because it genuinely can't be run in that state.
Or statistics may have been updated enough that an automatic ...
7
I "prime" the stored procedure with optimal values
You can do better than that.
Instead turn on the Query Store and force the plan you want.
Or use a query hint like OPTION (RECOMPILE) or OPTIMIZE FOR.
answered Mar 31 at 20:22
David Browne - Microsoft
31.5k2 gold badges32 silver badges69 bronze badges
6
Yes, an in-lined function can show different results than its out-lined (!?) counterpart. The following reliably reproduces the situation on my machine (Windows 10, 4 cores + HT @ 2GHz, 16GB RAM, SSD).
Configure the database and session to use Read Committed Snapshot Isolation (RCSI):
alter database Sandpit
set read_committed_snapshot on
with rollback ...
6
This service has to do with the Always Encrypted with Enclaves feature. I have sent a note to the team to get this more explicitly documented for customers. Some scenarios will break if that service is not enabled.
Conor Cunningham
6
Let the person who wrote this blog post clarify :)
Your quote is a bit out of context. The full paragraph is:
For example, if a data page stores 4,100 bytes of data, and, as result, has 3,960 bytes of free space available, PFS would indicate that the page is 51-80 percent full. SQL Server would not put a new row to the page if its size exceeds 20 ...
Only top voted, non community-wiki answers of a minimum length are eligible
