trying to remedy a S L O W stored procedure

To put it politely; our organisation hasn't fully thought through the implications of not having a fully trained SQL dba.

re 1. Above - excellent suggestion (and presumably one we should have thought of) and I'll test it in 5 and a half hours
re 2. What's an "execution plan"?
re 3. Point taken.

The failure was the motherboard exploding - non-technical term :-)  Reconfigured was a new box, old disk array, reinstalled OS, rebuilt db's - or something like that.  "Dammit Jim, I'm an accountant not a computer person . . ."

cawthron,

You can try running the "Show Estimated Execution Plan".

However this does not always work, especially if temporary tables have been utilised.

Sash

When you say the "rebuilt db's", was this done manually? or was it a case of restoring a backup? If the former, I would suggest that the most likely thing is that your DBA omitted to add an index somewhere. This is the most likely cause of a catastrophic change in performance on a database that has similar hardware, schema, data etc.

I offer an Amen, helleloolya to BillAn1's suggestion. Check your Primary Keys are all in place and look for indices that might've been omitted. Worst case, examine the execution plan and see where the lion's share of the analysis is lying.

You could also post the query here and let us hacks have a go at it.

>>The problem that you will not be able to avoid is that you will have to wait 6 hours to get the query plan.<<
I don't think so.
You can use SET SHOWPLAN_ALL ON to parse the query and see execution plan whithout executing the query, thus saving 6 hours of disk IO and/or network traffic

<BOL>
SET SHOWPLAN_ALL
Causes Microsoft® SQL Server™ not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

Syntax
SET SHOWPLAN_ALL { ON | OFF }
</BOL>

As for the perfs issue, it might indeed be an index issue. Yet the DB has most unlikely been "rebuilt" from scratch (you can rebuild the DB structure like that, not restore the data).
If the DB has been restored from a backup, or "re-attached" from a cold backup (sp_detach or *.mdf + *.lfd file copy  when the DB is offline), it may have been accidentally restored in the wrong place.

The default location to restore a DB might be on the system partition (typically C:\) if SQL Server has been installed there and if you don't specify an explicit path to another place. However on most real-life DBs, the data (MDF) and log (LDF) files are most likely to be installed on distinct drives ( you might have data on E:\, log on F:\, and save disk space on C:\ - the OS uses "free" disk space on the system partition to "swap" when there's not enough RAM).

So you could
- check there's enough free space on the C:\ drive (you should have at least twice the RAM free, say you have 1 Gb Ram on the server, you should have 2 Gb free on C: drive). Not enough space might cause the server to swap forever instead of processing queries.
- check the remaining space on the other drives
you can use
exec master..xp_fixeddrives
in QA to find out

- check file location and size
you can use
sp_helpfile for this

and post the results ...

Sorry Hilaire, I don't understand <BOL> ???

At the moment I kick off the process with a script;

execute interfaceinvoices_sp 'yymm'

So do you mean I can change this to;

SET SHOWPLAN_ALL
execute interfaceinvoices_sp 'yymm'

???

or do I have to add the set showplan command to the beginning of the sp?

Do you need any further help with this problem? Thanks.