5 edited tags
| link
4 improving title
| link

Monitor number of records returned in a long-running SELECT statement during SSAS processing

3 added 340 characters in body
source | link

On a SQL Server 2014, I have an SSAS tabular model which is querying a relational clustered columnstore table (with 800 mio. records), when the model is being processed. The query is just a simple SELECT statement with no joins, filters, etc. I am processing the model through an XMLA command, which is being fired by a SQL Server Agent job. The processing takes a couple of hours.

To get an idea of the progress of the processing, I would like to know the number of records returned so far by the SELECT statement. Is there any way to do this, when I know the SPID of the SELECT statement?

sys.dm_exec_requests does not seem to contain any record count information.

Also, I have tried to look in the DMV's of Analysis Services, to see if any of these objects could give me the information I need, but no luck.

The reason I think it must be possible to obtain this information somehow, is that when I process the tabular model through the graphical IDE of SSMS, it actually shows the row count while processing.

Update: It turns out the sys.dm_exec_requests DMV actually has a row_count column. Unfortunately, the contents of this column does not seem to reflect reality. For the first hour or so of processing, it had the value of "3", but I know from experience that several million records would have been returned within the first hour.

On a SQL Server 2014, I have an SSAS tabular model which is querying a relational clustered columnstore table (with 800 mio. records), when the model is being processed. The query is just a simple SELECT statement with no joins, filters, etc. I am processing the model through an XMLA command, which is being fired by a SQL Server Agent job. The processing takes a couple of hours.

To get an idea of the progress of the processing, I would like to know the number of records returned so far by the SELECT statement. Is there any way to do this, when I know the SPID of the SELECT statement?

sys.dm_exec_requests does not seem to contain any record count information.

Also, I have tried to look in the DMV's of Analysis Services, to see if any of these objects could give me the information I need, but no luck.

The reason I think it must be possible to obtain this information somehow, is that when I process the tabular model through the graphical IDE of SSMS, it actually shows the row count while processing.

On a SQL Server 2014, I have an SSAS tabular model which is querying a relational clustered columnstore table (with 800 mio. records), when the model is being processed. The query is just a simple SELECT statement with no joins, filters, etc. I am processing the model through an XMLA command, which is being fired by a SQL Server Agent job. The processing takes a couple of hours.

To get an idea of the progress of the processing, I would like to know the number of records returned so far by the SELECT statement. Is there any way to do this, when I know the SPID of the SELECT statement?

sys.dm_exec_requests does not seem to contain any record count information.

Also, I have tried to look in the DMV's of Analysis Services, to see if any of these objects could give me the information I need, but no luck.

The reason I think it must be possible to obtain this information somehow, is that when I process the tabular model through the graphical IDE of SSMS, it actually shows the row count while processing.

Update: It turns out the sys.dm_exec_requests DMV actually has a row_count column. Unfortunately, the contents of this column does not seem to reflect reality. For the first hour or so of processing, it had the value of "3", but I know from experience that several million records would have been returned within the first hour.

2 added 89 characters in body
source | link
1
source | link