Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server, if you're comparing the statistics IO from two queries, how do you ensure that caching doesn't become a factor?

For example (I'm making this up as I type, not a real example):

SELECT Type,
    NAME
FROM TableA a
LEFT OUTER JOIN TableB b ON a.id = b.id
WHERE b.id IS NULL ;

SELECT Type,
    NAME
FROM TableA a 
WHERE NOT EXISTS (
        SELECT 1
        FROM tableb
        WHERE id = a.id
        ) ;

What I do now: I run it that way, get a new connection (new SID) and run it like this (in reverse order):

SELECT Type,
    NAME
FROM TableA a 
WHERE NOT EXISTS (
        SELECT 1
        FROM tableb
        WHERE id = a.id
        ) ;

SELECT Type,
    NAME
FROM TableA a
LEFT OUTER JOIN TableB b ON a.id = b.id
WHERE b.id IS NULL ;

Is there a better way?

After the fact additional information

This is assuming that you're not a sysadmin.

share|improve this question

2 Answers 2

You'll need to clear the query cache between queries. See:

http://stackoverflow.com/questions/1873025/how-can-i-clear-the-sql-server-query-cache

for a previous answer on this.

share|improve this answer
    
Thanks, I've changed the question such to describe that the account does not have sysadmin –  Dan Andrews Jan 31 at 14:46

Is it realistic to assume that the data in tableA and tableB will never be in cache? What percentage of queries run on the system will be run when all of the data has to be pulled from disk?

There are two trains of thought on testing queries and comparing them:

  1. run them both with a cold cache (where you clear cache / buffers etc. before every single query)
  2. run them both with a warm cache (where you first prime the cache by running the queries multiple times before bothering to start comparing metrics)

Since ideally in real-world scenarios you will be dealing with 2. most of the time, it seems relatively futile to spend too much effort on 1. Unless you are in a case where the production environment is really low on memory, in which case I would rather spend money on more memory than the labor costs of trying to compare and tune queries that are always going to be dependent on physical I/O. I would think that realistic testing of 2. is more useful than 1., again in most cases.

If you really want to do this, I suppose you could forcefully fill up the buffer with data from other tables, supplanting the data from these two. Though I would be really concerned about tinkering this way too much with a system where you don't have sysadmin privileges (it's probably for exactly this reason, or at least one of the reasons).

The other suggestion would be: take a backup of the database, and restore it to a system where you are sysadmin. This may be dealing with different I/O subsystems, but aside from the differences in pure I/O performance, the relative differences between the queries should still be useful to compare when you can free the cache and the buffers freely.

share|improve this answer
    
Yes, this is the train of thought. I've always beat myself up over #1 but your addition and rational behind #2 is a good point. The developers do not have sysadmin, it's kind of hard for them to honestly check these things. –  Dan Andrews Jan 31 at 15:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.