Sql-server – Maintenance plans

sql server

I am an accidental dba..I did post a message a while ago about Index fragmentation and some of you suggested maintenance plan and others suggested custom Olla's scripts. I have downloaded his scripts and it is a little overwhelming now, but I am going to take your advice and implement it as soon as possible. Now, just to feel a little comfortable with the entire database maintenance tasks, I plan to implement it using the maintenance plan wizard. I did go through the book written by Brad McGehee like some of you suggested.

I have this sql cluster that has roughly 18 to 20 database. Right now, I am working with just one. This is a small database, 30GB in size and has roughly 100 users. I queried the database to find the index fragment percent level using the following command

select dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
dbindexes.[fill_factor] as 'Fill Factor',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id

where indexstats.database_id = DB_ID() and indexstats.page_count > 1000 
and (indexstats.avg_fragmentation_in_percent >= 10 and  
indexstats.avg_fragmentation_in_percent <= 40)
order by dbtables.[name]

Anywhere between 10 to 40%, I plan to re organize and update stats and greater than 40% I plan to rebuild. I ran the query and chose all the indexes that had above 40% fragment percent and 1000 pages and there were roughly around 15 tables and 21 indexes. I created a maintenance plan and chose the rebuild task for only those 15 tables. For the regorganize task, there were 10 tables with 25 indexes. I created a separate plan for this one too. I know that I have created two different plans, but I have never done this before and want to see how it works before I analyze scripts and make it more customized.

I have thought about the following maintenance plan apart from the back up plans that has already been set:

1) Index maintenance – bi weekly
2) history clean up – weekly
3) consistency checks – weekly

I have a few questions:

1) Update stats has never been done for this database. I did read that after reorganize index, I have to manually do update stats task. The auto stats update is enabled. Do I have to do update stats for only those indexes that get reorganized or update stats for the entire database. I did check how much time it would take by restoring a fresh copy of the database in the test system and running update stats for the entire database takes roughly 2 hrs time.

2) I will run these tasks during our maintenance window which is very limited. I am assuming I don't need to restart any servers after running these maintenance plans.

Your feedback and help will be really appreciated.

Thanks!

Best Answer

Index rebuild will update statistics of column with a equivalent of full scan. Reorganizing index will not update statistics at all.

For more detailed explanation Ben Nevarez has written more in his blog

1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

3) Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistics.

4) Reorganizing an index, for example using ALTER INDEX … REORGANIZE, does not update any statistics.

The same can be verified from Paul Randal Post

2) I will run these tasks during our maintenance window which is very limited. I am assuming I don't need to restart any servers after running these maintenance plans.

NO restart is required after running Index rebuild or update statistics. Why people referred you Ola's script as it has ability to look for columns with outdated stats and ignore the updated one. The script has more benefit like it would minimize the time need to run these maintenance task as well as less logs would be produced.

Related Solutions

Sql-server – How to specify a task in a SQL Server maintenance plan conditionally

I am not sure how dirty you want to get on this one but the only method is going to be opening up SQL Server Data Tools and building (or modifying) an SSIS package. One would be to import the maintenance plan into SQL Server 2012 Data Tools and modify the SSIS package. The other would be to create it from scratch. You have the same options as the maintenance plans when you create an SSIS package.

The one thing you want to add is an Expression Task, which is an awesome addition to SQL Server 2012. You could use the DATEPART SSIS expression to determine whether it was a weekday or weekend based on the system variable for package start time. Now I am not the best with expressions but this would be the one I would use:


DATEPART("dw",@[System::StartTime])

This will return a number between 1 and 7, with 1 = Sunday and 7 = Saturday. So from that task you would have two precedence constraints going to each path of your maintenance plan. If the value is 1 (one) then it goes to the rebuild index task, if it is anything else it goes to the reorganize task.

Although if I was going into this much detail I would stop using the index task with maintenance plans. I would suggest going to using T-SQL code that actually helps you maintenance the index fragmentation much more efficiently. I will say though I don't know if SQL Server has improved in 2012 with the index task. The most well known solution for index fragmentation management can be found here, Ola Hallengren.

Sql-server – How to search SQL Server for maintenance plans that execute specific tasks

Yes. This can be done by using a combination of the system SSIS tables and XQuery.

Note: In SQL 2012, this will only work for Maintenance Plans created within SSMS. If you used Data Tools to deploy a package that performs maintenance via the project deployment model, this will not work.

SQL Server saves maintenance plans as SSIS packages, hence the need for the system SSIS tables (sysssispackages and sysssispackagefolders).
The package-level data where we'll find the references to the tasks you're searching for will be found in the column sysssispackages.packagedata.

XQuery

If you are not familiar with XQuery, you can start here.

Find the task ExecutionType(s) for the Task(s)

The first thing that you'll need to find is the ExecutionType of the task(s) you're looking for.

Find an existing maintenance plan with the task(s) in question or build a new maintenance plan.
Remember the name(s) of the maintenance plans you're looking for.

Query msdb..sysssispackages for the plan name(s).

Example query to retrieve the package XML based on the maintenance plan name:

 SELECT 
    name PlanName   
    ,createdate PlanCreated
    ,CAST(CAST(pack.[packagedata] AS varbinary(max)) AS XML) PackageDataXml 
 FROM msdb.dbo.sysssispackages pack
 WHERE name = 'RebuildIndexes'

Click on the results in the PackageDataXml column which will open the raw XML in another query tab.
In the newly opened XML tab, search for the string "ExecutableType". (Nothing fancy required here - just good old-fashioned CTRL+F.)
- We do this because each task is saved as a DTS:Executable node, and each DTS:Executable node has a DTS:ExecutableType. The Executable type is what we can use in our final query to search for the specific tasks.
- You'll find some ExecutableTypes for Executable nodes that don't relate to our quest. Keep searching until you find the relevant Executable node(s).
- You'll know a good one when you see it, because the value for DTS:ExecutableType will have a useful descriptive name.
- Only grab the first portion of the ExecutableType, which is usually the part of the string before the comma.

Search for the maintenance plans using the ExecutableType(s)

The following query uses XQuery and the ExecutableType values that were found in the step above to search in all maintenance plans.

There are commented out lines that would allow for filtering for plans associated with active jobs and/or active schedules.
Credit goes to StuartLC who helped me with the XQuery portion of this.
Has only been tested on 2012 and 2008R2.

/*
  Description: Search SSISPackages w/ xQuery for specific 
  Maintenance Plan tasks. In this example: 

    --> Index Rebuild
    --> Shrink Database
    --> Update Statistics

Known compatible versions: 2008R2 & 2012

    --> No 2008 or 2005 instances to try it out on. :'(
*/

-- Build a temp table to retrieve the list of plans
-- that use the tasks we're searching for.

IF OBJECT_ID('tempdb..#BadPlans') IS NOT NULL 
  DROP TABLE #BadPlans;

CREATE TABLE #BadPlans 
(
  PlanName nvarchar(128)
  ,PlanCreated datetime
  ,PlanFolder nvarchar(128)
  ,TaskDescription varchar(50)
  ,TaskExecutableType varchar(200)
);

;WITH XMLNAMESPACES 
(
  'www.microsoft.com/SqlServer/Dts' AS DTS
  ,'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
INSERT INTO #BadPlans

--******2012 Results******

SELECT 
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(@DTS:Description)[1]', 'varchar(50)') 
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)') 
    AS TaskExecutableType
FROM 
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid 
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id            
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id        
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id    
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY 
-- Version-specific XPath for 2012. Look at this singular-plural-singular-plural-singular madness.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable/DTS:Executables/DTS:Executable') 
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%'

UNION ALL

--******2008R2 Results******

SELECT
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(DTS:Property[@DTS:Name="ObjectName"])[1]', 'varchar(50)') 
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)') 
    AS TaskExecutableType
FROM 
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid 
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id            
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id        
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id    
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY 
-- Version-specific XPath for 2008R2. Not as much madness here.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executable/DTS:Executable') 
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%';

SELECT * FROM #BadPlans;

Best Answer

Related Solutions

Sql-server – How to specify a task in a SQL Server maintenance plan conditionally

Sql-server – How to search SQL Server for maintenance plans that execute specific tasks

Related Question