1

We have an app that runs SQL Server 2008 R2 Express on the back end on a Windows 2008 R2 virtual server running on VMWare ESXi. Users complain that the app is very slow. I see very high disk IO that does not make sense to me.

Background:

  • Average disk queue length is around 60, max is around 250
  • Average disk reads/sec is around 2000, with max around 3500
  • Resource Monitor shows that sqlservr.exe is reading the .mdf file at around 40-60 MB/sec (see below). Write IO is very low.
  • The .mdf file is only 2 GB, which means the entire thing should fit into memory
  • The server has 32 GB RAM and typically less than 16 GB in use
  • The server has 8x 15k SAS disks in RAID10. It is a Dell with a PERC H710 controller, which is capable of taking an SSD to enable CacheCade (SSD-based read cache), which I am tempted to try, but it seems like the whole thing should be cached in RAM anyway so I am not sure this will help.
  • The SQL VM sits on a Dell server running VMWare ESXi 5. There is one other VM on that ESXi host, but it is a domain controller with very little IO.
  • This app previously ran on a standalone Windows 2003 server. We upgraded to Windows 2008 R2 and also upgraded the app to a newer version at the same time, so it's possible either of those factors may be contributing.

What are the options to dig into this deeper? Is there a way to see what the SQL server is doing under the hood (what queries are being executed, etc)? Does this behavior seem normal, and the load is just too much for the storage? I am wondering if something is not configured correctly.

enter image description here

Improve this question
11
  • What edition of SQLServer? Note that Express only supports 1GB so depending on how the database is queried by the application it could end up disk bound.
    – Brian
    May 12, 2014 at 15:57
  • On top, check stats for outragous slow queries - that may be aother reason that the RAM is badly used (table scans instead of use of indices).
    – TomTom
    May 12, 2014 at 16:00
  • @TomTom can you give more detail? I am not sure where to check that.
    – user1594322
    May 12, 2014 at 16:08
  • What edition of SQL Server 2008 R2? (there are several available from Express to Datacenter). See: support.microsoft.com/kb/321185 in the "How to determine which version and edition of SQL Server Database Engine is running" section.
    – Brian
    May 12, 2014 at 16:15
  • 2
    It means that SQL 2008 R2 Express will not use more than 1 GB of RAM, period. That's everything, not just cache.
    – mfinni
    May 12, 2014 at 16:51

2 Answers 2

Reset to default
1

http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx#Scalability

Espress won't use more than 1 GB of RAM for the SQL process. You need to buy and install at least Web or Standard to use more than 4 GB - they both support 64, which is more than you have, so go with one of those.

Also, being a DBA is a pretty deep topic, but it's very accessible via Technet and the Help file. "How do I know what queries are being executed" is pretty much a Day-1 topic (as is knowing what edition is appropriate for a given task.) Profiler isn't even available for Express, so you're up a creek as you are configured right now.

Improve this answer
1
  • SQL 2008 R2 Express here regularly consumes 1.6+ GB and commits nearly 2 GB. The oft-quoted 1 GB figure must be a subset or oversimplification. Would anyone care to expand?
    – underscore_d
    Sep 14, 2015 at 12:42
0

Look at query details - check queries that have a high physical or logical IO, isolate them, look at query plan. It is years that I used an oudated version like 2008 R2... http://gallery.technet.microsoft.com/scriptcenter/Find-inefficient-query-88f4611f may give you a start.

My pointer would be towards missing indices triggering table scans. THe profiler and tuning wizards may also help to get a grasp on where the problem is. IF 2008 R2 already has the activity overview in the manager then this has a nice window with the most expensive queries.

Improve this answer

Not the answer you're looking for? Browse other questions tagged .