Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-09 : 09:37:51
|
| Meenakshi writes "Hello,We’ve migrated a database from SQL 6.5 server to SQL 2000 server on Windows 2000 server. After migration, some stored procedures are slower in SQL 2000 server than SQL 6.5 server. The difference in time is very high �Esometimes as high as 7 or 8 times the time taken in 6.5 environment. The procedures create temp tables and use cursors. The application calls one SP, which in turn calls many other SPs, often in a loop. Also the DB size is 80 GB. The server and DB settings are same on both the servers. The h/w configuration is also the same.We need a quick solution for this problem.Any help / suggestions to improve the performance is greatly welcome.Thanks!" |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-02-09 : 12:43:52
|
| The 6.5 queries probably don't use ANSI joins. ANSI joins would be faster. 6.5 also allowed for DBCC PINTABLE which is not in 7.0 or 2000. You're SP's & indexes are probably not written to get the best that 2000 has to offer. I'd take a look at the SP and see if they can be rewritten to get rid of the cursor. I'll use a quote from NR for this one "Cursors are for people who don't know SQL". Have fun,Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
 |
|
|
|
|
|
Poor performance of some stored procedures in SQL 2000