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

Stopping and starting services

A script to Stop and Start the SQL Server Agent Service

-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

-- STOP SQL Server Agent
EXEC xp_servicecontrol 'STOP','SQLServerAGENT'
GO
-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

-- START SQL Server Agent
EXEC xp_servicecontrol 'START','SQLServerAGENT'
GO
-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

SQL Server Profiler

There are 2 ways to run SQL Profiler

• Run the SQL Profiler client tool through the GUI
• Server-side tracing where events are saved to a physical file on the server by using SQL Server system stored procedures and functions.

To view the number of traces running:

SELECT count(*)
FROM :: fn_trace_getinfo(default)
WHERE property = 5 and value = 1

To obtain more detail about the active traces:-

SELECT *
FROM :: fn_trace_getinfo(default)

To stop a trace using the traceid:-

EXEC sp_trace_setstatus 1, @status = 0
EXEC sp_trace_setstatus 1, @status = 2

status = 0 stops the trace
status = 2 closes the trace and deletes its definition from the server

A sample output :-

traceid,property,value
1,1,2
1,2,D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1504.trc
1,3,20
1,4,NULL
1,5,1


This means

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination

The Default trace
The default trace great for diagnosing performance problems, finding deadlocks, and auditing security information.The default trace does not capture all trace events, but it captures key information such as auditing events, database events, error events, full text events, object creation, object deletion and object alteration. It also captures log growth events which can be invaluable to troubleshooting disk capacity problems.