Any way to prioritize connections or queries?

[twoeyes]

Hi,

Is there anyway to prioritize particular queries or connections with MS SQL Server 2000? (or any version, for that matter)

I would like to run complex report queries at a lower priority than transactional type requests, and the site/company is too small to invest the resources into any sort of data warehouse type system.



----------------------------------------
Perceptus Solutions Inc.
A little computer support and web firm.

http://www.perceptus.ca

 

[Catadmin]

The only prioritizing that I'm aware of is Boosting SQL Server priority in Windows (server properties), priorities in deadlocks and priorities in Replicated data.

My best suggestion is to schedule these "long winded" queries as jobs that run in the middle of the night or when the SQL Server Idle Time (look at SQL Server Agent properties) gets below a certain threshold.

Sorry I can't help further.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

If you have a multithreaded system you can force the larger queries to use a single processor instead of using multiple processors, there by leaving the other processors for other users.

This is done with the MAXDOP option.

select *
from sysobjects
where name = 'test'
OPTION (MAXDOP 1)

Is this what you were looking for? It's the only way to prioritise queries over each other.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[twoeyes]

Thanks Catadmin and Denny.

I was hoping for something similar to the CPU priorities that operating systems provide. However, the MAXDOP option looks promising.

----------------------------------------
Perceptus Solutions Inc.
A little computer support and web firm.

http://www.perceptus.ca