Query Fast in Lab....Slow in Production

Query Fast in Lab....Slow in Production

Post by Jeff Delane » Wed, 25 Apr 2001 21:50:32



I have a query that does a 9 table join and yields about 10,000 records.
This query runs on all of our test servers in about 3 seconds. Our test
servers all run copies of the live database.

However, in the production environment, this query sometimes takes up to 20
minutes to complete! Yes, there are other things going on within the
production environment, but we certainly stress our test boxes a lot harder
than production takes on.

I recognize that SQL Server dynamically adjusts itself as the demands on its
resources change from moment to moment. Having stated this, what can we do
to get our confidence up that SQL server will perform the same query
consistently in production? What can we do to "guide" SQL Server down the
same execution path each time?

thanks
jeff

 
 
 

Query Fast in Lab....Slow in Production

Post by Dan Guzma » Wed, 25 Apr 2001 22:21:11


What version/SP of SQL Server are you running?

Compare the execution plan on the two systems.  If production shows a
parallel plan, you may want to try the MAXDOP 1 option to see if performance
improves.  SQL 7 SP3 contains fixes to address some parallelism issues.

Another possibility is that stats need to be updated.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:> I have a query that does a 9 table join and yields about 10,000 records.
> This query runs on all of our test servers in about 3 seconds. Our test
> servers all run copies of the live database.

> However, in the production environment, this query sometimes takes up to
20
> minutes to complete! Yes, there are other things going on within the
> production environment, but we certainly stress our test boxes a lot
harder
> than production takes on.

> I recognize that SQL Server dynamically adjusts itself as the demands on
its
> resources change from moment to moment. Having stated this, what can we do
> to get our confidence up that SQL server will perform the same query
> consistently in production? What can we do to "guide" SQL Server down the
> same execution path each time?

> thanks
> jeff


 
 
 

Query Fast in Lab....Slow in Production

Post by Jim » Wed, 25 Apr 2001 22:16:02


First of all compare no. of records in test and production server because it
is more likely that test server has few test data where as production serer
might be storing whole corporate history in millions of records,  compare
execution plan generated by sql server on both servers, if they do not match
then review your index strategy again, it is never a good idea to force sql
server to use a particular index and it is always better to use 'Perform
Index Analysis' option available in query analyzer to figure out what
indexes are required, this way we can be sure that sql server will use that
index.


Quote:> I have a query that does a 9 table join and yields about 10,000 records.
> This query runs on all of our test servers in about 3 seconds. Our test
> servers all run copies of the live database.

> However, in the production environment, this query sometimes takes up to
20
> minutes to complete! Yes, there are other things going on within the
> production environment, but we certainly stress our test boxes a lot
harder
> than production takes on.

> I recognize that SQL Server dynamically adjusts itself as the demands on
its
> resources change from moment to moment. Having stated this, what can we do
> to get our confidence up that SQL server will perform the same query
> consistently in production? What can we do to "guide" SQL Server down the
> same execution path each time?

> thanks
> jeff

 
 
 

1. Slow query execution through JDBC - runs fast in Query Analyzer

Hi Martin,

Was that trace done while SelectMethod was Cursor or Direct? If
SelectMethod was Cursor, what is in a Profiler trace when the SelectMethod
is set to Direct with executeQuery? Can you look at (/add) the Execution
Plan Event Class (in a Profiler trace), along with the Duration column, to
compare a Query Analyzer run with an executeQuery run? If the plans are
different and the parameters (if any) are identical, then I will find a
JDBC person to help us investigate why this is happening.  If the plans and
parameter are identical, then it appears we have network latency (unless
the Duration accounts for most of the time). Can you test using the latest
SQL Server Service Pack? It may turn out that I will need a replayable
repro, in which case we can work offline (and of course publish the useful
results back on the newsgroup when done). Just remove "online." from my
alias.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

2. Programming DTS objects

3. SQL 7.0 - Query running slow (10 - 12 min) on production server

4. Inserting new Field into a Table

5. Query Statment is Fast But Stored Procedure is Slow

6. Distribution Agent Error, HELP PLEASE!!!!!!!!

7. Q: slow/fast query

8. fusion for wingz/sybase

9. A fast query became very slow

10. slow queries on a novel 5.0 server, fast on an NT server and on win9x

11. SQL select query slow in ASP, but fast in VB

12. Stored Proc - SLOW, Query Analyzer - FAST

13. Changing Query Plans - Slow one time/Fast Another Time