February 7, 2014 at 12:08 pm
Why is it not good practice to use Wizard when creating a maintenance plan? Most DBA's recommends creating it with the script so you have more control on what can be done. My question is that if I have to use a Wizard for one of our Production environment, What much harmful can it be?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 7, 2014 at 12:14 pm
the answer is the maintenance plan is "good enough";
the maintenance plan is good, but not great; that's the sole difference.
the maintenance plan rebuilds everything, which is often not necessary; an example is you undoubtedly have a suite of static lookup tables that have values like the list of all cities/counties or states, or statuses.
if that table never changes, why rebuild it's index?
The recommendations for better scripts come specifically from that experience; no need to waste time and CPU on something that doesn't need to be done.
scripts like those at http://ola.hallengren.com/ are a peer reviewed and recommended upgrade from the data maintenance.
Lowell
February 7, 2014 at 12:18 pm
something to help you visualize:
anything with rows modified<5% don't really need to be touched, anything with rows modified < 30 % can take advantage of the REORGANIZE command; anything with more than that would really need to be rebuilt.
anything with less than say, 1000 rows or so wouldn't have any benefit of being reorganized or rebuilt, regardless of the # of rows modified; the rows modified would probably be the same after the process completed
the main plan would hammer all of them with a rebuild
SELECT *
FROM (
SELECT DISTINCT
DB_NAME() AS [Database],
S.name AS [Schema Name],
T.name AS [Table Name],
I.rowmodctr AS [Rows Modified],
P.rows AS [Total Rows],
CASE
WHEN I.rowmodctr > P.rows
THEN 100
ELSE CONVERT(decimal(8,2),((I.rowmodctr * 1.0) / P.rows * 1.) * 100.0)
END AS [Percent Modified]
FROM
sys.partitions P
INNER JOIN sys.tables T ON P.object_Id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
INNER JOIN sysindexes I ON P.object_id = I.id
WHERE P.index_id in (0,1)
AND I.rowmodctr > 0
) X
--WHERE [Rows Modified] > 1000
ORDER BY [Rows Modified] DESC
Lowell
February 7, 2014 at 1:42 pm
A basic maintenance plan is just terrible for large databases, because it rebuilds every table and every index regardless. You just can't waste that much I/O in a critical environment.
No real DBA would ever use a maintenance plan; I've never set one up, although I have had to maintain some inherited ones. It'd be like someone wanting to be taken seriously as an artist using a paint-by-numbers kit, or a professional BMX racer using training wheels on the course. It just is not done.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 7, 2014 at 1:52 pm
New Born DBA (2/7/2014)
Why is it not good practice to use Wizard when creating a maintenance plan? Most DBA's recommends creating it with the script so you have more control on what can be done. My question is that if I have to use a Wizard for one of our Production environment, What much harmful can it be?
To add to the above, the very short answer is efficiency.
The reasons why come down to "How big is your system?" Tools like the wizard are in place so your average user can make sure they don't have horrible problems in their systems because their data is small and they're not really beating on it. These are places that don't need a DBA. Computer hardware will outpace any bad design, inefficient practices, or poor setup they may have. They'll never see a return from their DBA that makes enough of a difference.
Once you get to a large enough system that this becomes important, you're talking hours of maintenance time. Sometimes nightly. Every 15 minutes you can shave off that sledgehammer of a wizard using a tool with finesse is 15 minutes you get back for overnight processing, cube building, letting the users on earlier, other maintenance... you get the idea.
Time is finite. It's even worse when you start to consider you may only have an hour a day for maintenance tasks. A DBA isn't needed on a system with a few gigs sitting on the local SATA drive. Not really, except maybe for setup. When you get to where a DBA is truly needed, these skills and techniques are powerful and important, if for no other reason than to increase your 'breathing room' inside your maintenance periods.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 7, 2014 at 2:02 pm
New Born DBA (2/7/2014)
Why is it not good practice to use Wizard when creating a maintenance plan? Most DBA's recommends creating it with the script so you have more control on what can be done. My question is that if I have to use a Wizard for one of our Production environment, What much harmful can it be?
Imagine an 8TB database that contains a table that is approaching 1.5Tb.
If you just use the GUI to create a maintenance plan, you could run into performance problems and affect production operations.
Consider that many people just enable everything in that Wizard. So they do a rebuild, then a reorg (both of which touch every table), then they update stats, then do a full backup then do a tlog backup, run a checkdb and conclude with shrinking the database. I have seen this exact setup on more than a few hundred servers.
That is a lot of stuff done on this 8TB database in a single session that just left the database worse off for wear. Sure they do a log backup but only once a day. Sure the defrag indexes but they immediately turn around and shrink the database and fragment all of their indexes back up to 90% or worse.
If using the Maintenance Plan GUI to create a plan, try to use some prejudice and be very careful. Maintenance is something that should be carefully thought out and planned then run as efficiently as possible.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2014 at 2:07 pm
ScottPletcher (2/7/2014)
No real DBA would ever use a maintenance plan;
thats a bit over the top surely?
Nowhere does it say using maintenance plans per se is not good practice. All that has been mentioned so far is they are not great for reindexing, and I agree they should not be used for that, but there is nothing bad about using them for backups or integrity checks, and they can do a lot of other things such as tidying up old files and msdb tables. A maintenance plan also has the execute SQL task, so you could incorporate your tailored reindex script into a plan if you wished, or anything else.
Their plug and play capability is just fine in a lot of circumstances, so start from there if you have to, but avoid the reindex or reorganise options, and develop from there to suit your environment and the challenges of your database(s).
I don't use them much myself but they are not a thing of evil.
---------------------------------------------------------------------
February 7, 2014 at 2:14 pm
One other thing about using the maintenance plans. There is a very important feature you can't do with regards to backups. The only means to perform a checksum on the backup is to do it via a script task in the maintenance plan. That is a tough nugget to swallow.
Also, configuring striped backups in the maintenance plan is possible but it must be done via script and then you lose a bit of flexibility in names of those backups that are striped.
Last bit is that maintenance plans typically take longer to complete than a script based routine. I have observed 20% more resource requirements for the maintenance plans over the use of the script based methods.
These are some big reasons why I don't use maintenance plans on high volume or large database servers.
That said, if the database is small or requires minimal features as far as maintenance - then a MP backup plan may be suitable.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2014 at 2:40 pm
george sibbald (2/7/2014)
ScottPletcher (2/7/2014)
No real DBA would ever use a maintenance plan;thats a bit over the top surely?
Nowhere does it say using maintenance plans per se is not good practice. All that has been mentioned so far is they are not great for reindexing, and I agree they should not be used for that, but there is nothing bad about using them for backups or integrity checks, and they can do a lot of other things such as tidying up old files and msdb tables. A maintenance plan also has the execute SQL task, so you could incorporate your tailored reindex script into a plan if you wished, or anything else.
Their plug and play capability is just fine in a lot of circumstances, so start from there if you have to, but avoid the reindex or reorganise options, and develop from there to suit your environment and the challenges of your database(s).
I don't use them much myself but they are not a thing of evil.
For me, it's not over the top, I literally never use a MP. I've seen too many odd errors in them. You delete a db that happens to be the first one in the MP and the whole thing is skipped or fails.
[
Personally, I prefer to have table-driven logic (which is why I don't use Ola Hallengren's scripts). Then I can fine tune the specific maintenance requirements for every table in detail. I can also easily change the backup schedules and/or locations; what type of checks are run; how many files each backup is written to; etc., without modifying any code.
I've even built override logic into the procs that maintain those tables so that I if have to temporarily override something, it can "auto-revert" at the specified datetime. When you manage huge numbers of servers/databases (sometimes alone!), that is really a huge plus.
]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply