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 ...
27
The documentation page containing download links for LocalDB 2016 onward is:
SQL Server Express LocalDB - Installation media
Direct download links are not officially supported.
The following LocalDB download links were copied from Scott Hanselman's blog post:
SQL Server 2016 SP1: https://download.microsoft.com/download/9/0/7/907AD35F-9F9C-43A5-9789-...
15
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, ...
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
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
35.6k3 gold badges38 silver badges79 bronze badges
11
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
...
11
Yes, all _UTF8 collations use code page 65001 as that is the code page for UTF-8. You can even use 65001 in a DOS / Command Window via:
chcp 65001
though not all programs and fonts will work seamlessly with it.
For _UTF8 collations, the code page is not controlled by the culture (i.e. Latin1_General vs Arabic) as it is for non-_UTF8 collations because code ...
11
create a new partitioned table with enough partitions to happily divide up all the existing and some-reasonable-time-into-the-future future data
create a view that union alls the two tables (may be simplest to rename the current table and replace it, temporarily, with a view having the old table name)
direct new writes to the new, partitioned table
...
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
21k2 gold badges34 silver badges65 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
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),...
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
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, ...
10
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 ...
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
About your server
This query spills badly in two places, despite a 29 GB memory grant. That's about the high end of what a query can ask for on your system with max server memory set to 115 GB.
The cause for a memory grant of that size is the optimizer estimating it will need to sort 46 GB of data:
You can see the memory grant percentage details by looking ...
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
Pam Lahoud SQL Enterprise Team Principal Program Manager at Microsoft said on twitter:
and contributed via comment here:
There are a few known issues that could be coming into play here. It's best to open a case with support if you can, the more data we have on these issues the more likely we'll be able to find a fix.
9
It is not possible to identify a table variable in tempdb's metadata by the variable name, as the table name is created with the hex name there and the mapping to the variable at runtime is done somewhere within the engine.
However, you can track things down to some degree if at least one of the column names that fits in-row is unique-ish enough. Let's say ...
9
The system view sys.indexes has a bit column named optimize_for_sequential_key.
It is not on by default because it is intended for use only where significant last-page contention is observed.
See Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY for more details.
9
If you're asking specifically what might happen when T1 has completed but not committed, the general answer is that T1 is unaffected by any changes made by T2*. T2 might or might not block depending on the isolation level and which access method (index path) it uses to locate the rows it should update.
* The one exception to this would be if TBL is a memory-...
8
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 ...
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
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 ...
8
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
35.6k3 gold badges38 silver badges79 bronze badges
8
Parallel redo threads are assigned in database recovery order, which does follow the sys.databases creation date.
That said, the separate parallel database recovery feature means each database recovery task can be assigned to a different SOS scheduler (when there are a large enough number of schedulers available).
Say you had 8 databases and 32 processors. ...
Only top voted, non community-wiki answers of a minimum length are eligible