Working With Databases

Databases in SQL Server 2008

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

This Chapter

• What this chapter covers – Databases, data bases and DBs • Creation and management

– Database file structure – An overview of configuring the database

This Chapter

• What this chapter does NOT cover – Every single database option available – Performance tuning and optimizations – Monitoring

This Chapter

• Pre-requisites to this chapter – You should already know how to create a database using the GUI

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

SQL Server Databases

• SQL Server databases have at least two files – Data files – Transaction Log files (commonly called “log files”)

• A database has at least one of each – We’ll look at when to have multiples of each later

SQL Server Databases

• Data files store everything that a user needs such as – The data – The indexes – Source code for routines (stored procedures, functions) – Security information (users, roles, permissions)

SQL Server Databases

• Transaction log files are used to protect SQL Server databases from “failure” • Failure could be – User error – Disk failure – Software failure – Dreaded BSOD (Blue Screen of Death)

SQL Server Databases

• Every write in a database is done inside of a transaction • All transactions are written first to the log and then to the data file • This is known as “Write Ahead Logging” – http://en.wikipedia.org/wiki/Write_ahead_logging

SQL Server Databases

• You begin a transaction with the BEGIN TRAN statement • A transaction may include many statements

SQL Server Databases

• A transaction remains “open” until either you: – COMMIT TRAN – ROLLBACK TRAN

(called “rolling back”)

• Once “closed”, the transaction may never be re-opened

SQL Server Databases

DECLARE @ProcessingHistoryId INT DECLARE @PayAmount DECIMAL(19,2) = $1.94 BEGIN TRAN INSERT PayrollProcessingHistory VALUES (‘Whigham’, ‘2009-04-01’, @PayAmount) SELECT @ProcessingHistoryId = SCOPE_IDENTITY()

INSERT ChecksToWrite VALUES (@ProcessingHistoryId , @PayAmount) COMMIT TRAN

SQL Server Databases

• Transactions in SQL Server are durable – Once the transaction is committed, the transaction will remain in the database even in the event of a system crash

• Demo of write-ahead logging

*More on transactions in our “SQL Server 2008 Programming” course

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Databases in SQL Server 2008

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

System Databases

• SQL Server uses SQL Server for configuration – Some configuration is in the registry – Some configuration is in XML files

• The majority of database and instance configurations are stored in the five system databases

This Chapter

• The five system databases: – master – model – msdb – tempdb – Resource

• Let’s look at each one

The master Database

• The master database: – Is the first database “loaded” by SQL Server service on start – Stores instance-level configuration such as memory, CPU, and the info set by sp_configure – Stores information about each database on the server – Stores the logins into the server – Stores routines that are shared among all databases

The model Database

• The model database: – Is a template for new databases – Whatever objects are in model will be created when a new database is created – Useful for standardization

The msdb Database

• The msdb database: – Stores any and all automation configuration and information • Log shipping, mirroring, jobs, alerts, mail • History of all of the above

– May optionally store SSIS packages – Stores history about backups – Used heavily by SQL Server Agent

The tempdb Database

• The tempdb database: – Is what the name implies: temporary storage – Temp tables are created here – Row versioning is done here – Is “wiped clean” at shutdown and created at startup • You can actually delete the database files on the hard drive after shutdown and SQL Server will create them anew

The Resource Database

• The resource database: – Hidden from view; must view at the disk level – A read-only database that contains all of the “system objects” in SQL Server • Each database has “pointers” to these system objects

– Makes it easy to upgrade • During upgrade, MSFT can just replace the resource database and immediately all databases on server have latest source code and objects

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Databases in SQL Server 2008

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Types of Databases

• Databases are either: – System databases – shipped with SQL Server; from Microsoft – User databases – everything else; not an official term

• A typical SQL Server has many user databases

Types of Databases

• User databases can fulfill many different functions – Customer Relationship Management (“CRM”) – Payroll – Document storage – Website analytics package – Configuration – Project management – Instrument measurement & Quality Control

Types of Databases

• Each of these databases has multiple needs – A CRM database needs to provide • Heavy read and reporting capability • Light to Moderate write capability • Could be anywhere from 5 reads for every write (5:1) up to 50:1 reads to writes

Types of Databases

• An instrument measurement database needs extremely fast write capability – 250+ writes per second – Reads are done at reporting level • Defect reports, abnormalities

– Could be 1000:1 writes for every read

Types of Databases

• A website analytics package might use multiple databases – One database needs extremely fast write capability to capture real time data – Another database needs extremely fast read capability for reporting

Types of Databases

• Database needs change with time – End of month reporting – End of year reporting – Tax season

Types of Databases

• Databases are classified as having one or more of these attributes: – Online transaction processing (“OLTP”) – Online analytical processing (“OLAP”)

• A database might serve both needs • It is generally better to serve a single purpose

Types of Databases

• A data warehouse is an example of an OLAP database • Data warehouses are “fed” by the OLTP systems on a scheduled basis – This ensures that the reports will be up to date – Often called a “batch load” or a “bulk load”

Types of Databases

• SQL Server Analysis Services (“SSAS”) stores a multidimensional data warehouse – Requires installing SSAS – Stores data in “cubes” – Use SSIS to load data from the SQL Server OLTP and/or OLAP database into the cubes * More information can be found in our SQL Server 2008 Analysis Services course

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Creating Databases in SQL Server 2008 Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Creating Databases

• You can create databases: – In the GUI (SSMS) – Transact-SQL scripts – Using SQL Server Management Objects (SMO)

Creating Databases

• The GUI is great for one-time use • Scripts and SMO are great for standardization and re-creation – A software vendor that wants to create a SQL Server database during setup – A DBA needs the same database installed on multiple servers

Creating Databases

• Transact-SQL scripts can be executed in – SSMS – sqlcmd.exe – .NET, Java, or any language that can connect to SQL Server – 3rd party query tools

Creating Databases

• SQL Server Management Objects (SMO) can be used – .NET, Java, or any language that can reference the necessary assemblies

Creating Databases

• Databases are just files – Data files – Transaction log files

• Files must be local – No mapped drives or UNC paths

Creating Databases

• Data files come in two flavors: – Primary data file: the first data file created • Generally has an “.mdf” extension

– Secondary data files • Generally have an “.ndf” extension

Creating Databases

• There is only one transaction log regardless of how many physical log files • Log files generally have an “.ldf” extension

Creating Databases

• “When do I need more than one data file and/or more than one log file?” – Two reasons: • Too much data to store in a single data file or extreme logging needs mean that too much logging for one single file • Performance

Why should I use multiple files?

• Space and file management – May not have space available for growth on current drive; add new drive and new file – Data or Log file may be too large for one file – Data or Log file may not be easily re-created on another machine if it gets too large • When restoring a database, you cannot split files!

Why should I use multiple files?

• Performance – The more disks, the faster performance – SQL Server can read two files on two separate disks in parallel • Can nearly double read and write access

Why should I use multiple files?

• Files can be added dynamically – Instant access – Essential at a time when you’ve run out of log space!

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Creating Databases in SQL Server 2008 Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Creating Databases

• After the database has been created, all file manipulation is done by referring to the logical file name – Adding space to a file – Shrinking a file – Removing a file

Creating Databases

• The logical file name is an abstraction layer – It need not refer to the database by name

• Physical file names cannot change while database is online – Logical file names can change at any time without affecting database status

Creating Databases

• Autogrowth is a safety net to prevent your database from preventing users from doing their jobs • The file will not grow until the file is 100% full – It then grows according to the file growth parameters

Creating Databases

• Autogrowth parameters: – Grow the file by a percentage of current size or a fixed amount (in MB) – Set maximum size

• Hint: Always set a maximum size – Do not let the file grow until it fills up the disk

Creating Databases

• Best Practices for Creating Databases – Create the database the size that you expect it to be in 12-18 months • This prevents autogrowth from happening during peak hours

– Do allow autogrowth • Keep parameters small and easy so that database can grow quickly and be usable quickly

Creating Databases

• Best Practices for Creating Databases (cont.) – Have a plan for growing the database in 12-18 months (or before autogrowth kicks in) – Always, always keep an eye on the file sizes • Particularly the transaction log

Creating Databases

• Expanding a file is easy and dynamic • We’ll take a look at working with multiple file databases in an upcoming video

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Creating Databases in SQL Server 2008 Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Filegroups

Filegroups

• A data file belongs to a filegroup • A filegroup contains n data files • A database may have n filegroups – A data file belongs to only one file group

• Filegroups allow you to do data placement and backup/restore on an entire filegroup

Creating Databases

• Each database has a PRIMARY filegroup – The primary data file belongs to the PRIMARY filegroup

Filegroups

• Transaction logs do not belong to filegroups

Multiple File Databases

• One advantage of filegroups is data placement – Tables can have “Regular Data” on one filegroup and “Text/Image” data on another CREATE TABLE dbo.MyTable ( MyColumn nchar(10) NOT NULL, MyVarColumn varbinary(MAX) NOT NULL ) ON RegularDataFG TEXTIMAGE_ON ImageDataFG GO

Multiple File Databases

