Long-Term Learning
Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
Physical Design
The process of producing a detailed database specific data model to meet end user requirements.
Conceptual Database Design
Constructing a data model for dealing with a real world issue without giving any physical considerations.
ER Modeling
Developing a visual representation of the real world problem as an ERD and have the details modeled in terms of entities, attributes and relations.
Logical Database Design
Design that requires the construction of a model of information that can be mapped into the various SQL Server objects.
First normal form (1NF)
____ require that you eliminate duplicative columns from the same table and create separate tables for each group of related data, and identify a primary key
Second normal form (2NF)
____ requires that you remove all subsets of data that apply to multiple rows of a table and relocate them in other separate tables, and create foreign keys
Third normal form (3NF)
____ requires that you remove all those columns that are not dependent on the primary key.
Denormalization
____ requires data redundancy by can reduce the number of joins to be use during query
Hardware
Network infrastructure
OS configuration
Server side applications
Client side applications
____ are factors that can affect SQL Server performance.
Database Engine Tuning Adivisor DTA
Toll that can examine how queries are processed and then recommend how you may improve the query processing performance.
Resource database
Use for holding system objects that are included with SQL Server and is read only.
User database
You should have the master database backed up whenever a ____ is created, changed or dropped.
sys.backup_devices
sys.database_files
sys.database_mirroring
sys.database_recovery_status
sys.databases
sys.master_files
The views related to databases and files are ____.
full, model database
Create only ____ database backups of the ____ as required since it rarely changes.
Instance, master database
To restore, the ____ must be running and the ____ must be accessible and partly or full usable.
ALTER DATABASE database_name MODIFY FILE
Stop the instance
Move the file
Restart the instance
To move a system database data or log file to a new instance you:
1. Run ____
2. Then ____ (NAME = logical name, FILENAME = 'path')
3. ____
4. ____
SQL Server's Filegroups
A feature that uses a proportional fill mechanism across all the files within each filegroup.
Database Engine, expand one file
When all the files in a filegroup are full, the ____ will automatically ____ at a time.
Primary file, model database
When size is not supplied, the size of the ____ will be set to the size of the file in the ____.
Secondary data file, log file
When size is not supplied for the ____ or ____ the default will be 1 MB.
Primary file group
Serves as the default group when database is created. Should only be used to keep system objects, and not as the default file group.
Create a new filegroup
Mark as Default
Create new data file (.ndf)
To use a secondary data file you need to ____, ____, and then ____.
ALTER DATABASE statement options
ADD FILE
... TO FILEGROUP
REMOVE FILE
MODIFY FILE
ADD FILEGROUP
REMOVE FILEGROUP
MODIFY FILEGROUP
Host one file per disk.
When using physical disk drives you should create multiple physical files per file group and ____ if possible.
Text, image columns
____ and ____ should be placed in a different file group on a different physical drive since they are I/O heavy.
Single User Mode
Database mode that allows for only a single user to be connected. All other users will be cut off without warning when set.
AUTO_UPDATE_STATISTICS_ASYNC, OFF
Before setting SINGLE_USER mode you must ensure that ____ option is ____.
Local temporary tables
Maintained only in the current session and it's name should always be prefixed with a #. Dropped automatically at the end of the current session.
Global temporary tables
Maintained across all the active sessions and it's name should always be prefixed with ##. Dropped automatically when session that created the table is ended.
stored procedure, DROP TABLE
All temporary tables will be automatically dropped when the ____ is finished or via the ____ command.
ALTER DATABASE, MODIFY FILE
You can adjust the file sizing by using ____ and then ____ T-SQL statements.
SET OFFLINE, SET ONLINE
____ and ____ options of ALTER DATABASE allows you to take the instance offline or bring it back online.
Data, Transaction log
____ and ____ files can be detached and attached to another instance. This makes a database easier to move.
Read-only database
Detaching a ____ will cause you to lose information about the differential bases of your differential backups.
CREATE DATABASE, FOR ATTACH
To attach a database make sure all MDF and NDF files are available and use the ____ with the ____ close.
Contained Database
A database that is isolated from other databases and the instance of SQL Server that hosts it.
Partially Contained Database
A database that allows some but not all features that cross the database boundary.
Contained database users, Windows users
Contained databases can be accessed by ____ with passwords authenticated by the database, and authorized ____ and Windows groups who connect directly to the database without logins in the master database.
Media family
Backups created on a single non-mirrored device or a set of mirrored devices within a media set.
Cannot, earlier
Backups created by recent version of SQL Server ____ be restored to an ____ version.
Full, differential
You should supplement a ____ database back up with a series of ____ database backups.
Differential, modified
The ____ backup is useful when a subset of data is ____ more often then the rest of the database.
Backup compression
A server-level configuration option that is set to off by default. Works to compress the backups.
Recovery models
These determine how much data loss is considered acceptable after a failure and what types of related functions are allowed.
Simple model
Recovery model that does not have log backups. Log space is reclaimed to keep requirements small. Can only recover to the end of a backup.
Full model
Recovery model that requires log backups and there will be no data loss. Possible to restore to a specific point in time.
Bulk-logged model
Recovery model that requires log backups. Permits high-performance bulk copy operations. It is not possible to restore to a specific point in time.
Complete database restore
The whole database is made offline for the duration and all data is recovered to a point where no uncommitted transactions exist.
Tail-log backup
Special backup that captures the log records that have not been backed up in other ways. Is needed to recover SQL Server to the latest point in time.
Maintenance Plan
Used to create Integration Services package to be run by an SQL Server Agent job. Must be in the sysadmin role to create or manage.
Maintenance Plan Tasks
Back up Database
Check Database Integrity
Execute SQL Server Agent Job
Execute T-SQL Statement
History Cleanup
Maintenance Cleanup
Notify Operator
Rebuild Index
Reorganize Index
Shrink Database
Update Statistics
Multiserver, master
To create a ____ maintenance plan there must be one ____ server with one or more target servers and it must be created and maintained on the master server.
TABLOCK
Option that specified that a table-level lock is used for the duration of the entire bulk-import operation.
BCP Utility
Used to bulk copy data between an instance and a data file in a separate user-specified format.
Character format, third party program
You should use ____ when bulk exporting data to a text file, or bulk importing data from a text file generated by a ____.
SQL Server Profiler
Records events as they occur. Through a trace you can look at the commands that have been executed against the database.
Event Classes
Broken down into categories, they provide different monitoring abilities in the traces.
Errors and Warnings event category
Has event classes produced when a SQL Server error or warning is raised.
Sessions event category
Has event classes produced when clients connect to and disconnect from an instance
Standard template
Collects general information on SQL Server connections, stored procedures and T-SQL Statements.
TSQL_Replay template
Gathers information of each T-SQL statement for recreating the same activity in the future.
Database Engine Tuning Advisor
Used to select and create an optimal set of indexes, indexed views, and partitions.
Workload
A set of T-SQL statements that execute against a database using trace files, trace tables, and/or T-SQL scripts.
sysadmin fixed, db_owner
The ____ server role is required to initialize it. After the initialization users who are members of the ____ fixed role can use it to tune the databases they own.
Workload analysis
____ can be performed against the master, model, msdb, ReportServer, ReportServerTempDB, and tempdb databases.
Advanced, max space
In the ____ options box you can define the ____ for recommendations or by the physical design structures.
3 times, free
If no value is specified, the Advisor will assume a space limit of ____ the current raw data size, OR the ____ space on all the attached drives in addition to raw data size.
Foreign keys
A constraint linked to a primary key in another table. Can also use columns that use UNIQUE.
Foreign key, self-referencing
When a ____ constraint references columns in tables in the same database or table, the table becomes a ____ table.
Check
Used to enforce domain integrity. Use when you want to limit the values that can be entered into a column.
Default
Use to insert a defined value into the column whenever there is not value specified. Can be used with INSERT to explicitly insert a default value.
UNIQUE argument
Means it will be a unique index. Cannot be created on columns with duplicate values.
CLUSTERED index
An index that has a logical order of key values which determines the physical order of the corresponding rows. Each table or view can have only one.
NONCLUSTERED index
An index where the physical order of the rows is totally independent of their indexed order. Can be unique or non-unique.
INCLUDE argument
Used to specify the non-key columns to be added to the leaf level of a nonclustered index.
Large object, cannot
____ date types such as ntext, varchar(max), xml and image ____ be used as the key columns for an index.
Primary key, automatically
If you create a ____, a unique clustered index on the column will be ____ created.
Fill-factor
Used to specify the percentage of space on each leaf-level page for data when rebuilding or creating an index. Server-wide default value is 0.
sysadmin, db_ddladmin, db_owner
To change the fill factor you should be a member of the ____ fixed server role, or the ____ and ____ fixed database roles.
Fragmentation
Exists when your indexes have pages in which the logical ordering does not match the physical ordering inside the physical file.
30%, ALTER INDEX REORGANIZE
A value between 5% and ____ is when you should use ____ to reorganize the index
30%, ALTER INDEX REBUILD
A value above ____ is when you should rebuild the index entirely using ____ WITH(ONLINE = ON)*.
Filtered Index
A non-clustered index optimized with a filter predicate to index only a portion of rows in a table.
XML, should, CREATE PRIMARY XML INDEX
____ columns are stored as binary large objects and ____ be given an index. Use ____ to produce a primary XML index.
Value XML index
Secondary XML index useful for performing value-based queries when the full path is not known.
Frequently, unique clustered
If a view is ____ referenced, you should create a ____ index on the view so the result can be stored like a table.
aggregations, write
Queries that have few ____ or joins would not be suitable for indexed view, and ____ intensive transactions would not be appropriate.
ANSI_NULLS, QUOTED IDENTIFIER
To create an indexed view ____ and ____ options must be ON when you run CREATE VIEW using WITH SCHEMABINDING.
Disable, clustered
If you ____ a ____ view it will be disabled, and the associated nonclustered indexes with get automatically deleted until index is dropped and rebuilt.
ALTER INDEX, DISABLE
During upgrades the Database Engine will automatically disable an index, but to use T-SQL statements you would use ____ with the ____ option.
Data type, partitions, boundary
The arguments of the partition function must have the same ____, define the same number of ____ and use the same ____ values.
ALTER INDEX, REBUILD
Use ____ with the ____ option to have an index rebuild using the same columns, index type, uniqueness attribute and sort order.
DBCC DBREINDEX
Rebuilds a clustered index but does not rebuild the associated nonclustered indexes unless you use the keyword ALL.
offline, shared lock, exclusive table
Since DBCC DBREINDEX is an ____ operation, when a nonclustered index is being rebuilt a ____ will be held on the table preventing modifications. When a clustered index is being rebuilt an ____ lock will be held, preventing table access entirely.
Pad Index
When enabled the percentage of free space that is specified by the fill factor is applied to the intermediate level pages of an index. It represents the same thing as fill factor for non-leaf levels.
Fill Factor
The values that represents the percentage to fill the data page files on index creation at the index leaf-layer.
More, SELECT
When you have ____ indexes, ____ queries will run faster, but INSERT, UPDATE, and DELETE will run slower.
Primary, automatically
When you have ____ and Foreign key constraints SQL Server will ____ create indexes for them.
Query Execution Plan
How SQL Server attempts to determine the best way to execute a submitted query.
Estimated execution, include actual
The Management Studio GUI lets you see the Query Execution Plan by selecting either the Display ____ Plan or ____ Execution Plan.
Execution Plan statements
SET SHOWPLAN_XML ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET STATISTICS XML ON
SET STATISTICS PROFILE ON
Display Estimated, SHOWPLAN_XML
The ____ execution plan is the same as using the SET ____ statement.
SET SHOWPLAN ON
SQL Server will return the execution information for each statement without actually executing it.
SHOWPLAN permission
A database level permission that is required on all databases referenced in order to execute SHOWPLAN statements.
Cannot, stored procedures
The SET SHOWPLAN statements ____ be used inside a ____ since they must be the only statement in a batch.
SET SHOWPLAN_TEXT
Used to produce readable output that is to be used by Win32 command prompt applications.
SET STATISTICS XML
Used to allow SQL Server to execute the T-SQL statements while generating in-depth information on how the statements were executed.
SET STATISTICS PROFILE
When on, each executed query will return a regular result set and then an additional result set that details the profile of the query execution.
SHOWPLAN SQL Server Profiler event classes
Showplan XML
Showplan ALL
Showplan Text
Showplan XML Statistics Profile
Showplan Statistics Profile
sp_show_statistics
Used to evaluate the selectivity of an index. Shows the current distribution statistic for a particular table and index, or for all tables and indexes.
STALE
Indicated on a result set. Means the query optimizer determined the statistic needs to be updated.
Multiple-column index
Used to evaluate filter expressions that are intended to match a prefix set of columns. Always put the most selective columns leftmost in the key.
Stored procedure, optimized
Put the queries in a _____ and SQL Server can usually come up with an ____ execution plan.
CREATE PARTITION FUNCTION
Creates a partition function and specifies the number of partitions, partition column and the range of partition column values for each partition.
Shared locks
Allows concurrent transactions to SELECT a resource under pessimistic concurrency control. During an update a transaction will attempt to change to an exclusive lock.
Exclusive, deadlock
If two or more transactions are trying to convert to ____ locks, a ____ will occur.
Exclusive lock
No other transactions can change data and read operations can carry on only with the presence of a NOLOCK hint or read uncommitted isolation level.
Intent locks
Used to single an intent to place locks at lower level. They can be intent shared, intent exclusive, or shared intent exclusive.
SQL Server:Locks
An object that provides information about SQL Server locks on resources that prevent concurrent use by different transactions.
Lock Timeouts
Tells the number of lock requests per second that timed out, not including NOWAIT locks.
Lock Timeouts/sec
Tell the number of lock requests per second that timed out including NOWAIT locks.
Table-level locking hints
Overrides SQL Server's locking scheme by forcing locks on a table.
FASTFIRSTROW
HOLDLOCK
NOLOCK
PAGLOCK
READCOMMITTED
READPAST
READUNCOMMITTED
REPEATABLEREAD
ROWLOCK
SERIALIZABLE
TABLOCK
UPDLOCK
XLOCK
NOLOCK
Used to NOT issue any shared locks or recognize exclusive locks. Use only with the SELECT statement.
UPDLOCK
Used to use update locks instead of shared locks when you read a table, and then hold the locks until the end of the transaction.
READ_COMMITED_SNAPSHOT
If OFF, shared locks will be used to prevent other transactions from modifying rows while the current transaction is reading, and block the statement from reading row modified until completed. If ON, row versioning will be used to present each statement with a consistent snapshot of the data.
SQL Server:Latches
An object that provides counters for monitoring internal SQL Server resource locks.
Locks, deadlocks
____, worker threads, memory, parallel query execution-related resources and Multiple Active Results Sets resources can all cause ____.
Deadlock graph
Event class that populates the TextData data column of the trace with data on process and objects involved in the deadlock.