How the hell does a query that usually takes ~500ms to run now magically takes 2.5h! What changed in 30mins URGHH

Now it should work faster.

@Alice shhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh

@arraysstartat1 didn,'t dunno what happened :/ trying to save those ids in another table and call join on them, it still executes way faster, my guess Is the recently submitted sitemap files to Yandex, with >6 hits a sec, 1.7lakh+ pages
Also, fuck your username mate damn -_-

@azuredivay So you are saying that machine has bigger load than usual and that would cause 500ms -> 2.5h? Certainly possible, but I suspect that either your query was waiting for lock or execution plan changed drastically.

@arraysstartat1 I thought so too, the db is a crawler one with 20-40 entries being added/updated a second, and one would expect that to be the bottleneck, but those queries weren't the top hoggers
I stopped the updating/adding to see if that had an effect on the culprit query's performance, but nope. It is a "select top XX cols from table where xid = id' form so except indexing, there isnt much scope of improvement (from how lil I know about sql that is)
to summarise, it couldn't be the lock as only reads were being performed and the query still took looong

@azuredivay I think that exclusive lock can prevent SELECT query as it requires shared lock and they are not compatible, tho I'm not too familiar with MS SQL Server.

@jespersh one of the Indexes for the ID in question was reverted, which acc to metrics brought down the query run times for the top running queries, but on the other hand, the sql server's 'idle' usage percentage (with crawling enabled, select query removed) went up to ~100% constantly for hours, compared to 40% before
For now I reenabled the index to note what gets affected n change things accordingly
At least now I know what caused things to go crazy all of a sudden :p

@arraysstartat1 il read on the sql locks for mssql, coz my thinking was that no write, all reads would lead to no locks but, who knows, il update y'all if I figure out what went wrong, apart from the indexing being reversed that is

Did you try removing delay(1000*60*60*2.5); ?

@LuxARTS I wish i wish -_-