We have an sql database that is fed by a website and also by internal stuff.
Today it’s been timing out on any query that involves an insert or update, but eratically. In other words it will work for a period, stop for a period, work for a period.
And it doesn’t get heavy use. when it’s working it can handle anything. when it’s not working it accepts nothing.
Any ideas? Ever seen this before?
Table lock? In older databases, an improperly indexed foreign key relationship would cause the entire child table to go into lock mode during any edit of the parent table (and occasionally vice-versa, but that was rarer). If the table is large and not memory-resident, then the disk read cycles (particularly if you’re also running into read-head contention issues) could bind things up pretty well.
If a table is in lock mode can simple non insert/update queries (queries that just request data) still be run on it?
The DB in question is only a few months old. It’s a microsoft sql server 2005 (or 2008, I’m not sure) dabatabase.
edit: when I say few months old - I mean it was updated from 2000 to 2005 only a few months ago. it’s been virtually problem free running up to now.
though my IT colleague has just told me he thinks he’s found the problem…
both of us are at home, remoting in, talking on skype. Isn’t technology great!
It sure didn’t in our case. As I understand it (and it’s been a while, this was an Oracle 7.2 database back in 99/00), the table locks because the engine needs to make sure only one thing is happening at a time, and since theoretically the table could only be partially updated, no queries either.
The problem went away after we did two things, first we ran the whole DB through a thorough analysis and added the appropriate indices. Second, we added a ton of memory to the box and pinned a whole bunch of key tables permanently into memory to avoid disk contention issues.
Edited to add: Please be sure to share the solution, I’d love to hear what it was. Never know when that might come in handy…
Is any update work being cached? Is it possible that the failures are either happening when the system is busy committing the cache? or (not sure how) getting rolled back inappropriately along with something else? Or just that the cache is broken and is losing data somehow?
Depending on the relative transaction isolation levels, microsoft sql can certainly treat a select and an insert/update differently in a table lock situation.
Have you tried looking at the ‘current activity’ on the DB at the time that the issue is occuring? (That’s what it’s called in 2000 enterprise manager, I’m not sure what the 2005 equivalent is or how to get to it.)
I will share the solution. My IT guy is like a yo yo at the moment - thinks he’s getting near to the problem then bam, it’s something else.
so far it might be network. he’s getting ping timeouts from the sql server.
Unfortunatelly I don’t have much useful access to the db from remote. the IT guy does. I’m twiddling my thumbs while he roots around, so I figured I’d do something useful with myself and ask you fantastic people 
edit: what I mean is I don’t have the use of sql server management studio (the 2005 equivalent of enterprise manager) from the machine I’m remoting to.
…and this is the reason companies hire (or should) qualified DBAs rather than jack-of-all-trdes IT guys who just go search the internet and post on message boards.
I mean, I guess if this isn’t an important application, it’s no big deal, but really, this would be something that a trained DBA with access to the server could resolve (or at least elimintate the DB from being the probable cause) within a few minutes, typically. How much are those minutes worth to your employer?
You don’t need management studio, if you have any simple query tools like Query Analyzer or equivalents, you can connect to the DB and use sp_who2 to see if there are any blocking spids while you are trying to insert or update.
I’m not the IT guy.
The IT guy I was speaking with isn’t the IT Manager. The IT manager was off work.
The IT Manager has put some monitoring on the server to see what is going on. Apparently the database is trying to resize.
The reason this company doesn’t have a dedicated DBA is that it isn’t some US giant with megabucks to spend. It is a small legally operating company trying not to fall into the crack created for illegally operating* companies by the US government. It would be stupid to pay a wage for a person who’s only purpose is to look after a DB that between me and the IT guy we can do ourselves. I ‘just searched the internet and post on message boards’ to be useful while the IT guy did more IT type stuff.
[sub]*defined as ‘illegally operating’ by the US govt.[/sub]
too late for edit: Our parent company does employ a web developer who is also a DBA. He charges £85 an hour and will create a 12 hour job plan (for a job that can be done by myself and the IT guy in 1 hour) by adding stupid amounts of red tape to the job. And treating the job as if it is on the **database_of_calculations_to_stop_the_earth_exploding **database.
If we can get away with not paying this guy, who will probably say something like “Oh you’ll have to shut your website down for a few months until I have time in my schedule” and once he does begin - will drag the job out for ten times the amount of time that is necesary, then we will.
I’m aware of what your company does, and I’m aware you’re not the IT guy. I deal with folks like you, business analyst types who have a smattering of DB knowledge, in my job. Typically, they know just enough to get a bunch of things done pretty cheaply, at first, but after a while they find they have dug themselves into a huge hole, and they then ask the fully trained folks to help them out of it with a simple fix. Of course, it’s never simple, but no one ever understands why it’s going to take 400 man-hours to set things straight. Things like standards, and designing for scalability, etc, never seem important until they’re biting someone on the ass.
A trained DBA is pretty cheap compared to redesigning your whole system. I imagine you uupgraded your database without DBA help because it seems pretty simple, huh?
Anyhow, did you ever get the problem resolved? How much is this costing you per day of unavailability?