SQL 2005 - processors usage very high

  • Good day,

    Busy trying to test SQL2005, and having a couple of issues. This is my biggest problem currently:

    Basically we copy all info from a view into a table over night so that we can report on a table rather than the view. This is only a temporary solution, until all the data has been moved into our data warehouse.

    This works perfectly well on SQL2000 SP4 (running on a desktop), but on SQL2005 (running on a HP ML520 G2 server) this runs endlessly. sp_who2 shows a load of SUSPENDED SPIDs, which I have not seen before.

    EDIT - While this query is running, the processor usage hits 95 - 100%.

    I have read that SP2 is a potential fix, but also read that it does not actually fix it. I am busy downloading this, and will install and test, but I am not confident in this solution.

    Does anyone have any other ideas?

    Thanks in advance,

    Graham

  • you might want to check for cxpacket waits - suspect parallelism may be blocking. Try adding a maxdop 1 hint to your query.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • CXPACKET 135165 3.150802E+07 186078

    Running the query with "OPTION (MAXDOP 1)". Currently no SUSPENDED SPIDs, but is still taking a lot longer than it does in SQL2000.

    Lets see how that goes...

  • I can only generalise about maxdop settings as any given situation may vary!! On a 4 way dual core ( with no HT ) when I had parallelism issues with particular queries I found a maxdop = to the number of sockets performed best where maxdop 1 was slower. e.g. all procs 30 mins , maxdop 1 = 20 mins, maxdop 4 = 15 mins.  

    The whole process obviously depends upon the query, with HT and dual cores try sockets first, then cores, and then work downwards for best response. I don't suggest making this change on a server basis, only per query. Efficient indexing usually reduces parallelism and makes parallel queries faster.

    You also need to find the bottleneck, can you batch this? check disk io completion time for both data and tlog drives and from the source data drive. As I don't know the disk subsystem I can only suggest possible timings, I'd probably not want to see a value for data/mdf/tempdb over 10 ms and ideally zero for t log, io waits on a t log write are very bad.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Ok, well timing for this is not that big an issue really. The view is rather complex and a I have said they will be removed shortly.

    The thing is that on a desktop (using a restore of the live database from last night) the SELECT will happily finish in 2:30, which I am perfectly happy with. However on the 2005 server, using the same restore, compatiability level set to 90, statistics and index jobs all run successfully, the same query keeps running.

    G

  • Forgot, SQL Server is als set to SIMPLE recovery in both environments. On the server, the t logs, data files, and tempdb are all on separate drives. RAID1, RAID 5, RAID0, respectively.

     

  • OK, SP2 has not helped in the least...

    Any suggestions?

  • try plotting the rate of data transfer into the destination tables, I assume the data is arriving?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • well, if I take out the insert, and just return top 10, takes a minute 30. if I return top 100 after 12 minutes its only at record 49.

    what is different between 2000 and 2005 that would stop something like this?

    at the end of the day, the 2000 machine is of a much lower spec than the 2005 machine

    they are both a restore of the same backup file

     

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply