Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
33,671
questions
0
votes
0
answers
18
views
New SQL2022 slower than old SQL2014 server
I'm planning and testing a new SQL2022 install to migrate from an older SQL2014 database. I've noticed during performance testing some major discrepancies with some bulky queries.
Here is a query I ...
- 123
0
votes
1
answer
25
views
How can I tell BCP to add headers to an output text file?
I am running this code; it only return data, how can I get headers in my file?
EXECUTE master.dbo.xp_cmdshell
'bcp "SELECT * FROM table1 " queryout G:\Backup\Test.txt -t"|" -c -T ...
- 1
0
votes
0
answers
26
views
Delayed 'Update' Message Insertion in MSSQL Broker: Troubleshooting and Resolution
I employ MSSQL 2019 triggers for 'insert,' 'update,' and 'delete' operations and utilizing MSSQL Broker to generate messages for consumption as audit messages. Each message triggers a specific row ...
0
votes
1
answer
69
views
SQL Server 2022 CU10 - bug? INCLUDE column ignored when Index Seek uses the Index itself [closed]
Due to SQL Server 2022 CU10 devising an expensive plan for the query below, to get an optimal plan, as a last resort, I use an INDEX HINT and OPTION ( FORCE ORDER ).
The production DBs have hundreds ...
- 327
0
votes
0
answers
79
views
Exporting data to INSERT INTO script using T-SQL [duplicate]
I need to export a table into a .sql script containing INSERT statements using T-SQL.
There are many public posts about using SSMS and clicking on things. I must create something that can be scripted. ...
- 231
0
votes
0
answers
46
views
expanding a current database
I am rewriting a server program. currently the program has order batching and orders.
(currently written in winforms with mssql database with no orm- to be rewritten as a blazor server app with mssql ...
3
votes
1
answer
130
views
SQL: How to look within each "cluster" value in one column and return those that ONLY have one specific value in another column
I have a scenario where I am querying "match clusters" for master records from an MDM database. Each "cluster" has a specific number and within that cluster, you'll have a row/...
- 33
-2
votes
1
answer
82
views
What is a "statement batch"?
I know what a statement is and I know what a batch is, but what is a "statement batch"? T-SQL's documentation uses the term in two places that I've found.
If a TRY...CATCH construct is not ...
- 45
0
votes
1
answer
41
views
Is there a keyboard shortcut to open a registered server?
Getting to the Registered Servers pane is easy enough with Ctrl-Alt-G.
Navigating thorugh the tree is easy enough with arrow keys.
Once the desired instance is found, is there a keyboard action that ...
- 231
4
votes
2
answers
246
views
HADR_SYNC_COMMIT waits on SQL Server
Let me preface this post by saying that I am missing a few events from my trace, but I have since added them for the next time this occurs.
Lately we have been seeing weird spurts of HADR_SYNC_COMMIT ...
- 235
1
vote
2
answers
46
views
Can't connect to named instance from remote server
I have researched a lot on this issue but can't seem to find a solution for it. I have a named instance created on a server but I can't connect using the Servername\InstanceName.
I am using SQL ...
- 573
0
votes
1
answer
62
views
If RAISERROR is as good as deprecated, how are you supposed to flush the PRINT buffer?
RAISERROR is as good as deprecated. The documentation says
New applications should use THROW instead of RAISERROR.
Yet, it is commonly used to flush the PRINT buffer.
Suppose that I'm writing a long ...
- 45
0
votes
0
answers
25
views
Need Help Solving a SQL Question - FIFO Buffer Replacement Strategy
I'm currently working on a SQL assignment that involves a page table and applying the FIFO (First-In-First-Out) buffer replacement strategy.
Here's a brief overview of the task:
I have a page table (...
- 15
0
votes
1
answer
43
views
Building a web project for my company using asp.net and having a concern about the storage size of SQL Server Express edition
In my company we have around 850 employees. We are going to build ASP.NET web project to handle many day to day procedures. My question is related to the database MS SQL Server Express which comes ...
- 1
1
vote
2
answers
78
views
coalesce more than 1 column in sql server
I have the following query where I search 2 tables for the item price
select productSerial, productName
coalesce(
(select top 1 price
from productPrice pp
where pp.id = wi.id
order by pp.date),
(...
- 119
0
votes
1
answer
47
views
SQL Server Extended Event Session for Attention is missing field sql_text
I have an extended event session set up to track Attention events, to monitor procedures that timed out. This is the create script:
CREATE EVENT SESSION [ApplicationTimeout3] ON SERVER
ADD EVENT ...
- 191
0
votes
2
answers
63
views
What is a difference in failover time for SQL Server FCI when database is in Full vs Simple recovery mode
I have 2-node SQL Server 2019 FCI cluster, which takes up to 4 minutes to failover from one node to another.
The cluster has 3 user databases, migrated from the older SQL server versions:
2 databases ...
- 103
0
votes
0
answers
44
views
Reporting only copy for a production database
I need a read-only copy of my production SQL Database so our reporting department can query it and not bring down production because of it being so resource intensive. Would replication still be the ...
- 427
1
vote
1
answer
54
views
What indexes should I put on this table, given this illustration of its contents and the query I will be running on it?
I am working with a table (which I control) that is being populated by another application (which I don't control), each time a record of a particular type in an external system is created or updated. ...
0
votes
0
answers
38
views
Balancing Legacy CRM Needs and Security in Multiple Databases
I have a set of databases on a server.
Database_alpha
Database_beta
Database_gamma
Database_delta
Database_alpha houses a legacy CRM system which has an inbuilt dependency on the sa account to ...
- 131
0
votes
1
answer
50
views
What could create block in that case when 'blocking' procedure/spid not do any select / almost nothing?
I had situation like this today- sorry that this is screenshot but it is easier way to show what i need to understand
As on screen - many processes was blocked by SPID 91
This SPID 91 querry was as ...
- 101
2
votes
2
answers
597
views
Why are these columns specified in the include clause of an index rather than as key columns
For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with ...
- 47
0
votes
0
answers
23
views
sqlserver alwayson audit setting
I am running SQLSERVER ALWAYS ON 2019 ENT VERSION.
I'm trying to STOCK an audit at the database level in SQLSERVER.
In the master, it was confirmed that the log audit was left at DATABASE LEVEL.
...
dante
0
votes
2
answers
55
views
how sorting nullable number and ones is last
my list something like this
Id
Value
ِDate
1
null
2020
2
0
2022
3
1
2021
4
1
2023
5
100
2024
2
250
2018
I want to sort by column [Value] so that ones are at the end
The result should be as ...
- 11
0
votes
2
answers
49
views
Duplication records with INNER JOIN
I’m validating records after a partition’s rotation.When I run the SELECT COUNT for each table I get 114,817,959 records.
SELECT COUNT(*) FROM SMS.dbo.MessagesPerf WITH (NOLOCK) WHERE
DateEntered &...
- 37
0
votes
1
answer
66
views
Windows Power Plans for SQL Server
Is the High Performance power plan in Windows Server still relevant in SQL Server setups on modern hardware/virtualization? Searching for articles from my usual trusted sources brought up nothing more ...
0
votes
2
answers
76
views
formating date colums causing performance issues
I have a query(written by my previous co-worker) with date columns returned as output but formatted, which s causing performance degradation (for below query returning 10k records, performance ...
- 25
1
vote
1
answer
65
views
Using table valued function as a table in sql server join
I have a scenario where SQL server using table valued function with joins to many other tables. The function returns many columns, which are not always desirable output for every report, causing lots ...
- 25
0
votes
0
answers
22
views
resume search with full text index
I have a database structure where resume stored in document column in WHDocument table in database for each person. During skilled person search in application the persons skill is also searched for ...
- 25
4
votes
1
answer
236
views
Catch Block Inside of a Trigger Does Not Get Hit When a SP With Dynamic SQL Errors Out - Causing the Larger Transaction to Fail
I am not a fan of triggers and dynamic sql, however, what I am working with requires both.
CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN
...
- 505
1
vote
1
answer
65
views
Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?
I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I ...
0
votes
0
answers
17
views
Backup via SAS to Azure Blob not working
I have changed my backups to go via Shared Access Signature rather than just a standard key (Microsofts preferred method).
However on a few large databases it fails after a while.
E.g. One database is ...
- 31
-1
votes
0
answers
58
views
using JSON output in SQL Server causing performance degradation
My Team is using JSON as input and output for SQL Server stored procedures. Its ok for less volume of data in output, but for reports with thousands of row output with many columns, creating a single ...
- 25
0
votes
0
answers
29
views
SSIS Error: "Metadata could not be determined because procedure uses temp table"
We have recently restored SSIS Packages and I'm facing this error whenever I try to execute a SSIS package:
Unable to determine metadata because « INSERT INTO #File_Results EXEC Master.dbo....
0
votes
0
answers
44
views
What permissions does SQL Server Agent Account needs to run the job?
I am wondering what kind of permissions [NT SERVICE\SQLSERVERAGENT] accounts needs to run the job?
Below are the steps that have been impleemented yet the job doesn't run with correct results expected....
- 577
-2
votes
0
answers
31
views
Can OLE Automation Cause Big CXPACKET Waits?
I had two things running on my server at the same time.
An SSIS package that triggers a hungry but heavily parallel stored procedure
A terrible loop that calls entirely too much OLE Automation (e.g. ...
- 45
-2
votes
2
answers
93
views
I've TABLOCKed a table. How can I read it?
I've TABLOCKed a table so I can run bulk inserts. How can I read it while it is still locked?
Under normal circumstances, I would call this obviously impossible. Luckily, there is one key factor that ...
- 45
-1
votes
0
answers
24
views
Managed Instance Failover Group Not Replicating New Databases
I have setup a managed instance failover group in Azure, but when I restore my databases onto it (small databases) they don't replicate across to the secondary.
I did the restores yesterday and they ...
- 31
4
votes
5
answers
1k
views
What are the idiomatic approaches to copying a table (and all of its data) from one server to another?
I have a massive table, let's say 500,000 rows. I want to copy it (schema and data) from one server to another. This is not an upsert or any kind of update; It's a one-off straight copy and paste. ...
- 45
0
votes
1
answer
44
views
What are good metrics for determining the usefullness/impact of an index in a SQL server database?
Background
I'm reviewing a database currently where someone applied indexes to almost everything in the database. Since we are now experiencing performance issues related to updating records, I'm ...
- 111
1
vote
1
answer
49
views
Gameplan for Upgrading SQL Server fr 2014 to 2017 & 2022
First I would like to say sorry if my knowledge within the DBA realm is very limited since I and several other team members were just given this project because we are significantly resource-limited.
...
- 27
3
votes
0
answers
92
views
Why am I getting RESOURCE_SEMAPHORE timeouts when there should be plenty of memory available for grants?
We occasionally have queries time out due to RESOURCE_SEMAPHORE waits but there appears to be plenty of available memory for memory grants. For example, I'm able to see timeouts by executing two ...
- 32k
0
votes
1
answer
38
views
Inserting Multiple Rows into a Table and Mapping Newly Created IDs Back to Original rownum in Another Table - SQL Server
I have the following tables
-- Create the #product table
CREATE TABLE #product
(
id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
name VARCHAR(100),
createdDate ...
- 437
0
votes
1
answer
108
views
Problems with MS SQL Server installation
I have an error connected with Database management system core when i'm installing MS SQL Server.
Error code: 0x84B40002
OS: Windows 10. SQL Server 2022 (16.x)
I already launched SQL Server repairing ...
- 1
-2
votes
1
answer
83
views
+50
Huge PREEMPTIVE_OS_GETPROCADDRESS Waits From sp_OAMethod. How to debug?
Some queries are hitting my CPU extremely hard. sp_WhoIsActive reports that sp_OAMethod is the cause (the sql_text column points to it) and that it has of huge waits of the ...
- 45
0
votes
0
answers
39
views
LSRestore job is taking more than 1 hr to complete. Threshold time is 45min
LSCopy and LSRestore job has been scheduled for every 10 mins. I got a trn file on my secondary server after every 6 min from Primary server. LSCopy is synced with LSBackup. The problem is that ...
0
votes
0
answers
46
views
Curious Memory Pressure Alert - SQL Server Process Physical Memory Low
We are, on occasion, receiving a SQL Server Process Physical Memory Low alert on one of our warehouse instances. The kicker is, I can find absolutely no indication of memory pressure at the time the ...
- 121
1
vote
2
answers
54
views
How do I get the object that executed the query from a query handle?
The database is microsoft SQL server.
I've got a query handle (sql_handle) from sys.dm_exec_query_stats
Is there a way to get that the object the query is part of?
for instance I want to have dbname....
- 113
1
vote
0
answers
56
views
How to track DB structural changes with minimum permissions
Background
I've been given a task to improve the performance of a database (SQL server) that a 3rd party manages. I've decided to do this by suggesting rewrites to the current views.
To help ...
- 111
0
votes
0
answers
76
views
Database restore takes 20 minutes longer when run through Agent job vs as T-SQL in a query window
When I run this statement in a query window as my own user account, the restore completes in 40 minutes. When I run it as a step in an agent job it runs in over 60 minutes. In the agent job, the step ...
- 1