SQL Queries

Identify blocking chain processes and head of the blocking chain with wait types.

Select Session_ID,Blocking_Session_ID
 From Sys.dm_Exec_Requests
 Where Session_ID > 50
 And Blocking_Session_Id <> 0

SELECT
spid
,sp.STATUS
,loginame 
,hostname 
,blocked 
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,50)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

SELECT
        @@SERVERNAME ServerName,
        DB_NAME() AS DatabaseName,
        object_id ObjectID ,
        OBJECT_NAME(object_id) StoredProcName ,
        OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
            cached_time AS CachedDateTime ,
        execution_count ,
        total_elapsed_time / ( 1000 * execution_count ) AS Avg_elapsed_time ,
        min_elapsed_time / 1000 AS min_elapsed_time ,
        max_elapsed_time / 1000 AS max_elapsed_time ,
        total_logical_reads / execution_count AS Avg_logical_reads ,
        total_physical_reads / execution_count AS Avg_physical_reads
FROM    sys.dm_exec_procedure_stats WITH ( NOLOCK )
WHERE   OBJECT_NAME(object_id) IS NOT NULL
        AND type = 'P'
        AND database_id = DB_ID()
ORDER BY execution_count DESC ;

DECLARE @spid INT
SELECT @spid = 70
SELECT req.session_id ,
req.start_time ,
req.status ,
req.wait_type ,
req.last_wait_type ,
req.logical_reads ,
req.total_elapsed_time ,
sqltext.text QueryText ,
SUBSTRING(sqltext.text, ( req.statement_start_offset / 2 ) + 1,
( ( CASE req.statement_end_offset
WHEN -1 THEN DATALENGTH(sqltext.text)
ELSE req.statement_end_offset
END - req.statement_start_offset ) / 2 ) + 1) AS StatementText ,
plantext.query_plan
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) sqltext
CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) plantext
WHERE req.session_id = @spid

;
WITH Table_RowCnt_SpaceUsed
AS ( SELECT object_id ,
reservedpages = SUM(reserved_page_count) ,
usedpages = SUM(used_page_count) ,
pages = SUM(CASE WHEN ( index_id < 2 )
THEN ( in_row_data_page_count
+ lob_used_page_count
+ row_overflow_used_page_count )
ELSE lob_used_page_count
+ row_overflow_used_page_count
END) ,
row_Count = SUM(CASE WHEN ( index_id < 2 ) THEN row_count ELSE 0 END) FROM sys.dm_db_partition_stats GROUP BY object_id ) SELECT schema_name = OBJECT_SCHEMA_NAME(object_id) , table_name = OBJECT_NAME(object_id) , row_Count , reserved = LTRIM(STR(reservedpages * 8, 15, 0) + ' KB') , data = LTRIM(STR(pages * 8, 15, 0) + ' KB') , index_size = LTRIM(STR(( CASE WHEN usedpages > pages
THEN ( usedpages - pages )
ELSE 0
END ) * 8, 15, 0) + ' KB') ,
unused = LTRIM(STR(( CASE WHEN reservedpages > usedpages
THEN ( reservedpages - usedpages )
ELSE 0
END ) * 8, 15, 0) + ' KB')
FROM Table_RowCnt_SpaceUsed
WHERE OBJECT_SCHEMA_NAME(object_id) IN ( 'ls','dbo' )
ORDER BY Table_RowCnt_SpaceUsed.row_Count DESC;

-- Enable SQLCMD Mode to Run this Script
:SETVAR pOldDatabaseName "Util"
:SETVAR pNewDatabaseName "UtilNew"


USE master;
GO
ALTER DATABASE $(pOldDatabaseName)
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

USE master;
GO
ALTER DATABASE $(pOldDatabaseName)
Modify Name = $(pNewDatabaseName);
GO

USE master;
GO
ALTER DATABASE $(pNewDatabaseName)
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;
GO

Kill SPIDS to Obtain Exclusive Database Lock

