top of page
Search
  • ABS

SQL Slow Performance

Check heavy query:


SELECT TOP 10 s.session_id,

r.status,

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time / (1000 * 60) 'Elaps M',

SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset) / 2) + 1) AS statement_text,

COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))

+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,

r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY r.cpu_time DESC

 


Check missing index:

SELECT CONVERT (VARCHAR(30),

GETDATE(),

126) AS runtime,

mig.index_group_handle,

mid.index_handle,

CONVERT (DECIMAL (28, 1),

migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

'CREATE INDEX missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)

+ ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,

'') + CASE WHEN mid.equality_columns IS NOT NULL

AND mid.inequality_columns IS NOT NULL THEN ','

ELSE ''

END + ISNULL (mid.inequality_columns,

'') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')',

'') AS create_index_statement,

migs.*,

mid.database_id,

mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE CONVERT (DECIMAL (28, 1),

migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

9 views0 comments

Recent Posts

See All

ivend-SAP Integration Monitor from SBO to ivend Query

Select IntegrationKey,Cast(SourceType AS Varchar(20)) As SourceType,SourceKey, OperationType, isNull(LogDatetime, getdate()) As LogDatetime, Case isNull(Status, 0) When 0 Then 'Not Processed' Else 'Fa

Post: Blog2_Post
bottom of page