0

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
3
  • 3
    If you give us the version of SQL Server, we can give you better ideas of things you can use to grab metrics. Not all versions are equal and there may be new features you can utilize in newer versions. Likewise, upgrading to the newest SQL Server Express may help with performance as well, but this is obviously a much more difficult and time consuming solution. Aug 2 '18 at 21:45
  • 2
    I would start with upgrading Express to the latest version (2017) on a test system and comparing your production workload there. You could benefit significantly from all the fixes and features that have been added in 10 years, can take advantage of new syntax, and get a new lease of life on support (your current version is well out of mainstream support, which means no more fixes or security updates unless you pay $$$ - and even that is only possible until next summer). Aug 3 '18 at 0:33
  • 1
    It's also worth determining whether the machine this is running on is installing automatic Windows updates. I've had instance where the "service module installer" (or whatever it's called exactly) has initiated itself and gobbled up all the compute power. If automatic updates are on, turn them off and do them manually.
    – pim
    Aug 3 '18 at 9:50
3

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.

1
  • Fantastic answer. No two ways about it.
    – pim
    Aug 3 '18 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.