Tuesday, March 19, 2013

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 =
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