The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Databases & Performance

What steps (simple and complex) would you take if your SQL queries were *suddenly* running more slowly than before and performance of your database became sluggish and noticely slow?
Not a DBA
Tuesday, March 13, 2007
It would depend on the database. In Oracle I'd be running statspack regularly to identify my top resource users, number of io's, wait events and other system statistics, so I ought to be able to see almost immediately what the problem is.
David Aldridge Send private email
Tuesday, March 13, 2007
This sounds like a homework or interview question.

To correctly answer, you need to know the DB server you are using.

Use a query analyzer, investigate the execution plan of your query, see where it is spending most of its time. 

Check memory and CPU metrics on your DB server while the query is running. 

Investigate database IO activity, disk space usage, and transaction log usage.

Make sure it's not the software retrieving the query and processing the results instead of a DB problem.

Check to see if there are new columns that are being joined in a query but do not have an index (the execution plan should highlight this if it exists)

While the query is running, check to see what the lock status of resources in the database are.

Make sure nothing else runs on your database server, minimal services.
Dan Fleet Send private email
Tuesday, March 13, 2007
When I see a database all of sudden begin to lose performance I go through a quick checklist:

- check to see if any objects have been added/modified during the time period the problem began.  Many times I've seen a quick change to resolve a production bug resulted in other problems, especially if the development team works under stressed deadlines with little time for extensive testing.

- check system resources especially diskspace.

- check the system event logs and sql logs for any underlying hardware issues.

- check the system for any recent OS updates, patches, permission changes, etc.
eric moore Send private email
Tuesday, March 13, 2007
One seamingly obvious but often times overlooked aspect is to verify that all appropriate attributes have been designated as primary keys or indexed.
Wanna be DBA
Tuesday, March 13, 2007
I'd call my DBA.
Tuesday, March 13, 2007
In order:

1) Missing Indexes
2) Bad query
3) Bad application code
4) Bad schema

It helps to build a way to profile your queries from within your application to find any hotspots.
Cory R. King
Tuesday, March 13, 2007
+1 to checking for locks
Tuesday, March 13, 2007
Before you go deep into optimization, maybe you could do some basic checks:
 * how much users you have on system (can you add another CPU or buy faster one )
 * how much data you have (if your database buffers become to small, can you upgrade memory)
Wednesday, March 14, 2007
The keyword *suddenly*  brings only one thing to mind. LOAD.
Check for active queries - did someone submit a bad sql 10,20,30 times? This is the cause for us, 9 times out of 10. It will also lead you down the path that others suggest too. All those running queries - were they just mistakes? Like forgeting join clauses or is there indeed a missing index?
It all comes back to the sql. Upgrading hardware/memory - those are not answers to *sudden* performance problems.
Thursday, March 15, 2007
make sure someone hasn't installed a virus checker on the machine, and it is running its daily full system scan

i have hit that one a few times
Thursday, March 15, 2007
check if your "program user" is the problem (probability is 90%).
Wednesday, March 21, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz