|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Performance difference between SQL 6.5 and SQL 2000 serverHello,
I've migrated my database from SQL 6.5 to SQL 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 - sometimes as high as twice or thrice the time taken in 6.5 environment. The client once mentioned to us that cursors are slow in SQL 2000 server. We need a quick solution for this problem. A vast majority of the procedures create 4-5 temporary tables and do an insert or update on the temporart tables. Then there are some select statements from the temporary tables. I've rebuilt the indexes, updated the statistics, created tempdb device on a separate drive. But no use. Any help will be greatly appreciated. Ravi,
I would look at the settings for the database as well. If you have Auto Close, Auto Shrink, Auto Create Stats and other settings checked that could be some of your problem. Chris <Ravi.Meenak***@gmail.com> wrote in message Show quote news:1138108502.952649.250460@z14g2000cwz.googlegroups.com... > Hello, > > I've migrated my database from SQL 6.5 to SQL 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 - sometimes > as high as twice or thrice the time taken in 6.5 environment. The > client once mentioned to us that cursors are slow in SQL 2000 server. > > > We need a quick solution for this problem. A vast majority of the > procedures create 4-5 temporary tables and do an insert or update on > the temporart tables. Then there are some select statements from the > temporary tables. > > I've rebuilt the indexes, updated the statistics, created tempdb device > on a separate drive. But no use. > > Any help will be greatly appreciated. > Also ensure that in SQL 2000 Server:
Size of the Tempdb is large enough Tempdb it is set to Autogrow by considerable size There is sufficient Free Space in your application's database Your application's DB is set to Autogrow by considerable size Please let us know if this helped you. Show quote "Chris Wood" wrote: > Ravi, > > I would look at the settings for the database as well. If you have Auto > Close, Auto Shrink, Auto Create Stats and other settings checked that could > be some of your problem. > > Chris > > <Ravi.Meenak***@gmail.com> wrote in message > news:1138108502.952649.250460@z14g2000cwz.googlegroups.com... > > Hello, > > > > I've migrated my database from SQL 6.5 to SQL 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 - sometimes > > as high as twice or thrice the time taken in 6.5 environment. The > > client once mentioned to us that cursors are slow in SQL 2000 server. > > > > > > We need a quick solution for this problem. A vast majority of the > > procedures create 4-5 temporary tables and do an insert or update on > > the temporart tables. Then there are some select statements from the > > temporary tables. > > > > I've rebuilt the indexes, updated the statistics, created tempdb device > > on a separate drive. But no use. > > > > Any help will be greatly appreciated. > > > > > The settings of the tempdb is same in 6.5 and 2000. There's enough
space in the DB. We've even moved tempdb device on a different drive. With all these, still the performance is very bad in 2000. The question is, with all the other factors and configuration options remaining the same between 6.5 and 2000, why is there such a big difference in performance? When we went from 6.5 to 7.0 we found that SQL needed bigger and faster
hardware. SQL 7's requirements are higher than 6.5. Did you migrate in place or upgrade to a different server? SQL2000 is better than SQL 7 but has the same sort of hardware requirements. Chris <Ravi.Meenak***@gmail.com> wrote in message Show quote news:1138196134.975962.278020@g43g2000cwa.googlegroups.com... > The settings of the tempdb is same in 6.5 and 2000. There's enough > space in the DB. We've even moved tempdb device on a different drive. > With all these, still the performance is very bad in 2000. The question > is, with all the other factors and configuration options remaining the > same between 6.5 and 2000, why is there such a big difference in > performance? > We've migrated to a different server, but the hardware configuration of
6.5 server and 2000 server are the same. Does this mean that SQL 2000 server needs better hardware than 6.5? What are the hardware specs for the 2 servers?
Chris <Ravi.Meenak***@gmail.com> wrote in message Show quote news:1138343333.576107.101060@o13g2000cwo.googlegroups.com... > We've migrated to a different server, but the hardware configuration of > 6.5 server and 2000 server are the same. Does this mean that SQL 2000 > server needs better hardware than 6.5? > Can you post an example? Include the Inout & Output tables definitions
if you can. -- Barry Young I got a few pointers, but not sure how to take this forward.
In all the slow procesures, 5-6 temporary tables are created. A lot of insert, update and select operations are happening on the temporary tables. When I ran the profiler to capture the SP:Recompile event I found that it's happening quite frequently. I read an article in MS site that for temporary tables SP is recompiled for every 6 rows of updation to temp tables. Moreover, the SP is very big and internally calls 3-4 procedures in a cursor loop. I believe all of this will warrant re-compilation. My question is: 1. Is there a way to turn off re-compilation of procedures? 2. Is there a way to increase the threshold for tempdb data updation - from 6 rows to 500 or more? It appears as though the procedure invocation is taking more time in 2000 than 6.5 Could it be because of recompilation? Please see the following articles: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586 http://support.microsoft.com/default.aspx?scid=kb;EN-US;q195565 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp Any help / suggestions would be greatly appreciated. If recompiles caused by Temporary Tables is in fact your problem, then
the last article that you reference has a number of suggestions to address it: -- Use Table vairables instead (my preference) -- Use Temporary tables with [keep plan] -- Use Temporary tables with [keepfixed plan] -- Barry Young  |
|||||||||||||||||||||||
Other interesting topics