Solved

Performance issues with SQL server 2008 R2

Posted on 2014-07-22
17
311 Views
Last Modified: 2014-07-25
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?
0
Comment
Question by:pgreetisada
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 4

Expert Comment

by:Philip Portnoy
ID: 40212382
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
 

Author Comment

by:pgreetisada
ID: 40212394
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
 
LVL 4

Expert Comment

by:Philip Portnoy
ID: 40212407
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:pgreetisada
ID: 40212783
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
 
LVL 4

Expert Comment

by:Philip Portnoy
ID: 40212820
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
 

Author Comment

by:pgreetisada
ID: 40212864
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
 
LVL 4

Expert Comment

by:Philip Portnoy
ID: 40212870
I recommend checking the Performance Monitor Disk Read Queues while running those reports and posting the numbers.
0
 

Author Comment

by:pgreetisada
ID: 40214592
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
 

Author Comment

by:pgreetisada
ID: 40214624
I see that I should have added another file to that script to add the five you suggested.
0
 
LVL 4

Expert Comment

by:Philip Portnoy
ID: 40215177
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40215237
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
 
LVL 4

Accepted Solution

by:
Philip Portnoy earned 500 total points
ID: 40215533
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
 

Author Closing Comment

by:pgreetisada
ID: 40219512
This was an in-depth response process and it appears to be exactly what we needed.  Extremely grateful for the help!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40219525
?? "Yeah, you can do like that." is an "in-depth response"??  LOL.
0
 

Author Comment

by:pgreetisada
ID: 40220090
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40220699
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn about cloud computing and its benefits for small business owners.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question