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 ...
14
If somebody stumbles upon this, here is the full download URL for SQL Server 2019 LocalDB
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
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
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,...
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 ...
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.6k2 gold badges32 silver badges62 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
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 at 23:33
David Browne - Microsoft
25.2k2 gold badges25 silver badges59 bronze badges
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
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
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)...
8
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 than the ...
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
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 ...
7
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, ...
7
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:
?:\...
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, ...
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 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, ...
6
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 ...
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 ...
6
That is not an authoritative source. Please follow Paul Randal's process here, as he's the one who "decided to add a new feature to SQL Server 2005 called EMERGENCY-mode repair".
5
The optimization is implemented by applying flow control on the workers trying to insert to reduce heavy contention and convoys. The idea is based on an unfair insert mutex per scheduler and it can help avoid latch waitlist buildup and convoys as a result. When users opt-in per index with the option, OPTIMIZE_FOR_SEQUENTIAL_KEY, and then we do traffic/flow ...
5
As far as I can tell the feature has not been officially announced yet.
There are a couple of resources available on this feature from members of the community:
Youtube video from Erik Darling
Blog post from Ned Otter
This would appear to be the configuration option that will allow you to put some TempDB metadata tables into memory, preventing many types ...
5
Yes, this is actually fairly easy to accomplish using Module Signing. With module signing, you create a module (in this case, a stored procedure) and grant the required permission to the module, not to user or login. You only grant the user permission to execute the module. And, by doing this, not only can you be granular enough to effectively only grant the ...
5
Rather than focusing on how to improve a query like this, which is what the other answers are doing, I'm going to try to answer the question being asked: why doesn't the optimizer produce a plan like the one you've described (that scans the Users table, and then seeks into the two indexes on the Comments table).
Here's your original query again (note I'm ...
Only top voted, non community-wiki answers of a minimum length are eligible
