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.

Filter by
Sorted by
Tagged with
1
vote
1answer
64 views

SQL Server - same query occasionally taking 30x longer

I've been trying to debug a performance issue with one of our .NET web applications. It appears that a certain query usually runs quickly, but occasionally is very slow, resulting in the page taking >...
-1
votes
1answer
37 views

Query to get yesterday date with specific amount of time

I am struggling to query for a specific time frame between 21:50 and 21:59: SELECT Username, EventTime, EventsEx.FirstName, EventsEx.Surname, PeripheralName, EventTypeDescription, CardNumber ...
2
votes
1answer
23 views

DACPAC Deployment Rebuilding All Indexes

I'm in process of deploying a DACPAC provided by our ISV. The DACPAC is rebuilding every index in the database. We're testing in a Lab now, however, this will create a significant impact to our ...
1
vote
2answers
55 views

Trying to identify what process is making a connection to a specific DB in SQL Server

I am attempting to track down... something... that is making a connection to a specific database without (apparently) doing anything with the connection. However, so far I have had no luck. The data ...
-4
votes
2answers
52 views

Is a database trigger an appropriate implementation for an e-commerce application's promotions/reward logic? [closed]

I am attempting to architect an e-commerce rewards/promotions program for purchasing items. Here are some typical scenarios: $X amount of dollars off a purchase for a specific day $X amount of ...
4
votes
2answers
68 views

Does a computed column break 3NF (third normal form)?

From my understanding of 3NF, there should not be any transitive dependency. However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is ...
2
votes
2answers
159 views

When should the IGNORE_DUP_KEY option be used on an index?

Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index. My goal is to ...
1
vote
1answer
43 views

How to Keep Audit Data and Effectively Query Large Tables

Thanks for reading this. I have two types of data - demographic data and transactional data. The demographic data is only being stored when it changes. I’m trying to keep an audit trail for the ...
2
votes
1answer
44 views

SQL Server Encryption with “localhost” certificate

Using SQL Server 2014/2016: I'm trying to encrypt my connections from my application to my local SQL Server instance. As I read the documentation, SQL Server seems to very much prefer certificates ...
-1
votes
1answer
41 views

Select column if specific type of child record exists otherwise fallback

I got a table named "Order", an order has multiple child records in the table "OrderStatus". The orderstatus contains steps following the progress of an order. The orderstatus contains a datetime ...
2
votes
1answer
54 views

Availability Group Clustering - Cluster Group Preferred Owner

We have a three node AG. Two nodes in a local data center, one in a different subnet DR site. Am I correct that the Cluster Name should have two IPs, just like the listener? Also, is there a way to ...
0
votes
0answers
22 views

Transaction Replication - re-initialization the subscription after changing an article's structure

I'm using SQL Server 2014 SP3CU2 Enterprise Edition. I have a transaction replication on a DB with some very large tables. I needed to change the structure of a column on one of the tables that were ...
5
votes
2answers
567 views

SQL Always On COPY ONLY backups - what's the point if I cant restore the AG from these backups?

Think I already know the answer to this but I'll ask anyway. Situation is: SQL 2012 Always On - 2 synchronous replicas. 7 TB worth of Sharepoint DBs in 1 AG. Before patching - I want a Full DB ...
1
vote
1answer
56 views

What is the purpose of keeping filegroup or filestream backup in MSSQL?

I'm a new DBA for SQL SERVER. Currently I'm learning about filestream on SQL server. So I tried to restore only filegroup of filestream to other database, but I get an error message: An exception ...
0
votes
1answer
45 views

Pre allocate ids

I have an import to multiple tables, and would like to pre-allocate ids in the various tables, so that I can prepare all of the data without locking the tables. Previously I have used this to ...
0
votes
1answer
23 views

Joining tables with all rows for each unique column value

I have 2 tables as follows: | A | +----------+ | ty_date | | ly_date | +----------+ and | B | +-------------+ | locationid | | ty_date | ...
0
votes
1answer
58 views

Log truncation issue in AlwaysOn AG

We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' ...
0
votes
1answer
49 views

Can I connect to the database as the user “dbo”?

If not, why? During security audit we are being asked if the dbo login is interactive. My understanding is that the account is automatically created and managed by SQL server itself and is not ...
0
votes
1answer
39 views

How to pass a table-valued parameter to sp_execute_external_script?

I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit ...
0
votes
1answer
34 views

How to find total used and remaining space in Azure SQL mi

I queried sp_databases and the sum of space occupied by all db's was 380Gb. The total allocated space for the managed instance is 800Gb. But, enquiring with the infra team found that we have only ...
15
votes
1answer
408 views

Which costing factors go into the optimizer choosing different types of spools?

Spoolum In SQL Server there are several kinds of spools. The two that I'm interested are Table Spools and Index spools, outside of modification queries. Read only queries, particularly on the inner ...
7
votes
1answer
101 views

FORMAT returns large row size and data size

I am surprise with one of my findings that using a FORMAT () does have very big impact on the row size and data size. It is almost 250x more of the size of not applying FORMAT (). My question is: 1) ...
0
votes
1answer
44 views

Insert top 253 values where value in select distinct tradernames

I am trying to insert top 253 values for each trader from a temp traders table into temp results table. I am using following code to do it. CREATE TABLE #trad ( Trad varchar(50)) IF @trad is ...
3
votes
0answers
63 views

No backupset selected to restore

I have 2 cloud SQL Servers, one in Azure, one in AWS (I don't think the cloud platforms matter here but including them just to be thorough). I'm unable to restore a database from the Azure server to ...
-3
votes
1answer
150 views

Can I update my Microsoft Enterprise SQL Server? [closed]

I just have a quick question about SQL Server and the various tools that come with it. I updated my SQL Server Management Studio to the latest version because I wanted to use the STRING_AGG function,...
0
votes
1answer
74 views

Join one-to-many field in a single result without concatenation

I suffer from the same problem as in Concatenate one-to-many field in single result?, but I need a result where the 'many' fields occupy their own columns and aren't concatenated in one column. Any ...
2
votes
1answer
65 views

Is it possible to retrieve/get the query hash of a query without searching the DMOs?

Sometimes I want to compare the performance of two queries by running them multiple times while an extended events session is capturing the metrics. To avoid too much filtering and/or noise I would ...
3
votes
1answer
76 views

SUM Case Logic with Distinct

This is my first post, so please be gentle :) I have a couple of queries: SELECT COUNT(DISTINCT(h.USERID)) FROM MHGROUP.USERHISTORY h with (NOLOCK) JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID ...
1
vote
0answers
32 views

Has my repair fixed incomplete patching?

I am patching a SQL2008R2 Express Edition instance. On the first failed attempt, all seemed to be fine - SQL loaded, dbs online, @@VERSION returned the expected results, but errors were found during ...
1
vote
1answer
62 views

Index Fragmentation Monitoring: Can I make this query faster?

I have a query that is run against each database on two of my servers using a PowerShell script to initiate the script and throw the results into a CSV thus the need to have temp tables. Is there a ...
0
votes
0answers
23 views

Encryption procedure other than Always Encrypted

I would like to ask if there are other encryption method in MS SQL other than always encrypted?
0
votes
0answers
42 views

Why index REBUILD does not reduce index fragmentation? [duplicate]

I used ALTER INDEX REBUILD to remove index fragmentation. In some cases, REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation?
1
vote
1answer
51 views

SQL Server on Windows Server (VMware) - online storage swap

I have a VM cluster designed to store multiple SQL Server instances. We use variety of storage providers (i.e. 3PAR, XIV) which have to be changed now (not relevant to what brand). On Oracle on Linux ...
0
votes
1answer
29 views

How we can we know SQL Replication components installed in SQL server

Can somebody please let me know how can we identify whether Replication components installed in SQL server. Tried below: 1.we had changed the server name earlier so while checking with configure ...
-2
votes
1answer
71 views

A RETURN statement with a return value cannot be used in this context [closed]

I'm using MS SQL and I'm trying to create a function to use later in a query: CREATE FUNCTION "DiasLaborales"( "@month" INT, "@year" INT ) RETURNS INT LANGUAGE SQL NOT DETERMINISTIC CONTAINS ...
0
votes
2answers
100 views

Should I believe Query Store runtime stats?

There is this weird behavior of Query Store runtime stats that I observe at many of my servers with different scenarios that makes me not to believe these stats. Or am I wrong in something? E.g. ...
12
votes
2answers
556 views

What is hot spotting in the context of adding files to tempdb?

I'm trying to find out whether it is possible to add tempdb files to a SQL Server without having to restart the SQL Server service. I saw this answer here on Database Administrators: Tempdb Add files ...
-5
votes
2answers
142 views

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended? [closed]

I want to atomically reserve inventory for N objects for M users. I have two inventory tracking tables, one for global inventory and one for personal inventory. Global Inventory Table Columns: ...
3
votes
0answers
43 views

Moving from dynamic disk allocation to direct space disk via storage pool in SQL server

Pardon me if I am not clear in asking the question since I have very less understanding from storage terms used or how it's used with Sql server. Currently our storage team is testing to move disk ...
1
vote
2answers
40 views

Migration to SQL Database using Transaction Replication - down time?

I am looking to migrate two on-prem SQL Servers into Azure Hyperscale. Both 28 cores, 1.5 TB of RAM with 10TB data disks. I can NOT afford any downtime during the migration and have heard transaction ...
15
votes
2answers
2k views

What happens when there is no available physical memory left for SQL Server?

While googling I found some conflicting information. Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server:...
0
votes
2answers
77 views

Database Doesn't Contain Current Data After Restoring Last Full Backup from Production

I restored my last full backup from production database to reporting database server, but last restored database on my reporting server doesn't contain current tables, procedures, functions etc. What ...
0
votes
1answer
60 views

Deadlock on partitioned table

we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing. Our setup is like this: ...
6
votes
1answer
666 views

Deleting millions of records on SQL Server 14.0

I have two tables containing 200 Millions of records each. I have to delete from them, approximately 70 Millions of records, based on an integer value in a column. I'm deleting them in chunks of 4000,...
12
votes
3answers
294 views

Queries and updates extremely slow after IndexOptimize

Database SQL Server 2017 Enterprise CU16 14.0.3076.1 We recently tried switching from the default Index Rebuild maintenance jobs to the Ola Hallengren IndexOptimize. The default Index Rebuild jobs ...
0
votes
1answer
37 views

How to loop through all table rows and perform subquery using values of same row?

I have a table structured as follows: | reference | valA | valB | xml | 1 NULL NULL <xml stuff> 3 NULL NULL <xml stuff> 4 NULL NULL ...
-3
votes
1answer
54 views

Recommend SQL database backup for db size 1TB

Currently, our NAV 2013 database has been growing. It takes about 1TB now and is running on SQL Server 2008 R2 on Windows Server 2008 R2 Enterprise Edition. We would like to check if there any best ...
6
votes
2answers
230 views

Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure

I have noticed this error occasionally in the SQL error log: spid20s,Unknown,AppDomain 79 (master.sys[runtime].78) is marked for unload due to memory pressure. I am using SQL Server 2016, SP1 CU5 (...
3
votes
2answers
957 views

How to get all months in a query where one month has no matches?

I need to obtain every month by year (to fill up a graph). However, I found out that the first month had no data whatsoever and I wanna reflect that. The query is this one: SELECT tblMes.id, COUNT(*) ...
0
votes
0answers
74 views

Optimising pagination with large dataset and offset [duplicate]

Suppose that I have 50 million record of user. And the business requirement is to allow the admin to select any pageNumber and any pageSize to retrieve the user record Of course, it will not be a ...