• Filegroups can also be marked as read only – Any objects on the filegroup are read only while the rest of the database remains read/write ALTER DATABASE LearnItFirst MODIFY FILEGROUP [Sales2001] READONLY

Filegroups

• When there are multiple files in a filegroup, SQL Server uses a proportional file strategy – SQL Server does not fill up one file and then fill up the next file • Doing so would kill performance on a system that had files on separate drives

Filegroup Example

• LearnItFirst database has two data files in the MyFG filegroup – DataFile1: 100MB free – DataFile2: 200MB free

• SQL Server will write one extent to DataFile1 and two extents to DataFile2 – An extent is eight pages

Filegroup Example

• Once all files in the filegroup are full, it will expand the first file and write until it is full before expanding the second file

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Creating Databases in SQL Server 2008 Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Multiple File Databases

Multiple File Databases

• Adding a log file is easy – The log will now be “striped” across all log files – Can be done using GUI, TSQL or SMO

• Adding a data file is just as easy – The data will now be ”striped” across all files in the filegroup – Can be done using GUI, TSQL or SMO

Multiple File Databases

• When you add a new file to the database, it defaults to the PRIMARY filegroup – Best practice for multiple data file databases is to create a separate filegroup for user data and leave the primary data file as the sole file in the PRIMARY filegroup

Multiple File Databases

• Removing a file is hard – Requires “emptying” the file – Emptying the file requires you to move the data on the file to other data files in the same filegroup

• Demo of – A database filling up – Adding/removing files

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Configuring Databases

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Configuring SQL Server Databases

• Instance configuration affected every database on the instance – A single SQL Server instance may serve 1,000+ databases

• Each database may serve a different purpose and may benefit from individual configuration

Configuring SQL Server Databases

• SQL Server databases are autonomous; any configuration to one database has no effect on other databases • Different functions require different configurations – OLTP, OLAP, BLOB storage, Historical data only, mix

BLOB: Binary Large Object

Configuring SQL Server Databases

• This section focuses on the individual database configuration • We’ll cover the main options only – There are more than 30 different options

• We’ll cover upgraded databases in an upcoming section

Database Configuration

Database Configuration

• Database options are set by choosing between a predefined set of values – Most common is True/False

• Some database options override instance options – ANSI NULLs, Quoted Identifiers, et al

Database Configuration

• There are 6 categories of configuration – Automatic – Cursor – Miscellaneous – Recovery – Service Broker – State

• Let’s take a look at each of these in the next video!

Configuring Databases

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Database Configuration

• There are 6 categories of configuration – Automatic – Cursor – Miscellaneous – Recovery – Service Broker – State

Database Configuration

• Settings in the “Automatic” Category control how SQL Server proactively manages your database – “Auto Close” – Takes the database offline when the last user leaves the database • Defaults to FALSE

Database Configuration

• Settings in the “Automatic” Category (cont.) – “Auto Create Statistics” • Statistics are used by SQL Server to compute the fastest route to data • If TRUE, SQL Server will create statistics on columns the first time they are referenced. • Defaults to TRUE; Do not change unless you know this will help your situation

Database Configuration

• Settings in the “Automatic” Category (cont.) – “Auto Shrink” • If your database has free space, SQL Server periodically will remove the free space and give it back to the operating system • Defaults to FALSE

Database Configuration

• Settings in the “Automatic” Category (cont.) – “Auto Update Statistics” • Statistics can get out-of-date (“stale”) due to large amounts of UPDATEs and INSERTs • When SQL Server encounters a query with out-of-date statistics, the statistics are updated prior to the query execution – This can cause a lag

• Defaults to TRUE

Database Configuration

• Settings in the “Automatic” Category (cont.) – “Auto Update Statistics Asynchronously” • Statistics are updated when encountered but the query does not wait for the statistics update command to complete • Defaults to FALSE

Database Configuration

• There are 6 categories of configuration – Automatic – Cursor – Miscellaneous – Recovery – Service Broker – State

Database Configuration

• Settings in the “Cursor” Category control how SQL Server behaves with cursors – Few applications use cursors today – Be careful about testing before/after you change

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Configuring Databases

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Database Configuration

• There are 6 categories of configuration – Automatic – Cursor – Miscellaneous – Recovery – Service Broker – State

Database Configuration

• Some settings in the “Miscellaneous” Category override Instance-level defaults – Can set “Default Connection Options” at instancelevel – Can be override at both database-level and connection-level WARNING: Some apps depend on certain settings so do thorough testing when making changes

Database Configuration

• Other settings in the “Miscellaneous” Category are database-specific – Date Correlation Optimization Enabled • Used for foreign keys on DATETIME columns • Defaults to FALSE SELECT * FROM Parent p JOIN Child c ON p.DateTimeKey = c.DateTimeKey

Database Configuration

• Other settings in the “Miscellaneous” Category are database-specific – Cross-database Ownership Chaining Enabled • Covered in next chapter

– Trustworthy • Covered in next chapter

– VarDecimal Storage Format Enabled • In SQL Server 2008, all tables use the VarDecimal Storage Format • Was a changeable setting in 2005

Database Configuration

• More settings in “Miscellaneous” – Parameterization • • • •

Specifies how SQL Server works with query plan re-use Two choices: SIMPLE or FORCED Defaults to SIMPLE Can be overridden at query level

Database Configuration

• Parameterization continued… – When parameterization is SIMPLE and SQL Server recognizes a query that it thinks will be used in the exact same way but with different SARGs (Search ARGuments), it will attempt to create a reusable execution plan • Previously called “auto-parameterization”

Database Configuration

SELECT * FROM AdventureWorks.Production.Product WHERE ProductID = 1

SQL Server recognizes that this query will likely have multiple variations SELECT * FROM AdventureWorks.Production.Product WHERE ProductID = 1 SELECT * FROM AdventureWorks.Production.Product WHERE ProductID = 5

SQL Server creates a reusable, parameterized query plan that satisfies both queries

Database Configuration

• In SIMPLE parameterization, SQL Server will only create reusable plans for queries that it believes it can be reused • In FORCED parameterization, any literal in any SELECT, INSERT, UPDATE or DELETE statement is parameterized

Database Configuration

• FORCED parameterization can offer a tremendous performance gain for applications in which the database is not auto-parameterizing queries

Database Configuration

• FORCED parameterization can slow down databases as well due to a bloated plan cache or reusing sub-optimal plans • See this article for more: – http://msdn.microsoft.com/en-us/library/ms175037.aspx

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Configuring Databases

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Database Configuration

• There are 6 categories of configuration – Automatic – Cursor – Miscellaneous – Recovery – Service Broker – State

Database Configuration

• Settings in the “Recovery” Category control how SQL Server verifies the contents on a “page” – SQL Server stores data and indexes on “pages” – The “Page Verify” option tells SQL Server how to check that the data/index page is not corrupt – Data can become corrupt due to incomplete I/O writes and/or disk errors

Database Configuration

• “Page Verify” has three choices: – CHECKSUM (the default) • SQL Server computes a checksum of what the page’s checksum would be if the wrote was successful • After the write, SQL Server compares the checksum values • If they are not the same, the page is logged in msdb’s suspect_pages table

Database Configuration

• “Page Verify” has three choices (cont.): – TornPageDetection • SQL Server writes a “flag” bit for every 512 bytes • By counting the “flags”, SQL Server can determine if the page has the correct number of bytes • However, it cannot determine that the data is corrupt inside of the 512 bytes – It just counts that 512 bytes were written

Database Configuration

• “Page Verify” has three choices (cont.): – None • No page verification is done

• Suggestion: Leave the default setting asis (CHECKSUM)

Database Configuration

• There are 6 categories of configuration – Automatic – Cursor – Miscellaneous – Recovery – Service Broker – State

Database Configuration

• Settings in the “Service Broker” Category control… well, the Service Broker • Service Broker allows for messaging and queuing applications

Database Configuration

• There are 6 categories of configuration – Automatic – Cursor – Miscellaneous – Recovery – Service Broker – State

Database Configuration

• “State” controls whether SQL Server database is – Online or Offline • i.e. accepting connections or not

– Read-Only or Read-Write

• Databases default to Online, Read-Write

Database Configuration

• “State” also controls whether SQL Server is – Single-user, Multi-user or “Admins Only” • “Admins Only” is technically called RESTRICTED_USER

– Whether “Transparent Data Encryption” is enabled • More on this later (requires more setup)

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

An Overview of Indexes and Partitioning Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Indexes

• SQL Server has three types of indexes • (or “indices” if you prefer)

– Clustered indexes – Nonclustered indexes – Full-Text indexes • Not covered in this course

Indexes

• Most tables have both clustered and nonclustered indexes – Only tables that store large/wide text columns that need frequent searching would use Full-Text

• The purpose of an index is to speed up searches

Indexes

• Queries use “Search Arguments” or SARGs SELECT PhoneNumber FROM PhoneBook WHERE LastName=‘Whigham’ AND FirstName=‘Scott’

• To satisfy the SARGs, it helps if the data is sorted in LastName, FirstName order

Indexes

• A clustered index is really how the actual data is stored – A clustered index on “LastName, FirstName” means that the actual data on the page is sorted in alphabetical order by LastName, FirstName

• When you create a primary key, the default is to create a clustered index on that key

