Top 10 Wait States in SQL Server

 select top 10 *

from sys.dm_os_wait_stats

where wait_type not in --remove common waits to identify worst bottlenecks

( 

'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',

'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',   

'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',

'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 

'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP', 

'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 

'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',

'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 

'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'

) 

order by wait_time_ms desc

Script to Identifying possible CPU pressure via signal wait time

 SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

Most active database in sql server

SELECT

DB_NAME(mf.database_id) AS databaseName,

name AS File_LogicalName,

CASE

WHEN type_desc = 'LOG' THEN 'Log File'

WHEN type_desc = 'ROWS' THEN 'Data File'

ELSE type_desc

END AS File_type_desc

,mf.physical_name

,num_of_reads

,num_of_bytes_read

,io_stall_read_ms

,num_of_writes

,num_of_bytes_written

,io_stall_write_ms

,io_stall

,size_on_disk_bytes

,size_on_disk_bytes/ 1024 AS size_on_disk_KB

,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB

,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs

JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id

AND mf.FILE_ID = divfs.FILE_ID

ORDER BY num_of_Reads DESC