Re: Performance

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Have you checked the index fragmentation on the utilized indexes?

Best,
Nils Loeber

"Richard Douglass" <RDouglass@xxxxxxxxxxx> schrieb im Newsbeitrag
news:uAPu7mPVGHA.5724@xxxxxxxxxxxxxxxxxxxxxxx
I am having a problem with a customer. The app we are supporting was
developed using Client-Side queries that select * from views. Those views
are built using other views. (yes, layers of views) The performance isnt
all too bad, about 300 milliseconds per request. The optimizer loaded up
the underlying tables with tons of its own indexes and statistics. They
dont seem to help much when the queries are run isolated in QA.

I picked several tables and removed all optimizer hints and statistics,
leaving just the indexes from the app and the few we created for testing.
The performance was stable at about 250 milliseconds on average. No big
deal. Stayed this way for almost 2 weeks.

Last week the performance on the queries climbed to 1500 milliseconds for
no known reason. The CPU and DURATION are both approx 1500 all day long.
No code changes have been made, no settings changed, nothing.

Any ideas why I would see such a drop in performance?



.

Relevant Pages

  • Re: Performance
    ... Then the first time you called the query you passed a non-typical value that ... the underlying tables with tons of its own indexes and statistics. ... dont seem to help much when the queries are run isolated in QA. ... The performance was stable at about 250 milliseconds on average. ...
    (microsoft.public.sqlserver.clients)
  • Performance
    ... dont seem to help much when the queries are run isolated in QA. ... leaving just the indexes from the app and the few we created for testing. ... The performance was stable at about 250 milliseconds on average. ... code changes have been made, no settings changed, nothing. ...
    (microsoft.public.sqlserver.clients)
  • Re: SQL Server (?) performance issues
    ... have your DBA run a trace of any queries taking over 1000 milliseconds and ... have him dump the results to a table that you have access too. ... over 75,000 Milliseconds in Production. ...
    (microsoft.public.sqlserver.server)
  • Re: Reasons for query execution time difference ???
    ... These queries look like it is updating statistics. ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... I restored the database from a backup and> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the> indexes & statistics are up to date... ...
    (microsoft.public.sqlserver.server)
  • Re: Auto update statistics
    ... We recently solved a problem that was> sucking up proccessor cycles and limited the number of clients on a> database server. ... It is highly likely that there> are numerous inefficient queries. ... On view was that having this on would cause spikes in>>> utilization whenever the auto update kicks in during peak load time,>>> and so this option should be turned off and update statistics should>>> be run via a job during off times. ... running a bunch of inefficient queries will put more>> stress on the server than allowing SQL Server to update statistics as it ...
    (microsoft.public.sqlserver.server)