USE master
GO
ALTER DATABASE AdventureWorks
SET OFFLINE WITH ROLLBACK IMMEDIATE
/*
Msg 5061, Level 16, State 1, Line 4
ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks'. Try again later.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.
*/
USE [master]
GO

DECLARE @kill varchar(8000) = ''; 
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' + CHAR(13)
FROM sys.dm_exec_sessions
WHERE database_id = db_id('AdventureWorks')
SELECT @kill

-- kill 57; kill 58; kill 62; kill 64; kill 67; kill 68;

EXEC(@kill);

SELECT VirtualServerName = SERVERPROPERTY('ServerName') ,
ActiveNode = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ,
PassiveNode = CASE ( SELECT SERVERPROPERTY('IsClustered')
)
WHEN 0 THEN 'Stand-Alone'
WHEN 1
THEN ( SELECT NodeName
FROM sys.dm_os_cluster_nodes
WHERE NodeName <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
)
ELSE CAST(( SELECT COUNT(*)
FROM sys.dm_os_cluster_nodes
WHERE NodeName <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
) AS VARCHAR(2)) + ' Passive Nodes'
END

SELECT PassiveNodes = NodeName
FROM sys.dm_os_cluster_nodes
WHERE NodeName <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

SELECT ClusterDrives = DriveName
FROM sys.dm_io_cluster_shared_drives
ORDER BY DriveName


SELECT @@SERVERNAME
-- NVSQL3A
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

SELECT * FROM sys.dm_os_cluster_nodes

BACKUP DATABASE DatabaseName
TO DISK ='\\NetworkShare\DatabaseName.bak'
WITH COMPRESSION,COPY_ONLY , CHECKSUM

RESTORE VERIFYONLY FROM DISK = '\\NetworkShare\DatabaseName.bak'

RESTORE FILELISTONLY FROM DISK ='\\NetworkShare\DatabaseName.bak'

RESTORE HEADERONLY FROM DISK = '\\NetworkShare\DatabaseName.bak'

RESTORE LABELONLY FROM DISK = '\\NetworkShare\DatabaseName.bak'

RESTORE DATABASE DatabaseName
FROM DISK ='\\NetworkShare\DatabaseName.bak'
WITH MOVE 'DataFileName' TO 'DataDrive\DataFileName.mdf'
, MOVE 'LogFileName' TO 'LogDrive\LogFileName.mdf'
,REPLACE

Dynamic Restore from Backup Flie
http://ramblingsofraju.com/sql-server/dynamically-restore-sql-server-database-from-backup/

— Identify Restored Database Source and TimeStamp

SELECT [rs].[destination_database_name] ,
[rs].[restore_date] ,
[bs].[backup_start_date] ,
[bs].[backup_finish_date] ,
[bs].[database_name] AS [source_database_name] ,
[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

EXEC sys.xp_cmdshell 'dir \\networkshare-nas\share\'

/*
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
*/

SELECT *
FROM sys.configurations
WHERE name = 'xp_cmdshell'

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
SELECT *
FROM sys.configurations
WHERE name = 'clr enabled'
/*
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
16390 xp_cmdshell 0 0 1 0 Enable or disable command shell 1 1
*/

EXEC sys.xp_cmdshell 'dir \\networkshare-nas\share\'

/*
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
*/

SELECT *
FROM sys.configurations
WHERE name = 'xp_cmdshell'

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
SELECT *
FROM sys.configurations
WHERE name = 'clr enabled'
/*
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
16390 xp_cmdshell 0 0 1 0 Enable or disable command shell 1 1
*/

Script to Create a Login  ,  Create Database User from the Login
and Granting SELECT,UPDATE,INSERT,DELETE,EXEC Permissions

/******************************************************************************************************
** Desc: Create SampleLoginName login
** Auth: Raju Venkataraman
** Date: 07/27/2016 Created
**************************
** Change History
**************************
** CR Date Author Description
1 07/27/2016 Raju Venkataraman Create SampleLoginName Login and grant EXEC AND CRUD Permissions
** ----- ----------------------- ------------------------------------------------------------
********************************************************************************************************/
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
SET XACT_ABORT ON

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128)
SET @DB_NAME = (SELECT DB_NAME(dbid) FROM MASTER..SYSPROCESSES WHERE spid=@@SPID)
PRINT '-----------------------------------------------------------------------'
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------'
PRINT '-----SQL COMPILED BY: Raju Venkataraman'
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME
PRINT '-----SCRIPT START TIME: ' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)
PRINT '-----MachineName : ' + cast( serverproperty ( 'MachineName' ) as varchar )
PRINT '-----SQL Instance : ' + cast( @@SERVERNAME as varchar )
PRINT '-----DB User : ' + current_user
PRINT '-----System User : ' + system_user
PRINT '-----Host : ' + host_name()
PRINT '-----Application : ' + app_name()
PRINT '-----TranCount : ' + cast (@@trancount as varchar)
PRINT '-----------------------------------------------------------------------'
GO
----------------END SQL SCRIPT HEADER---------------------

BEGIN
BEGIN TRY

USE MASTER
IF NOT EXISTS (SELECT 1 FROM master.sys.sql_logins WHERE [name] = 'SampleLoginName')
CREATE LOGIN [LoginName] WITH PASSWORD='passwordvalue', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF


USE AdventureWorks

IF DB_NAME()<>'AdventureWorks'
RAISERROR('AdventureWorks Database Doesn''t exist in this Server',16,1)

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = 'SampleLoginName')
Begin
CREATE USER [LoginName] FOR LOGIN [LoginName]
GRANT SELECT,UPDATE,INSERT,DELETE,EXEC TO [LoginName];
End


END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH


END;
GO

-------BEGIN SQL SCRIPT FOOTER------------------------------------
PRINT '-----------------------------------------------------------'
PRINT '-------------------FINISHED SQL SCRIPT---------------------'
PRINT '--COMPLETED TIME:' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)
PRINT '--TranCount : ' + cast (@@trancount as varchar)
PRINT '------------------------------------------------------------'

Enable successful and Failed Logins

-- To Enable Failed and Successful Logins . By Default Only Failed Logins are Captured
-- Need to Restart the Server for this to effect
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3;
GO

Query Error Log for Successful and Failed Logins

   
 -- Query for Successful Logins
EXEC sys.sp_readerrorlog @p1 = 0, -- int -- 0 = current, 1 = Archive #1, 2 = Archive 
    @p2 = 1, -- int  -- 1 or NULL = error log, 2 = SQL Agent log
    @p3 = 'succe', -- varchar(255) -- String one we want to search for
    @p4 = 'login'
 -- varchar(255)  -- String two we want to search to further refine the result 

-- Login succeeded for user 'xxxxxx'. Connection made using Windows authentication. [CLIENT: xx.xx.xx.xx]

 -- Query for Failed Logins
EXEC sys.sp_readerrorlog @p1 = 0, -- int -- 0 = current, 1 = Archive #1, 2 = Archive 
    @p2 = 1, -- int  -- 1 or NULL = error log, 2 = SQL Agent log
    @p3 = 'failed', -- varchar(255) -- String one we want to search for
    @p4 = 'login' -- varchar(255)  -- String two we want to search to further refine the result 

-- Login failed for user ''. Reason: Could not find a login matching the name provided. [CLIENT: ]

Enumerate User Permissions

EXECUTE AS USER = 'samplelogin';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;

Enumerate Database Owners and Change it to “SA”

SELECT  SUSER_SNAME(owner_sid) DatabaseOwner ,
        *
FROM    sys.databases;

SELECT  'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];'
FROM    sys.databases
WHERE   name NOT IN ( 'master', 'model', 'tempdb' );

ALTER AUTHORIZATION ON DATABASE::[Demo] TO [sa];

