Current queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time / 1000/60 AS Total_Elapsed_Time_Min
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
order by 6 desc 

Get SQL Table sizes

select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 as 'size'
from    
      sys.dm_db_partition_stats, sys.objects
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id

group by sys.objects.name
order by size desc

index usage

SELECT
  DB.[NAME] AS [Database name],
  OBJECT_NAME(S.[OBJECT_ID]) AS [Object name],
  I.[NAME] AS [Index name],
  USER_SEEKS, -- number of index seeks
  USER_SCANS, -- number of index scans
  USER_LOOKUPS, -- number of index lookups
  USER_UPDATES, -- number of insert, update or delete operations
  STATS_DATE(I.[OBJECT_ID], I.INDEX_ID) AS [Statistics date]
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S 
  INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
  INNER JOIN SYS.DATABASES AS DB ON DB.DATABASE_ID = S.DATABASE_ID 
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
  --AND I.[NAME] like 'IX_%'
  AND S.DATABASE_ID = DB_ID()

SQL expensive queries

SELECT
CAST(
      (select SUBSTRING(text,eq1.statement_start_offset/2 +1 ,
      ((CASE eq1.statement_end_offset
      WHEN -1 THEN DATALENGTH(text)
      ELSE eq1.statement_end_offset
      END - eq1.statement_start_offset)/2) + 1) AS Query
      from sys.dm_exec_sql_text (eq1.sql_handle))
     AS nvarchar(max)) AS [Query_StatementText],
CAST(
      (select text AS Query
      from sys.dm_exec_sql_text (eq1.sql_handle))
     AS nvarchar(max)) AS [Query_BatchText],
CAST(eq1.sql_handle AS varbinary(max)) AS [Query_Handle],
CAST(eq1.plan_handle AS varbinary(max)) AS [Query_PlanHandle],
eq1.statement_start_offset AS [Query_StatementStartOffset],
eq1.statement_end_offset AS [Query_StatementEndOffset],
eq1.execution_count AS [Query_ExecutionCount],
eq1.creation_time AS [Query_CreateDate],
eq1.last_execution_time AS [Query_LastExecutionTime],
eq1.plan_generation_num AS [Query_PlanGenerationNumber],
CAST(eq1.query_hash AS varbinary(max)) AS [Query_Hash],
eq1.total_worker_time AS [Query_TotalWorkerTime],
eq1.last_worker_time AS [Query_LastWorkerTime],
eq1.min_worker_time AS [Query_MinWorkerTime],
eq1.max_worker_time AS [Query_MaxWorkerTime],

      case when ( (DATEDIFF(s,eq1.creation_time, getdate()) ) != 0)
      then
      (eq1.total_worker_time) /
      (DATEDIFF(s,eq1.creation_time, getdate()) )
      else
      0
      end
     AS [Query_BurnRateWorkerTime],
eq1.total_elapsed_time AS [Query_TotalElapsedTime],
eq1.last_elapsed_time AS [Query_LastElapsedTime],
eq1.min_elapsed_time AS [Query_MinElapsedTime],
eq1.max_elapsed_time AS [Query_MaxElapsedTime],

      case when ( (DATEDIFF(s,eq1.creation_time, getdate()) ) != 0)
      then
      (eq1.total_elapsed_time) /
      (DATEDIFF(s,eq1.creation_time, getdate()) )
      else
      0
      end
     AS [Query_BurnRateElapsedTime],
eq1.total_physical_reads AS [Query_TotalPhysicalReads],
eq1.max_physical_reads AS [Query_LastPhysicalReads],
eq1.last_physical_reads AS [Query_MinPhysicalReads],
eq1.min_physical_reads AS [Query_MaxPhysicalReads],

      case when ( (DATEDIFF(s,eq1.creation_time, getdate()) ) != 0)
      then
      (eq1.total_physical_reads) /
      (DATEDIFF(s,eq1.creation_time, getdate()) )
      else
      0
      end
     AS [Query_BurnRatePhysicalReads],
eq1.total_logical_reads AS [Query_TotalLogicalReads],
eq1.last_logical_reads AS [Query_LastLogicalReads],
eq1.min_logical_reads AS [Query_MinLogicalReads],
eq1.max_logical_reads AS [Query_MaxLogicalReads],

      case when ( (DATEDIFF(s,eq1.creation_time,getdate()) ) != 0)
      then
      (eq1.total_logical_reads) /
      (DATEDIFF(s,eq1.creation_time, getdate()) )
      else
      0
      end
     AS [Query_BurnRateLogicalReads],
eq1.total_logical_writes AS [Query_TotalLogicalWrites],
eq1.last_logical_writes AS [Query_LastLogicalWrites],
eq1.min_logical_writes AS [Query_MinLogicalWrites],
eq1.max_logical_writes AS [Query_MaxLogicalWrites],

      case when ( (DATEDIFF(s,eq1.creation_time,getdate()) ) != 0)
      then
      (eq1.total_logical_writes) /
      (DATEDIFF(s,eq1.creation_time, getdate()) )
      else
      0
      end
     AS [Query_BurnRateLogicalWrites],
CAST(eq1.plan_handle AS varbinary(max)) AS [Query_$Identity]
FROM
 sys.dm_exec_query_stats  AS eq1
ORDER BY
[Query_BurnRateWorkerTime] DESC
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY