trying to remedy a S L O W stored procedure

Posted on 2004-11-07
Last Modified: 2010-05-18
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?

Question by:cawthron
    LVL 16

    Accepted Solution

    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?

    Author Comment

    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 . . ."
    LVL 8

    Assisted Solution

    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
    LVL 8

    Expert Comment


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

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

    LVL 17

    Expert Comment


    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.
    LVL 7

    Expert Comment

    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.
    LVL 26

    Expert Comment

    >>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 ...

    Author Comment

    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?

    LVL 26

    Assisted Solution

    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

    LVL 16

    Expert Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now