SQL Server Performance

Sql 2000 - only one core is used in a query

Discussion in 'Performance Tuning for Hardware Configurations' started by silverfx001, Dec 12, 2009.

  1. silverfx001 New Member

    I have an SQL server that is running a DataBase software written by us. The server has 2 dual core processor and since the time we migrated it it was running very fast - when the software made a query the 4 Cores were flat out 100% and we had an answer in 4 seconds.
    Recently I realised that the server no longer uses all 4 cores, it takes only one core to the max, that is flat out but the process now takes up to 12-14 seconds.
    I have checked the CPU affinity settings and it looks like all CPUs are to be used but in reality it looks as it it was set to use any cores, but only one core at a time.
    It is not the nature of the query I beleive, as we are doing exactly the same queries that we did before but the behaviour certainly changed
    The server is a 2 x Opteron Dual Core HP DL 185 rinning Windows 2003 server and MSSql 2000 Spk 4
    I just checked another server that has our software installed on it and all cores are utilized when this same query is run.
    The only thing was done recently is that we have truncated the Log file. Could that make a difference?
  2. Adriaan New Member

    Get execution plans on both servers. It may be due to different data on the different installations, or outdated statistics.
  3. silverfx001 New Member

    I actually found the reason. It was the SQLSERVERAGENT service running you are down to one core, at least that is the way it looks.
    Starting the service fixed the problem, flying again :)
    Thanks anyway
  4. silverfx001 New Member

    Sorry I meant if you don't have it running you are down to one core...

Share This Page