How to SELECT COUNT from tables that are currently INSERT?

Question

Hi, there is an INSERT statement that works in the table TABLE_A, which takes a lot of time, I would like to see how it progressed.

I tried to open a new session (new request window in SSMS), while the long statement is still in process, I executed the request

SELECT COUNT(1) FROM TABLE_A WITH (nolock) 

hoping that it will return immediately with the number of rows each time I run the query, but the test result was even with (nolock), but still it returns only after the INSERT statement is completed.

What did I miss? Am I adding (nolock) to an INSERT statement? Or is it impossible?

---

(Edit) Well, I found what I missed. If you use CREATE TABLE TABLE_A first, then INSERT INTO TABLE_A will work SELECT COUNT. If you use SELECT * INTO TABLE_A FROM xxx without first creating TABLE_A, then not the following will work: not even sysindexes.

Answer 1

If you are using SQL Server 2016, the Direct Query Statistics feature allows you to view the insertion progress in real time.

A screenshot was taken when inserting 10 million rows into a table with a clustered index and one nonclustered index.

This shows that the insert was 88% full at the clustered index, and it was followed by a sort operator to get the values ​​in the order of the non-clustered index before inserting into the NCI. This is a lock statement, and sorting cannot output lines until all input lines have been consumed, so the statements to the left of them will be executed at 0%.

Regarding your question on NOLOCK

Trivial check

Compound 1

 USE tempdb CREATE TABLE T2 ( X INT IDENTITY PRIMARY KEY, F CHAR(8000) ); WHILE NOT EXISTS(SELECT * FROM T2 WITH (NOLOCK)) LOOP: SELECT COUNT(*) AS CountMethod FROM T2 WITH (NOLOCK); SELECT rows FROM sysindexes WHERE id = OBJECT_ID('T2'); RAISERROR ('Waiting for 10 seconds',0,1) WITH NOWAIT; WAITFOR delay '00:00:10'; SELECT COUNT(*) AS CountMethod FROM T2 WITH (NOLOCK); SELECT rows FROM sysindexes WHERE id = OBJECT_ID('T2'); RAISERROR ('Waiting to drop table',0,1) WITH NOWAIT DROP TABLE T2 

Compound 2

 use tempdb; --Insert 2000 * 2000 = 4 million rows WITH T AS (SELECT TOP 2000 'x' AS x FROM master..spt_values) INSERT INTO T2 (F) SELECT 'X' FROM T v1 CROSS JOIN T v2 OPTION (MAXDOP 1) 

Sample Results - Showing Row Increase

SELECT queries with NOLOCK allow dirty reads. They actually don't lock the locks and can still be locked, they still need to lock the SCH-S (circuit stability) on the table ( and in the heap it will also accept the hobt lock ).

The only thing incompatible with SCH-S is the SCH-M lock (circuit modification). Presumably, you also executed some DDL in the table in the same transaction (for example, you might have created it in the same channel)

In the case of using a large insert, where the approximate result of the flight is good, I usually just polled sysindexes , as shown above, to extract the count from the metadata, and not to actually count the rows ( non-obsolete alternative DMVs are available )

When an insert has a broad update plan , you can even see how it inserts various indexes there.

If a table is created inside an insert transaction, this sysindexes request will still be blocked, although the OBJECT_ID function OBJECT_ID not return a result based on uncommitted data regardless of isolation level. Sometimes you can get around this by sys.tables NOLOCK instead of object_id from sys.tables .

Answer 2

The short answer . You cannot do this.

Longer answer . One INSERT statement is atomic . Thus, the query either inserted all the rows or inserted none of them. Therefore, you cannot calculate how far he has come.

An even longer answer : Martin Smith gave you a way to get what you want. If you still want to do this, of course, to you. Personally, I still prefer to embed in controlled parties, if you really need to track the progress of something like that. Therefore, I would rewrite INSERT as several smaller statements. Depending on your implementation, this may be a trivial task.

Answer 3

Use the query below to find a counter for any large table or locked table or inserted table in seconds. Just replace the name of the table you want to find.

 SELECT Total_Rows= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'TABLENAME' AND (index_id < 2)