Module 11: Performing Ongoing Database Maintenance
1 of 33
Module
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Maintenance
Contents:
Lesson 1: Lesson 2: Lesson 3: Lab:
Module The Microsoft® with minimal
periods of time the database
engine requires Database corruption maintenance
ongoing Recovering
from corruption
indexes can also
continue to work
periodically
remove any fragmentation Wizard to assist
Maintenance Plan ongoing
maintenance
Objectives After completing
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
2 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Ensure database • Maintain indexes. • Configure
Lesson It is rare for depends upon particular, issues If you do not complex or troublesome) recover a corrupt
database engine corruption. In databases. significantly more attempting to corrupted
copy of the database. You can use database corruption.
circumstances correct, DBCC
CHECKDB
Lesson After completing • Describe database • Use DBCC • Describe the • Explain how
Introduction
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
3 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
There are two • Physical integrity
Server
requested • Logical integrity entry points
every index index entry.
Without regular
check the
integrity, it only
specified. While
the CHECKSUM read, or when frequently, which
when the data is is not read checked, will not
be found for
Overview
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
4 of 33
DBCC is a utility, facilities. In Checker. While primary function referred to as
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
management Consistency option is a product, it is wider
variety of tasks DBCC CHECKDB The CHECKDB database, to detect CHECKDB
structure of a DBCC separately if required.
The most important
Option DBCC CHECKALLOC
DBCC CHECKTABLE
allocation
specified table and associated with the database.
DBCC CHECKCATALOG
logical in the
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
5 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Option that describes database objects. user tables.
DBCC CHECKDB
links for
FILESTREAM
Note: scope
beyond the
Repair Options Even though
repair a
database without
restore a
backup of the
DBCC
CHECKDB
restore a
database from
that time are
available. Online Concurrent DBCC CHECKDB resources, so CHECKDB consistent view while DBCC restored backup
I/O and CPU DBCC works with a running against a better than
not running DBCC Disk Space The use of an DBCC CHECKDB as the database DBCC CHECKDB
space. same volumes available for volumes
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
6 of 33
depends upon
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
CHECKDB.
DBCC CHECKDB
provide an
estimate of the
an
ESTIMATEONLY Backups and It is considered
performing a
backup. This
of the
database.
DBCC CHECKDB
DBCC CHECKDB
executing.
• DBAs often
it
substantially regularly use version of
databases. If you the full your specific
business requirements. • You can use nonclustered
checks of but does not
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
7 of 33
affect system indexes. The
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
table option is that you
can rebuild • You can only database compatibility of the internal
database is in detailed checks spatial data
types. • You can use
table lock on
each table
database
snapshots.
other users
from updating • The ALL_ERRORMSGS
from the
command, • The ESTIMATEONLY
database.
DBCC CHECKDB
Repairing a database
typically better
to restore it from
back up a
database before
for the
corruption—otherwise
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
8 of 33
As well as providing
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
CHECKDB
shows the repair
CHECKDB offers
two repair options.
mode. The options
are: • REPAIR_REBUILD
option only
works with • REPAIR_ALLOW_DATA_LOSS corrupt pages
de-allocates the operation
completes,
integrity point
of view. Significant
do not
consider any involved in
specified table is DBCC
CHECKCONSTRAINTS
In the example REPAIR_ALLOW_DATA_LOSS If the transaction repair. However,
emergency mode database and you
should only use
Demonstration: In this demonstration,
Demonstration Use the DBCC 1.
Ensure and log
are running, the
password 2.
In the D:
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
9 of 33
3.
Start SQL
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
database engine
instance 4.
Open the
folder.
5.
Select the
options and
click Execute
and displays
detailed 6.
Select the messages
informational AdventureWorks
database 7.
Select the click Execute consistency
CorruptDB and identifies some output tells
you the 8.
Select the This attempts
click Execute. error
because 9.
Select the
Execute. This
succeeds,
consistency issue.
10. Select the this technique guarantee
Note that available. No constraints) is
provided. 11.
Select the
Execute. This
succeeds, 12. Select the Execute
and click structure is now
consistent. 13. Select the number constraint
Note that a foreign-key data has
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
10 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
been lost.
Lesson Another important performance SQL Server needs performance
optimal operations where fragmented so the Defragmenting or
rebuilding the Index management Before learning
plan schedules. more about
how indexes
Lesson After completing • Explain how • Describe the • Describe how • Use FILLFACTOR • Explain the • Implement • Describe how
How Indexes
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
11 of 33
Whenever SQL pages (known amount of effort order based on
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
read all the reduce the selects a single has to scan
all the orders
can find the
row quickly
difference here
could be between
hundreds.
Indexes can help
can also
impact data modification
they demand
additional disk
indexes to
improve query
beyond the control
of the database in this module.
be discussed no proper
indexing already
Types of
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
12 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Rather than storing
data pages are
not stored in
logical
ordering. This Clustered Index A table with within the table.
for pages The key is
commonly called Because the index on a table. There is a common order. While fragmentation
single clustered clustered index. stored in was true, physical and
logical order Consider searching page, SQL Server in the root page smaller ranges, where every
OrderID. In the root for the range divided into a point node.
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
13 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Index and data
across all
pages at the
For example,
imagine a table (Each extent order, SQL Server page is logically
in order. of the logical the index. The within the
database pages. Nonclustered A nonclustered
in the table
in the way that
has no
clustered index),
the data
rows are stored.
number on the
page. If the underlying
logically linked
in the order of
the clustering
key. This is then
the desired
rows. Other Types SQL Server • Integrated
flexible
searching • The GEOMETRY • Primary and • Large data
Index Fragmentation
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
14 of 33
Index fragmentation that read data, indexes initially
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
For operations However, if your the index pages
to need splitting.
process is
more complicated
index page.
Internal vs. There are two • Internal fragmentation of. This often when an update
are capable also happen situation,
empty space • External fragmentation sequenced inserted into placed at the order must
held in be logically likely to be index pages in accessing pages
that are not
Detecting Fragmentation
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
15 of 33
SQL Server provides sys.dm_db_index_physical_stats
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
column of the to analyze the
level of fragmentation SQL Server
fragmentation in the
properties page
FILLFACTOR
The availability performance
there is no
free space, a
split across
the two. This You can alleviate when creating when you create defaults to zero,
on each page FILLFACTOR option FILLFACTOR taken as the
percentage of The following
leaving 30
percent free
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
16 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Using FILLFACTOR
ALTER TABLE ADD CONSTRAINT PRIMARY ( ContactID ) WITH ( GO
Note: both lead been requested.
While value has specified.
By default, the
You can use
it in conjunction
to be
allocated in the
Ongoing
When SQL Server
become
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
17 of 33
fragmented.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
clustered and
nonclustered REBUILD Rebuilding an space by compacting
reclaims disk setting, and
reorders the
SQL Server
drops all indexes
of that fails,
it rolls back the Because SQL
rebuild operation
can use a large
change the
recovery model before performing minimally-logged
models operation. A and completes
faster. Use the ALTER Rebuilding an
ALTER INDEX
REORGANIZE Reorganizing
level of
clustered and
leaf-level pages to
match the logical, the index pages. interrupt a reorganize
compacts possible to means that,
on a large index, For heavily fragmented appropriate option
most rebuild or 26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
18 of 33
reorganize indexes.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
job that
regularly performs Reorganizing
ALTER INDEX
Online Index
The Enterprise
users are
accessing the
no available
maintenance
operations such as
index rebuilds. When performing index that tracks consistency,
temporary mapping occurring. For beginning and end
the operation.
prevent metadata
changes. This
commands such
as ALTER TABLE Because of the
operations are
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
19 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
typically slower The following Rebuilding an
ALTER INDEX
REBUILD = ON,
MAXDOP =
Note: indexes
nonclustered
Updating
One of the main
deciding
which indexes
distribution
of the data in
columns, and
creates them
database
option is enabled.
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
20 of 33
SQL Server This is the default
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
AUTO_UPDATE_STATISTICS is enabled. option for most
application database For large tables, update statistics
SQL Server to would
otherwise update You can also
STATISTICS
against a table sp_updatestats
Demonstration: In this demonstration,
Demonstration Maintain Indexes 1.
If you did
that the
20462C-MIA-DC
log on to
20462C-MIA-SQL
Pa$$w0rd.
Then, in 2.
If SQL
to the
MIA-SQL 3.
Open the
4.
Select the
click
Execute
clustered
folder.
index on 5.
Select the
click Execute.
This inserts
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
21 of 33
6.
Select the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Execute. In the
results, avg_page_space_used_in_percent 7.
Select the
Execute.
This updates 8.
Select the
Execute. In the
results, avg_page_space_used_in_percent
changed as the data
pages have 9.
Select the
click
Execute 10. Select the
Execute. In
the results, avg_page_space_used_in_percent fragmentation.
Lesson You have seen that you will Wizard you can
Maintenance maintenance tasks Maintenance Plan common database
maintenance While the Maintenance realize that you
important to your own
maintenance
Lesson After completing • Describe SQL
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
22 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Monitor database
Overview
The SQL Server routine database
perform your database is
regularly backed
wizard creates
SQL Server
Server Agent
tasks. You can schedule • Database • Database • Database • Update statistics • History cleanup • Execute agent • Backup tasks.
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
23 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Cleanup tasks.
Note: individual
with
Monitoring
SQL Server packages. Because
run SSIS maintenance plans
by using the The results that
plan tables,
dbo.sysmaintplan_log
You can
view the entries
by using the
Log File Viewer.
the file
system, and
Server
Agent.
Note: a retention msdb
implement files, and
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
24 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Demonstration: In this demonstration,
Demonstration Create a Maintenance 1.
If you did
that the
20462C-MIA-DC
log on to
20462C-MIA-SQL
Pa$$w0rd.
Then, in 2.
If SQL
to the
MIA-SQL 3.
In Object
Maintenance
Plans, 4.
In the Maintenance
5.
In the Select
Maintenance.
Note the 6.
In the New drop down
the Occurs to 3:00 AM,
and click 7.
In the Select
Maintenance
Tasks page,
o
Check
o
Reorganize
o
Update
o
Back
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
25 of 33
8.
On the
9.
On the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
AdventureWorks
database 10. On the
database and
click OK 11.
On the
database and
click OK 12. On the database for every
AdventureWorks backup file \Demofiles\Mod11
\Backups\ 13. On the
is selected,
change 14. On the
has
completed, 15. In Object
Maintenance and click
Execute 16. Wait a Maintenance
Maintenance and
click View 17. In the Log
the Daily
Maintenance 18. Keep clicking
completed.
Then click 19. In the D:
Maintenance_Subplan_1_xxxxx.txt
file that 20. In the Backups
database has been
created.
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
26 of 33
Lab: Performing
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Maintenance
Scenario You are a database databases on of database on
responsibility for maintenance index
fragmentation.
Objectives After completing • Use DBCC • Defragment • Create database
Lab Setup Estimated Time: Virtual machine: User name: ADVENTUREWORKS Password: Pa$$w0rd
Exercise
Scenario There has been check the consistency verify the logical
but you want to CHECKDB to HumanResources, and
InternetSales 26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
27 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
The main tasks 1.
Prepare
2.
Check
3.
Repair
Task 1: Prepare
1.
Ensure then log
running, and password
Pa$$w0rd 2.
Run Setup.
Task 2: Check
1.
In SQL Server
the integrity
of the following
o
AWDataWarehouse
o
HumanResources
o
InternetSales
Note:
required.
Task 3: Repair
1.
Use the
issues were
discovered
o
Alter
o
Run
o
Alter
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
28 of 33
2.
Use the
Result: After check database
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
resolved.
command to
Exercise
Scenario You have identified HumanResources fragmentation
the amount of database tables that
are heavily fragmented. The main tasks 1.
View Index
2.
Defragment
Task 1: View
1.
Query the fragmentation
about index HumanResources
database. 2.
Note the
avg_fragmentation_in_percent values for
each index
Task 2: Defragment
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
29 of 33
1.
Rebuild
2.
Query the fragmentation
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
database. about index HumanResources
database
Result: After
Exercise
Scenario You have also
proper index
maintenance
detection of
any consistency automatically you will create
maintenance is maintenance occurs, weekly basis.
The main tasks 1.
Create
2.
Run a Maintenance
Task 1: Create
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
30 of 33
1.
Create a
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
maintenance
plan should
o
Check
o
Reorganize
o
Update
o
Perform
database. in the
R:\Backups\ o
Write
Task 2: Run
1.
Run the
2.
View the
Management
Studio. 3.
When the
4.
Verify that
generated. \Backups
folder.
Result: After plan.
maintenance
Review Review Question(
Module In this module,
detect and repair
database integrity
fragmentation.
Finally, you
Best Practice:
tasks.
following
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
31 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
best practices. • Run • Synchronize • If corruption the
only repair
• Defragment • Update operations.
normal
• Use
Review Question(
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
32 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:53
Module 11: Performing Ongoing Database Maintenance
33 of 33
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:53
