What is .TUF file?

What is .TUF file? What is the significance of the same? Any implications if the file is deleted?

.TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.
When the database is in Standby mode the database recovery is done when the log is restored; and this mode also creates a file on destination server with .TUF extension which is the transaction undo file.

This file contains information on all the modifications performed at the time backup is taken.

The file plays a important role in Standby mode… the reason being very obvious while restoring the log backup all uncommited transactions are recorded to the undo file with only commited transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL server will fetch all the uncommited transactions from undo file and check with the new transaction log backup whether commited or not.

If found to be commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

Query Plan (DMV)

 Here is a query that you can run to get the Total Elapsed time of queries and the number of times those queries were hit. It also give's you the query and the plan used for each of those.

select qs.execution_count
     , qs.total_elapsed_time, qs.last_elapsed_time
     , qs.min_elapsed_time, qs.max_elapsed_time
     , 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 statement_text
      , qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

Possible Bad NC Indexes

-- Possible Bad NC Indexes (writes > reads)

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]

,i.name AS [Index Name]
,i.index_id
,user_updates AS [Total Writes]
,user_seeks + user_scans + user_lookups AS [Total Reads]
,user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC
, [Total Writes] DESC
, [Total Reads] ASC ;

Unused indexes

-- List unused indexes

SELECT OBJECT_NAME(i.[object_id]) AS [Table Name]

,i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id         
           FROM sys.dm_db_index_usage_stats AS s         
           WHERE s.[object_id] = i.[object_id]         
           AND i.index_id = s.index_id         
           AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

Index Read/Write stats

--- Index Read/Write stats (all tables in current DB)

SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName]

,i.name AS [IndexName]
,i.index_id
,user_seeks + user_scans + user_lookups AS [Reads]
,user_updates AS [Writes]
,i.type_desc AS [IndexType]
,i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) , writes DESC ,reads DESC ;

Missing Indexes in current database

-- Missing Indexes in current database by Index Advantage

SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )

AS [index_advantage]
    ,migs.last_user_seek
    ,mid.[statement] AS [Database.Schema.Table] ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,migs.unique_compiles
    ,migs.user_seeks
    ,migs.avg_total_user_cost
    ,migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC ;