Database Mail Catalog views

Database Mail Catalog views display metadata about the status of e-mail messages processed by Database Mail.

USE MSDB
select * from sysmail_allitems

Contains a row for each attachment submitted to Database Mail.

USE MSDB
select * from sysmail_mailattachments

Contains returned message details for Windows or SQL Server

USE MSDB
select * from sysmail_event_log

Contains a row for each message sent successfully

USE MSDB
select * from sysmail_sentitems

Contains one row for each failed message

USE MSDB
select * from sysmail_faileditems

Contains row for Messages with unsent or retrying status which are still in the mail queue

USE MSDB
select * from sysmail_unsentitems

SQL Server Version Numbering

This defines the product version numbering format for SQL Server. It is MM.nn.bbbb.rr which is defined as:
MM - Major version
nn - Minor version
bbbb - Build number
rr - Build revision number

Taxonomy

Taxonomy is the science of classification. It comes from the Greek taxis meaning arrangement or division and nomos meaning law. It provides a conceptual framework for discussion, analysis, or information retrieval. It arranges hierarchically, elements of a group (taxon) into subgroups (taxa) that are mutually exclusive. Each group has the same properties, behaviours, and constraints. And each increasing level has further properties, behaviours, and constraints.

In biological terms this would be the classification of species.

Fragmentation

Fragmentation is the storing of data non-contiguously on disk.

Detecting Fragmentation

The level of fragmentation can be determined by running the Dynamic Management View (DMV): sys.dm_db_index_physical_stats.
To return information for all databases detailing all statistics for all tables and indexes within the instance of SQL Server run

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

A couple of the DMV columns are defined below

avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.. The percent of logical fragmentation (out-of-order pages in the index). The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.

avg_page_space_used_in_percent : Average percentage of available data storage space used in all pages. Higher the value, the better it is. A value is lower than 75%, some corrective action should be taken.

Reducing Fragmentation

After the degree of fragmentation is identified from sys.dm_db_index_physical_stats, a guideline on the course of action to be taken is listed below

avg fragmentation in percent	Suggested Action
> 5%	No action required
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON) can be executed online or offline. A replacement for DBCC DBREINDEX

Filestream

There are various types of data

• Structured Data is data which is stored in a relational database

• Semi Structured Data is often of xml type and can be stored in the database

• Unstructured Data is such as images, video etc

Unstructured Data or binary large object (BLOB) data needs to be reviewed to decide where it is stored. BLOB’s smaller than 256kb are better stored inside a database and BLOB’s larger than 1 MB, for filestream, are best stored outside of the database. Storing blobs in the database using the data type varbinary(max) is limited to 2GB per blob.

Filestream allows unstructed data to be stored in the NTFS file system in directories called data containers which are listed as filegroups in the database. This allows transactional consistancy between structured and unstructured data and allows point in time recovery. Deleting or renaming or any filestream files directly in the file system will corrupt the database. Each row has a unique row ID. Fragmentation can occur on the NTFS file system and regular defragmentation is required. Streaming of data through the filestream is more efficent than through the conventional database set up however backing up the database with filestream files is slower than just backing up the database. Each Filestream data container may need to be on it’s own volume to stop data contention. Data stored on the NTFS file system can be compressed but it is expensive if the data is uncompressable. Filestream requires integrated security and filestream cannot block antivirus scanning. If a file is infected it is best to quarantine the file and use DBCC CHECKDB to identify the missing file. Filestream has a garbage collection process to remove files no longer required, which is automatic, when the checkpoint process runs.

Filestream has to be enabled at the windows level before the SQL Server level.

Filestream data can not be encrypted. Database Mirroring does not support filestream although failover clustering does.

Data Collection Security

There are 3 roles

Mdw_admin
Grants read and write to the Management Data Warehouse (MDW). It allows users to purge and clean up jobs to manage the amount of data
Mdw_reader
Grants users with read only access
Mdw_writer
Allows write and upload of data to the MDW

Data Collection

This product is a development of performance dashboard, database reports and built on top of standard DMV's. Historical data is persisted and is not affected by service restarts.

Plan for data growth of up to 250 - 500 MB a day on the Management Data Warehouse (MDW). The default Data Retention Period in MDW is 4 weeks . Microsoft detected approximately 3 - 4% increase in CPU performance when running this application.

The System Data Collection Sets contain 3 types of data collector Disk Usage, Query Statistics and server activity. The data is stored in a Management Data Warehouse (MDW). The data is collected by running SQL Server Agent jobs and have associated SSIS packages.
Disk Usage Data Collection
This captures information about disk usage for data and log files for each database. This can be used to show disk space trends
Query Statistic Data Collection
This collects information about different queries that run on the server and statistics to help in tuning efforts and identify the greatest impact on resource usage
Server Activity Data Collection
Colelcts CPU, Memory and performance counts. Helps identify bottlenecks