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 |
|
stester
Starting Member
1 Post |
 Posted - 2008-02-18 : 09:20:44
|
| I would like to ask for help. We had no problems with dynamic queries in SQL 2000, which were very fast. But when we ran the same queries in SQL 2005, it was many times slower lasting several seconds. I guess it has something to do with creating execution plan, because when I run it second time, it is suddenly extremely fast. But after just a little change (like adding space character), the speed is very slow again. If it is caused by execution plan in SQL 2005, is it somehow possible to change its settings so that it will behave like in sql 2000?Thank you for answers! |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-02-18 : 09:30:21
|
| When did you upgraded to SQL server 2005. Did you upgraded SQL server statistics? After upgradation statistics are lost. I am not sure if this is because of lost statistics, but you can try upgrading them.---- Code to update statistics for all the tables in the databasesp_msforeachtable 'update statistics ?'---- EndRegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
 |
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-02-18 : 20:14:26
|
| Sounds like you may have a number of re-compiles happening. Have you taken a look at how many of these are happening? I would consider getting out of Dynamic SQL. It does offer a lot of flexibility to the end user however it can cause performance issues.Chris Shawwww.SQLonCall.com |
|
|
|
|
|
|
Different behavior in dynamic sql