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

user1594322
  • 229
  • 1
  • 4
  • 10
  • 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 '14 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 '14 at 16:00
  • @TomTom can you give more detail? I am not sure where to check that. – user1594322 May 12 '14 at 16:08
  • What edition of SQL Server 2008 R2? (there are several available from Express to Datacenter). See: http://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 '14 at 16:15
  • Well, in this case: Hire a pro, or start reading a book. I won't make a crash course in system administration on a site for programmers. Too broad for a Q&A format. – TomTom May 12 '14 at 16:17
  • @Brian I see, version 10.50.4000.0 – user1594322 May 12 '14 at 16:18
  • Keep reading till you see XXXXXX Edition, the XXXX is what edition your engine is. – Brian May 12 '14 at 16:22
  • Oh no, it says: Express Edition with Advanced Services (64-bit). They purchased a SQL Server for this, obviously it didn't get setup. When you say it supports 1GB, do you mean 1GB cache in RAM? – user1594322 May 12 '14 at 16:29
  • 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 '14 at 16:51
  • Use Enterprise edition. Don't be a cheapskate. – Tom O'Connor May 13 '14 at 14:23
  • 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 '15 at 12:42

2 Answers2

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.

mfinni
  • 36,144
  • 4
  • 53
  • 86
  • 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 '15 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.

TomTom
  • 51,649
  • 7
  • 54
  • 136