SQL Server Performance

Poor performance with SQL 2008 R2 Cluster.

Discussion in 'SQL Server 2008 Performance Tuning for Hardware' started by David_W, Jan 24, 2011.

  1. David_W New Member

    Hi guys first time poster.
    We have recently upgraded our application (medical application) from a pervasive SQL backend to a MS SQL backend.
    Since we (really the software vendor) did the db conversion we have noticed a major loss in performance. Now we did of course upgrade our hardware along with this change over but i'm not 100% sure what the problem is.
    i when configuring our environment followed MS/HP/Our software vendors best practices as best i could being some times all 3 did not recommend the same thing.
    EG: i configured our san and servers hardware as per HP the cluster as per MS/Vendor and SQL as per Vendor.
    Now i'm not the most MS SQL savy person but have been doing and will be doing more MS SQL courses along with the other staff which has helped. i've also been doing alot of reading on google and this site (this site was recommended by one of my course trainers).
    Now down to it.
    Our setup is 2 x HP DL580G5 4 x 6 core 2.67ghz with 32gb of ram DUAL Path 8GB FC Server 2008 R2 cluster.
    a HP EVA4400 with 32 x 15k FC disks with 2 x Brocade 8GB FC switches.
    i have spent the last few days logging the average disk latencys across the tempdb/mdf/ldf drives via windows as per Software vendor i have the MDF and LDF on different drives and TempDB also has it's own drive. alothugh the recommendation from HP is to have all these share the same spindles a hot topic i know.
    from 6am - 6pm (our main business hours) i am seeing an average of 4ms on the tempdb drive 4ms on the mdf drive and 1ms on the ldf drive. i am seing an average disk queue length of less then 1. CPU unt is averaging 3% total across an average business day. next i though the memory might not be sufficent but the buffer cache hit ratio is constantly on 100.
    Hopefully someone can offer some advice on what the problem maybe our DB is only 23gb in size the tempdb was sized according to vendor. the only thing i haven't done which the vendor recommend is split tempdb into multiple files per cpu. does this make a large amount of difference most of my research suggest this is more an sql 2000 thing? just looking for some advice to investigate further possible hardware issues before i go to our vendor to look at the DB.
    not sure if it helps but users report that at 6am when theres about 5 users on the application is just as slow as 11:30am when there is around 120 users connected. all this said reports that use to take 45minutes to run on our old pervasive sql db now run in about 5minutes on the new db.
  2. satya Moderator

    Welcome to the forums.
    Before we jump into any conclusion confirm the kind of databas maintenance & optimization jobs or processes executed after the upgrade has been finished.
    In general for the first time the database needs to get updated statistics for optimum perfomance, I see that it could be a network lag or outdated indexes or fragmentation causing reports to run slower than expected.
  3. David_W New Member

    Actually the reports are the only thing thats improved. since though i have setup a restore job on another server and have pointed all our reports to run off that DB to reduce load on the live. only a few reports run off the live now that need up to date info.I can't tell you what was done post upgrade this was all handled by the vendors engineer. we have a agent job running every night to defrag indexes and the translogs are backed up n an hourly basis.
  4. satya Moderator

    How about update statistics on the tables that will have modified data?
    Also did you check the frequently running queries Execution plan?
    In SQL 2008 management studio you can view that in Activity Monitor *Right-click on server to invoke under Processes or Recent expensive queries.
  5. David_W New Member

    USE [MOSAIQ]
    EXEC sp_updatestats
    GO
    That query is run as an agent job every night if that helps.
  6. satya Moderator

    OK what was the result out of this stored procedure?
  7. David_W New Member

    Hope this is what you are looking for.
    Message
    Executed as user: . ...update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 4 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[FavoriteRxList] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 10 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[TagInstance] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[DeleteLog] [SQLSTATE 01000] (Message 15650) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) 4 index(es)/statistic(s) have been updated, 0 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[Progress] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 4 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[BeamLineDeviceCP] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 22 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[TagList] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , ... The step succeeded.
  8. David_W New Member

    What would be the recommended starting point for splitting tempdb on a server with 24 physical cores? would 6 be enough to see an improvment if we would see one? also with splitting the tempdb files atm my single file is 10gb if i split it into say 6x2048mb files would this cause problems would SQL want the main mdf to stay at 10gb? meaning i'd have to make all files 10gb?
  9. satya Moderator

    Is it a Quad core or Dual core?
    Also what is the physical memory set on server?
  10. David_W New Member

    it's 4 cpu with 6 cores per cpu. all physical cores. 32gb of ram.
  11. satya Moderator

    Also presuming this is not a virtual machine, the optimal number of tempdb data files depends on the degree of contention that you see in tempdb, so I see you can test the tempdb files in equal to 1/4 to 1/2 the number of cores.
  12. David_W New Member

    Yep it's physical not vm. so 6 files would be a good start? so will SQL deal ok with turning my 10gb single file into say 6x2048mb files?
  13. satya Moderator

  14. David_W New Member

    i already have data collection warehouse running thanks Satya. btw what counters should i look at to indicate tempdb is a problem when i look at the disk I/O usage report in Data Warehouse. it highlights the tempdb in red usually something about it's % of disk transfers being over 50%?
  15. satya Moderator

    David
    Thats perfect in terms of having the MDW setup already, means you can obtain the trend of CPU & Query statistics for a period of time.
    COming on your TEMPDB question I would like to go back to basics referring the following important factors:
    • Space. This refers to both used and unused space in data and log files.
      • I/O bottlenecks.
        • Contention in DML operations. This relates to page and extent allocations and deallocations for all type of objects in tempdb.
          • Contention in DDL operations. This relates to the manipulation of metadata when user objects in tempdb are created or dropped. Note that internal objects are not reflected in metadata tables
        • Also one of the technet article refers about Performance Counters
          Database: Log File(s) Size(KB)
          Returns the cumulative size of all the log files in the database. This size can grow if you have not set a maximum size for the log in tempdb.
          Database: Log File(s) Used (KB)
          Returns the cumulative used size of all log files in the database. A large active portion of the log in tempdb can be a warning sign that a long transaction that is preventing log cleanup.
          Free Space in tempdb (KB) (SQL Server 2005)
          Tracks free space in tempdb in kilobytes. Administrators use this counter to determine if tempdb is running low on free space so they can take appropriate corrective action. This counter accounts for space allocated by all three types of objects in tempdb.
          Version Store Size (KB) (SQL Server 2005)
          Monitors the size in KB in both version stores. If a version store is not shrinking, it implies that a long-running transaction is preventing version store cleanup.
          Version Generation Rate (KB/s) (SQL Server 2005)
          Monitors the version generation rate in kilobytes per second (KBps) in both version stores.
          Version Cleanup Rate (KB/s) (SQL Server 2005)
          Monitors the version cleanup rate in KBps in all version stores. If the version cleanup rate is lower than the version generation rate, the version store will use more and more space in tempdb. However, if the version cleanup rate is 0 but the version generation rate is not, there is probably a long-running transaction that is preventing the version store cleanup.
  16. sgoodwi3 New Member

    David, from your description of CPU usage and SAN latencies, you need to look elsewhere in your application stack for the problem. You want your reads to be <20MS and writes <=2MS. EVA's are much more effective when configured as a pool of disks, but this does not appear to be your problem. The CPU is barely used, and your IO is screaming fast. The issue seems to be outside of the DB tier. Look at connectivity, network, and isolate component time from application down to database
  17. Luis Martin Moderator

    Sgoodwi3, welcome to the forums!:)
    This thread is 3 years old.

Share This Page