December 15, 2021 at 3:39 pm
Hi all,
I have a problem with this query:
SELECT top 10 (total_elapsed_time/execution_count)/1000 AS 'AVGEXETIME',
'AverageIO' = (total_logical_reads + total_logical_writes) / qs.execution_count,
'TotalIO' = (total_logical_reads + total_logical_writes),
'Executioncount' = qs.execution_count,
'IndividualQuery' = SUBSTRING (qt.text,(qs.statement_start_offset/2)+1,
((CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) ,'ParentQuery' = qt.text,'DatabaseName' = COALESCE(DB_NAME(qt.dbid),DB_NAME(CAST(pa.value as int))+'*','Resource'),qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid'
ORDER BY AverageIO DESC
When run in SQL 2019 this query it's very slow, but no problem when run in SQL 2012.
December 15, 2021 at 5:48 pm
My first step would be to load up the execution plan on both systems and make sure they look similar, if not identical.
Next, I would check for blocking while your query is being run. It MAY be even self-blocking in the event SQL tried to make it parallel and there is no good way to do that parallel and it blocks itself. Adding a query hint of MAXDOP 1 or adjusting cost threshold of parallelism to a higher number MAY help.
But without seeing the execution plan, it is hard to say why it is slower in 2019.
On a side note - a pet peeve of mine is inconsistent formatting. You seem to have a bit of it there. Your first column uses <column> AS <alias> and most of the others use <alias> = <column. Then you have a big chunk at the end where you have 4 columns returned all combined on a single line, where 2 have aliases and 2 don't. It won't cause any query failure, but if I had to review the code, it would fail verification as it is difficult to read and has inconsistent formatting and correcting the issue is fairly easy.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply