I have SQL Server running on my local machine. I write integration tests in c# and I need to clean up data after each test run. Only I have access to the server, and it does pretty much nothing besides what is in the tests. There are about ~50 raw sql queries that delete records from various tables. I don't run any heavy deletes, at most I delete 1-10 records within single query. I don't see any heavy cpu usage and a lack of memory. I measure statistics of the clean up queries on c# side using a stopwatch, and I noticed an odd behaviour. As a whole it takes around 2 seconds to clean up data and each query takes a small portion of it, however this number is unstable, usually it takes 2 seconds, but sometimes it drops to 0.2 seconds (where all underlying queries just run faster), or goes up to 7 seconds, but these fluctuations are not as frequent. I run the same test multiple times, it works roughly on the same data each time. I decided to change sql process priority to the highest via Task Manager. And, I see big improvement, it now takes 0.2 seconds to run deletes and I don't observe slow deletes at all. What can cause slowness in the first place, and is there any indicator to pinpoint it?
Processes with higher priority run faster, however in my opinion the difference shouldn't be that dramatic.
My spec Microsoft SQL Server Developer (64-bit), 16.0.1145.1, Windows 10 Pro (10.0), 13th gen intel(r) core(tm) i9-13980hx.
UPD. I don't think it's related to queries themselves, and there is any point in posting query plans. There is something wrong with overall process on my server. I run following test in db with a single table.
create table abc
(
id int primary key
)
If I run following code
delete from abc
declare @count int = 1
while @count < 1000
begin
insert into abc values (@count)
set @count = @count + 1
end
set @count = 1
DECLARE @StartTime DATETIME2 = GETDATE();
while @count < 1000
begin
delete from abc where id = @count
set @count = @count + 1
end
DECLARE @EndTime DATETIME2 = GETDATE();
SELECT DATEDIFF(millisecond, @StartTime, @EndTime) AS [DurationInMilliseconds];
I ran it 10 times. And in normal priority I got following measurements:
200 244 380 336 283 310 464 407 350 480
avg=345.4 stddev=70.8
in high priority I get following measurements:
120 77 57 74 153 80 123 66 74 67
avg=89.1 stddev=25.74
Also, it definitely doesn't affects only delete's, it feels like everything runs multiple times faster when I change priority. It just that I started to measure this particular case. I've stumbled upon priority by accident, and in my experience changing priority usually has negligible or zero impact, I don't think the original delete's are run in reasonable time in normal. There is no data in the tables, almost all tables are empty or have one or two rows.
Here are plans for the delete when it performs slow
https://www.brentozar.com/pastetheplan/?id=Z1irmeobbE https://www.brentozar.com/pastetheplan/?id=9XWnRDRdHf
Here are plans that perform well
https://www.brentozar.com/pastetheplan/?id=DS94YXry8t https://www.brentozar.com/pastetheplan/?id=1cpMHw4pqo
Don't hesitate to ask me to do additional tests.
UPD2. Actually some of my colleagues have the same issue, we use the same laptops and similar set of software. But they don't know reason for that either, they have some hypothesis, but nothing convincing in particular. What they have found that enabling 'Boost SQL Server Priority' property also solves the issue. They also noticed that they db tests are running slower on that laptops, and tests don't consume CPU (and they usually do) unless they change priority.
