My Second session on BSSUG meeting was more advanced: 

Using SSMS short-cuts for most useful queries.

 

Here is a list short cuts for 9 most useful SQL Server queries:

 

Ctrl+F1 - List of all tables in DB with their sizes and rowcounts:

SELECT OBJECT_NAME(OBJECT_ID) TableName,

row_count,

ROUND(CAST(reserved_page_count AS float)/128.,3) as SizeMB,

ROUND(CAST(used_page_count AS float)/128.,3) AS UsedSpaceMB

FROM sys.dm_db_partition_stats st

WHERE index_id < 2 and OBJECT_NAME(OBJECT_ID) not like 'sys%'

ORDER BY row_count DESC;

Ctrl+3 - advanced SP_LOCK with name of objects and filtering:

SELECT tl.request_session_id as SPID, DB_NAME(tl.resource_database_id),

  CASE WHEN tl.resource_type = 'OBJECT'

    THEN OBJECT_NAME(tl.resource_associated_entity_id)

  WHEN tl.resource_associated_entity_id = 0 THEN 'N/A' END as Entity_Name,

  pt.index_id, tl.resource_type as "Resource",

  tl.resource_description as Description,

  tl.request_mode as Mode,

  tl.request_status as "Status"

FROM sys.dm_tran_locks tl

LEFT JOIN sys.partitions pt ON tl.resource_associated_entity_id = pt.partition_id

WHERE tl.resource_database_id = DB_ID() and tl.resource_type <> 'DATABASE';

Ctrl+4 - Size of all files in the database + size of tempdb files.

DECLARE @t table(DBN varchar(128), FNM varchar(128), PFNM varchar(256), Size float, Used float, FT int)

INSERT INTO @t(DBN, FNM, PFNM, Size, Used, FT)

EXECUTE (' SELECT DB_NAME() as DB_Name, Name, physical_name, size, FILEPROPERTY(name, ''SpaceUsed''), type FROM sys.database_files;')

INSERT INTO @t(DBN, FNM, PFNM, Size, Used, FT)

EXECUTE ('USE tempdb SELECT DB_NAME() as DB_Name, Name, physical_name, size, FILEPROPERTY(name, ''SpaceUsed''), type FROM sys.database_files;')

SELECT t1.DBN as "Database Name", t1.FNM as "File Name", CASE t1.FT WHEN 0 THEN 'Data' ELSE 'Log' END as "File Type", t1.PFNM as "Physical Name", ROUND(t1.Size/128.,3) as SizeMB, ROUND(t1.Used/128,3) AS UsedSpaceMB,

ROUND(t2.Size/128.,3) as TempDBSizeMB, ROUND(t2.Used/128,3) AS TempDBUsedSpaceMB

FROM @t t1, (SELECT SUM(Size) as Size, SUM(Used) as Used, FT FROM @t WHERE DBN = 'tempdb' GROUP BY FT) t2

WHERE t1.DBN != 'tempdb' and t1.FT = t2.FT

ORDER BY t1.FT, t1.DBN, t1.FNM;

 

Ctrl+5 - Size of all files of all databases on SQL Server instance:

DECLARE @t table(DBN varchar(128), FNM varchar(128), PFNM varchar(256), Size float, Used float)

INSERT INTO @t(DBN, FNM, PFNM, Size, Used)

EXEC sp_msforeachdb @command1="use [?] SELECT '?' as DB_Name,Name,physical_name,size,FILEPROPERTY(name, 'SpaceUsed') US FROM sys.database_files;"

SELECT

  DBN as "Database Name",

  FNM as "File Name",

  PFNM as "Physical Name",

  ROUND(Size/128.,3) as SizeMB,

  ROUND(Used/128,3) AS UsedSpaceMB

FROM @t

ORDER BY DBN, FNM;

Ctrl+6 - List of all currently running sessions with texts of queries and batch, execution plan, execution time, blocking processes, blocking resources, used memory, host name, etc.  full replacement of SP_WHO2:

