Strange Stored Procedure Behavior (Dev vs Prod)

25 pts.
Tags:
Microsoft SQL Server 2000
SQL Server 2005
SQL Server Query
SQL Server stored procedures
Stored Procedures
I have a stored procedure that runs on my Production database in 0.394 seconds. However, when I try to run the SP on a DEV box it takes well over 15 minutes to run *every* time. I have checked indexes and everything seems to be identical (I did a backup/restore to the Dev box in the first place). The only major difference (besides the computer hardware) is that the Production DB is SQL Server 2000 and DEV is SQL Server 2005. Production environment hardware: Xeon 1.6GHZ (x2) 8GB RAM Dev environment hardware: P4 3GHZ (x1) 4GB RAM I can't imagine the hardware difference is causing that kind of slow down, but I didn't want to rule it out. And I really don't think that is the problem because other queries seem to run ok (I haven't tested all 175 of them yet though). Also, just so you know, the # of records in the tables is 1399, 3528, 28442, 8230, 100 and 19 so we're not talking about loads of data. Like I said before, the query runs in less than a second in production. Any suggestions as to what might be causing this in my DEV environment? Here is the query from the stored procedure that is taking so long to run... Keep in mind that I have replaced declared and temporary variables with hard-coded variables in several places for simplicity sake. SELECT DISTINCT(rfq.rfqid), rfq.startdate FROM dbo.rfqs AS rfq LEFT JOIN dbo.organizations AS org ON rfq.orgid = org.orgid LEFT JOIN dbo.bidsubmissions AS bs ON rfq.rfqid = bs.rfqid LEFT JOIN dbo.rfqtimext AS rfqet ON bs.subid = rfqet.subid LEFT JOIN dbo.timezones AS tz ON rfq.tzid = tz.tzid WHERE ((org.spid IN (1) AND rfqet.newendtime IS NULL AND rfq.enddate > DATEADD(hh, tz.offset, GETUTCDATE()) AND rfq.enddate < '12/31/3000 23:59:59.999' AND org.[name] LIKE '') OR (org.spid IN (1) AND rfqet.newendtime IS NOT NULL AND rfqet.timeextID = (SELECT MAX(timeextID) FROM dbo.rfqtimext AS rte LEFT JOIN bidsubmissions AS bs ON rte.subid = bs.subid LEFT JOIN dbo.rfqpause AS rp ON rte.pauseid = rp.pauseid WHERE bs.rfqid = rfq.rfqid OR rp.rfqid = rfq.rfqid) AND rfqet.newendtime > DATEADD(hh, tz.offset, GETUTCDATE())) AND rfqet.newendtime < '12/31/3000 23:59:59.999' AND org.[name] LIKE '' OR rfq.ispaused = 1)

Answer Wiki

Thanks. We'll let you know when a new response is added.

The first thing that comes to mind is that SQL Server 2005 can’t use the statistics from SQL Server 2000 so it is not going to make the best decisions when it comes to selecting the execution plan to use.

Can you post the execution plans from both your dev server and production server?

I’d start by updating the stats of the tables on the dev server.

This is one of the reasons that it is recommended that you use the same version for your dev and production systems.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,295 pointsBadges:
    report
  • Noonway
    I agree with you regarding running the same version in both environments, but unfortunately most of our databases are SQL Server 2005 with only this particular one not being upgraded yet. Thanks for the tips so far. I do have a weekly maintenance plan running on that database and it has run since I loaded it. I did the following: * tested all indexes for fragmentation and did not find any * updated stats * cleared the proc cache Nothing sped up the query in the DEV environment. Logs are located on same hard disk (40GB free). The MDF is only 90MB. The LDF is 50MB. I really don't think disk I/O is the issue here but... Is there a way to test for that? I did run an execution plan on both environments and came up with some differences however I don't know enough about execution plans to know where to go from here. Maybe one of you could make a suggestion based on these... I also have statistics on the execution plan for each step if those would help let me know? PRODUCTION Plan 1 Scanning the entire clustered index . 2 Scanning the entire non-clustered index . 3 Scanning the entire non-clustered index . 4 Sorting the input from steps 3 . 5 Matching rows from two suitably sorted input tables exploiting their sort order. 6 Scanning the entire clustered index . 7 Scanning the entire non-clustered index . 8 Matching rows from two suitably sorted input tables exploiting their sort order. 9 Use each row from the top input step 5 to build a hash table, and each row from the bottom input step 8 to probe into the hash table, outputting all matching rows. 10 Use each row from the top input step 1 to build a hash table, and each row from the bottom input step 9 to probe into the hash table, outputting all matching rows. 11 Scanning an internal table of constants. 12 Restricting the set of rows based on predicate . 13 Scanning the entire clustered index . 14 Scanning the entire clustered index . 15 Scanning the entire clustered index . 16 Use each row from the top input step 14 to build a hash table, and each row from the bottom input step 15 to probe into the hash table, outputting all matching rows. 17 Use each row from the top input step 13 to build a hash table, and each row from the bottom input step 16 to probe into the hash table, outputting all matching rows. 18 Restricting the set of rows based on predicate . 19 Computing summary values for groups of rows in a suitably sorted stream. 20 Restricting the set of rows based on predicate . 21 Appending multiple input tables to form the output table. 22 For each row in the top (outer) input (step 10), scan the bottom (inner) input (step 21), and output matching rows. 23 Use each row from the top input step 22 to build a hash table, and each row from the bottom input step UNKNOWN to probe into the hash table, outputting all matching rows. 24 Rows were returned by the SELECT statement. DEV Plan 1 Scanning the entire clustered index . 2 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations. 3 Scanning the entire clustered index . 4 Scanning a particular range of rows from the clustered index . 5 For each row in the top (outer) input (step 3), scan the bottom (inner) input (step 4), and output matching rows. 6 Use each row from the top input step 2 to build a hash table, and each row from the bottom input step 5 to probe into the hash table, outputting all matching rows. 7 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations. 8 Scanning the entire clustered index . 9 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations. 10 Scanning the entire non-clustered index . 11 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations. 12 Use each row from the top input step 9 to build a hash table, and each row from the bottom input step 11 to probe into the hash table, outputting all matching rows. 13 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations. 14 Use each row from the top input step 7 to build a hash table, and each row from the bottom input step 13 to probe into the hash table, outputting all matching rows. 15 Scanning the entire clustered index . 16 Scanning the entire clustered index . 17 Scanning the entire non-clustered index . 18 Use each row from the top input step 16 to build a hash table, and each row from the bottom input step 17 to probe into the hash table, outputting all matching rows. 19 Use each row from the top input step 15 to build a hash table, and each row from the bottom input step 18 to probe into the hash table, outputting all matching rows. 20 Restricting the set of rows based on predicate . 21 Computing summary values for groups of rows in a suitably sorted stream. 22 Use each row from the top input step 21 to build a hash table, and each row from the bottom input step UNKNOWN to probe into the hash table, outputting all matching rows. 23 For each row in the top (outer) input (step 14), scan the bottom (inner) input (step 22), and output matching rows. 24 Restricting the set of rows based on predicate . 25 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations. 26 Sorting the input from steps 25 . 27 Rows were returned by the SELECT statement. This line jumps out at me from the DEV environment: Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations. Any suggestions?
    25 pointsBadges:
    report
  • Denny Cherry
    Its actually a lot easier if you can simply run the query in the SQL Server 2005 Management studio against both machines having it display the execution plan. Then right click on the execution plan and select save plan then zip the files up and stick them somewhere so I can download them and take a look at them.
    66,295 pointsBadges:
    report
  • Noonway
    We were actually able to get the query to run by changing around the sub-query to make it part of the main query via a left outer join and to use nolock hints throughout.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following