TSQL and performance 1

[vlitim]

Hi,

I have a number of stored procedures that when run take all the processing power of the server and pretty much cripple it. What I need to know is there anyway of telling SQL to only use a certain amount of the processor so that other people using the server will be able to carry on. Obvioulsy these stored procedures will run slower but that is not a big consideration at the moment as they really need to be backround processes.

Cheers

Tim

 

[NoCoolHandle]

Are you sure it isn't locking?

SQL server will time slice threads (worker threads/commands/storedprocs/whatever you want to call it)

try running sp_who2 when you are running your proc to see.

 

[NoCoolHandle]

Stupid question... is this a multi proc box and does it "always" cause problmes or just some of the time?

 

[vlitim]

the server only has one processor and its not locking because it is effecting people accessing different databases on the same server, which to me suggests it is taking all of the procesing power for itself!??

 

[NoCoolHandle]

It might also be disk i/o..

I remember a bizzare issue a few years ago where we had a dual proc box.

If I ran a query that did a big table scan.. It was slow.....

So. I took the primary proc away (using the affinitly mask setting) and the performance was great.

This was (and it might have changed depending on os/sp etc) because the primary processor is the only one on an NT box that handles I/O. The last proc is used for something else (but I can't remember - network I think)

So what was happening was this proc would go out and 1 thread would start getting rows, then the next thread would start to process the results, the back to getting results.. Combine this with other querys.. The bottleneck that the main query caused did what you are describing.


You might want to look at indexing structures that could remove a need for disk access..


Also look at
SET QUERY_GOVERNOR_COST_LIMIT

 

[SQLSister]

If you have sps using more than a few milliseconds apeice, you need to learn about performance tuning. THere are some good books on it as it is too complex a subject to discuss here, but some quick general advice.

If you are using cursors in these sps, don't. Learn to use set-based SQL statments as much as possible.

Do not use Not in - use left joins instead.

Do not ever return more fields than you need. Don't join to tables you don't need to. Use numeric fields for the join fields, especially avoid mulitple key fields as the joining fields.

If you are inserting or updating large nuimbers of records (i mean in the millions), consider breaking the process up into bathces.

Look at what jobs are running inthe background. Badly timed backups can cause slowness.

Consider if you need to denormalize.

Learn to use profiler and performance monitor.

Look at your indexing. Indexing the right things can speed things up considerably.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief