13 votes
Accepted

Compilations/sec high when load testing a simple procedure

A compilation is not the same as an optimization. Compilation itself is cheap. It happens any time a match is not found in the plan cache. Optimization is much more expensive. A compilation may or may ...
Paul White's user avatar
  • 84.1k
12 votes
Accepted

What is "execution count" in Query Store?

Query Store Execution count in GUI maps to the column count_executions in sys.query_store_runtime_stats Total count of executions for the query plan within the aggregation interval. Since you're ...
Zikato's user avatar
  • 4,507
8 votes

Are there any possibilties to mitigate data loss risk on delayed durability

Are there any possibilities to mitigate the risk pushing SQL server to flash data to disk if someone tries to restart a server? SQL Server uses write-ahead logging to guarantee durability so log ...
Dan Guzman's user avatar
  • 28.2k
4 votes
Accepted

Failover in AG without listener

A listener is not required for a failover as @JonathanFite mentioned in comments. I just ran a quick test on a lab AG without a listener and it succeeded without issues. On a side note, I suggest you ...
Dan Guzman's user avatar
  • 28.2k
4 votes

How can I trace unknown high CPU usage on SQL Server?

ongoing If it happens again, I'd suggest using sp_WhoIsActive: EXEC sp_WhoIsActive @get_plans = 1, @get_avg_time = 1; The two additional parameters are to collect execution plans, and to look ...
Erik Darling's user avatar
  • 40.8k
4 votes

Simplified view for double-entry inventory transfers

Seems like it's pretty simple conditional aggregation SELECT TransNo, ItemNumber, FromLocation = MIN(CASE WHEN FromToDesc = 'FROM' THEN Location END), ToLocation = MIN(CASE WHEN FromToDesc = ...
Charlieface's user avatar
  • 12.9k
4 votes

Automatically fetch USD Exchange Rate from API and Update it in Microsoft SQL Database

The documentation for sp_execute_external_script says: Use sp_execute_external_script to execute scripts written in a supported language. Supported languages are Python and R used with Machine ...
4 votes
Accepted

ApplicationIntent = ReadOnly and Writes queries

If application that connects to SQL and does both read and write queries using a single connection string that has ApplicationIntent = ReadOnly, does it mean that it will error out if application will ...
David Browne - Microsoft's user avatar
3 votes
Accepted

Do statistics have an impact on delete performance?

Do statistics play any role in how SQL actually executes delete operations? Yes. Statistics affect estimates and estimates affect anticipated costs. SQL Server has a few optimizations it can apply to ...
Paul White's user avatar
  • 84.1k
3 votes
Accepted

Group by running totals with reset

Assuming you only want to pack the trades in strict trade ID order: CREATE TABLE #Test ( OrderID char(1) NOT NULL, TradeID char(2) NOT NULL, Qty integer NOT NULL, PRIMARY KEY (OrderID,...
Paul White's user avatar
  • 84.1k
3 votes
Accepted

RESTORE DATABASE - The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time

I remember I had similar problem during the database restore, in my case it were reporting queries that run every 15-20 min and took tens of gigabytes of RAM for memory grants, as well as some extra ...
Aleksey Vitsko's user avatar
3 votes
Accepted

what sql server is holding in the used memory?

[...] is there a way to find out what sql server is holding in there memory? Yes and no, depending on what you mean by "holding in there memory" (sic). If you mean what's using memory in ...
Sean Gallardy's user avatar
3 votes
Accepted

how to find out the free space inside a database file when the database is not online?

However, how can you do that when the database is offline? There really isn't an option built in to do this as the allocation management items are in the database. The databases would have to be ...
Sean Gallardy's user avatar
3 votes
Accepted

how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?

You can use sys.dm_tran_database_transactions to find out how much log has been written and therefore whether it needs to be rolled back. select dt.database_transaction_log_bytes_used, dt....
Charlieface's user avatar
  • 12.9k
2 votes

Performance problems on SQL Server 2016 that goes away after running two updates flipping a null column value

Looks like it's mostly caused by the fact that both plans are essentially bad plans. One does a full scan, the other does many lookups. You are asking the server to choose between a rock and a hard ...
Charlieface's user avatar
  • 12.9k
2 votes
Accepted

Performance problems on SQL Server 2016 that goes away after running two updates flipping a null column value

The difference between the two plans is that in the first (slow) one, Sql Server expects that only 20 rows will satisfy the WHERE clause, and it resolves to use an index seek followed by lookups in ...
Andrea B.'s user avatar
  • 1,558
2 votes

Got an error when trying to get ER-diagram on SQL Server

Windows error code 0x534 indicates an invalid SID, likely an invalid database owner. Change the database owner to a valid login to keep the SSMS diagram tool happy: ALTER AUTHORIZATION ON DATABASE::...
Dan Guzman's user avatar
  • 28.2k
2 votes

SP_SERVER_DIAGNOSTICS_SLEEP wait type

SP_SERVER_DIAGNOSTICS is in suspended state and starttime is not refreshed. That's correct. It runs in an infinite loop and has a SLEEP wait type added to the end of it when it is waiting to run ...
Sean Gallardy's user avatar
2 votes
Accepted

Adding custom table to local third-party ERP database

can I directly add a table to this ERP database safely, maintaining new additions with a trigger, or will there be some kind of garbage collection/maintenance routine generally found on databases that ...
J.D.'s user avatar
  • 37.5k
2 votes
Accepted

Ola Hallengren's Maintenance Solution script doesn't dump transactional log file to Destination

Kedar. First of all, to fix the discrepancy between @@SERVERNAME and its SERVERPROPERTY counterpart, please run the following script: https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%...
Eitan Blumin's user avatar
2 votes
Accepted

Convert numbers to words | Spell out DECIMAL USD 1259.75 as 'One thousand two hundred fifty-nine dollars and seventy-five cents'

Modify the 100/1000/1000000 "WHEN" portions of the SELECT statement to be like this, and you should achieve your desired result. WHEN @Number BETWEEN 100 AND 999 THEN dbo.NumberToWords((...
Craig's user avatar
  • 194
2 votes

Is there any way to send SMS without OLE Automation?

Is there any way to send SMS without OLE Automation? You can write your own C# solution using CLR and deploy it to SQL Server, such as a CLR procedure. Doing so will allow you to (either directly or ...
J.D.'s user avatar
  • 37.5k
2 votes

database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

how can I find the list of databases I actually can connect to? Msg 976, Level 14, State 1 the database is synchronizing and it is showing as ONLINE You'll want to see if the replica, in a ...
Sean Gallardy's user avatar
2 votes
Accepted

Extra `CASE WHEN` Removes Text from Dynamically Created String

According to documentation this is antipattern known to return incorrect results: Avoid the following pattern for recursive use of variables and expressions: syntaxsql SELECT @Var = <expression ...
Yano_of_Queenscastle's user avatar
1 vote
Accepted

Database Mirroring - Failed Logins on primary after failover

In Database Mirroring, failover is detected by attempting to connect to the principal first, then if that fails, the driver attempts connection to the failover partner server. So, if you have an ...
Hannah Vernon's user avatar
  • 70k
1 vote
Accepted

DBeaver does not recognize declared variable when executing SQL script on SQL Server

This is DBeaver's issue as it has its own way of processing sql scripts compared to SSMS or Azure data studio. In DBeaver, each SQL statement in a script is executed separately and therefore, any ...
Kin Shah's user avatar
  • 62.2k
1 vote
Accepted

SQL Server Service Broker on a High Availability Group

I don't see any reason to stop the agent, you will need to take the database out of the AG, then run the command on the Primary with rollback immediate as you know - --alter database [<dbname>] ...
rvsc48's user avatar
  • 553
1 vote
Accepted

Why does data file growth fail even with plenty of disk space?

The solution was to add another data file! Because of the rapid rise and fall of the node, we had to turn it back on ("Start Role" in Windows clustering) and then rush to execute this ...
nateirvin's user avatar
  • 760
1 vote
Accepted

Text Search on Multiple Columns without enabling FULL TEXT Search

This will produce the result you want: WITH Searches AS ( SELECT F.FaxDocID, F.CustID ,COALESCE(C.CFNAME,'') AS FName ,COALESCE(C.CLNAME,'') AS LName ,F.RemoteCSID, F....
Aardvark's user avatar
  • 140
1 vote

dm_exec_query_stats returning all NULLs for Worker and Elapsed time

Answer From Sean Gallardy : Milliseconds isn't set anywhere, it's null, dividing by the null returns null. –
QFirstLast's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible