SQL Server Performance

New Server - SQL Knowledge - Low

Discussion in 'Performance Tuning for Hardware Configurations' started by fiddler, Oct 6, 2006.

  1. fiddler New Member

    Hi all,

    Probably a very basic question but her goes anyway.

    We recently moved a SQL database form an old Dell PIII single processor server with not a lot of memory or enough disk space left for more then 1 nights backup to a new Dell Power Edge 1850 with Two 2.8 Gbyte Dual Core Processors with 4 Gig Singel bank memory and 136 Gig Disk space. The Database is around 2 gig

    The performance we are getting is worse than the old server, some functions are showing the good old hourglass as a regular occurence.

    We have run the client on the server, so eliminating network communication as the cause?, with no improvement.

    I am fairly new to SQL server management, I have done the MS course but it did not prepare me for this, can anyone tell me where to look for and answer?

    All suggestions more then welcome.

    Thanx

    Andy
    FMD IT Manager
    University of Reading
  2. catullus Member

    Start by looking at the performance monitor: %CPU, avg disk queue length, commited bytes, pages/sec, network utilization. This first step should indicate if and where you have a hardware bottleneck. Maybe you could post the results?

    Early morning, when I wake up,
    I look like Kiss but without the makeup

    --Robbie Williams, Strong
  3. fiddler New Member

    I've just put some counters on.

    I am now totally confused!

    We have two dual core processors but the monitor sees 8 instances surely it should be 4?

    So this may be pointing to a config error at server level?

    Andy
    FMD IT Manager
    University of Reading
  4. joechang New Member

    your new system has 2 processor sockets, each of which are dual core
    for a total of 4 cores
    each in based on the Pentium 4 (Xeon version), which supports Hyper-Threading
    that is why perf counters show 8

    if this system is slower than the old Pentium III, your problem is probably related to parallel execution plans
    set the Max Degree of Parallelism to 1
  5. fiddler New Member

    Just another thought!

    When we installed SQL it asked us for teh number of processors, 2 dual core we entered 4 - was this correct?

    Sorry we were a bit hit and miss on this and the house which supplies the database is of very little help.

    Andy
    FMD IT Manager
    University of Reading
  6. catullus Member

    Look at the processor tab on the properties of your server in Enterprise Manager. If all available processors are selected, this is OK. Otherwise, select all & reboot SQL (if possible).

    Early morning, when I wake up,
    I look like Kiss but without the makeup

    --Robbie Williams, Strong
  7. fiddler New Member

    That last one looks OK!

    However I did notice that Boost SQL Server Priority on Wondows was not checked, as this is teh only app on the server it would seem sensible to do that Yes? No?

    The worrying facto ar present is that there are only 10 users - very soon this will increase to around 40!


    Andy
    FMD IT Manager
    University of Reading
  8. Adriaan New Member

    Don't boost anything.

    Like joechang suggested, see what happens if you set the Max Degree of Parallelism to 1. This is a known bug where too many CPUs are used for simple queries. Was supposed to have been fixed in a 2000 SP, but apparently still exists in 2005.

    If you can trace it to specific queries, then you can add the OPTION (MAXDOP 1) clause at the end of each query, instead of the server-wide setting for Max Degree of Parallelism.
  9. fiddler New Member

    Thanx for that one there, we are still looking round and changing nothing until we are sure.

    My biggest problem is the potential increase in users in the not too distant future, the server room amy be nice and cool but...............


    Andy
    FMD IT Manager
    University of Reading
  10. Adriaan New Member

    We've seen some instances where an index would have helped, with blocking as the outside symptom. Adding the MAXDOP option after each relevant query, the problem was solved.

    So you might also look into the indexes available for the poor performing queries - perhaps the Index Tuning Wizard has a suggestion or two.
  11. fiddler New Member

    From all the database work I've done in the past - going right back to Dataease and dbII in DOS I suspect this last one will help anyway. There is a but here though, the databse itself is a third party supplied product, we can't get through to the code and I wouldn't even if I could.

    Once I have proved the server is running well I can then hit them with a big stick if we can't get any improvement in the performance.

    As an aside I took over support & management of this system a month or so back and took support management on board 2 weeks ago. I've had over 20 support requests through on software bugs in the last week. They have been using this (with a high level of frustration) for 2 years now.

    Not good to say the least, I have to be very specific about what I want and what is happening as I think they are getting a little 'sensitive' when I email them. I had the support agreement dug out for me today, I think that says where I am at.

    Thanx for all the help and please keep your thoguhts coming in.

    Andy
    FMD IT Manager
    University of Reading
  12. cstrong New Member

    How did you actually move the databases to the new server? Full restore of system and user database?

    The standard sql server installation out of the box is good enough for the majority of databases.

    Was the performance issue noticable immediately or gradually? If you only restored the production database and didn't restore any system databases, did you then create any maintenance plans which include optimisations or have you done any reindexing, updating stats etc?


    Clive Strong
    SQL Server DBA
  13. fiddler New Member

    Hi - long time in coming - apologies to all - we changed the Max Degree of Parallelism to 1 in the end and it worked instantly.

    Big thanx for all the comments and help recieved, it was good to know we were not alone on this one.

    Andy
    FMD IT Manager
    University of Reading

Share This Page