Indexes

• Consider this query: SELECT LastName, FirstName FROM PhoneBook WHERE PhoneNumber = ‘+1(214) 555-1212’

• Would having the data sorted by LastName, FirstName order help this query?

Indexes

• Solution: create a nonclustered index on PhoneNumber! – A nonclustered index is a copy of the index keys and the primary key sorted by the index keys – Now a copy of the PhoneNumber column is created along with a pointer back to the primary key

Indexes

• Indexes use a fill factor to determine how full to create the page – A fill factor of 50% means that the page will be created 50% full (thus leaving 50% for new rows to be written to the page)

Indexes

• A full index/data page cannot have any more data written to it – Imagine a phone book page full of “Smith” entries – No room to write any more – Next page is a full page of “Sullivans”

• A new “Smith” moves to town – How does this record get added to the PhoneBook table?

Indexes

• Solution: A Page Split! – When a page split occurs, a new page is created (somewhere on the disk) – Half of the original page’s data is “cut and pasted” onto this new page along with the new row

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

An Overview of Indexes and Partitioning Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Indexes

• Page splits cause poor performance in multiple ways – The time it takes to allocate a new page (and maybe even having to grow the database) – The time it takes to “cut and paste” (and log) – The pages are not contiguous any longer thus causing more work at the disk level

• This is called “Index Fragmentation”

Indexes

• Solution: You need to Rebuild or Reorganize Your Indexes Periodically – Called “Defragmentation” – Speeds up performance

Indexes

• An index “REBUILD” creates the best performance • SQL Server re-creates the index internally again • Once complete, the existing index is dropped • There is a period of time where the index/data is unavailable (“locked”) • No pages are split after running – all are contiguous

Indexes

• An index “REORGANIZE” does a “best effort” defragmentation but leaves the data online • Reorganizes the “leaf nodes” of the index • Some fragmentation may still exist since it does not fix page splits

Indexes

• To rebuild an index, use the ALTER INDEX command -- Rebuild all indexes on table ‚Members‛ ALTER INDEX ALL ON Members REBUILD WITH(ONLINE = ON) -- ONLINE=ON keeps the table available -- during the rebuild process

Indexes

• To rebuild an index, use the ALTER INDEX command -- Rebuild only the primary key index ALTER INDEX PK_Members ON Members REBUILD WITH(ONLINE = ON, FILLFACTOR=50) -- Keeps data available but uses a new -- fill factor of 50%

Indexes

• To reorganize an index, use the ALTER INDEX command -- Rebuild only the primary key index ALTER INDEX PK_Members ON Members REORGANIZE -- Reorgs are always online operations

Indexes

• Index defragmentation needs to be part of your normal maintenance – How often you do each requires a thorough understanding of your table data and usage

• You will create jobs and maintenance plans to do this work using Transact-SQL scripts

Indexes

• Scenario: “Members” table adds 10,000 new rows per week and is heavily read – Primary key is an auto-incrementing IDENTITY – Fill factor for all indexes is 75% – One primary key (on IDENTITY column) – Six nonclustered indexes to cover common queried columns

Indexes

• Should we rebuild or reorg? – Wrong question! – Start with the idea that you want to rebuild but will “fall back” to a reorg in the event that the table is too heavily used • In this event, scheduled maintenance windows (if possible) can be the time that you do rebuilds

Indexes

• Frequently Asked Questions – How often should I defrag? • As often as possible without affecting users • When the table becomes fragged!

– Should I rebuild/reorg all indexes at once or do them individually? • It’s easier management-wise to do all at once • Large indexes/tables may require separate operations

Indexes

• Let’s do a demo!

An Overview of Indexes and Partitioning Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Partitions

• Large tables often have data that is used in different ways – An “Orders” table might have 2-10 years of data – Some data is heavy read/write while other data is read-only

Partitions

• With large tables – Index maintenance can take hours – Queries against large tables can take a long time • In a table of 200,000,000 rows, how long would it take to find all orders placed on Jan 1, 2001 at 23:43:23.243?

Partitions

• Large tables can be partitioned into smaller, more manageable subsets – An “Orders” table can be partitioned into one partition per year – A query for rows WHERE OrderDate = ‘Jan 21, 2001’ now only needs to access a single partition

• Indexes can be partitioned as well

Partitions

• Partitions allow you to create subsets of the data and to place them strategically – Partitions can be spread across multiple filegroups – Queries only need to access the partition(s) that serves their data

Partitions

• Partitions can be spread across multiple filegroups – Some partitions can be read-only while others are read-write – Can backup more heavily-written-to partitions more frequently

Partitions

• Rows are partitioned horizontally – This means that the entire row is in a single partition

