I’ve been having some big problems with 100% CPU usage at intermittent times, but recently getting worse as it’s a shared database which is used for both Intranet applications and an external large multi-tenancy web CMS platform.

I have have used the following query from Microsoft to find the 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

This has helped me find the query that’s causing the issue and also to build an index that should help the issue.

The query it has returned is this;

(@P1 varchar(5))SELECT a.id_e, a.path, a.filename, a.width, a.height, b.name AS type, c.name AS object, image_number
                    FROM ila.dbo.images a
                    JOIN ila.dbo.image_types b ON a.type = b.id
                    JOIN ila.dbo.image_objects c ON b.object = c.id
                    WHERE a.sid = @P1 and b.name in ('info', 'thumbnail', 'source', 'original', 'large', 'main', 'main_large', 'main_extra_large', 'extra_large') ORDER BY a.image_number ASC

What would really help me to do this is if I could get SQL Server to actually tell me what the @P1 param that is being passed in, this is a unique site identifier and PK.

Is there anyway to modify the original query from Microsoft so that the actual @P1 param value, will be shown when query ran?