We have a client running SQL Express.
The line of business application appears slow at times and I am trying to diagnose the cause. I suspect it may be because of the Express version.
If the client was to upgrade it would involve significant cost so I need to be sure and have some metrics I can present as part of a business case.
How can I monitor SQL Server to identify if the limitation so the Express version are causing bottlenecks?
MajorVersion ProductLevel Edition ProductVersion
SQL2008 R2 SP2 Express Edition (64-bit) 10.50.4042.0
Best Answer
Ray Barley created a post on MS SQL Tips regarding the physical limitations of SQL 2008 R2.
Looking at the specs, it looks like Memory or CPU will be the indicators you need to use.
So what you need to do from this point is measure the CPU and Memory to see if you have issues with either or neither.
Pinal Dave gives great tips on his blog SQL Authority on figuring out if you have CPU pressure.
Tibor Nagy made a good post on finding memory bottlenecks too on MS SQL Tips.
Paul Randal on SQL Skills also talks about how to find the pain points and measure who your trouble children are.
Things to keep in mind is that it may not be the specs of SQL Express causing you issues exactly. It could still be indexes, statistics, disk speed, network, etc. That's why the DMV's are important, you can measure and find the pain points that you measure. One key note is that historical information is important in this regard and you are running SQL Express. You may need another instance of SQL Express to monitor your current one so you can store enough data to create a historical view to analyze. You can potentially export the data daily and then import to another environment to analyze so you keep your database small.
Another side option would be to install a free trial of Database Performance Analyzer / SQL Sentry / Redgate to monitor your instance for a short period of time as those typically watch the DMV's and collect similar data. I would expect within about a week or less of collecting those metrics you could identify where your pressure points are in that environment.