November 5, 2010 at 9:48 am
I have an microsoft access database that is connected to SQl2008 Express. Periodically I have users that timeout when inserting records. I found the indexes were highly fragmented (over 90%). After I would rebuild the indexes the problem seemed to ease.
I then changed the fill factor of the indexes to 90%. This keeps the index fragmentation very low. Thought the problem was solved, but it seems to be creaping back. I next looked at the locks. Right now I see 99 locks on the database. AL of which Database locks for resource_associated_entity_id = 0. I do not see any locks on the tables.
My VBA fo the insert is db.Execute "Insert into Notes (ID,Notes,Entered,EnteredBy,OfficeID) Values ('" & Forms!Main!ID & "','" & Me!Notes & "', '" & Me!Entered & "', '" & Me!EnteredBy & "', '" & Me!OfficeID & "')", dbFailOnError.
Seems that sometimes the inserts are quick sometimes, slow others, and timeout others. I am just inserting 1 record.
Any suggestions.
November 5, 2010 at 9:49 am
How many users at a time?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2010 at 10:05 am
50 Users, the db is 250MB. The server is also a Terminal Server. All users Terminal in and run the Access app.
November 8, 2010 at 6:17 am
50 users on SQL Express is almost certainly your problem. It can really only deal with about 5 at a time. It's got throttles in it that are meant to keep it below that number.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2010 at 12:36 pm
SQL Express in not throttled. It does however have some limits. It only addresses 1GB of Ram adn 1 CPU and max db size is 10GB.
November 8, 2010 at 12:50 pm
bill 36362 (11/8/2010)
SQL Express in not throttled. It does however have some limits. It only addresses 1GB of Ram adn 1 CPU and max db size is 10GB.
I agree but you may want to read the whole paragraph: "...there is no workload throttle and the engine performs as in other editions. There is no hard-coded limit to the number of users that can attach to SQL Server Express but their CPU and memory limits impose practical limits on the number of users that can achieve acceptable response times from a SQL Server Express database."
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2010 at 1:17 pm
Do more Maintainance for you database
follow these steps
1) check waittypes --troubleshoot queries or change design ioptions
2) Regular backups based on business
3) Re-build or Re-org Indexes based on level of fragmentation.
4) Check Filegrowths
5) Find Large tables and take appropriate measures to troubleshoot.
Thanks,
Pavan Srirangam.
November 8, 2010 at 2:05 pm
Thanks for the replies. Perhaps I will move it try it on standard edition. You think the 1GB RAM is limiting SQL's ability to handle that many users?
The cost to upgrade to the standard ediditon would be is $7,200 per server. I am presently have 3 servers, yikes. Could easily move them to 2 servers, but still a lot of coin. May look into MySQL.
November 8, 2010 at 2:11 pm
bill 36362 (11/8/2010)
SQL Express in not throttled. It does however have some limits. It only addresses 1GB of Ram adn 1 CPU and max db size is 10GB.
Those are the throttles I'm refering to. And it's 4 GB max size, not 10, per Microsoft. (See: http://www.microsoft.com/sqlserver/2005/en/us/express-features.aspx)
You can get around the size limit by splitting data into multiple DBs. Even a table can be federated this way, with some limits.
You can kind of get around the CPU limit by using multi-core, multi-thread CPUs, as per http://support.microsoft.com/kb/914278.
You're kind of stuck with the 1 GB limit till you start getting into multiple virtual machines and/or SQL instances, and those can be a bear to manage.
But 50 users on 1 CPU had better be one heck of a CPU if you don't want serious bottlenecks, even if the queries are all small enough that 1 GB of RAM is adequate. And if the RAM isn't adequate and it's going onto the disk (tempdb and/or swap), then the slows are going to get serious.
Honestly, I don't know that I'd bother with any deeper diagnostic than that in this case. Download a free trial of SQL Enterprise, see if that solves the performance problems (it likely will if the hardware is at all adequate for 50 concurrent users). Alternately, if the hardware is just barely up to SQL Express standards, upgrade that first.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2010 at 2:21 pm
MS used to actaully throttle the performance of msde. With the release of R2 the db size increased to 10GB. I would think 50 users would be chump change for a server class machine, but what do I know. I am the one with the problem huh?
Thanks again.
November 8, 2010 at 2:29 pm
The first thing I would question would be the Access code.
If the query is running slowly through the VBA, then does it also run as slowly directly in a SSMS query window?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 8, 2010 at 2:32 pm
bill 36362 (11/8/2010)
MS used to actaully throttle the performance of msde. With the release of R2 the db size increased to 10GB. I would think 50 users would be chump change for a server class machine, but what do I know. I am the one with the problem huh?Thanks again.
50 users is chump change for a server class machine, but if the SQL engine can only access 1 GB of RAM and 1 CPU, that's probably only a small piece of your server. (At least I hope you don't refer to a computer with 1 GB of RAM as a server class machine. 🙂 )
Basically, SQL Express isn't meant to be a server-class service. That's what Standard and Enterprise editions are for. SQL Express is really for very small offices or single users.
I'm not saying this is definitely your problem. I'm saying that it's a very high probability it's your problem. Kind of like, if you go to a doctor in the winter, because you have a headache, you're throwing up a lot, you have a fever, and you were recently exposed to someone with the flu, he's probably going to operate on the assumption you have the flu first, and worry about more complicated problems if flu meds don't help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2010 at 2:38 pm
pavan_srirangam (11/8/2010)
Do more Maintainance for you databasefollow these steps
1) check waittypes --troubleshoot queries or change design ioptions
2) Regular backups based on business
3) Re-build or Re-org Indexes based on level of fragmentation.
4) Check Filegrowths
5) Find Large tables and take appropriate measures to troubleshoot.
Thanks,
Pavan Srirangam.
Some of these might be relevant, but how would large tables and "appropriate measures to troubleshoot" have anything at all to do with periodically slow inserts? And what would backups have to do with that at all?
These are good general advice, but they don't really address the question at hand, with the exception of checking wait types. I'll bet it's CPU slowing things down (as per data on SQL Express), and possible RAM (ditto), but it's worth checking. Filegrowth has some slight possibility of being relevant, but with only 50 concurrent users, even filegrowth and autoshrink combined is unlikely to produce the symptomology that's been outlined here. Same for indexes and large tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 10:53 am
Looks like SQL Express was the culprit. I upgraded it to SQL standard. It has been up for a week and no problems. Can't tell you how much time I spent re writting parts of the application.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply