May 4, 2011 at 4:28 pm
Hi All,
We are using SQL Server 2005 Enterprise edition with SP4 installed on it. Actually its our reporting server.
Every month we will restore the Full backups from production db server to Reporting server. we use to run a query to pull the count of records after the restoration on reporting server. Its select query which uses VIEWS to fetch the data.
From last month we are facing one strange issue. After restoring the production server full backup on reporting server when we execute the select query its taking hours. I have performed rebuild index and update stats last time and re-ran the query, then the select query executed with in 11 sec's. But this month again we are facing the same problem and i have ran the rebuild and update stats job. but still the select query is taking long time to complete 🙁
Any inputs/ suggestion please to get rid of this issue..
Fast response is much appreciated.
Regards,
Jai
May 4, 2011 at 6:46 pm
There's not enough information for anyone to help you with anything other than a random guess. Please post the definitions for the underlying tables, including any indexes, the view definition and the execution plan for the running query. With that information, the tuning gurus will have enough to get started.
May 5, 2011 at 12:32 am
jai_422 (5/4/2011)
Hi All,We are using SQL Server 2005 Enterprise edition with SP4 installed on it. Actually its our reporting server.
Every month we will restore the Full backups from production db server to Reporting server. we use to run a query to pull the count of records after the restoration on reporting server. Its select query which uses VIEWS to fetch the data.
From last month we are facing one strange issue. After restoring the production server full backup on reporting server when we execute the select query its taking hours. I have performed rebuild index and update stats last time and re-ran the query, then the select query executed with in 11 sec's. But this month again we are facing the same problem and i have ran the rebuild and update stats job. but still the select query is taking long time to complete 🙁
Any inputs/ suggestion please to get rid of this issue..
Fast response is much appreciated.
Regards,
Jai
I suspect your data has probably reached the "tipping point" for that particular proc or procs and they'll likely need to be rewrittenl.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2011 at 12:40 am
There is definitely not the proper informaiton. Can you share one example of any query, the time taken, tables involved, data contained within them and result number of records etc ?
May 5, 2011 at 5:26 am
If updating the stats works, then are you updating stats in production? Is production on a different version of SQL Server? If so, it's possible that you're effectively performing an upgrade after which all statistics are invalid & must be updated. Without a lot more information, these are just guesses.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2011 at 7:30 am
You asked for a fast response, so I say the fastest way to figure out what is going on and correct it is to get a professional tuner connected to your system. You can hunt-and-peck on this forum for days and still not find the root cause.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply