We have a SQL server, version 7, that has 4 CPU's. Some of our staff have reports that must be run at random times before cutting checks etc. and these reports bog down the servers CPU's big time, enough so that we can not access the server via Enterprise Manager and at times not even through Query Analyzer. During this time of course other users can not do standard work of saving or accessing the database. Our Processor queue length never seems to go over 1 now. (We recently went from 2 to 4 CPU's do to a 1.4 average queue length).
I have heard that is is possible to limit the number of CPU's that certain query's or events can use. What is the proper terminology for this and can a person do this with limited knowledge? If this can be done during "working hours" so that during off hours query's can use all CPU's, even better.
This is a typical situation, where everyone looks at the DBA for the solution. My gutt feeling would be to run the SQL Profilier, capture the query and do an analysis of the Query. Before thinking about changing the SQL Server HW set up, make sure that you do the following:
1. Test the Query running for the report
2. Run update Stats
3. Recompile the Query
4. Check for the Locks