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
0
votes
1answer
36 views

Why can't I control the I/O affinity?

I'm running a 2008r2 EE version of SQL Server on a machine (Not a VM) with 4 Sockets of Intel® Xeon® Processor E7-8880 v3 with Hyperthreading enabled. I'm attempting to validate with the affinity for ...
0
votes
0answers
38 views

How to change IOPS on SQL DB server

Can anyone advise please? An application I'm supporting requires IOPS on DB server to be increased during a process, in order to improve throughput and then put back to original value after the ...
-1
votes
1answer
20 views

Determine Available Blocks of Contiguous Ids

We have a SQL Server table that has an int autoincrement primary key column on it. The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 ...
-2
votes
1answer
28 views

Find a doubled value between two columns in the same row

We discovered a bug in our program where there are some instances of a doubled value. I am trying to write a query to find all instances of this doubled value. Right now I am using WHERE (GRIR....
0
votes
1answer
28 views

Is there a way to customize SQL Server Performance Dashboard?

The report in SQL Server Management Studio presents System CPU Utilization broken out by a minute. Is there a way to specify the interval - perhaps by 10 seconds?
0
votes
1answer
21 views

How do you detect a change in grouping when using Partition By in SQL Server 2012+ and execute logic based upon the change in the same SQL statement?

If I have a SQL statement that contains something like, ROW_NUMBER() OVER(PARTITION BY school_area ORDER BY school_area) I get: 1 someschool1 schoolarea1 2 someschool2 schoolarea1 3 someschool3 ...
0
votes
0answers
20 views

How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?

Hope you are well I'm been a bit stuck trying to develop the code to get the inheritance of extended properties. I will explain the situation: The thing is have a query to list all extended properties ...
0
votes
0answers
48 views

Multiple SQL instances on the same server and how to manage TempDB

Probably a bizarre approach but I don't really have much say in the setup. We have a large server deployed in Azure and the plan is to get 16 separate instances on this machine. We plan on using the ...
0
votes
0answers
12 views

SSIS and SQL Agent - running jobs using proxy vs using MSA

We have multiple servers, each with its own SQL Server instance. We have external companies that develop applications for us. This companies deploy SSIS packages and run them via SQL Agent. I'm ...
0
votes
1answer
45 views

Understanding how T-SQL transactions are working

I would like to understand deeply what is going on with my transactions: I have a rule which executes each second 1 or 2 transactions (I receive a purchase event and I must save the purchased item and ...
0
votes
1answer
36 views

Doubts about table partitions

I am studying table partitions and I still have some things that I am not clear about. I created a partition function that organizes the data for the last three months. CREATE PARTITION FUNCTION ...
1
vote
0answers
51 views

How to determine the Tipping Point of which Physical Join Operator gets used to process a query?

I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
3
votes
1answer
53 views

Is Trace Flag 6534 Relevant Past SQL Server 2016?

Based on this link, I see that trace flag 6534 is relevant for SQL Server 2012 through 2016, but no mention of SQL Server 2017 or 2019. Is it still relevant for those editions or has it been folded ...
1
vote
0answers
55 views

Stored procedure running indefinitely, same statements executed in batch completes in a second; WITH RECOMPILE has no effect

Why would a proc just run without terminating (increasing IO and CPU in sp_whoisactive, nothing blocking) when all the exact same statements run fine by themselves in SSMS in a few seconds, but the ...
0
votes
1answer
44 views

Is it the right scenario to use spatial index?

I have used spatial index recently on one table to improve query which filter records by given date range, reference identifier and whether they are inside polygon. Here's the example of the query ...
0
votes
2answers
63 views

Does changing database growth rate force a new execution plan?

For many years there has been a long running SQL Agent job that has taken nearly two hours to complete. Approximately two weeks ago on a slight whim a decision was made to change the growth rate on ...
0
votes
0answers
14 views

Azure SQL Server DTU Percentage degrades over time with API Throttling

I've been facing a weird DB behavior when trying to post records to my api. I have a function that throttles requests at a request rate of 10-15 req/s, each request results in a lookup and an insert (...
0
votes
2answers
61 views

What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?

I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like: WHERE varcharColumn <> '' and varcharColumn2 = '2' and ...
0
votes
0answers
49 views

Stored Procedure fails on tempdb-diskspace when executed by SQL Agent

I developed a bunch of ETL procedures which are executed by an overlaying SQL Script and write Data to separate DB. The Script uses a single Transaction, ensuring we only get complete data. This ...
0
votes
0answers
53 views

SQL Server Tempdb space usage high on secondary always on [closed]

I am facing issue where in my secondary always on server, tempdb usage is very high. Both primary and secondary are allotted same resources and have same disk size. Secondary is configured for read ...
-2
votes
0answers
34 views

Insert Rows Until Condition Is Met [closed]

I'm trying to create a temp table that will have a field as the concatenation of year and quarter, e.g. 20212, as well as other fields. I would then like to insert additional rows for each row in my ...
3
votes
3answers
100 views

Reducing Temp Table Scans when joining two temp tables using OR conditions

I'm working on a complicated query that I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
0
votes
1answer
59 views

How to reduce big size database files (Data File ) SQL Server without SHRINK?

I found a couple of 'question' regarding shrinking big datafiles but I think that is not very accurate to my situation. So here is my question I have a big database with almost 8TB of Data File Size ...
6
votes
1answer
326 views

Why does switching two column values work by simply reassigning the values in T-SQL?

For example, the following query works just as intended in Microsoft SQL Server (T-SQL). UPDATE Customer SET ContactName = Customer.City, City = Customer.ContactName; I would like to know why the ...
-1
votes
0answers
32 views

Which performs better on average Oracle Database pulling data from MSSQL or MSSQL pulling data from Oracle database? [closed]

I'm working on a task that requires data that is spread on different database engine, namely Oracle and Microsoft SQL Server, too be processed as one and to generate certain data through several ...
-1
votes
0answers
49 views

Updating SQL Server Maintenance Solution to use CommandExecute

All, Looking to implement the Stored Procedure 'CommandExecute' in our environment. Cannot find any documentation about the parameter options for CommandExecute: @DatabaseContext nvarchar(max), @...
0
votes
1answer
35 views

Failover and Failback SQL server Distributed availability group with different versions

I know that SQL server Distributed availability groups can sync data between two different Major versions, Lets say Global Primary - 2016 Secondary AG - 2017 This sync will work and I can do failover ...
0
votes
0answers
52 views

SQL Server - BackupDiskFile - Referencing to deleted TR Backup file

We have a backup job which creates transaction log backups every 10 mins for databases. Backup files which older than 2 days are deleted. In ERRORLOG file - I see the record which I cannot get/...
0
votes
0answers
30 views

SQL Sever Alwayson - Increase the WSFC failover threshold

Im trying to simulate the Failover in my SQL server 2017 alwayson by stopping and starting the SQL server service. It worked 2 times, but after that, the failover is not happening, It showing an error ...
0
votes
1answer
43 views

create column for a table from select data from another table column

I have a table that shows the date information of a topic. FirstTable looks like this: id DateP 1 1397/01/02 2 1398/05/09 3 1398/06/07 4 1398/08/09 5 1399/02/01 I want to create columns for ...
0
votes
0answers
33 views

Hidden SQL server replica in always on availablity group

I want to deploy SQL server 2017 always on availablity group also with a replica for DR purpose. I want to use 1 primary(prod) 2 secondary(prod) 1 secondary(DR) Now my question is, the failover can ...
0
votes
2answers
17 views

SQL server 2017 distributed availability group connectivity after failover

I'm going to deploy SQL server distributed availability groups with SQL server 2017. From the documentation, I understand that we can have listeners for primary AG and secondary AG. The application ...
0
votes
2answers
62 views

Count and last row of a batch(Top) with a insert statement

Problem statement : I've a big table and I read only Top 10000 elements of the table at a time. This is for batch processing(insertion) which has a where clause and one order by statement. Now, in ...
0
votes
1answer
58 views

Does putting constraints decrease the required size to store my data in SQL Server?

I would like to know if putting check constraints decrease the required column size or not? For example, when I create an int column and a check constraint on it to store the numbers between 100 and ...
3
votes
2answers
118 views

Why does a Full Backup restore of my database give me the error "RESTORE can only create a database when restoring either a full backup..."?

I've setup a maintenance plan to take a daily Full backup of all databases on my server. I've also setup a second maintenance plan to take Transaction Log backups every 5 minutes. The Transaction Log ...
2
votes
1answer
67 views

OBJECT_ID(...) function vs join on sys.tables and sys.schemas

When querying metadata for a specific table, I can either use the OBJECT_ID function or join the relevant system tables (sys.tables and sys.schemas). For example, both of those queries will return ...
0
votes
0answers
33 views

load large data and split and store into two tables in sql server

I need to take large amount of data from a table and split that into two different tables in databse (sql server). How it is possible. i need to automate this action either in sql server side or ...
1
vote
2answers
120 views

SQL Server - Deadlock during two concurrent DELETE

I've got a Java app exposing a REST API to delete records in a table (SQL Server), sometimes the API must handle concurrent deletion requests (each request uses a different DB session) Here is an ...
0
votes
1answer
66 views

Number of executions of the same sub query by the DB engine

If I have this SQL query SELECT (select count(*) from tableA) as cnt, t1.* FROM ( SELECT t.* FROM ( SELECT * FROM tableA UNION ALL SELECT * ...
10
votes
1answer
170 views

Could I have an undetected deadlock?

Upon running sp_whoisactive on a server for the first time, I was greeted by something unexpected: Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking ...
0
votes
1answer
50 views

sp_BlitzCache and SPID

I'm using sp_BlitzCache and it's very helpful, I ran this command: Sp_BlitzCache @SortOrder = 'duration'; GO How do you relate the query with a SPID as I found the query with the longest duration? So ...
5
votes
1answer
158 views

Index Definition Order and ORDER BY Clause

So i was doing my morning blog reading and stumbled upon this fun exercise: https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/ Here is the query in question from the ...
2
votes
1answer
64 views

Updating varchar column over linked server with parameterized query causes remote scan and cursorfetch

I'm issuing a fairly simple update of single varchar column against a remote linked server. E.g.: UPDATE Hydrogen.CRM.dbo.Customers SET EyeColor = 'Blue' WHERE CustomerID = 619 And that works fine ...
2
votes
1answer
67 views

MS SQL attach MDF/LDF without FileTable folder

Is it possible to attach a DB that had a FileTable w/o the FileTable files? We were hit with ransomware and the image files are corrupt but the mdf/ldf are fine. I can lose the images, but I really ...
0
votes
0answers
35 views

script to detect alwayson redo latency in SQL Server

I have 2 node SQL 2019 AlwaysON setup where i regularly see latency for redo thread in AG dashboard when applying the redo transactions on secondary. we have identified that this redo thread delay in ...
0
votes
0answers
23 views

Why Sql execution delays sometimes?

I made my project in VB.NET with SQL Server as backend but sometimes SQL query execution delays even from SSMS. I have the following query which I execute every time I want to fetch a product list: ...
0
votes
0answers
34 views

sp_WhoIsActive CREATE statement in sql_text column while executing object inside another [duplicate]

I am running fabulous sp_WhoIsActive with these parameters: EXEC dbo.sp_WhoIsActive @get_task_info = 2 ,@find_block_leaders = 1 ,@get_outer_command = 1 ,@get_transaction_info = 1 ,@...
-1
votes
1answer
35 views

How to correct number of rows sampled (index statistics) in a large table? [closed]

I am looking at index statistics of a large table. The number of rows sampled are 300. Where as the total rows in the table are 345100. I want to fix such that a substantial amount of rows are sampled,...
-1
votes
0answers
29 views

How does sql server decide when to stop after starting to read an index?

Let's say the sqls server is reading an index. It may be clustered or non clustered index. Say the index has values from 1 to 999999. And assume sql server decides to read the index. When the where ...
0
votes
1answer
25 views

SSIS debuging tool in SSMS 2016

I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have ...

1
2 3 4 5
613