Useful queries for identifying least and most used indexes , quick way rebuild all indexes , gather index name and size .

Least Used 20 Indexes

SELECT TOP 20
@@SERVERNAME ServerName,
DB_NAME() AS DatabaseName,
o.name AS ObjectName ,
i.name AS IndexName ,
i.index_id AS IndexID ,
dm_ius.user_seeks AS UserSeek ,
dm_ius.user_scans AS UserScans ,
dm_ius.user_lookups AS UserLookups ,
dm_ius.user_updates AS UserUpdates ,
p.TableRows
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.object_id = i.object_id
INNER JOIN sys.objects o ON dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN ( SELECT SUM(p.rows) TableRows ,
p.index_id ,
p.object_id
FROM sys.partitions p
GROUP BY p.index_id ,
p.object_id
) p ON p.index_id = dm_ius.index_id
AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id, 'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.TableRows > 1000
ORDER BY ( dm_ius.user_seeks + dm_ius.user_scans
+ dm_ius.user_lookups + dm_ius.user_updates ) ASC;

Results

Top 20 Most Fragmented Objects

SELECT TOP 20 
@@SERVERNAME ServerName,
DB_NAME() DatabaseName,
OBJECT_NAME(ind.object_id) AS TableName ,
ind.name AS IndexName ,
indexstats.index_type_desc AS IndexType ,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL,
NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 25
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Results:

Missing Indexes

SELECT TOP 20
@@SERVERNAME ServerName,
DB_NAME(dm_mid.database_id) AS DatabaseName ,
OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) TableName ,
dm_migs.user_seeks ,
dm_migs.user_scans ,
dm_mid.equality_columns ,
dm_mid.included_columns ,
dm_migs.avg_user_impact * ( dm_migs.user_seeks
+ dm_migs.user_scans ) Avg_Estimated_Impact ,
dm_migs.last_user_seek AS Last_User_Seek
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_id = DB_ID()
ORDER BY dm_migs.user_seeks + dm_migs.user_scans DESC;

Results

Percentile Calculation
This comes very handy to calculate percentiles for stored procedure Duration which are captured in Trace Files.

 -- Percentile Calculation
  SELECT    StoredProcName
    -- A crosstab query to aggregate the results
            ,
            [25th Percentile] = MAX([25th Percentile]) ,
            [50th Percentile] = MAX([50th Percentile]) ,
            [75th Percentile] = MAX([75th Percentile]) ,
            [100th Percentile] = MAX([100th Percentile])
  FROM      ( SELECT    StoredProcName
        -- Calculate percentile rankings of interest (25%, 50%, 75%, 100%)
                        ,
                        [25th Percentile] = PERCENTILE_CONT(0.25) WITHIN GROUP ( ORDER
                        BY Duration ) 
                                OVER ( PARTITION BY StoredProcName ) ,
                        [50th Percentile] = PERCENTILE_CONT(0.50) WITHIN GROUP ( ORDER
                        BY Duration ) 
                                OVER ( PARTITION BY StoredProcName ) ,
                        [75th Percentile] = PERCENTILE_CONT(0.75) WITHIN GROUP ( ORDER
                        BY Duration ) 
                                OVER ( PARTITION BY StoredProcName ) ,
                        [100th Percentile] = PERCENTILE_CONT(1.00) WITHIN
                        GROUP ( ORDER BY Duration ) 
                                OVER ( PARTITION BY StoredProcName )
              FROM      ( SELECT    LEFT(TextData,( CHARINDEX('@', TextData,CHARINDEX('dbo', TextData)) ) - 1) AS StoredProcName ,
                                    [Reads] ,
                                    [Writes] ,
                                    [CPU] ,
                                    [ServerName] ,
                                    [Duration] ,
                                    [StartTime] ,
                                    [EndTime]
                          FROM      [ITTools].[dbo].[AWSRDSTrace]
                          WHERE     ( CHARINDEX('@', TextData,
                                                CHARINDEX('dbo', TextData)) ) > 0
                        ) der
            ) a
  GROUP BY  StoredProcName;

Miscellaneous queries 

SELECT @@SERVERNAME ServerName,local_net_address,local_tcp_port FROM sys.dm_exec_connections
WHERE session_id = @@SPID
AND net_transport ='TCP'

EXEC master..xp_logininfo 'DomainName\DevTeam', @option = 'members'
/*
account name type privilege mapped login name permission path
DomainName\username user user DomainName\username DomainName\NTGroupName
*/
EXEC sys.sp_MSloginmappings @loginname = N'DomainName\GroupName', -- nvarchar(258)
@flags = 0 -- int

 EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Available Drive Space

-- Gather Space Availability Data 
DECLARE @DriveSpace TABLE
(
[DriveLetter] NVARCHAR(255) ,
[SpaceFreeinMB] NVARCHAR(255)
);
INSERT INTO @DriveSpace
EXEC xp_fixeddrives;

SELECT @@SERVERNAME ,
[DriveLetter] ,
[SpaceFreeinMB]
FROM @DriveSpace;

Results:

Virtual File Stats:
Returns I/O statistics for database files, including log files
Function Usuage : fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } ) 

https://msdn.microsoft.com/en-us/library/ms187309.aspx

SELECT  @@SERVERNAME AS ServerName ,
        LEFT(saf.[filename], 1) + ':' AS DriveLetter ,
        COUNT(saf.fileid) AS NumFiles ,
        CAST (SUM(saf.[size] / 128.0) AS NUMERIC(18, 3)) AS SizeMB , -- convert to Megabytes from 8-k pages
        SUM(NumberReads) AS NumberReads ,
        SUM(NumberWrites) AS NumberWrites ,
        CAST(SUM(BytesRead) AS NUMERIC) / ( 1024 * 1024 * 1024 ) AS BytesRead_GB ,
        CAST (SUM(BytesWritten) AS NUMERIC) / ( 1024 * 1024 * 1024 ) AS BytesWritten_GB ,
        SUM(IoStallMS) AS IoStallMS
FROM    ::
        fn_virtualfilestats(DB_ID(), NULL) vfs -- NULL for  all Databaes
        INNER JOIN master..sysaltfiles saf ON vfs.dbid = saf.dbid
                                              AND vfs.fileid = saf.fileid
GROUP BY LEFT(saf.[filename], 1) + ':'
ORDER BY DriveLetter ASC; 

Virtual File Stats

Virtual File Stats by Database

SELECT  @@SERVERNAME AS ServerName ,
        d.name AS [Database] ,
        f.physical_name AS [File] ,
        ( fs.num_of_bytes_read / 1024.0 / 1024.0 ) [Total MB Read] ,
        ( fs.num_of_bytes_written / 1024.0 / 1024.0 ) AS [Total MB Written] ,
        ( fs.num_of_reads + fs.num_of_writes ) AS [Total I/O Count] ,
        fs.io_stall AS [Total I/O Wait Time (ms)] ,
        fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM    sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) AS fs
        INNER JOIN sys.master_files f ON fs.database_id = f.database_id
                                         AND fs.file_id = f.file_id
        INNER JOIN sys.databases d ON d.database_id = fs.database_id; 

SQL Server Wait Statistics
Wait Statistics are very useful to identify Problem Areas

-- Gather Wait Statistics
IF OBJECT_ID('tempdb..#Waits') IS NOT NULL 
DROP TABLE #Waits

