Poor performance with SQL 2008 R2 Cluster. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Poor performance with SQL 2008 R2 Cluster.

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.

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.

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.
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.

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

OK what was the result out of this stored procedure?

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.

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?

Is it a Quad core or Dual core?
Also what is the physical memory set on server?

it’s 4 cpu with 6 cores per cpu. all physical cores. 32gb of ram.

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.

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?

It should be, you can monitor the CPU and Memory usage while the load is running.
As you are using SQL 2008 why not setup data collector *(Management data warehouse) that will collect the information for historical purpose. MDW references:
http://www.sql-server-performance.com/articles/per/Management_Data_Warehouse_p1.aspx
http://msdn.microsoft.com/en-us/library/dd939169(v=sql.100).aspx
FYI

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%?

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.

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
Sgoodwi3, welcome to the forums!:)
This thread is 3 years old.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |