1 way of fetching high cpu queries

-- top 10 queries by worker time

SELECT TOP 10

[qs].[last_worker_time],

[qs].[max_worker_time],

[qs].[total_worker_time],

[qs].[execution_count],

stmt_start = [qs].[statement_start_offset],

stmt_end = [qs].[statement_end_offset],

[qt].[dbid],

[qt].[objectid],

SUBSTRING([qt].[text], [qs].[statement_start_offset] / 2,

(CASE WHEN [qs].[statement_end_offset] = -1

THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text])) * 2

ELSE...

Thanks Jeff for those pointers. I am basically checking for an extended event trace definition file to track these numbers.

I never used sp_blitz and not sure how it will behave...

Checking on what columns should be of interest if we create some sort of trace.

Thanks Jeff.

How can sp_whoisactive help?

Anyone has any perfmon.msc file to measure how busy is my sql server?

Hi David,

You mean to say, I/O and CPU goes hand in hand?

How to identify if it is M.A.R.S and disable it?

Got it. Thank you.

I want to consider "Backup Size GB" and "DeltaNormal" columns and calculate percentage.

I know the formula (difference/old_or_prev_val)*100.0 but I am stuck I am stuck how to compare the previous row...

Hi Jeffrey,

Thanks for the explaination.

Is there a DMV or a way to confirm that redo is happening ?

-Bob

Hi All,

Thanks for sharing your inputs. Some observations before we resolved the issue.

The log_reuse_wait_desc was initially showing "Active_Transaction". Then we had to check with the application team and saw some...

Is there a proper way of capturing sql stmts which are causing the log file to grow abnormally ?

No backups are written to these drives.

What is the right way of what is causing these I/O warnings and what needs to be captured or monitored?

Thanks Michael. After sql restart, the database went to Recovery pending state and we were able to drop the database.