[Note: Before working with the example please read the VERY IMPORTANT NOTE at the end of this page.]

if your database files (data and log files) are stolen, those database files can be attached in another SQL Server instance, or if your backup is stolen, it is restored in another SQL Server instance.  So non-encrypted data can be accessed easily. With Transparent Data Encryption (TDE), the data and log files and also backup files are automatically encrypted, and the data within these files cannot be accessed without an encryption key.

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. TDE does not provide encryption across communication channels. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

As a summary
More...

select top 50 coalesce (db_name(st.dbid), db_name(convert (int, pa.value)), 'Empty') as DBName, 
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS StatementText,
st.text as ProcedureTextOrBatchText,         
qs.plan_generation_num as PlanGenerationNumber,
qs.execution_count as ExecutionCount,
(qs.total_worker_time/1000) as CPUTimeTotal,
((qs.total_worker_time/1000)/qs.execution_count) as CPUTimeAvg,
(qs.total_elapsed_time/1000) as DurationTimeTotal,
((qs.total_elapsed_time/1000)/qs.execution_count) as DurationTimeAvg,
qs.total_physical_reads as PhysicalReadsTotal,
(qs.total_physical_reads/qs.execution_count) as PhysicalReadsAvg,
qs.total_logical_reads as LogicalReadsTotal,
(qs.total_logical_reads/qs.execution_count) as LogicalReadsAvg,
qs.last_execution_time as LastExecutionTime
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where attribute = 'dbid' 
--order by CPUTimeTotal desc
--order by CPUTimeAvg desc
--order by DurationTimeTotal desc
--order by DurationTimeAvg desc
--order by PhysicalReadsTotal desc
--order by PhysicalReadsAvg desc
--order by LogicalReadsTotal desc
order by LogicalReadsAvg desc

There is very useful dynamic management view (DMV) to identify expensive queries. It is called “sys.dm_exec_query_stats”.

The query can be used to identify the expensive queries. And then I will explain in detail the DMVs and columns used in the query.

More...

If you check the sql server books online you can see the description like below. And then I will give explanation with an example.

sys.dm_db_index_usage_stats (Reference SQL Server BOL)

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view.
The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view.You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

sys.dm_db_index_operational_stats (Reference SQL Server BOL)

You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.
you cannot use these counters to determine whether an index has been used or not, or when the index was last used.
An active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started.

I use Adventureworks sample database.
Let’s create a table and indexes for this table.
More...

Indexes are really the most important things for sql server performance. They are created to improve the query performance. Another important thing is index count for the objects. If we have many indexes for any table, there may be negative impact over performance. So it is important to create necessary indexes. If some of them are unused we need to clean them because all transactions touching the object will cause all indexes for this particular object to be organized from the beginning.

How can we identify the unused indexes or rarely used indexes.

More...

Create table tempdb.dbo.sys_dm_os_wait_stats
(Run_time datetime, wait_type nvarchar(60), waiting_tasks_count bigint,
 wait_time_ms bigint, signal_wait_time_ms bigint)
go
insert into tempdb.dbo.sys_dm_os_wait_stats ( Run_time, wait_type,
waiting_tasks_count, wait_time_ms, signal_wait_time_ms) 
select getdate() as 'Run_Time',wait_type,waiting_tasks_count,
wait_time_ms,signal_wait_time_ms  
from sys.dm_os_wait_stats 
order by wait_time_ms desc

--wait for 1 minute and get the last data and compare it with last saved snapshot
--you can re-run the query for every 1 minute and check any wait_type which is
--being at the top
--check the wait_type and trying to work on it

select getdate() as 'Run_Time', TEMP_WS.Run_time as Snapshot_Time,
WS.wait_type,
WS.wait_time_ms-TEMP_WS.wait_time_ms as "delta_wait_time_ms",
WS.waiting_tasks_count-TEMP_WS.waiting_tasks_count as "delta_waiting_tasks_count",
WS.signal_wait_time_ms-TEMP_WS.signal_wait_time_ms as "delta_signal_wait_time_ms",
(WS.wait_time_ms-TEMP_WS.wait_time_ms)-
                   (WS.signal_wait_time_ms-TEMP_WS.signal_wait_time_ms)
                                                   as "delta_resource_wait_time_ms",
((WS.wait_time_ms-TEMP_WS.wait_time_ms)-
                        (WS.signal_wait_time_ms-TEMP_WS.signal_wait_time_ms))/
                                 (WS.waiting_tasks_count-TEMP_WS.waiting_tasks_count)
                                                   as "delta_avg_resource_wait_time_ms"
from sys.dm_os_wait_stats WS
left outer join tempdb.dbo.sys_dm_os_wait_stats TEMP_WS
on WS.wait_type=TEMP_WS.wait_type
where WS.wait_type not in ('BROKER_TASK_STOP','Total','SLEEP','BROKER_EVENTHANDLER',
'BROKER_RECEIVE_WAITFOR', 'BROKER_TRANSMITTER','CHECKPOINT_QUEUE',
'CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP',
'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE',
'SERVER_IDLE_CHECK', 'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP',
'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP',
'SNI_HTTP_ACCEPT','SQLTRACE_BUFFER_FLUSH','TRACEWRITE','WAIT_FOR_RESULTS',
'WAITFOR_TASKSHUTDOWN', 'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR', 
'CLR_SEMAPHORE', 'BROKER_TO_FLUSH', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX', 'XE_DISPATCHER_JOIN', 
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
and (WS.wait_time_ms-TEMP_WS.wait_time_ms) > 0
order by "delta_wait_time_ms" desc

--if you want you can truncate the table and re-run the query
truncate table tempdb.dbo.sys_dm_os_wait_stats

--at the end you can drop the table
drop table tempdb.dbo.sys_dm_os_wait_stats

You have a performance issue and try to solve the problem. Which action do we need to consider first?

We will have a look at sys.dm_os_wait_stats DMV (Dynamic Management View). It requires “VIEW SERVER STATE” permission to query this DMV.

It contains aggregated wait statistics.

You can run the following query to get the wait statistics for each wait type. More...

We will analyze the query below

Use BlogDB
go
SELECT * FROM dbo.Employee
WHERE SalesOrderNumber = 'SO43691'

Employee table is a HEAP table. There is a non-clustered index on Employee Table.

CREATE NONCLUSTERED INDEX IX_SalesOrderNumber ON Employee (SalesOrderNumber)

Let’s get the Index Levels and PageIDs for index called IX_SalesOrderNumber.

More...

DBCC IND is undocumented and need four parameters.

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid },
{ nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )

nonclustered indid = non-clustered Index ID
1 = Clustered Index ID
0 = Displays information in-row data pages and in-row IAM pages (from Heap)
-1 = Displays information for all pages of all indexes including LOB (Large object binary) pages and row-overflow pages
-2 = Displays information for all IAM pages

Employee table is a Heap table.

And there is only one non-clustered index on this table. You can find the script of the table below. More...