 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Query running slow in production
Hi, First here is my setup. I have a two node active/passive SQL 2005 Enterprise 64bit cluster. There is a database (DB1) that is set up for snapshot replication to another SQL 2005 64 bit server. The replication runs once a night around 2:00am. I also have a query with about 5 joins that should run on DB1 in production but it runs more than two hours and then fails with semaphore time out error message. The same query runs on the replicated DB1 at the subscriber with out any issues (completes within 35 seconds). My active/passive cluster is powerful enough to support the load (each server is a 4 quad core box with 32GB or ram attached to external array). I don't see any hardware errors in the even viewer and I don't see any locks by running sp_who2 during the run on that query. Nothing else runs on the cluster. Why would the same query running against essentially the same database (except one is on the publisher and one is on the subscriber) behave differently? Any suggestions on where I should look first in production? T. > Why would the same query running against essentially the same database > (except one is on the publisher and one is on the subscriber) behave > differently? Compare the query execution plans on the 2 servers. I see that you are using snapshot replication, which means the replicated database will have updated stats following the snap and influence the execution plan. Perhaps stats on the publisher are due for an update.  SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > Hi, > [quoted text clipped - 18 lines] > Any suggestions on where I should look first in production? > T. On Jul 2, 8:36 am, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> wrote: > > Why would the same query running against essentially the same database > > (except one is on the publisher and one is on the subscriber) behave [quoted text clipped - 33 lines] > > Any suggestions on where I should look first in production? > > T. But how can I check execution plan on the publisher if the query never completes and times out? Is there another way? T. > But how can I check execution plan on the publisher if the query never > completes and times out? Is there another way? > > T. Look at the ESTIMATED query plan - third button to the right of "Execute" on the default Management Studio toolbar... To add to Tracy's response, you can also display the estimated plan by pressing ctrl-L in the SSMS query window. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > On Jul 2, 8:36 am, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> > wrote: [quoted text clipped - 41 lines] > > T. One more option is to use: SET SHOWPLAN_XML ON This will return the execution plan instead of executing the query. HTH, Plamen Ratchev http://www.SQLStudio.com Check out update statistics (or sp_updatestats) in BOL. Just run it and see if it helps. Also, can you post the exact message you get when the query fails (something about semaphore). Are the two servers configured EXACTLY the same (sp_configure, with show advanced options on). BTW, how much ram is sql server currently taking up on the box? SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > On Jul 2, 8:36 am, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> > wrote: [quoted text clipped - 41 lines] > > T. On Jul 2, 8:36 am, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> wrote: > > Why would the same query running against essentially the same database > > (except one is on the publisher and one is on the subscriber) behave [quoted text clipped - 35 lines] > > - Show quoted text - updated stats - same thing. Query runs a long time. > updated stats - same thing. Query runs a long time. Did you compare the execution plans? SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ On Jul 2, 8:36 am, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> wrote: > > Why would the same query running against essentially the same database > > (except one is on the publisher and one is on the subscriber) behave [quoted text clipped - 39 lines] > > - Show quoted text - updated stats - same thing. Query runs a long time. > Hi, > [quoted text clipped - 18 lines] > Any suggestions on where I should look first in production? > T. It could be caused by something trivial such as waiting on lock. > > Hi, > [quoted text clipped - 22 lines] > > - Show quoted text - I have WITH (NOLOCK) option on every joint and I don't see anything in sp_who2 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.