• Time is a common partitioning strategy – One partition for each year – One partition for each quarter – One partition for each month

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

An Overview of Indexes and Partitioning Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Partitions

• Creating partitions requires: – A Partitioning Function – A Partition Scheme – A table/index

Partitions

• Partitioning Functions define “what goes where” – You define boundaries and ranges

• Boundaries define the number of partitions • Ranges define how the boundaries are defined

Partitions

CREATE PARTITION FUNCTION ScottsPF (INT) AS RANGE RIGHT FOR VALUES (2007, 2008)

Partitions

• This partition function can be used for any table/index that has an INT column • Three partitions will be created above • RANGE defines how the partitions are defined

Partitions

RANGE RIGHT FOR VALUES (2007, 2008) Partition 1

Partition 2

Partition 3

WHERE TheYear < 2007

WHERE TheYear >= 2007 WHERE TheYear => 2008 AND TheYear < 2008

TheYear is an INT column in the above table

Partitions

RANGE LEFT FOR VALUES (2007, 2008) Partition 1

Partition 2

WHERE TheYear <= 2007 WHERE TheYear > 2007 AND TheYear <= 2008

Partition 3 WHERE TheYear > 2008

Partition Schemes

• A Partition Scheme maps the partition function(s) to a filegroup(s) – Partition functions can belong to multiple partition schemes – One partition scheme can only contain one partition function

Partitions

CREATE PARTITION FUNCTION ScottsPF (INT) AS RANGE RIGHT FOR VALUES (2007, 2008) GO CREATE PARTITION SCHEME MyScheme AS PARTITION ScottsPF TO (myFilegroup1, myFilegroup1, myFilegroup2)

Partition Schemes

• The preceding sample mapped the three partitions from “ScottsPF” to two different filegroups TO (myFilegroup1, myFilegroup1, myFilegroup2)

• Partition schemes can also map all partitions to the same filegroup ALL TO (myFilegroup1)

Partition Schemes

• You can partition tables and/or indexes – If you partition the table, the indexes are partitioned by default (unless otherwise specified during index creation)

Partitions

CREATE PARTITION FUNCTION ScottsPF (INT) AS RANGE RIGHT FOR VALUES (2007, 2008) GO CREATE PARTITION SCHEME MyScheme AS PARTITION ScottsPF ALL TO (‘default’) GO CREATE TABLE MyTable (MyID INT, TheYear INT) ON MyScheme(TheYear)

Partition Schemes

• Let’s do a demo!

Managing Database Files

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Managing Database Files

• We’ve already seen how to grow databases – By adding space to an existing file – By adding additional data or log files

• Now it’s time to see how to make our “footprint” smaller!

Managing Database Files

• Up first: Removing a Database • When you remove a database, you are removing – Users – Data (tables, indexes) – Stored procedures, functions, triggers, etc

Managing Database Files

• Removing a database deletes the files – Once a database has been deleted, it cannot be un-deleted! • Edit -> Undo will not help!

• You cannot delete a database that is in use

Managing Database Files

• To delete a database in the GUI, right click on the database and select Delete • To delete a database using Transact-SQL: DROP DATABASE MyDb

Dropping a database

Managing Database Files

• To protect ourselves from dropping databases, we employ two strategies: – Backups – Scripts

• We’ll look at backups in an upcoming chapter • Let’s look at scripts now!

SQL Server Scripts

• Sql scripts can do almost anything – Create, alter, drop databases – Create, alter, drop tables, functions, stored procs – Insert, update, delete rows

• All “real” DBAs use scripts • Scripts are typically stored in .sql files

SQL Server Scripts

• Scripts can be executed – In SSMS – Using sqlcmd.exe – Inside of any custom or 3rd party app that can connect to SQL Server – In batch files (that call sqlcmd) – SQL Server Management Objects (SMO) – PowerShell • Which can handle it’s own scripts and sql scripts!

SQL Server Scripts

• You can also generate scripts – Create, alter, drop databases – Create, alter, drop tables, functions, stored procs – Insert, update, delete rows

• Script generation can be done – In SSMS – “By Hand”

SQL Server Scripts

• Let’s demo!

Managing Database Files

Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

Managing Database Files

• Shrinking databases is a sometime necessity • You can shrink – Individual data files or log files – An entire database (all files)

• Shrinking can be done with the GUI, TSQL, or any of the other methods

Managing Database Files

Shrinking a Database

• There are two options for shrinking an entire database 1. Remove all free space after the last page • PROs: Fastest, least impact • CONs: Not likely to remove as much as the 2nd option

Shrinking a Database

• There are two options for shrinking an entire database (cont.) 2. Reorganize the entire database to compress the pages at the “front” of the file, then remove free space • PROs: Removes maximum amount of free space, can specificy a “Target Percentage” of free space (“I want 10% free space left in file”) • CONs: Slow, performance hog

Managing Database Files

Shrinking a Database

• To shrink a database using Transact SQL, use DBCC SHRINKDATABASE DBCC SHRINKDATABASE ( database_name [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] )

Shrinking a Database

• NOTRUNCATE – Compress pages to “front” of file – Does not release freed space to OS – Only available in T-SQL

• TRUNCATEONLY – Does not compress the pages to the front of the file – Releases freed space after the “last” page to the OS – Default in SSMS

Shrinking a Database

-- TRUNCATEONLY: DBCC SHRINKDATABASE(N'LearnItFirst.com') -- TARGET SIZE of 10% free space: DBCC SHRINKDATABASE(N'LearnItFirst.com‘,10) -- NOTRUNCATE: DBCC SHRINKDATABASE(N'LearnItFirst.com', NOTRUNCATE)

Shrinking a Database

• Shrinking individual files offers more flexibility – Can specify target sizes, truncation options or empty individual files DBCC SHRINKFILE ( { logical_file_name | file_id } { [ , EMPTYFILE ] | [[ , target_size ][ , {NOTRUNCATE | TRUNCATEONLY }]]} )

Shrinking a Database

• EMPTYFILE – Moves pages to another file in the file group

• target_size – Cannot compress data – Defaults to MB – If not specified, uses the creation size

Shrinking a Database

• Let’s demo!

Suggested Changes After the Upgrade Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

After the Upgrade

• After your upgrade is complete, there are still critical steps 1. 2. 3. 4. 5. 6.

Patching the new SQL Server installation Verifying/changing configuration settings Testing Implement any changes that testing uncovered Testing Acceptance/sign-off

After the Upgrade

• After your upgrade is complete, there are still critical steps 1. 2. 3. 4. 5. 6.

Patching the new SQL Server installation Verifying/changing configuration settings Testing Implement any changes that testing uncovered Verifying configuration settings Acceptance/sign-off

After the Upgrade: Patching

• It’s likely that, after your upgrade, there are hotfixes, patches, service packs, etc available • It is often difficult to determine which is needed – How do you know you are experiencing a bug until you test your server?

After the Upgrade: Patching

• Remember that: – Service packs are the most-tested – Cumulative Updates are the next-most-tested – Critical Updates are the least-tested

• You don’t have to install the most recent updates – Unless there is a specific security issue

After the Upgrade

• After your upgrade is complete, there are still critical steps 1. 2. 3. 4. 5. 6.

Patching the new SQL Server installation Verifying/changing configuration settings Testing Implement any changes that testing uncovered Verifying configuration settings Acceptance/sign-off

After the Upgrade: Compatibility

• The first and most obvious thing to understand is compatibility level • SQL Server allows a database to use one of three compatibility levels – 80 (SQL Server 2000) – 90 (SQL Server 2005) – 100 (SQL Server 2008)

After the Upgrade: Compatibility

• Changing a database’s compatibility level provides partial backward compatibility – Setting a database’s compatibility level to 80 makes that database behave as though it was on a SQL Server 2000 instance – Allows you to upgrade to SQL Server 2008 without making breaking application changes

After the Upgrade: Compatibility

• When you upgrade a database/instance, the previous version remains as the compatibility level – If you upgrade “Northwind” from SQL Server 2000 to 2008, the Northwind database remains in SQL Server 2000 compatibility level • All other databases on SQL Server 2008 are in SQL Server 2008 compatibility level

After the Upgrade: Compatibility

• Let’s demo!

Suggested Changes After the Upgrade Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

After the Upgrade

• After your upgrade is complete, there are still critical steps 1. 2. 3. 4. 5. 6.

Patching the new SQL Server installation Verifying/changing configuration settings Testing Implement any changes that testing uncovered Verifying configuration settings Acceptance/sign-off

After the Upgrade

• If you upgraded from SQL Server 2005 – There are no database-level configuration changes needed

• If you upgraded from SQL Server 2000, you might want to make changes

After the Upgrade

ALTER DATABASE ‘AdventureWorks’ SET ANSI_NULL_DEFAULT ON , ANSI_NULLS ON , ANSI_PADDING ON , ANSI_WARNINGS ON , ARITHABORT ON , AUTO_CREATE_STATISTICS ON , AUTO_UPDATE_STATISTICS ON , NUMERIC_ROUNDABORT ON , PAGE_VERIFY CHECKSUM

After the Upgrade

• After your upgrade is complete, there are still critical steps 1. 2. 3. 4. 5. 6.

Patching the new SQL Server installation Verifying/changing configuration settings Testing Implement any changes that testing uncovered Verifying configuration settings Acceptance/sign-off

After the Upgrade

• What should you test? – Have the developers unit test any applications using the database • Not always possible with 3rd party apps

– Reports should be verified to ensure that they run – SQL Server jobs should be run to ensure they still “work” as expected – DTS and/or SSIS packages

After the Upgrade

• Known trouble areas – SQL Server jobs • If jobs point to specific files/folders, verify that – SQL Server service(s) have access – The file/folder paths have not changed or, if they have, change job definition

– DTS to SSIS – Database compatibility

After the Upgrade

• After your upgrade is complete, there are still critical steps 1. 2. 3. 4. 5. 6.

Patching the new SQL Server installation Verifying/changing configuration settings Testing Implement any changes that testing uncovered Verifying configuration settings Acceptance/sign-off

After the Upgrade

• Once the DBA has completed his/her testing, recruit other departments – Developers test “plumbing” – Quality Assurance (“QA”) tests application compatibility – System administrators test backup routines

The SQL Server 2008 Sample Databases Presented by Scott Whigham

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

What We’re Going to Cover

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading

The SQL Server 2008 Sample Databases

• SQL Server 2008 does not ship with any sample databases – SQL Server 6.x shipped with the pubs database – SQL Server 7.0 and 2000 shipped with pubs and Northwind – SQL Server 2005 shipped with AdventureWorks and AdventureWorksDW

The SQL Server 2008 Sample Databases

• There are sample databases for SQL Server 2008 – You must be smart enough to figure out • • • •

What they are called How to download them How to install them Which to use for what purpose

The SQL Server 2008 Sample Databases

• Let’s answer each question individually : – What they are called • The sample databases are called collectively “AdventureWorks” • They are the sample databases for the fictitious AdventureWorks Cycling Company

What is AdventureWorks

• The AdventureWorks Cycling Company – Sells through internet and brick & mortar channels – Covers commonly used patterns for working with products, sales, contact management and more

The SQL Server 2008 Sample Databases

• AdventureWorks stores roughly three years worth of sales data in: – An OLTP database – An OLAP database

The SQL Server 2008 Sample Databases

• How do you get the sample databases? – The files are available at http://www.codeplex.com/MSFTDBProdSamples – Shameless marketing from MSFT

The SQL Server 2008 Sample Databases

• How do you get them onto your SQL Server? – One of three ways: • Install from .msi • Restore from backup • Run the scripts

– Good luck!

The SQL Server 2008 Sample Databases

• How do you know which to use for what purpose? – There are three different types of databases: • OLTP, OLAP and a “Light” version of the OLTP

The SQL Server 2008 Sample Databases

• This is where it gets confusing… – Each database has several names depending on where you look – There are as many as three different “versions” of each database in the default installation – There are many different files you can download once you actually figure out where to look for it

The SQL Server 2008 Sample Databases

• A look at the default install using .msi installation:

The SQL Server 2008 Sample Databases

• On the CodePlex website, they make mention of three databases in the AdventureWorks suite: – AdventureWorks OLTP – AdventureWorks OLAP – AdventureWorks LT (Light

The SQL Server 2008 Sample Databases

• AdventureWorks OLTP actually goes by many names – AdventureWorks OLTP in documentation/website – AdventureWorks OLTP and AdventureWorks 2008 OLTP in the downloaded .zip file – AdventureWorks and AdventureWorks2008 in installation

The SQL Server 2008 Sample Databases

• AdventureWorks OLAP also goes by many names – AdventureWorks OLAP in documentation/website – AdventureWorks 2008 Data Warehouse in the downloaded .zip file – AdventureWorksDW and AdventureWorksDW2008 in installation

The SQL Server 2008 Sample Databases

• AdventureWorks LT (Light) is the fewertables version of AdventureWorks OLTP – Meant to be simpler to learn and train people on

The SQL Server 2008 Sample Databases

• Which should I use when? – “I’m a total beginner to SQL Server” • AdventureWorksLT

– “I want to learn about the new datatypes and/or new features in SQL Server 2008” • AdventureWorks2008

– “I want to learn about data warehousing” • AdventureWorksDW

– “I want to learn about the new features in SQL Server 2008 data warehousing” • AdventureWorks2008DW

• Creating Databases • Adventure Works • Configuring Database Options

Management

• Overview of Chapter • How Databases Work in SQL Server • The System Databases • Types of Databases

Creation and Demo

Overview

In the next video…

• Indexes and Partitioning • Managing Database Files • What to Do After Upgrading