Maintaining and Automating SQL Server Lesson 26 Skills Matrix Skill Matrix Automating Administrative Activities • Maintaining an efficient operation means you continually—as in all the time—watch for new bottlenecks. • Reducing a bottleneck simply reveals a new bottleneck, so your job is to balance all bottlenecks equally so no one interferes more than another. • Balancing bottlenecks requires a proactive program of regular maintenance. Maintenance functions are best performed during off-hours or minimal-use hours so as to cause the least interference to productivity. • Automating these functions allows you to sleep well at night (the SWAN principle). Maintenance Plans Maintaining Indexes • You learned you need indexes on most SQL Server tables to speed up access to the data. • Without these indexes, SQL Server would need to perform table scans, reading every record in the table, to find any amount of data. • To keep your indexes running at peak efficiency, you must perform periodic maintenance on them. Maintaining Indexes • The primary issue to watch for in an index is page splitting. • A page split is caused when a page of data fills to 100 percent and more data must be added to it. • When a new page created is out of order leads to fragmentation. Reorganizing and Rebuilding Indexes • If the amount of fragmentation on your index is less than 10 percent, you really don’t need to do anything. • When it’s from 10 percent to 30 percent, you should reorganize your index; and anything higher requires a rebuild. • To reorganize an index, use the ALTER INDEX REORGANIZE statement. Maintaining Statistics • Whenever you perform a query on your data, SQL Server’s Query Optimizer creates a map of which tables and indexes to use to execute the query as fast as possible. • This map is called an execution plan. • To choose the fastest index for the query, SQL Server must know how much of the data in the index is applicable to the query being run. • To get this information, SQL Server reads the index statistics, which tell the database engine what the index holds. Maintaining Statistics • The problem is that indexes change when the data in the underlying table changes. When the index changes, the statistics become out of date. • This isn’t a problem if you created the index with the default value of STATISTICS_NORECOMPUTE OFF, because SQL Server will automatically update statistics for you. • But if your server is low on resources and you had to turn this value on, then you will need to update your statistics manually. Maintaining Databases • Database files are volatile. • They are constantly being changed and updated, so just like any other volatile file on a system, they can become corrupt. • Consequently, it is important to perform periodic maintenance on database files. DBCC CHECKDB • You can use DBCC CHECKDB to check the allocation, logical, and structural integrity of objects in the database. • This is necessary because databases are in a constant state of flux; in other words, data are always being inserted, updated, and deleted. • So, it stands to reason that occasionally something is not going to be written to disk correctly and will need to be repaired. Shrink Databases • When users enter new data into a database, SQL Server may have to expand the size of the data and log files on disk to accommodate the new data. • When this happens, the size of the physical file on disk increases, so the hard drive has less space available for other files. • When a user deletes data, though, the size of the physical file is not reduced; the file remains the same size. • This condition can result in wasted system resources and possibly cause problems for other processes that need disk space. Shrink Databases • Using SQL Server Management Studio, you can shrink the entire database or just a single file at a time. • SQL Server also provides the DBCC SHRINKFILE and DBCC SHRINKDATABASE utilities to enable you to script these functions or include them in a job for scheduled processing. • DBCC SHRINKDATABASE reduces the size of the data and log files in the specified database, although the database cannot be made smaller than the minimum size specified when the database was originally created. Understanding Automation Basics • You can automate nearly any administrative task you can think of through SQL Server. That may sound like an exaggeration, but look at the features you can automate: – Any Transact-SQL code – Scripting languages such as VBScript or Jscript using PowerShell – Operating system commands – Replication tasks Understanding Automation Basics • This functionality is powerful, so before you start to use automation, you need to know how it works. • At the heart of SQL Server’s automation capability is the SQL Server Agent service (also referred to as “the agent”). Understanding Automation Basics • Automation and replication are the sole functions of the service, which uses three subcomponents to accomplish its automation tasks: – Alerts – Operators – Jobs Configuring Database Mail • You can use Database Mail to send e-mail for the SQL Server services using the standard Simple Mail Transfer Protocol (SMTP). • It is actually a separate process that runs in the background, so if a problem occurs, SQL Server is unaffected. • Database Mail is also scalable because it uses the Service Broker queue, which allows the request to be handled asynchronously and even saves the request if the server goes down before it can be handled. Operators • When an alert is fired, it can be sent to a user. • Users who need to receive these messages are known in SQL Server as operators. • Operators are used to configure who receives alerts and when they’re available to receive these messages. Operators • You need to configure several settings to enable SQL Server to contact the appropriate people when problems occur. • An operator is the object used in SQL Server to configure all these settings. • Net Send messages are messages sent from a source machine to a destination machine that pop up on the user’s screen. Operators Jobs • A job is a series of tasks that can be automated to run whenever you need them to run. • Some of these tasks need to be accomplished in succession. Jobs Creating Alerts • An alert is fired when an event (usually a problem) occurs on SQL Server; some examples are a full transaction log or incorrect syntax in a query. • These alerts can then be sent to an operator so that they can be addressed. • Alerts are based on one of three features: an error number, an error severity level, or a performance counter. Types of Alerts • Standard Errors • Custom Errors • Creating Performance Alerts • WMI Alerts Alerts Maintenance Plan Wizard • You need to perform many tasks to keep your databases running at peak performance at all times. • Such tasks as index reorganizations, database file size reductions, and database and transaction log backups all need to happen on a regular basis to keep your server running smoothly. • To avoid all the labor of creating multiple jobs for multiple databases, use the Maintenance Plan Wizard. Summary • This lesson covered a lot of ground, but what you learned here will save you a lot of time and effort in server administration and reporting. • First you learned about required maintenance—that you need to perform many tasks on your server to keep it running smoothly and efficiently. • You must back up databases and transaction logs, reorganize index and data pages inside the database files, check for database integrity regularly, and keep statistics up to date. Summary • You also learned that automation includes three main components: operators, jobs, and alerts. • Operators are the individuals who are notified when a problem needs attention; they can be notified via e-mail, pager, or Net Send messages. • Jobs are series of tasks and schedules that can be automated to activate at any time; they can include Transact-SQL code, command executive code, or scripting language code. Summary • You investigated mail support. • To configure mail support, you learned you first need create a mailhost database and add a profile and an account. • When these are in place, you can start sending e-mail. • If you want to send mail to operators, you need to make MSDB a mailhost database. Summary • You learned how to create operators and configure them to receive e-mail, pager, or Net Send messages. • You can also configure them to be available only at certain times of the day by setting their availability. • In addition, you learned how to create jobs. • You can configure jobs to run any type of code at any time, and you can configure them to inform an operator when they complete, when they succeed, or when they fail. Summary • Next you set alerts, which are used to notify an operator when an error has occurred. • Not all errors fire an event, though—only those that are written to the Windows Event log and have an alert configured will fire an alert that notifies someone. • You learned how to create alerts that are based on the standard error messages that come with SQL Server, as well as how to create your own custom error messages that can be used for any purpose. Summary • You then learned how to create and use performance alerts to stop problems before they start. • You also learned how to create WMI alerts so you can be notified when server events occur, such as CREATE TABLE or other DDL statements. Summary • After learning the importance of periodic maintenance and how to schedule jobs to run automatically, you learned how to tie all of that together by using the Maintenance Plan Wizard to automate these processes for you. Summary for Certification Examination • Know how to find and fix index fragmentation. When users insert data in a table, SQL Server must make room for the new data in the table and any associated indexes. • When an index page gets too full, SQL Server moves half of the data to a new page to make room for more data on the existing page. • This is called a page split; when it occurs, the pages are no longer contiguous, and a condition called fragmentation occurs. You can use sys.DM_DB_ INDEX_PHYSICAL_STATS to find the amount of fragmentation on your indexes. If your index is less than 30 percent fragmented, you can reorganize it; anything higher than 30 percent requires a rebuild to bring the index back into shape. Summary for Certification Examination • Know how to update statistics. SQL Server uses statistics to figure out which index, if any, you can use to speed up a SELECT query. • If you created an index and told SQL Server not to automatically recompute statistics for you, then you will need to update them yourself using the UPDATE STATISTICS command. • Know how to repair databases. Because databases are just files on the hard disk, they can become corrupted on occasion. • You need to know how to use DBCC CHECKDB to find and fix imperfections in your databases. Summary for Certification Examination • Understand Database Mail. Database Mail is used for sending e-mail from SQL Server. • This is especially useful for sending notifications for alerts and errors. Make sure you know how to set up a mailhost database and configure the SQL Server Agent to use a profile to send mail. Summary for Certification Examination • Understand jobs, operators, and alerts. SQL Server automation is built on three primary features. • Jobs are a series of steps that can be run on a set schedule. • Operators are people or groups of people to be notified by e-mail, pager, or Net Send message when something happens on the server. • Alerts are fired when something happens on the system, such as an error. • Alerts can notify an operator and automatically run a job when fired. Summary for Certification Examination • Know the Maintenance Plan Wizard and Copy Database Wizard. • You can automate all the maintenance tasks discussed in this lesson by using the Maintenance Plan Wizard. • Just run the wizard, and it will create all the appropriate jobs for you and then run them on a set schedule. • The Copy Database Wizard is just as handy. • When a developer needs to copy a database from development to production, for instance, or you need to copy a database from a satellite office to the home office, you can use this wizard to simplify the process.