Database performance tests 2

[Katy44]

I am trying to find out about the performance of some of our databases by running queries on them. Is there a best way to do this? Like a set of 'standard' query types that are used to assess performance?
Sorry about the vague question but I don't really know where to begin - I just know I don't want to start running any old query randomly.

 

[johnherman]

First of all, put the query in a loop (Transact SQL for Sybase and Microsoft, PL/SQL for Oracle, etc) and run the query many times in the loop. Divide the time elapsed by the number of loops and get an average time. This helps by reducing the chance that some CPU or memory intensive activity occurred during the time you ran the query that one time.

As far as what kind of queries to test, it depends on your environment - what you do with your database.

For data warehouses, queries which select large sets of rows joining many tables are representative.

For transactional systems, need a mix of inserts and updates along with queries which select a small number of rows which might be joined to several reference or lookup tables.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[Katy44]

Thank you very much. That's exactly what I wanted to know. I wondered about how to allow for network issues (the queries can take 5 mins to run - very boring to keep pressing go) - loops would work nicely.
It's a data warehouse system, so it sounds like I need to join all the necessary tables and run a few selects!

 

[jrbarnett]

The easy way of measuring the network effect is to run it from the server console, noting down the times.
Reboot the server to flush any cached data, then rerun the same tests from a workstation connecting via whatever methods you use.
Then subtract the former results from the latter. Its not a precise method, but good enough for most uses.

There are standard queries for testing the performance of databases, but you are likely to find it overkill for your requirements. Try the Transaction Processing Council (

http://www.tpc.org).

John

 

[harebrain]

First of all, put the query in a loop (Transact SQL for Sybase and Microsoft, PL/SQL for Oracle, etc) and run the query many times in the loop.

Correct me if I'm wrong, John, but those DBMSes cache queries and results. So if you perform a query n times in a loop, the performance time for loop 1 is substantial and meaningful, while successive performance for loops 2 through n is a function of retrieving the same results from the cache; i.e., for loops 2..n, the time will be both negligible and meaningless.

Or am I missing something?

 

[johnherman]

harebrain has a good point.

For SQL Server 2000, you need to limit the cache to zero using the msExchESEParamCacheSizeMax parameter.

For Oracle, cache is always turned on. However if you do a second query to a separate database within the loop, you can eliminate the cache as Oracle can only cache data from one source database.

I don't have reference materials available for DB2 or Informix at this time - sorry.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[MDXer]

if your using MS SQL this site has some excellent methods for examining system performance.

http://www.sql-server-performance.com/

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Katy44]

Thanks John. I am going to have to limit the cache on SQL Server because I've just noticed that the queries are running faster every time I run them!
I've given up on the Oracle for now - just looking to improve performance generally on SQL Server.

MDXer - thanks for the reference, it looks very useful. It looks like what I need, as I have been told in the past that performance tuning is a 'black art' and there are no guidelines! That kind of list with general hints and tips and things to check was exactly what I wanted at the time.