Re: ADP vs QA Queries

We reindex once a week and update statistics every night, my be could
reindex some more, but the question is how is possible some query runs so
different in Access and QA enviroment, at the same time?
I suppose they should use the some query plan, or not?
Thanks again
Ruggiero Lauria




"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ha
scritto nel messaggio news:%23keujPipIHA.3568@xxxxxxxxxxxxxxxxxxxxxxx
The most likely explanation is a use of bad query plans because the
statistics are out of date or the first times the SP are called - and
compiled -, they are with atypical parameters. The first step would be to
update the statistics using the sp_updatestats stored procedure (or use
UPDATE STATISTICS is you want to work on a more detailed level) and clean
the caches after that:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

After that, if you still have problems, you can think about reindexing
and - very important -, you can
take some precautions about the use of atypical parameters and
recompilations in order to eliminate the reuse of bad query plans. See:
http://sqlknowledge.com/index.php?op...d=65&Itemid=41

Reindexing:
http://blog.sqlauthority.com/2007/01...ics-on-tables/

Good article on the use of the With Recompile option and of intermediary
variables in order to deactivate the parameters sniffing from SQL-Server
(the use of intermediary variables is probably better than to use the With
Recompile option because recompiling big procedures take time):
http://blogs.msdn.com/khen1234/archi...02/424228.aspx . (It's useless to
use both technics at the same time).

If you are with SQL-Server 2005, you can also play with the OPTIMIZE FOR
option instead of deactivating the parameters sniffing or using the With
Recompile option; see:
http://www.sql-server-performance.co...guides_p1.aspx

and: http://www.sql-server-performance.co..._hints_p1.aspx

Finally, here's a good reference on recompilation:
http://www.microsoft.com/technet/pro...05/recomp.mspx

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Ruggiero Lauria" <ruggiero.lauria@xxxxxxxxxxx> wrote in message
news:uXKNUFipIHA.4672@xxxxxxxxxxxxxxxxxxxxxxx
I have really a strange issue.
Same Query "select......" runned via ADP goes in timeout, from profiler
i saw it took about 30000 ms, runned under Query Analyzer took 300 ms!
Any Idea, I have a serious performace issue.
thanks
Ruggiero Lauria







.

Relevant Pages

  • Performance degraded after DBCC DBReindex
    ... I have been tried to Reindex all tables' indexs as I expected this can ... improve the performance of query e.g. DBCC DBReindex ('dbo.', ... update statistics after the reindex? ...
    (microsoft.public.sqlserver.server)
  • Re: SEQUENTIAL SCANS
    ... table A has another individual index on column t. ... Above query correctly takes INDEX PATH. ... you did run update statistics low for table B, ...
    (comp.databases.informix)
  • Re: SEQUENTIAL SCANS
    ... Above query correctly takes INDEX PATH. ... It's perplexing why would optimizer behave like this. ... you did run update statistics low for table B, ...
    (comp.databases.informix)
  • Re: Update Statistics and poor performance
    ... Is it possible that most sql's run efficiently during update stats ... 9.30UC2 is the last version released before the update statistics code was rewritten in 9.30xC3. ... For your server version you can enable this updated algorithm in dostats by adding the -o flag which inverts the server version detection logic. ... particularly odd looking query. ...
    (comp.databases.informix)
  • Update Statistics and poor performance
    ... Is it possible that most sql's run efficiently during update stats ... UPDATE STATISTICS MEDIUM FOR TABLE pin_mast DISTRIBUTIONS ONLY; ... UPDATE STATISTICS HIGH FOR TABLE pin_mast DISTRIBUTIONS ... When I ran the query manually through dbaccess it returned ok ...
    (comp.databases.informix)
Quantcast