SELECT c.session_id,

       r.blocking_session_id as Blk_Id,

       r.wait_type, r.last_wait_type, r.wait_time, r.wait_resource,

       DB_Name(r.database_id) as [DB_Name], s.login_name, r.command,

       SUBSTRING(t.text, r.statement_start_offset/2,(CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) as Query_Text,

       tqp.query_plan AS Query_Plan,

       -- CAST(tqp.query_plan AS XML) AS Query_Plan,

       qmg.query_cost,

       r.total_elapsed_time / 60000. as total_time_Min,

       qmg.request_time as Memory_Request,

       qmg.requested_memory_kb,

       qmg.granted_memory_kb,

       qmg.required_memory_kb,

       qmg.used_memory_kb,

       qmg.max_used_memory_kb,   

       r.status,

       r.cpu_time,

       r.total_elapsed_time,

       r.granted_query_memory as Memory,

       c.num_reads as cnn_reads,

       c.last_read,

       c.num_writes as cnn_writes,

       c.last_write,

       t.text as Batch_Text,

       qp.query_plan AS Batch_Plan,

       r.reads as request_reads,

       r.writes as request_writes,

       r.logical_reads,

       s.host_name,

       s.program_name,

       c.client_net_address

FROM sys.[dm_exec_connections] c

CROSS APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS t

INNER JOIN sys.dm_exec_sessions s on c.session_id = s.session_id

LEFT JOIN sys.dm_exec_query_memory_grants qmg on c.session_id = qmg.session_id

LEFT JOIN sys.dm_exec_requests r on c.session_id = r.session_id

CROSS APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS qp

CROSS APPLY sys.dm_exec_text_query_plan(r.[plan_handle],r.statement_start_offset,r.statement_end_offset) AS tqp

WHERE c.[most_recent_session_id] != @@spid and s.is_user_process = 1 

ORDER BY c.session_id;

Ctrl+7 - List of currently opened sessions on a SQL server with the last query text

SELECT

  cn.session_id,

  r.blocking_session_id as Blocked_By,

  s.login_name,

  s.[host_name],

  cn.client_net_address,

  s.[program_name],

  t.[text],

  cn.num_reads as cnn_reads,

  cn.last_read,

  cn.num_writes as cnn_writes,

  cn.last_write

FROM sys.[dm_exec_connections] cn

INNER JOIN sys.dm_exec_sessions s on cn.session_id = s.session_id

LEFT JOIN sys.dm_exec_requests r on cn.session_id = r.session_id

CROSS APPLY sys.dm_exec_sql_text(cn.[most_recent_sql_handle]) AS t

WHERE cn.[most_recent_session_id] != @@spid

  and s.is_user_process = 1

ORDER BY cn.session_id;

Ctrl+8 - Current list of all cached queries in SQL server.

SELECT

  DB_NAME(st.dbid) DatabaseNm,

  st.objectid,

  OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

  st.TEXT AS SQLBatch,

  SUBSTRING(st.text,1+qs.statement_start_offset/2,(CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) as Query_Text,

  qp.query_plan,

  qs.creation_time,

  qs.execution_count,

  cp.usecounts AS UseCounts,

  cp.refcounts AS ReferenceCounts,

  qs.max_worker_time,

  cp.size_in_bytes/1048576. AS SizeMb

FROM sys.dm_exec_query_stats qs

LEFT JOIN sys.dm_exec_cached_plans AS cp on qs.[plan_handle] = cp.[plan_handle]

CROSS APPLY sys.dm_exec_sql_text(qs.[plan_handle]) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.[plan_handle]) AS qp;

 

Ctrl+9 - Database size/ Table size if table name used as a parameter.

SP_SPACEUSED

Ctrl+0 - Text of any Stored procedure, View, Trigger or Function:

SP_HELPTEXT