3

I have SQL Server Express on an AWS micro instance. These instances have only 615MB of RAM. For the most part it performs fine. My application can make up to about 15 db connections. However, every once in a while it just freezes for a couple of minutes.. my own application logs show all kinds of query time-outs, new db connection attempts failing because of an incorrect pwd (even though I am sure the attempt is using the correct pwd). But after a couple minutes everything turns out fine again.

I have checked eventvwr.msc logs but it doesn't reveal anything. I know there are not backups running at the time. And I have inspected SQL Server logs from SQL Server Management Studio but these too don't reveal anything.

Any ideas? Am I pushing the limits of SQL Server? I have read before the Express Editions run fine until certain connection thresholds get met. Could the number of db connections be an issue?

  • 1
    The definition of micro instance is: "Micro instances are a very low-cost instance option, providing a small amount of CPU resources." With that level of provisioning it might not be surprising that it takes time to up the CPU. But, as you note, it does so eventually. I would say talk to Amazon about your experience and goals to see what you need to do to get better performance. Regarding Express Edition limits, unless you are running SQL Server Express 2005, I would think it unlikely that you are hitting such a serious limit. – RLF Mar 15 '14 at 20:59
1

There is couple of important point missed

Please check whether Auto Close option is set to true for database. I have seen lots of similar issue where Auto close option was culprit.

Is OS resifing on Virtual machine or Physical ?. If its is virtual look for any VM snapshot backup happeing this might also cause freezing.

Other thing can be, do you have backup taken for database using third party tool/windows native backup if so these tools freeze data files for couple of sec to sync metadata and take consistent backup.And you can see VIRTUAL_DEVICE in errorlog when you specifically look ta backup information in it.

Hope this helps

0

Go to windows task manager and look at the Performance tab. Is the CPU running at 100%? Does the physical memory use exceed 675mb?

I would expect both to be the case, or at least all of the RAM is being used up. The pagefile is stored on a spinning disk so is much slower to access. Work out the size of instance you require based on how many users you expect and upgrade your T1.micro to something else.

If changing your instance type results in your problems going away this will confirm your instance is too small.

Good job on getting your app to work on a micro instance. It will scale well compared to a system that has been developed on the latest/greatest hardware without any consideration for performance.

  • Thanks. Regarding the CPU/Memory pegging at 100% - this is difficult to know since it is often after the fact when I inspect my application logs I see all the problems. Is there a way to see a history of this? – M Schenkel Mar 18 '14 at 12:25
  • At the end of the day it probably is just too small. I will try a "small" instance for a couple days to see if problem goes away. – M Schenkel Mar 18 '14 at 12:26

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.