March 27, 2009 at 2:52 am
Morning guys,
I have a question on performance troubleshooting. It's a bit broad and possibly very amateur - feel free to point an laugh.
I help look after a DB which has various applications hanging off. It has a lot of business logic tied in at the DB layer in the triggers. The table keys are all custom munged varchar GUIDs rather than autoincrement ints (There's only one place in the DB where this is useful, but all tables use this style anyway)
A lot of the tables have auto-created indexes which haven't been updated in a long time. We have improved indexing in a lot of places, but it's slow going.
We're in the long, gradual process of rewriting the useful legacy functionality into a set of web based apps, but in the meantime we have a combination of spreadsheets, Access frontends (some compiled only), hotsync-over-odbc devices (Some of these remote!) and PHP. Where we can, we've altered user logins to be app specific, if not user-specific, allowing tighter security and better visibility of which apps are running/locked etc.
Surprisingly, the db performs quite well, but the load on this db can be quite erratic, with pauses and lockups throughout the day. Mainly this is noticable in page load times, though the hotsync suffers also. I'm currently trying to debug a spike which has been going on all day - we usually only get a few discrete spikes.
Running through the Profiler, I have worked out a lot of the long running and possibly harmful queries and either rewritten or even excluded these.
But just because a query takes 20 seconds to run, doesn't mean it thrashed the IO or otherwise seriously degraded performance generally. There can be many factors, a lot of which are invisible. There may be a lot of 'fast' queries that can also be sped up or dropped, and the load is simply higher.
What I'd ideally like to be able to do is graph the combined load on disks and CPU over time and be able to see counts of all the distinct queries at that peak.
Are there any tools out there to help me do this? Any docs you'd recommend? I've a feeling I should be looking at performance cubes - will they actually help?
Thanks very much in advance! 🙂
Cheers,
-Oli
March 27, 2009 at 2:56 am
Hi,
Sorry, just realised I posted this in the the wrong forum - trying to delete but it's still here!
Reposting this to 'performance' forum
🙂
Cheers,
-Oli
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply