Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Stored Procedures slower after upgrade to SQL 7

Posted on 1999-07-06
Medium Priority
Last Modified: 2010-03-19
This weekend we upgraded a 150+ user SQL 6.5 system to 7.  There are multiple Stored Procedures that are running considerably slower on the system since the upgrade.  Is there anything to document this problem?  What can be done to fix it?
Question by:stevenvanooyen
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

gmoriak earned 300 total points
ID: 1096178
sp_recompile table_name
where table_name is the name of a commonly used table.  Run it for each of your commonly used tables.  Any SP's using the table will be recompiled and possibly quicker next time they run.

If that doesn't work, ISQL in version 7 has a new feature called Index Analysis.  Run your query using this and it may find an index you should add.

I had a similar problem upgrading from 4.2 to 6.0 years ago.  A SP that had taken 15 seconds began to take 5 minutes to run.  It the process of improving the SQL engine the compiler no longer handler my query syntax very well.  I rewrote the SQL and was able to fix the problem.  I suspect that this is what is happening to you now.  If you need help, post the SQL and I may be able to help optimize.

Author Comment

ID: 1096179
I think you are right on track.  We discovered that an index (3-field PK) was returning very slow.  My guess is that it wasn't being used.  We rebuilt that key on that table and checked all of the others.  That made a huge difference.  

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

670 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