May 18, 2012 at 3:47 am
my stored procedures are running very slow, probably using wrong execution plans,
there are 100s of tables
if i take backup and then delete the database will it improve perfomance?
since the table will reindex
is it correct?
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 18, 2012 at 4:41 am
sukhoi971,
If you just want to rebuild indexes then you could use this script. This is the simplest version to do this. There are few others complex script which are based on index fragmentation and based on certain level of fragmentation, doing reorganize OR rebuilt.
-- Please change the schema and table name according to your objects.
select 'ALTER INDEX ALL ON '+table_schema+'.'+table_name +' REBUILD;'
from
information_schema.tables
where
table_type = 'BASE TABLE'
and
table_schema in ('dbo','scham1')
and
table_name like 'tbl_%'
You could also use profiler to identify sps with longest duration, to narrow down the problem.
Backup and Dropping database can never be a option for production database, but depends if you could afford this.
So what do you think?
Cheers.
May 18, 2012 at 4:56 am
sukhoi971 (5/18/2012)
my stored procedures are running very slow, probably using wrong execution plans,there are 100s of tables
if i take backup and then delete the database will it improve perfomance?
since the table will reindex
is it correct?
No. It's not correct. Database backup/restore does not reindex. So, don't try that.
Please update the statistics and recompile the stored procedures. It might fix wrong execution plans.
May 18, 2012 at 4:58 am
Backup and restore does not rebuild indexes. The only way it may help is by reducing physical fragmentation, but, conversely, it may also increase it. My advice would be not to do it. If you suspect physical fragmentation, use OS level tools to diagnose and fix it. Otherwise, concentrate on indexes and statistics first of all, and make sure your code is optimal. You'll also want to monitor disk, network, processor and memory in case they are causing a bottleneck.
John
May 18, 2012 at 5:42 am
sukhoi971 (5/18/2012)
my stored procedures are running very slow, probably using wrong execution plans,there are 100s of tables
if i take backup and then delete the database will it improve perfomance?
since the table will reindex
is it correct?
Good gosh no!
The best thing you'd get out of this is a complete recompile on all stored procedures, but you can get that by running DBCC freeproccache(). Although, I don't recommend running that in a production environment. Instead, understand why you're getting poor performance. What's the cause?
"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 18, 2012 at 6:27 am
No, not at all, that's more likely to degrade performance as it flushes the plan and data cache and will require them to be repopulated entirely. That restore will result in downtime during the restore and higher CPU and IO afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2012 at 5:44 pm
sukhoi971 (5/18/2012)
my stored procedures are running very slow...
Were they ever actually running fast with the amount of data you currently have?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply