How to check progress / estimated percentage done for a SELECT query / statement in SQL Server

Assuming you have a long-running SELECT statement
or SELECT query and you want to see its progress,
like how many percents already done
(percentage done/the percentage of work completed
/Progress (% Complete)).



Is there a way to see the progress of a SELECT statement
in SQL Server?

I will say it again, we are talking about a SELECT query and not operations that
populate the “percent_complete” field in sys.dm_exec_requests

Like below:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP

DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

Well, there is a way (with some limitations, of course:)).
it is not perfect, but it will definitely give you some visibility.

The approach is to compare the estimate row counts
vs the actual row counts for each node in the execution plan.

It will not work accurately in case you have an incorrect estimated number of rows
Also if there are some lock waits (blocking), they will break the calculations.

To use this query/approach you need to be on SQL Server 2019 (15.x)
Or to enable trace flag 7412 (lightweight profiling) starting from SQL 2016 (13.x) SP1
This trace flag will tell SQL Server to expose additional information
For each node in the execution plan, like how many rows already processed.

Note: Lightweight profiling is enabled by default in SQL Server 2019 (15.x)

SELECT er.session_id,

er.start_time, 

DATEDIFF(ms,er.start_time,GETDATE()) AS elapsed_time_ms,

DATEDIFF(ss,er.start_time,GETDATE()) AS elapsed_time_Sec,

er.wait_type, er.wait_time, 

SUBSTRING(st.TEXT, (er.statement_start_offset / 2) + 1,((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE er.statement_end_offset END - er.statement_start_offset) / 2) + 1) AS statement_text, 

CONVERT(XML, qp.query_plan) query_plan, 

CASE WHEN sum(eqp.estimate_row_count) > 0 THEN 100 * SUM(eqp.row_count) / SUM(eqp.estimate_row_count) ELSE NULL END AS percent_done

FROM sys.dm_exec_requests er

INNER JOIN sys.dm_exec_query_profiles eqp ON er.session_id = eqp.session_id AND er.request_id = eqp.request_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st

CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle, er.statement_start_offset, er.statement_end_offset) qp

WHERE er.session_id > 50 

AND er.session_id != @@spid

GROUP BY er.start_time, er.session_id, er.wait_type, er.wait_time, st.TEXT, er.statement_start_offset, er.statement_end_offset, qp.query_plan

HAVING SUM(eqp.estimate_row_count) > 0

Try it 😊
Of course, it works also for SELECT statements with multiple JOINs.

Source:
http://blog.sqlgrease.com/how-to-view-percent-done-of-a-long-executing-query/?unapproved=27776&moderation-hash=d582e3993f3b1f9ed5a1e1c3dc012222#comment-27776

How to Copy Error Message Text

To continue with off topic tips, like in my previous post.
I would like to share with you another great tip.

Do you ever wanted to copy the error message from
windows pop up?
For example, to copy the text from:

This is very simple, just press Ctrl-C when the error message pop
up is active, meaning it selected(Blue color in the pop up header)

After this the error message will be copied to Clipboard and
you can past it, for example, in notepad.

That`s all folks, thanks for reading.

How to replace ENTER with comma in MS Word

I want to share with you very useful tip I got from my college.
There are cases when you need to generate a comma separated
string for 'IN' clause in a Select statement. For example:

SELECT    *
FROM    SomeTable
WHERE  SomeColumn IN (xxx,yyyy,www,zzzz)

Let`s assume that you have some values separated by Enter,
like this:

42344
32432
54435
45345
43534
45243

This also can be the result of copying some column values
from a select in the SSMS:

Ok, suppose you copied these values and pasted them in MS Word
document. The quickest way to replace the Enters with commas
is to use Find and Replace option (Ctrl-H)

Press Ctrl-H and enter the following:

Now click on "Replace All" button:

Here are some more useful codes you can use:

TAB CHARACTER
Type ^t or type ^9

ANY DIGIT
Type ^#

ANY LETTER
Type ^$

You can see more codes here

Note: All this above can be also done in SSMS by using Regular Expressions:
See my post about it.

P.S: do you know the way how to do this in Notepad?
I think it is not possible , because the "Replace" dialog box
in Notepad does not work with non-printing characters.

I will be glad to know if I am wrong.