Hi
I've upgraded a server from SQL 2005 to SQL 2008 but the database runs slower when running certain Stored Procedures especially against records which contain more data than others.
Its been suggested that I run a basic reindex to see if this resolves.
Can someone take a log at the screens-hot and advise if this will remove any data from my database - if so then this isn't the right thing to do.
Thanks James
p.s I will now attach a screen-shot if I can as not done that before using this Forum
the maintenance plan is fine (though I don;t use shrink database on everything). If the proc is running slower, look at the execution plan. There may be missing indexes/statistics
Hi gbritton
In the end I found a lot of duplicate data which explains why a Stored Proc was slow.
Theres no need for me to follow the maintenance plan now. Saying that we have engineers who set these servers up - do you think it would be a good idea for them to basically set this up to run say once a month?
Cheers
James
how often you run a plan depends on the volatility of the data. The more stable, the less frequently you need to run the plan. There's no set rule. Once a week is fairly typical. I really question the shrink operation though
Uncheck the shrink database option! Shrinks should never be done on a schedule and rarely should be done anyway. Shrinks cause massive fragmentation and then a performance issue when the file needs to grow.
Only shrink the database when you've deleted a large amount of data and know that the space is not needed for a long time. Or another example where a shrink is okay is when a runaway query was run and caused the log to grow to a huge size that is not needed.
Also - pick either Reorganize or Reindex Indexes - not both.
Reorganize indexes is an online operation whereas Reindex will be offline for non-Enterprise Editions of SQL Server. However, if you have the maintenance window a reindex will be better (and shorter).
Hi Jeff
I've just been reading up in it. I have a couple of big time windows this weekend so I'm going to do a Check Database Integrity first, then Rebuild Database, then Clean Up History and then Maintenance Cleanup Task.
As I initially use the Wizard, I'm going to choose 'Separate schedules for each task'. Do you know if the Check Database Integrity were to finds errors, will it abort the schedule to run the next steps? I think it would if I chose 'Single Schedule for the entire plan or no schedule'. With my plan, I don't think it will abort which is what I want to happen as I need to get the databases speeded up as much as I can this weekend.
Cheers
James
If you use separate schedules - they will not stop the other processes from running. Just a suggestion - but using the wizard is confusing (to me).
Instead of using the wizard, just create a new maintenance plan. Then you can create a plan that meets your exact requirements, including multiple sub-plans with separate schedules.
Regardless of how you schedule and build your maintenance (Maintenance Plans vs Agent Jobs) - you need to consider what steps should be taken and when the next step should process.
A generic outline would be:
History Cleanup (separate plan or job) - perform history cleanup for maintenance plans, agent jobs, backup history. This can be run daily, weekly or monthly depending on how you want to manage your history. Set it to keep enough history to insure you can trend appropriately for your organization.
System Database Maintenance (plan or agent job). This will have 3 components - database integrity check for all system databases, backup system databases, remove old backup files. The backup step will only run upon successful completion of the integrity checks.
User Database Maintenance (plan or agent job). This will have 3 (or more) components - with the integrity check, rebuild indexes, update statistics (column stats only), backup and remove old backup files. The dependencies on this will be success after the integrity check - completion after index rebuilds/update statistics - and success between the backup and remove backup files.
You can move the rebuild and update statistics to a separate schedule to be run weekly depending on your maintenance window, the size of the databases, etc... Note: for larger databases you want to implement a process that rebuilds indexes based on level of fragmentation instead of rebuilding all - and use a separate task for updating statistics.
User Transaction Log Maintenance (plan or agent job). This will backup the transaction logs for all user databases at the frequency you need for each database. Multiple sub-plans/jobs will be created depending on each databases requirements.
This basic outline can then be adjusted for each system - as needed to support the business. For some systems, adding differential backups - switching to weekly full, daily differential for example - will be fairly easy to plug in...
The goal is to insure your databases are backed up...but only if the integrity checks are good. All other steps between the integrity check and the backup should be set for completion - that way if they fail your backups will still execute.
Hi Jeff
I'm just using the Maintenance Wizard for now until I increase my knowledge. My next stop is to learn about Maintenance Plans and include what you say wherever I can.
When you refer to backups are you referring to backup of the database to somewhere else on the hard-disks? My organisation are just doing nightly backups to tape at the moment but definitely backing up the Transaction Log more regularly seems like an excellent idea - as long as it doesn't slow down the users on the system
Both of these are really the same as using maintenance plans. The issue with all of these methods is understanding what maintenance needs to be performed on each system.
It doesn't matter what utility you use to perform the maintenance...you could implement SQL Litespeed from Quest and do all maintenance from their maintenance plans - or Redgate's utilities, or Idera's, or home grown procedures and agent jobs.
The outline I provided above is not specific to using SQL Server Maintenance Plans...it is generic for any utility you use for maintenance. You need to determine what maintenance needs to be performed...then determine what works best for you and your organization.
