Performance issues with SQL server 2008 R2

I have a CRM database running on SQL 2008 R2.  The server was running slowly so we upgraded the hardware and now have a 8 processor, RAID 10, 64GB RAM server with the OS on a SSD and the log files on separate RAID 10 drives.  The old server ran sluggishly and now the new server is doing the same.  The vendor who supports the DB has sent their engineers into the system and reported nothing amiss, yet processes that used to take a few minutes are taking 2 hours now.  The CPU utilization rarely hits 5%.   Physical memory is at 73%.  Network utilization is low.  The highest response time for any process is 6ms.  I ran DBCC memorystatus per a doc from MS but I am not sure what I am looking for.  The only table result that looked potentially off is this one.

Buffer Pool      Value
Committed      5242880
Target      5242880
Database      4421552
Dirty      38393
In IO      0
Latched      1
Free      58196
Stolen      763132
Reserved      0
Visible      5242880
Stolen Potential      4217604
Limiting Factor      17
Last OOM Factor      0
Page Life Expectancy      16544

What should I be looking for to determine why this system is slowing down?
pgreetisadaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
Well, first get the stats on wait types.
Querty for it is here: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Are you doing maintenance, such as Index rebuilds/etc?
0
pgreetisadaAuthor Commented:
Here are the stats.  The maintenance goes off every night at 3AM.

WaitType      Wait_S      Resource_S      Signal_S      WaitCount      Percentage      AvgWait_S      AvgRes_S      AvgSig_S
CXPACKET      1450945.39      1418048.48      32896.91      601614028      64.32      0.0024      0.0024      0.0001
PAGEIOLATCH_SH      407157.35      406906.84      250.50      8543112      18.05      0.0477      0.0476      0.0000
PAGEIOLATCH_EX      161108.21      161019.21      88.99      22247627      7.14      0.0072      0.0072      0.0000
LATCH_EX      40568.34      39532.50      1035.84      9544210      1.80      0.0043      0.0041      0.0001
OLEDB      29853.80      29853.80      0.00      625362435      1.32      0.0000      0.0000      0.0000
ASYNC_IO_COMPLETION      27539.68      27539.66      0.01      170      1.22      161.9981      161.9980      0.0001
BACKUPBUFFER      26518.08      24723.90      1794.18      2995772      1.18      0.0089      0.0083      0.0006
0
Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
You have a lot of PAGEIOLATCH_XX waits.
Where tempdb is located? What drive is it, slow, fast? How many cores does your CPU have? Is it a VM or not? How many data files does tempdb have?
If you run performance monitor trace for Avg. Disk Read/Write Queue for drives where databases are located, what will be the numbers?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pgreetisadaAuthor Commented:
D drive.  Fast - RAID 10 brand new.  Two hard drives.  Not a VM.  2 processors.  6 cores.  tempdb has 1 file.  D Drive Read avg. 1.484, D Drive avg write .014, E drive avg read 0.00, E drive avg write 0.004.
0
Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
Your disk numbers look OK.
Try the following:
- Add 5 data files to tempdb. Place them in the same place as other tempdb files. Make sure that the size is absolutely the same for all files, including the existing one. Restart SQL Server.
- Check if it helps. Do not roll this change back, it won't hurt you.
- Change Max degree of parallelism to 4. Check if it helps. If it does not - change to 2. Check if it helps. If not - roll the change back to 0.

And when exactly the slowness occurs? Reports/inserting data/some other operations?
Do you have max/min SQL Server memory set?

Please note that there's >60% probability that there are problems with database design. PAGEIOLATCH waits usually occur when Database Engine is performing full index scans, which is always bad.
0
pgreetisadaAuthor Commented:
I really appreciate all of your help.  I have to wait until after hours to restart SQL.  I will make these changes and post results.  

The slowness occurs on query intensive reports and builds throughout the day.

Minimum is 0.  Max is 40960.

After the server upgrade did not resolve the wait issues I suspected the DB design was faulty.
0
Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
I recommend checking the Performance Monitor Disk Read Queues while running those reports and posting the numbers.
0
pgreetisadaAuthor Commented:
Can the new files go onto a different drive?  The E Drive has a ton more free space.  Is this syntax correct?  The current tempdb is listed as 1129MB.

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'e:\tempdb2.mdf', SIZE = 1129);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'e:\tempdb3.mdf', SIZE = 1129);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'e:\tempdb4.mdf', SIZE = 1129);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev5, FILENAME = 'e:\tempdb5.mdf', SIZE = 1129);
GO
0
pgreetisadaAuthor Commented:
I see that I should have added another file to that script to add the five you suggested.
0
Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
Of course they can.
Yes, syntax is correct, but there should be 1 file on top of that, you're correct. To ensure that files are of the same size (SQL Server sometime rounds up the values) I'd recommend making them 1130 and then making first one 1130 as well.
0
Scott PletcherSenior DBACommented:
If the current tempdb is listed as 1129MB, and it's all in one file, then when you have 5 files, each one will need to be only ~1/5 of that:

 ALTER DATABASE tempdb
 MODIFY FILE (NAME = tempdev, SIZE = 230MB);
 ALTER DATABASE tempdb
 ADD FILE (NAME = tempdev2, FILENAME = 'e:\tempdb2.mdf', SIZE = 230MB);
 ALTER DATABASE tempdb
 ADD FILE (NAME = tempdev3, FILENAME = 'e:\tempdb3.mdf', SIZE = 230MB);
 ALTER DATABASE tempdb
 ADD FILE (NAME = tempdev4, FILENAME = 'e:\tempdb4.mdf', SIZE = 230MB);
 ALTER DATABASE tempdb
 ADD FILE (NAME = tempdev5, FILENAME = 'e:\tempdb5.mdf', SIZE = 230MB);
0
Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
Yeah, you can do like that. It's going to grow after that, but sometimes, to shrink existing tempdb file down you need server restart.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pgreetisadaAuthor Commented:
This was an in-depth response process and it appears to be exactly what we needed.  Extremely grateful for the help!
0
Scott PletcherSenior DBACommented:
?? "Yeah, you can do like that." is an "in-depth response"??  LOL.
0
pgreetisadaAuthor Commented:
Jim, I awarded the points for what helped me.  It was an in-depth answer.  The "Yeah you can do like that" was one small part of a whole bunch of suggestions I got from Phillip to get to the root cause of what was delaying our server.
0
Anthony PerkinsCommented:
JI awarded the points for what helped me.  It was an in-depth answer.  
Nobody is questioning who you award points to.  The point that you are missing is that readers in future will be reading this thread and will gravitate to the one comment that is decidedly not "in-depth" and clearly does not address the original question.  All we are suggesting is that you follow the EE guidelines regarding accepting comments as solutions so that we all benefit from it.  Notice that I said comments and not comment.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.