[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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?

0
cawthron
Asked:
cawthron
  • 2
  • 2
  • 2
  • +3
3 Solutions
 
muzzy2003Commented:
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?
0
 
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 . . ."
0
 
SashPCommented:
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
SashPCommented:
cawthron,

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

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

Sash
0
 
BillAn1Commented:

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.
0
 
ala_frostyCommented:
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.
0
 
HilaireCommented:
>>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 ...
0
 
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;

SET SHOWPLAN_ALL
execute interfaceinvoices_sp 'yymm'

???

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

0
 
HilaireCommented:
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)

SET SHOWPLAN_ALL ON
exec interfaceinvoices_sp 'yymm'

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

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


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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now