Wait Statistics

For looking wait statistics problems need to use

1	SELECT * FROM sys.dm_os_wait_stats

For clear wait statistics neet to run this :

1	DBCC SQLPERF(N'sys.dm_os_wait_stats',CLEAR)

The are a lot of wait types , most important are :

• CXPACKET - most common type, it's mean that we have parallel operation is SQL Server, this happens when when we have table scans.
• PAGEIOLATCH_XX -  wait for data file  page to be read into memory. Modes : SH - page will be read, EX - page will be changed
• ASYNC_NETWORK_IO - SQL Server waiting for  client to send data.
• WRITELOG - waiting for transaction log for saving to disk.
• PAGELATCH_XX - waiting for access to in-memory data file page

SQL server troubleshooting tools

The are few tools for investigations and troubleshooting :

• SQL Profile
• Extended Events
• Activity monitor
• IOMeter
• Sqlio

SQL Profile

This tools was created for profiling SQL queries, for investigating query performance, and different statistics and parameters of query. More information in here. This tools is deprecated and will be replaced by Extended Events.

Extended Events 

Extended events was created for replacing SQL Profile.And it can do the same as profile but use minimal resources. More information here.

Activity monitor

Activity monitor is tools for monitoring memory, CPU, disk resources etc.  More information here. 

IOMeter

IOMetter - tools for investigating performance of disks. More info here.

SQLIO

SQLIO - is tools for investigating raid performance for I/O operations. More info here.

Database compression

In SQL Server we have two types of compression :

• Row
• Page

Let's look at it more deeply.
For create table with row or page compression we need to do it in this way :

1 2	create table testPageCompression (col1 varchar(max), col2 varchar(max)) with (data_compression = row)

1 2	create table testPageCompression (col1 varchar(max), col2 varchar(max)) with (data_compression = page)

It possible to check if it working just looking into properties window by table:

Also we can compress heap, clustered index, non clustered index, indexed view , partitions of tables in partitioned table , file stream objects and backup files.

Implementing Transparent Data Encryption(TDE)

For encrypting database we need to do this few steps:

1. Create master key on master database :

   1	create master key encryption by password = 'testPassword'

2. Create a server certificate derived from database master key :

   1	create certificate test_cert with subject = 'test_cert'

3. Use our database and create database encryption key by our server certificate:

   1 2	create database encryption key with algorithm = AES_256 encryption by server certificate test_cert

4. Set encryption on on database level:

   1	alter database test set encryption on

There are few restrictions and possible problems with encrypted databases :

• If you don't backup our master key, certificate and encryption  key it will not possible to restore or move our database to another server 
• Encrypted database have performance overhead