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
