Reports :Queries using most CPU worker time •Shows the longest running queries across your estate.
karl.oliver
Posts: 33 Bronze 1
is there a way to increase number of queries reported on? appears to be set at 5 which is really too low
Tagged:
Comments
The individual server overviews show the top 10 queries, and these can be sorted by different criteria such as Execution count, CPU Time, Logical reads etc. A new table is generated according to the selected option, so different queries are likely to be displayed.
To look at the worst performing queries across an estate, the reports are used, and these currently only show the top 5. However, this is something we're looking into. I'm interested to know a bit more about how you would use this:
- how many queries would you typically want to see across your estate (for example, are there cases where the 50th worst query is something you would take a look at, or is it more likely fewer)
- what do you do with the 'Top x queries' list? Do you have a process of working through them? Is it for troubleshooting, or to head off problems?
Any further detail you have on this would be really useful as we consider where to focus our efforts.
Thanks
Adam
Our data warehouse dev team are keen to get a report for troubleshooting, and to head off problems 10 would be sufficient. in our top 5 the highest cpu user is not even a dw job
no idea what it is it takes one of the top 5 spaces, in fact is the top cpu usage As i preciously mentioned somewhere it would be really good if user and time query was run is captured.example below we dont even know what it is when it was run or who runs it ! so the recording of it is really not useful.
SELECT sm.object_id AS id, definition AS text, 1 AS colid, (CASE WHEN OBJECTPROPERTY(so.object_id, N'IsReplProc')<>0 OR so.type = 'P' THEN 1 ELSE 0 END) AS number, CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit) AS Encrypted, -- https://msdn.microsoft.com/en-us/library/ms175081.aspx CONVERT(bit, CASE WHEN so.type = 'D' AND so.parent_object_id != 0 THEN 1 ELSE 0 END) AS IsDefault, ISNULL(sm.uses_quoted_identifier, 0) AS QuotedIdentifier, NULL AS ctext FROM sys.sql_modules sm WITH (NOLOCK) LEFT JOIN sys.objects so WITH (NOLOCK) ON so.object_id = sm.object_id WHERE (so.type in ('P', 'V', 'TR', 'TA', 'IF', 'FN', 'TF',