March 15, 2016 at 9:12 pm
Hi,
I am facing a problem with querying data on the Log shipping secondary server.
The same select statement run on primary returns data in a few seconds while on secondary it runs for 4 long minutes.
I have made sure, SQL settings, MAXDOP, memory, CPU are all the same on both servers.
Have compared the actual execution plans of both, they are the same. Indices for the table in question are in place.
What else can be a reason for a slow secondary server?
A few questions that I have in mind are,are the secondary stats stale? Does update stats on the primary also update the stats for the secondary DB?
Regards,
SQL Learner
March 15, 2016 at 9:38 pm
sqllearner44 (3/15/2016)
Hi,I am facing a problem with querying data on the Log shipping secondary server.
The same select statement run on primary returns data in a few seconds while on secondary it runs for 4 long minutes.
I have made sure, SQL settings, MAXDOP, memory, CPU are all the same on both servers.
Have compared the actual execution plans of both, they are the same. Indices for the table in question are in place.
What else can be a reason for a slow secondary server?
A few questions that I have in mind are,are the secondary stats stale? Does update stats on the primary also update the stats for the secondary DB?
Regards,
SQL Learner
What happens if you run the query more than once ?
Try adding the following just before your query...
set statistics io on
This will show you how much IO the query is doing - maybe the physical IO is higher on one server
As for statistics - assuming that the secondary is up to date, the statistics will be exactly the same on both servers. Log shipping brings everything from the primary to the secondary database - practically, log shipping is equivalent to doing a database backup and then restore it and all of the subsequent transaction logs to the secondary.
March 16, 2016 at 5:38 am
Where is the secondary server located and what are the wait stats during query execution?
March 21, 2016 at 11:18 pm
running the query more than once is a tad bit faster.
I set the stats on and compared it with the primary stats, look almost the same.
March 22, 2016 at 3:40 am
there are a few cx packet waits.
I have tried many options for max dop.
The server has 8 physical processors with 2 logical cores, so per the M/S recomendaton , MAXDOP was set to two. have changed to different values and tested, not seeing any benefits
April 11, 2016 at 1:43 am
Any inputs please?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply