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 |
|
spirit1
Cybernetic Yak Master
11752 Posts |
 Posted - 2007-02-21 : 05:05:37
|
We've had a bit bizzare SQL thing to solve.we have 2 identical databases on the same serveran original and a restored backupeverything worked nicely on originalbut one query was taking 13 minutes to create an exec plan on restored one.when the plan was created the query executed in 2 secondsthe cached plan lasted for 5 query executionsthen it went to 13 minutes again on the 6th run.this was reproducible like clockwork.no data was inserted or updated in the process.the query was pretty simple in the form ofselect ...from t1 and 24 left joins on different tableswhere t1.id = 1 and t1.someOtherId <> 5order by 21 id columns from different tables the solution in the end was to drop all statistics on all of the tables that were in the query.and then running aselect * from EachTableInAllTablesInJoin3 times for each tableafter that all is well...is this some weird statistics issue???Go with the flow & have fun! Else fight the flow  blog: http://weblogs.sqlteam.com/mladenp |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 05:11:06
|
| Auto Update Stats doing something goofy perhaps? |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-21 : 05:24:47
|
| i have no idea...we do have it turned on.Go with the flow & have fun! Else fight the flow blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 06:17:08
|
| There is some flag / date somewhere that tells you when a statistic was last updated."this was reproducible like clockwork."... might be worth checking that value before / after your N'th iteration that caused it to change to the Slow Lane!Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-21 : 07:16:46
|
care to specify that i bit more accuratly?  Go with the flow & have fun! Else fight the flow blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 11:11:57
|
| This bit:"There is some flag / date somewhere that tells you when a statistic was last updated."??'Coz if so if I could have remembered where that was stored I would happily have included it!I do remember trawling for it once before because we thought that AutoStats was firing and crippling performance for 30 minutes at a time (but it turned out to be the Database Extension instead)Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-21 : 11:25:23
|
| yup that bit i hoped that other guys and gals also had some ideas on this...Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 11:32:30
|
http://www.sql-server-performance.com/statistics.aspquote:
DBCC SHOW_STATISTICS (table_name , index_name)This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining
Might that do it?Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-21 : 11:44:04
|
| thanx.i'll play with this when i have more time on my hands...Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
SQL exec plan generating