August 3, 2006 at 9:59 am
Our system is hybrid, on same server we have reporting database whose long-running queries consume a lot of server resources – CPU, hard-disk I/O, memory, and OLPT database whose transactions are short. Sometimes OLTP transactions experience timeouts, not because of locking issues - actually there were no lock when timeouts occur, but because these transactions queries are waiting in queue to get server resources occupied by large reporting queries. They have wait time values in sysprocesses table.
My question is there a way to set priority for certain OLTP stored procedures so they won't wait until server resources will be freed from large reporting processes?
Thanks
August 7, 2006 at 8:00 am
This was removed by the editor as SPAM
August 7, 2006 at 9:10 am
seen this question posted several times ( prob not from same person ) The answer is no and my answer is always the same - welcome to DSS Hell!
Largely the mix of oltp and dss on the same box is always going to give problems, the usual way around is to migrate the dss to another box. You could split to instances and allocate memory and cpu to each but you'd still probably have contention on i/o.
Optimising your dss queries to minimise i/o is probably the best way forward.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply