Maintaining your Notification Server is essential. Managing an Altiris database server is like owning a speedboat: You need to do maintenance to keep things running smoothly and to prevent issues. Just like your boat requires regular tune-ups, your database needs regular tune-ups to guarantee best performance.
To help you keep your database in tip-top shape, this article shows you how to setup a maintenance plan for Notification Servers running SQL 2005 whether it’s on-box or off-box. Although this might not be the silver bullet that will fix all your issues, setting up a maintenance plan can help to a degree with issues where your notification servers seems to be slow.
SQL maintenance plan for SQL Server 2005
Once you setup a maintenance plan for SQL, you should have it setup so that it runs every week. In exceptional circumstances you may want to set it to every month. This might be a good idea if your notification server is heavily used on weekends, otherwise stick with a weekly schedule. Part of the maintenance plan will reset the indexes so that it uses 10% of free space.
When SQL Server receives a new query, it attempts determine the best possible plan for resolving that query. It considers a number of different factors as it analyzes the query and maps out a way in which to retrieve the information requested. Whether or not the query optimizer deems an index to be useful in resolving a query largely depends on the information contained in the statistics for that index.
If the statistics are outdated and do not accurately represent the distribution of values within the table, the query optimizer may not produce an optimal plan for resolving the query. Misleading statistics may result in the optimizer not using an index when it should, or using an index when it would be more efficient to scan the table. That's why it is crucial that the statistics for an index be updated regularly and preferably right after the indexes are updated.
To find out when the statistics where last updated for an index use the following query. It makes use of the sys.indexes and sys.tables catalog views, along with the STATS_DATE() function, to retrieve the date that each index was last updated for every user table in the current database.
SELECT t.name AS Table_Name, i.name AS Index_Name, i.type_desc AS Index_Type, STATS_DATE(i.object_id,i.index_id) AS Date_Updated
FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.type > 0
ORDER BY t.name ASC ,i.type_desc ASC, i.name ASC
When determining what time to set for the plan to run, you want to pick a time that your notification server has low usage. You might think that this would be simple, just choose a weekend, but if you have inventory or saved reports, software deployments then you’ll on the weekend, you’ll want to schedule around those events.
If you are not sure about when would be the best time for to this, check the reporting that Altiris has for server performance. You can find these reports under Reports -> Notification Server Infrastructure -> Server Performance Reports. Use these to determine what day(s) your server has the lowest utilization.
Follow these steps to build the SQL Maintenance plan for SQL 2005:
- Verify the SQL Server Agent service is running on your SQL server. The SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are also called jobs. SQL Server Agent uses SQL Server to store job information and jobs contain one or more job steps. Each step contains its own task, for example, backing up a database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. This step is important because by default the SQL Server Agent service is disabled when SQL Server 2006 is installed, unless you explicitly choose to autostart the service.
- Open SQL Server Management Studio. If you aren’t familiar with this tool, it is new with SQL Server 2005, and is typically installed as part of the client tools, so you can run it remotely instead of having to be directly connected on the SQL server.
- Expand the Management folder. From here you can view all the current maintenance plans, logs and other useful features for SQL.
- Right-click on Maintenance Plans and select Maintenance Plan Wizard.
- When the SQL Server Maintenance Plan Wizard info page opens up, click Next to get to the next page. This page will list all the features available for setting your custom maintenance plan. Although there are several features available including, checking database integrity, performing index maintenance, updating database stats and performing database backups, we are going to focus on just two areas since we are mainly concerned about performance.
- Give the maintenance plan a useful and descriptive name such as “Rebuild Indexes for Altiris Databases”. This name will be what you see in the Maintenance plans folder once you finish creating the plan.
- Keep the default option to use a Single schedule for the entire plan or no schedule. Click the Change button to enter your chosen schedule for the rebuild maintenance plan. For the two tasks we are doing (rebuilding indexes and updating statistics) it would not be a good idea to do these separately.
- Enter in the time you want these to run at. To determine the best time see the opening paragraphs of this article. For this example we enabled a recurring task every 1 week on Sunday at Midnight. We chose a start date of 9-9-08 and no end date. Click OK.
- Click Next.
- Check the options to Rebuild Indexes and Update Statistics. The Rebuild task reorganizes data on the data and index pages by rebuilding indexes. This improves performance of index scans and seeks. This task also optimizes the distribution of data and free space on the index pages, allowing faster future growth. The Update Statistics task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgements about data access strategies. (i.e. it should be faster.) and click Next.
- Make sure that “Rebuild Index” task is at the top and click Next.
- Click the Database drop-down.
- Select These databases and check all the used Altiris databases you have on your SQL server and then click OK.
- Select Change free space per page percentage to and set its value to 10% if you are using a weekly schedule or 20% if you are using a monthly schedule. “Sort results in tempdb” should generally not be used, however, if SQL memory resources are low, then this will help, but it does cause rebuilding to take a lot longer. This option really becomes a Catch 22 issue in that case.
Also make sure that Keep index online while reindexing is unchecked. Altiris databases uses ntext fields which prevent clustered indexes from being rebuilt online for tables that have a ntext field. Then click Next.
- Now we are setting the options for the Update Statistics task. Select These databases and chose the same databases as before. Click OK.
- Select the option to update All existing statistics and make sure you have selected the Scan Type of Full scan (Note: A full scan can take quite some time so, if needed, a 50 percent sample scan should be sufficient). And Click Next.
- If you want a log to review on how your maintenance tasks are doing check the option to Write a report to a text file and allow it to write to the default location or the location of your choice. Click Next.
- Review your Maintenance Plan settings to make sure everything looks OK and then Click Finish. That’s it! You can click close once the wizard completes the task.
Your results may vary depending on the environment. However, if you use the Maintenance Plan Wizard to perform the tasks that are listed above you are likely to experience increased performance in SQL Server 2005 and thereby your Altiris Notification server database. These steps will not work with a SQL Server 2000 Database.
I would also recommend running your SQL server database on a 64-bit server, many companies have reporting significant performance increases by doing this. If possible running an enterprise version of SQL Server should help with performance, especially if you have a large amount of memory you want to make use of.
While these steps are designed for the Altiris databases in particular, you could also follow these basic steps for any other large SQL databases you have in your environment. If you don’t experience improvement in performance, or you do for the first month or so, go check the logs in the location from step 17 and look for errors to see I the maintenance plan failed.