; SELECT  [wait_type] ,
                [wait_time_ms] / 1000.0 AS [WaitS] ,
                ( [wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS] ,
                [signal_wait_time_ms] / 1000.0 AS [SignalS] ,
                [waiting_tasks_count] AS [WaitCount] ,
                100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER ( ) AS [Percentage] ,
                ROW_NUMBER() OVER ( ORDER BY [wait_time_ms] DESC ) AS [RowNum]
        INTO    #Waits
        FROM    sys.dm_os_wait_stats
        WHERE   [wait_type] NOT IN ( N'BROKER_EVENTHANDLER',
                                     N'BROKER_RECEIVE_WAITFOR',
                                     N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
                                     N'BROKER_TRANSMITTER',
                                     N'CHECKPOINT_QUEUE', N'CHKPT',
                                     N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
                                     N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT',
                                     N'DBMIRROR_EVENTS_QUEUE',
                                     N'DBMIRROR_WORKER_QUEUE',
                                     N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL',
                                     N'DISPATCHER_QUEUE_SEMAPHORE',
                                     N'EXECSYNC', N'FSAGENT',
                                     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
                                     N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL',
                                     N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
                                     N'HADR_LOGCAPTURE_WAIT',
                                     N'HADR_NOTIFICATION_DEQUEUE',
                                     N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
                                     N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
                                     N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
                                     N'PWAIT_ALL_COMPONENTS_INITIALIZED',
                                     N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
                                     N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
                                     N'REQUEST_FOR_DEADLOCK_SEARCH',
                                     N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK',
                                     N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
                                     N'SLEEP_DCOMSTARTUP',
                                     N'SLEEP_MASTERDBREADY',
                                     N'SLEEP_MASTERMDREADY',
                                     N'SLEEP_MASTERUPGRADED',
                                     N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK',
                                     N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP',
                                     N'SNI_HTTP_ACCEPT',
                                     N'SP_SERVER_DIAGNOSTICS_SLEEP',
                                     N'SQLTRACE_BUFFER_FLUSH',
                                     N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                                     N'SQLTRACE_WAIT_ENTRIES',
                                     N'WAIT_FOR_RESULTS', N'WAITFOR',
                                     N'WAITFOR_TASKSHUTDOWN',
                                     N'WAIT_XTP_HOST_WAIT',
                                     N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
                                     N'WAIT_XTP_CKPT_CLOSE',
                                     N'XE_DISPATCHER_JOIN',
                                     N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT' )
                AND [waiting_tasks_count] > 0;


                SELECT  @@SERVERNAME ServerName ,
                        MAX([W1].[wait_type]) AS [WaitType] ,
                        CAST (MAX([W1].[WaitS]) AS DECIMAL(16, 2)) AS [Wait_S] ,
                        CAST (MAX([W1].[ResourceS]) AS DECIMAL(16, 2)) AS [Resource_S] ,
                        CAST (MAX([W1].[SignalS]) AS DECIMAL(16, 2)) AS [Signal_S] ,
                        MAX([W1].[WaitCount]) AS [WaitCount] ,
                        CAST (MAX([W1].[Percentage]) AS DECIMAL(5, 2)) AS [Percentage] ,
                        CAST (( MAX([W1].[WaitS]) / MAX([W1].[WaitCount]) ) AS DECIMAL(16,
                                                              4)) AS [AvgWait_S] ,
                        CAST (( MAX([W1].[ResourceS]) / MAX([W1].[WaitCount]) ) AS DECIMAL(16,
                                                              4)) AS [AvgRes_S] ,
                        CAST (( MAX([W1].[SignalS]) / MAX([W1].[WaitCount]) ) AS DECIMAL(16,
                                                              4)) AS [AvgSig_S]
                FROM    #Waits AS [W1]
                        INNER JOIN #Waits AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
                GROUP BY [W1].[RowNum]
                HAVING  SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95; -- percentage threshold

Sample Results:

SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN 'Mirroring is On' ELSE 'No mirror configured' END AS IsMirrorOn,
mirroring_state_desc,
CASE WHEN mirroring_safety_level=1 THEN 'High Performance' WHEN mirroring_safety_level=2 THEN 'High Safety' ELSE NULL END AS MirrorSafety,
mirroring_role_desc,
mirroring_partner_instance AS MirrorServer
FROM sys.database_mirroring
GO