Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Performance issues with SQL server 2008 R2

Posted on 2014-07-22
17
Medium Priority
?
327 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 70

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 2000 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 70

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
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.

609 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