Archivi tag: sql

Performance Analysis SQL Server

sp_whoisactive: https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

CPU Real-Time SQL Utilization:

EXEC dbo.sp_WhoIsActive
@get_transaction_info=0,
@output_column_list ='[session_id][start_time]
                  [cpu][status][context_switches][wait_info][program_name]
                 [database_name][sql_text][host_name][open_tran_count]', 
@sort_order='[CPU]DESC'

/*delta*/
EXEC dbo.sp_WhoIsActive
@delta_interval=5, @get_task_info = 2,
@output_column_list ='[session_id][start_time][context switches]
                  [CPU_delta][reads_delta][writes_delta][tempdb_writes_delta]
                  [tempdb_reads_delta][tempdb_current_delta]
                  [database_name][host_name][login_name]', 
@sort_order='[CPU_delta]DESC'

Query History Log:

SELECT --TOP 1000 
[dd hh:mm:ss.mss]
      ,[session_id]
      ,[sql_text]
      ,[sql_command]
      ,[login_name]
      ,[wait_info]
      ,[CPU]
      ,[tempdb_allocations]
      ,[tempdb_current]
      ,[blocking_session_id]
      ,[blocked_session_count]
      ,[reads]
      ,[writes]
      ,[physical_reads]
      ,[used_memory]
      ,[status]
      ,[open_tran_count]
      ,[percent_complete]
      ,[host_name]
      ,[database_name]
      ,[program_name]
      ,[start_time]
      ,[login_time]
      ,[request_id]
      ,[collection_time]
  FROM [dbwhoisactive].[dbo].[tablelogwhoisactive]
    ORDER BY [collection_time] DESC

Simplified CPU Real-Time SQL Utilization:

select
    r.session_id,
    r.cpu_time,
    ((r.total_elapsed_time/1000.0)/60.0) as MinutesRunning,
    s.login_name,
    c.client_net_address,
    s.program_name
    ,sjj.name as Job
    ,sj.step_name as 'Step Job'
    ,st.text as query
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
left JOIN msdb.dbo.sysjobsteps sj
  ON SUBSTRING(s.[program_name],30,34) =
  CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sj.job_id), 1)
left JOIN msdb.dbo.sysjobs sjj
ON sj.job_id = sjj.job_id
where s.login_name <> 'sa' 
and r.session_id <> @@spid
order by r.cpu_time desc, ((r.total_elapsed_time/1000.0)/60.0) desc

Blocking Sessions SQL:

SELECT  DISTINCT    
[Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Host Name Block]     = ISNULL(ws.host_name, N''),        
[Login Block]         = ws.login_name,       
[Database Block]      = ISNULL(db_name(wp.dbid), N''),     
[Application Block]   = ISNULL(ws.program_name, N''),
[Session ID]    = s.session_id,
[Host Name]     = ISNULL(s.host_name, N''),        
[Login]         = s.login_name,       
[Database]      = ISNULL(db_name(p.dbid), N''),     
[Application]   = ISNULL(s.program_name, N''),
[SQL block] = ISNULL(wst.text,''),
[SQL] = ISNULL(st.text,'')
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) 
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id) 
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id
AND r.request_id = t.request_id) 
LEFT OUTER JOIN   (     
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num     
FROM sys.dm_os_waiting_tasks   ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1   
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN sys.dm_exec_sessions ws ON w.session_id=ws.session_id
LEFT OUTER JOIN sys.sysprocesses wp ON (ws.session_id = wp.spid)
LEFT OUTER JOIN sys.dm_exec_requests wr ON (ws.session_id = wr.session_id)
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(wr.sql_handle) wst
WHERE NOT w.blocking_session_id IS NULL
ORDER BY 1  desc;

 

SQL User Databases Relocation

To move a data or log file as part of a planned relocation, follow these steps:

  1. Run the following statement.
    ALTER DATABASE database_name SET OFFLINE;
  2. Move the file or files to the new location.
  3. For each file moved, run the following statement.
    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
  4. Run the following statement.
    ALTER DATABASE database_name SET ONLINE;sq
  5. Verify the file change by running the following query.
    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID(N'<database_name>');

Check SQL Index Fragmentation

USE database
GO
SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count, 
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'database'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO

Detect SQL Head blockers

select r.session_id, ss.host_name,r.blocking_session_id as block_id,
CASE WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
ELSE '' END as head,
r.status,r.wait_type, s.text as session_sql, r.wait_time as session_wait_time
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions ss on r.session_id = ss.session_id
LEFT OUTER JOIN sys.dm_exec_requests r2 ON ss.session_id = r2.blocking_session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) s
WHERE r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL)

 

Detect CPU expensive running queries

select
    r.session_id,
    r.cpu_time,
    ((r.total_elapsed_time/1000.0)/60.0) as MinutesRunning,
    s.login_name,
    c.client_net_address,
    s.program_name,
    st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
where s.login_name <> 'sa'
and r.session_id <> @@spid
--where st.text like '%your query string to search for%';
order by r.cpu_time desc