Long-performing queries on a single table

[aaksyonenko]

working on that. main problem is that this is a live DB intertwined with other DBs and under constant load. so I don't think I'll get to sleep any time soon... sigh... Hey, thanks a whole bunch for sticking this out, I appreciate it!

[aaksyonenko]

No, actually. the volume of transactions is roughly the same as it's always been. this is an old, stable app that doesn't change much. I do need to go through all that SP code, though, to make sure none of my devs took any nasty shortcuts.

[yosiasz]

devs have access to live SQL Db??? dont you have a gating mechanism for deploying bits??

boss, you need to revisiting this whole thing, bottom up. its like easter egg fest, new surprises every post

[aaksyonenko]

LOL No, they don't. But I'm not the only one approving code releases to live db. I just need to double-check after everyone dow, to make sure nobody's locking resources on this thing or running ad-hoc stuff. audit time...

[jeffw8713]

@aaksyonenko

SELECT TOP 1
@T_ID = id,
@T_TYPE = [type]
FROM
stack WITH(NOLOCK)
WHERE
machinenum = @T_MachineNum
AND
datetimepr <> 'INPROGRESS'
AND
(
[date] = replace(convert(char(8),getdate(),11),'/','')
AND
[time] <= replace(convert(char(8),getdate(),8),':','')
)
ORDER BY
priority desc, [time] asc

I want to go back to the original query - since this is a 'stack' or queue, then I would assume that you want the read process to pull the next available row (record) to be processed. If so - then do you really care (or need) to filter on [date] and [time]?

SELECT TOP 1
       @T_ID = id,
     , @T_TYPE = [type]
  FROM stack WITH(NOLOCK)
 WHERE machinenum = @T_MachineNum
   AND datetimepr <> 'INPROGRESS'
 ORDER BY
       [date]
     , [time]
     , priority desc;

Wouldn't this get you the earliest date (which should be today - if everything is processing as expected) with the earliest time and greatest priority. Just a thought...

@aaksyonenko

Mea culpa! I really should have mentioned that.

The AWS instance has 32 Gb RAM and a Quad-core CPU. It has 4 SQL instances running on it. The instances are memory-capped as follows:
2048 Mb (our problem child)
5000 Mb
7500 Mb
16000 mb
for a total max 30548 Mb usable by SQL, leaving a minimum of 1.5 Gb RAM for the OS.

Believe me, I am thinking long and hard about moving off SQL Server, at least for this particular DB. Unfortunately, that is a major architectural and development project. All my instances work with each other, they've been SQL Server since before SQL 2000. Never had a problem with this.

I am not aware of any throttling on AWS end, besides, both the server and the client are in the same AWS subnet, so their data exchange is internal to that AWS subnet.

Is it possible that all instances have allocated to their maximum allocation - and the OS needs additional memory? If these instances are not locking pages in memory then the OS could request memory from any instance...

On a system with a single instance and 32GB of memory - I would leave 4GB of memory for the OS. For a system with multiple instances - I would monitor more closely and increase the memory available to the OS if I saw any issues.

Also - do you have every instance set with 'Boost SQL Server Priority'? Or only this instance? I would definitely disable that feature - across all instances.