Solved

Performance issues with SQL server 2008 R2

Posted on 2014-07-22
17
296 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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:ScottPletcher
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:ScottPletcher
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now