Why does my SQL Server code sometimes respond slower than normal when I haven't changed anything?

A. This could be for a variety of reasons :-

1. Are you passing the same parameters through the code each time? SQL could be choosing a different access path for different values in an index. Showplan would tell you what is going on here.
2. Could be that another process has filled the data cache with pages from other tables meaning you need to do a lot more physical i/o. The show i/o stats stuff will show you whether this is the case.
3. You could be contending with the checkpoint process - this flushes all dirty pages to disk.
4. You could be contending with another user SQL process for disk i/o or being blocked on allocation of locks. Check sysprocesses and syslocks for the locks/blocking stuff. Check NT perfmon disk stats for disk contention.
5. Another non-SQL process could be running - e.g. NT backup, virus check, disk compression etc.
6. If the process is an update one then it could be that this particular run of the process needs more page and extent splits than normal. Extent splits especially can take some time.
7. Does the process do any select into's? If so you may be falling foul of system table locking. Set traceflag 5302 if this is the case.
8. Unless you are sending raw/passthrough SQL via db-lib or odbc direct then monitor the process to see what SQL is actually being passed. ODBC and all higher level access methods all make use of stored-procedures, cursors etc. even for the simplest SQL query. It may be a side-effect of some system generated SQL that is causing you the problems.

---