trying to remedy a S L O W stored procedure

I run a monthly process that essentially interfaces data between two SQL servers.  The process - a script is used to launch a stored procedure - updates an accounting table in one database on server A using sales records from a second database on server B.  It's a relatively simplistic procedure; check the record in server B meets specified month-end criteria, and if it does then bring the record across - with a couple of extra fields joined in fron other server B tables.

At the beginning of the year this process, which generally brings around 1,000 sales transactions and 10,000 transaction lines, took about 3 minutes to run.

However, in July our dba (not really SQL trained) reconfigured server B after a hardware failure, and everything was theoretically set up just the way it was before.  This seemed to work in that none of the sales staff using server B on a day-to-day basis have any problems but my month-end process now takes 6 hours - instead of 3 minutes.

The dba and myself have no idea on what might have changed to cause this massive increase in process time for what remains a simplistic stored procedure.

Does anyone have any idea on what we might be able to check to diagnose / fix this issue so I get back to a 3 minute run time?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A dba who isn't SQL trained? Sounds dangerous.

1. Try running the select directly on B to check whether the extraction or the transfer is the bottleneck.
2. If it is still the extraction (i.e. the select query itself) then look at the execution plan for the query using query analyser - identify the step(s) that are using the highest percentage of the overall query cost. Consider adding indexes to speed these up.
3. Bear in mind that if the sales staff are using very simple queries, then they may well have been hit as well but just not noticed - it could be a server problem. If their queries had been of the order of 20 ms, say, they would still only be up to a couple of seconds, and may just not have noticed.

What was the hardware failure, and what does "reconfigured" mean?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cawthronAuthor Commented:
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 . . ."
Hi cawthron

> re 2. What's an "execution plan"?
It is the plan the Database Engine uses to execute the query.

The execution plan will show which parts of the query are taking the most time, and therefore let you concentrate on the problem.

1. Open SQL Query Analyser.
2. Insert your query code into the editor
3. From the query menu select "Show Execution Plan"
4. Execute your query.

The problem that you will not be able to avoid is that you will have to wait 6 hours to get the query plan.

Cheers Sash
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


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

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


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

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.


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 ...
cawthronAuthor Commented:
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;

execute interfaceinvoices_sp 'yymm'


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

BOL stands for Books online,
or Transact-SQL help (available online or from the Query Analyser help menu)

Run the script in Query Analyser (not from osql command line, you need to capture the output)

exec interfaceinvoices_sp 'yymm'

--now check the execution plan
--once done you can switch back to normal

SET SHOWPLAN_ALL {ON|OFF} is local to your session, it does not affect the other users

Do you need any further help with this problem? Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.