20 January 2023

💎SQL Reloaded: Monitoring the Synapse serverless SQL pool with Dynamics Management Views II

Identifying the SQL Server DMVs which are accessible for the Serverless SQL pool (see previous post), allowed me to identify besides sys.dm_exec_requests_history three more DMVs with statistics on the statements executed on the server: sys.dm_request_phases, sys.dm_request_phases_task_group_stats and sys.dm_request_phases_exec_task_stats. Untofurtunately, there seems to be no documentation available on these DMVs, and, at the time the post was written, there were also no further hits on google.com or bing.com found on the same.

sys.dm_request_phases

sys.dm_request_phases  provides insights in the phases an execution statement goes through, and seems to summarize the other two views:

-- Azure Serverless SQL pool: request phases
SELECT TOP (100) dist_statement_id
, RPH.dist_request_id
, TRY_CAST(RPH.id as bigint) id
, TRY_CAST(RPH.parent_ids as bigint) parent_ids
, RPH.start_time
, RPH.end_time
--, RPH.total_elapsed_time_ms
--, RPH.total_elapsed_time_ms/1000.0 total_elapsed_time_sec
--, RPH.min_time_ms
--, RPH.min_time_ms/1000.0 min_time_sec
--, RPH.max_time_ms
--, RPH.max_time_ms/1000.0 max_time_sec
--, RPH.avg_time_ms
, RPH.avg_time_ms/1000.0 avg_time_sec
--, RPH.stdev_time_ms
--, RPH.stdev_time_ms/1000.0 stdev_time_sec -- it has no values
--, RPH.min_rows
--, RPH.max_rows
--, RPH.avg_rows
--, RPH.stdev_rows -- it has no values
, RPH.total_rows
--, RPH.total_bytes_processed
, RPH.total_bytes_processed/1028.0 total_kb_processed
, RPH.state_desc
, RPH.operation_type
, RPH.input_dop
, RPH.output_dop
, RPH.task_retries
, RPH.error_id
FROM sys.dm_request_phases RPH
ORDER BY Id
dist_statement_iddist_request_ididparent_idsstart_timeend_timeavg_time_sectotal_rowstotal_kb_processedstate_descoperation_typeinput_dopoutput_doptask_retrieserror_id
8C4386DC...820E9FC6...12...09:58:34.213...09:58:36.3371.03120302343.310311succeededShuffle1100
8C4386DC...820E9FC6...20...09:58:36.447...09:58:39.7131.89197145.193579succeededReturn1100
C9524971...680DCB55...34...10:05:46.747...10:05:47.0570.20320302343.310311succeededShuffle1100
C9524971...680DCB55...40...10:05:47.057...10:05:48.4801.40606630.101167succeededReturn1100
FD2D17AD...C9453EF2...56...11:58:54.060...11:58:55.2970.547101534.098249succeededComputeToControlNode1100
FD2D17AD...C9453EF2...60...11:58:55.297...11:58:55.4200.125104.074902succeededReturn1100
9FB0A268...CAA533DE...78...11:59:16.483...11:59:16.7000.20320302343.310311succeededShuffle1100
9FB0A268...CAA533DE...80...11:59:16.700...11:59:18.6401.92267143.673151succeededReturn1100
1732AB0D...AC1A4F10...910...11:59:25.950...11:59:26.1400.17220302343.310311succeededShuffle1100
1732AB0D...AC1A4F10...100...11:59:26.140...11:59:27.4501.29796635.185797succeededReturn1100

Notes:
1) The foreign keys and dates (in the above and below queries) were truncated to accomodate all the important attributes in the snapshot of the values returned.
2) Based on the exisitng queries, there are two records for each executed statement, a Shuffle or ComputeToControlNode followed by a Return (see operation_type). In more complex scenario there are several Shuffles and Broadcasts and a Return. According to the Microsoft team, even if for serverless SQL pools there's no Data Movement Service (DMS), there's a similar algorithm responsible for moving the data between the nodes.
3) Because in serverless SQL pool each query has its own distribution statement id, the min, max, avg and total values will have the sames values across the columns. Therefore, the columns with redundant values were commented.
4) The Id of the request phase seems to have numeric values despite being defined as alphanumeric. I tried to cast the values to bigint for sorting purposes.

sys.dm_request_phases_task_group_stats

sys.dm_request_phases_task_group_stats stores metadata about the requests breakdown at task group:

-- Azure Serverless SQL pool: request phases breakdown at task group
SELECT TOP (100) RPT.dist_request_id
, TRY_CAST(RPT.id as bigint) id
, TRY_CAST(RPT.parent_ids as bigint) parent_ids
, RPT.dist_statement_id
, RPT.state_desc
, RPT.start_time
, RPT.end_time
, RPT.input_dop
, RPT.output_dop
, RPT.operation_type
, RPT.task_retries
FROM sys.dm_request_phases_task_group_stats RPT
ORDER BY id
dist_request_ididparent_idsdist_statement_idstate_descstart_timeend_timeinput_dopoutput_dopoperation_typetask_retries
820E9FC6...128C4386DC...succeeded63809805514213255163809805516338269311Shuffle0
820E9FC6...208C4386DC...succeeded63809805516447616363809805519713300111Return0
680DCB55...34C9524971...succeeded63809805946745002163809805947057495311Shuffle0
680DCB55...40C9524971...succeeded63809805947057495363809805948479368211Return0
C9453EF2...56FD2D17AD...succeeded63809812734060711263809812735295106711ComputeToControlNode0
C9453EF2...60FD2D17AD...succeeded63809812735295106763809812735420297011Return0
CAA533DE...789FB0A268...succeeded63809812756482608463809812756701350411Shuffle0
CAA533DE...809FB0A268...succeeded63809812756701350463809812758638854911Return0
AC1A4F10...9101732AB0D...succeeded63809812765951362063809812766138851411Shuffle0
AC1A4F10...1001732AB0D...succeeded63809812766138851463809812767451360111Return0

Notes:
1) The DVM seems to return the same number of records as sys.dm_request_phases.
2) Observe the format of the start_time and end_time, probably the timestamps come from the Spark cluster and were not translated into an SQL Server data type.

sys.dm_request_phases_exec_task_stats

sys.dm_request_phases_exec_task_stats stores metadata about the requests breakdown at task level:

-- Azure Serverless SQL pool: request phases breakdown at task
SELECT TOP (100) RPE.dist_request_id
, TRY_CAST(RPE.id as bigint) id
--, RPE.min_time_ms
--, RPE.max_time_ms
, RPE.avg_time_ms/1000.0 avg_time_sec
--, RPE.stdev_time_ms
, RPE.total_bytes_processed
--, RPE.min_rows
--, RPE.max_rows
--, RPE.avg_rows
--, RPE.stdev_rows
, RPE.total_rows
, RPE.error_id
FROM sys.dm_request_phases_exec_task_stats RPE
ORDER BY id
dist_request_ididavg_time_sectotal_kb_processedtotal_rowserror_id
820E9FC6...11.0312343.31031120300
820E9FC6...21.8917145.19357990
680DCB55...30.2032343.31031120300
680DCB55...41.4066630.10116700
C9453EF2...50.5471534.098249100
C9453EF2...60.1254.074902100
CAA533DE...70.2032343.31031120300
CAA533DE...81.9227143.67315160
AC1A4F10...90.1722343.31031120300
AC1A4F10...101.2976635.18579790

What does all this mean?

The lack of documentation makes it challenging to interpret the values of the views besides the data and metadata they offer. In a paper on POLARIS, the code given to the serveless SQL pool engine, a taks is defined as "a careful packaging of data and query processing into units [...] that can be readily moved across compute nodes and re-started at the task level" [1]. Therefore, one can assume that this is the level targetted by the sys.dm_request_phases_exec_task_stats DMV. Further on, the tasks are grouped at phase level according to the sys.dm_request_phases_task_group_stats, the metadata from the two DMVs being further combined into sys.dm_request_phases DMV. 

If the meaning is kept from dedicated SQL pools, a shuffle operation indicates that data is moved between the frontend and backend nodes to satisfy a request, while a Result represents the operation of returning the result selt to client. The "ComputeToControlNode" operation involves a simple select (e.g. SELECT top 10) from a CETA and therefore no "Shuffle" is needed.

Requests' history

Further on, one can use the "Distributed statement id" to join the execution request phases with the request history, however matches will be found only for a small subset of the records (probably the executions since the pool started):
 
-- Azure Serverless SQL pool: requests history with request phase info
SELECT top 100 ERH.status
, ERH.transaction_Id
, ERH.distributed_statement_Id 
, ERH.query_hash 
--, ERH.login_name 
, ERH.start_time
, ERH.end_time 
, ERH.command 
, ERH.query_text 
--, ERH.total_elapsed_time_ms
, ERH.total_elapsed_time_ms/1000.0 total_elapsed_time_sec
--, ERH.data_processed_mb
, ERH.data_processed_mb
, RPH.avg_time_ms/1000.0 avg_time_sec
, RPH.total_rows
, RPH.total_bytes_processed/1028.0/1028.0 total_mb_processed
, RPH.state_desc
, RPH.operation_type
, RPH.input_dop
, RPH.output_dop
, RPH.task_retries
, RPH.error_id
, ERH.error
, ERH.error_code 
FROM sys.dm_exec_requests_history ERH
     JOIN sys.dm_request_phases RPH
	   ON ERH.distributed_statement_Id = RPH.dist_statement_id
	  --AND RPH.parent_ids = 0 -- only the parent
ORDER BY RPH.Id DESC

Here's a subset of the result set focusing only on the statistical values:
 
distr_statement_Idstart_timeend_timetotal_elapsed_time_secdata_processed_mbavg_time_sectotal_rowstotal_mb_processedoperation_typeidparent_ids
{8C4386D......8:24.4300000...8:39.826666615.396101.03120302.279484738326Shuffle12
{8C4386D......8:24.4300000...8:39.826666615.396101.89196.950577411478Return20
{C952497......5:45.2100000...5:48.49333333.283100.20320302.279484738326Shuffle34
{C952497......5:45.2100000...5:48.49333333.283101.40606.449514753891Return40
{FD2D17A......8:52.1400000...8:55.41666663.276100.547101.492313471789ComputeToControlNode56
{FD2D17A......8:52.1400000...8:55.41666663.276100.125100.003963912451Return60
{9FB0A26......9:15.1300000...9:18.63666663.506100.20320302.279484738326Shuffle78
{9FB0A26......9:15.1300000...9:18.63666663.506101.92266.949098395914Return80
{1732AB0......9:24.6900000...9:27.45000002.76100.17220302.279484738326Shuffle910
{1732AB0......9:24.6900000...9:27.45000002.76101.29796.454460892023Return100

Notes:
As can be seen, the volume of data processed and the elapsed time values don't match between the two tables, though they are close. The differences probably result from further steps occuring in the process. 

Happy coding!

Previous Post  <<||>>  Next Post

References:
[1] Josep Aguilar-Saborit, Raghu Ramakrishnan et al, "POLARIS: The Distributed SQL Engine in Azure Synapse", VLDB Conferences. PVLDB, 13(12): 3204 – 3216, 2020, DOI: https://doi.org/10.14778/3415478.3415545

15 January 2023

💎🏭SQL Reloaded: Monitoring the Synapse serverless SQL pool with Dynamics Management Views I

I feel sometimes flying blind when I build or troubleshoot SQL queries and I don't have the query plan and/or further statistics to understand how the database engine works, why some queries take longer than expected, etc. Unfortunately, Synapse serverless SQL pool doesn't seem to support showing exection plans in SQL Server Management Studio as per now (SHOWPLAN_XML is not supported for SET). I looked at my old queries based on the the sys.dm_exec_requests and sys.dm_exec_query_stats  DMVs, however the results didn't proved to be what I was searching for. (

This weekend, I found Sidney Cirqueira's post on monitoring Synapse serverless SQL pools where he describes how to do that via the Monitoring hub, DMVs, QPI library, respectively Log Analytics. (You should check regularly the Azure Synapse Analytics Blog as it's full of goodies!)

Thus, I found out that there's a new DMV called sys.dm_exec_requests_history which provides at least the duration and the volume of data processes by each statement run on the service:

-- Azure Serverless SQL pool: requests' history 
SELECT top 100 ERH.status
, ERH.transaction_Id
, ERH.distributed_statement_Id 
, ERH.query_hash 
, ERH.login_name 
, ERH.start_time
, ERH.end_time 
, ERH.command 
, ERH.query_text 
--, ERH.total_elapsed_time_ms
, ERH.total_elapsed_time_ms/1000.0 total_elapsed_time_sec
--, ERH.data_processed_mb
, ERH.data_processed_mb/1028.0 data_processed_gb
, ERH.error
, ERH.error_code 
FROM sys.dm_exec_requests_history ERH
ORDER BY ERH.data_processed_mb DESC

It isn't much information, compared with the columns returned by sys.dm_exec_requests, but it's something to start with. At least it allows focusing on the queries with the longest duration (use the above query sorting the records based on the total_elapsed_time_ms descending) or highest volume of data processed:

-- Azure Serverless SQL pool: queries with most data processed
SELECT TOP 50 ERH.query_text  
, COUNT(*) no_runs
, SUM(ERH.total_elapsed_time_ms) total_elapsed_time_ms
, SUM(ERH.data_processed_mb) data_processed_mb
, SUM(ERH.data_processed_mb/1028.0) data_processed_gb
, MIN(ERH.start_time) first_run_date
, MAX(ERH.start_time) last_run_date
FROM sys.dm_exec_requests_history ERH
GROUP BY ERH.query_text
HAVING COUNT(*)>1
ORDER BY data_processed_mb DESC

The same query can be slightly changed to retrieve the volume of data processed by month:
 
-- Azure Serverless SQL pool: data processed by month
SELECT Convert(nvarchar(7), ERH.start_time, 23) [period]
, COUNT(*) no_runs
, SUM(ERH.total_elapsed_time_ms) total_elapsed_time_ms
, SUM(ERH.data_processed_mb) data_processed_mb
, SUM(ERH.data_processed_mb/1028.0) data_processed_gb
, MIN(ERH.start_time) first_run_date
, MAX(ERH.start_time) last_run_date
FROM sys.dm_exec_requests_history ERH
GROUP BY Convert(nvarchar(7), ERH.start_time, 23)
HAVING COUNT(*)>1
ORDER BY data_processed_mb DESC

One can add in the grouping also the login name to break down the analysis by the login that issued the query. Organization's domain can be used to differentiate between system or organization-baed queries.

The volume of data processed is stored also in the sys.dm_external_data_processed DMV aggregated for the current day, week, respectively month as part of the cost control related feature:
 
-- Azure Serverless SQL pool: volume of data processed
SELECT type 
, data_processed_mb 
, data_processed_mb/1028.0 data_processed_gb
FROM sys.dm_external_data_processed

And here's how the output looks like:
 
typedata_processed_mbdata_processed_gb
daily2300.223735
weekly3770.366731
monthly223522217.433852

Notes:
1) I still need to play with the DMVs to understand their scope and limitations.
2) The view appears also in the list of DMVs I idenfitied to be supported the by Synapse serverless SQL pool. As I discoered later, 3 more DMVs are available with useful statistics.
3) The queries based on sys.dm_exec_requests and sys.dm_exec_query_stats DMVs seem to return only the running query based on them.  (Actually, the DMVs seem to work.)
4) The view is available also in SQL Server 2022, though it doesn't seem to be used.
5) According to the above-mentioned source, the view is provided for ticket purposes to help customers better troubleshooting the SQL requests. Use the distributed_statement_id in the tickets raised with Microsoft to troubleshoot any issues with Synapse.
6) Unfortunately, also the useful Query Store feature is not yet supported, even if the DMVs related to it seem to be available. Attempting to enable it results in the error:
Msg 15869, Level 16, State 9, Line 1
QUERY_STORE is not supported for ALTER DATABASE


Happy coding!

💎🏭SQL Reloaded: Data Management Views for the Synapse serverless SQL pool (& Microsoft Fabric Warehouse)

Unfortunately, the Dynamic Management Views (DMVs) for serverless SQL Server pools don't seem to be documented (or at least I haven't found them in the standard SQL Server documentation). I was thinking some weeks back how I could retrieve them easily as cursors aren't supported in serverless. In the end the old-fashioned loop got the job done (even if might not be the best way to do it):
 
-- retrieving the data management views in use with the number of records they held
DECLARE @view_name nvarchar(150)
DECLARE @sql nvarchar(250)
DECLARE @number_records bigint 
DECLARE @number_views int, @iterator int

DROP TABLE IF EXISTS dbo.#views;

CREATE TABLE dbo.#views (
  ranking int NOT NULL
, view_name nvarchar(150) NOT NULL
)

INSERT INTO #views
SELECT row_number() OVER(ORDER BY object_id) ranking
, concat(schema_name(schema_id),'.', name) view_name
FROM sys.all_views obj
WHERE obj.Type = 'V'
  AND obj.is_ms_shipped = 1
  --AND obj.name LIKE 'dm_exec_requests%'
ORDER BY view_name
SET @iterator = 1 SET @number_views = IsNull((SELECT count(*) FROM #views), 0) WHILE (@iterator <= @number_views) BEGIN SET @view_name = (SELECT view_name FROM #views WHERE ranking = @iterator) SET @sql = CONCAT(N'SELECT @NumberRecords = count(*) FROM ', @view_name) BEGIN TRY --get the number of records EXEC sp_executesql @Query = @sql , @params = N'@NumberRecords bigint OUTPUT' , @NumberRecords = @number_records OUTPUT IF IsNull(@number_records, 0)> 0 BEGIN SELECT @view_name, @number_records END END TRY BEGIN CATCH -- no action needed in case of error END CATCH; SET @iterator = @iterator + 1 END DROP TABLE IF EXISTS dbo.#views;

As can be seen the code above retrieves the system views and dumps them in a temporary table, then loops through each record and for each record retrieves the number of records available with the sp_executesql. The call to the stored procedure is included in a TRY/CATCH block to surpress the error messages, considering that many standard SQL Server DMVs are not supported. The error messages follow the same pattern: 

Msg 15871, Level 16, State 9, Line 187
DMV (Dynamic Management View) 'dm_resource_governor_resource_pool_volumes' is not supported. 

 On the instance I tested the code, from a total of 729 DMVs only 171 records were returned, though maybe there are some views not shown because the feature related to them was not yet configured:
 
View nameDescription
INFORMATION_SCHEMA.COLUMNSReturns one row for each column (*)
INFORMATION_SCHEMA.PARAMETERSReturns one row for each parameter of a user-defined function or stored procedure (*)
INFORMATION_SCHEMA.ROUTINE_COLUMNSReturns one row for each column returned by the table-valued functions (*)
INFORMATION_SCHEMA.ROUTINESReturns one row for each stored procedure and function (*)
INFORMATION_SCHEMA.SCHEMATAReturns one row for each schema in the current database
INFORMATION_SCHEMA.TABLESReturns one row for each table or view in the current database (*)
INFORMATION_SCHEMA.VIEW_COLUMN_USAGEReturns one row for each column in the current database that is used in a view definition
INFORMATION_SCHEMA.VIEW_TABLE_USAGEReturns one row for each table in the current database that is used in a view
INFORMATION_SCHEMA.VIEWSReturns one row for each view that can be accessed by the current user in the current database
sys.all_columns
sys.all_objects
sys.all_parameters
sys.all_sql_modules
sys.all_views
sys.allocation_units
sys.assemblies
sys.assembly_files
sys.assembly_types
sys.columns
sys.configurations
sys.credentials
sys.data_spaces
sys.database_automatic_tuning_options
sys.database_automatic_tuning_options_internal
sys.database_credentials
sys.database_files
sys.database_filestream_options
sys.database_mirroring
sys.database_mirroring_endpoints
sys.database_permissions
sys.database_principals
sys.database_query_store_internal_state
sys.database_query_store_options
sys.database_recovery_status
sys.database_resource_governor_workload_groups
sys.database_role_members
sys.database_scoped_configurations
sys.database_scoped_credentials
sys.databases
sys.dm_exec_connections
sys.dm_exec_query_stats
sys.dm_exec_requestsReturns information about each request that is executing in SQL Server.
sys.dm_exec_requests_historyReturns information about each request that executed in SQL Server; provided by Microsoft for troubleshooting.
sys.dm_exec_sessions
sys.dm_external_data_processed
sys.dm_os_host_info
sys.dm_request_phasesReturns information about each request phase performed in request's execution.
sys.dm_request_phases_exec_task_statsReturns information about each task performed in request's execution.
sys.dm_request_phases_task_group_statsReturns information aggregated at task group level about each task performed in request's execution.
sys.endpoints
sys.event_notification_event_types
sys.extended_properties
sys.external_data_sources
sys.external_file_formats
sys.external_language_files
sys.external_languages
sys.external_table_columns
sys.external_tables
sys.filegroups
sys.fulltext_document_types
sys.fulltext_languages
sys.fulltext_system_stopwords
sys.identity_columns
sys.index_columns
sys.indexes
sys.internal_tables
sys.key_encryptions
sys.linked_logins
sys.login_token
sys.master_files
sys.messages
sys.objects
sys.parameters
sys.partitions
sys.procedures
sys.query_store_databases_health
sys.query_store_global_health
sys.resource_governor_configuration
sys.resource_governor_external_resource_pools
sys.resource_governor_resource_pools
sys.resource_governor_workload_groups
sys.routes
sys.schemas
sys.securable_classes
sys.server_audit_specification_details
sys.server_audit_specifications
sys.server_audits
sys.server_event_session_actions
sys.server_event_session_events
sys.server_event_session_fields
sys.server_event_session_targets
sys.server_event_sessions
sys.server_memory_optimized_hybrid_buffer_pool_configuration
sys.server_permissions
sys.server_principals
sys.server_role_members
sys.servers
sys.service_contract_message_usages
sys.service_contract_usages
sys.service_contracts
sys.service_message_types
sys.service_queue_usages
sys.service_queues
sys.services
sys.spatial_reference_systems
sys.sql_dependencies
sys.sql_expression_dependencies
sys.sql_logins
sys.sql_modules
sys.stats
sys.stats_columns
sys.symmetric_keys
sys.sysaltfiles
sys.syscacheobjects
sys.syscharsets
sys.syscolumns
sys.syscomments
sys.sysconfigures
sys.syscurconfigs
sys.sysdatabases
sys.sysdepends
sys.sysfilegroups
sys.sysfiles
sys.sysindexes
sys.sysindexkeys
sys.syslanguages
sys.syslockinfo
sys.syslogins
sys.sysmembers
sys.sysmessages
sys.sysobjects
sys.sysoledbusers
sys.sysperfinfo
sys.syspermissions
sys.sysprocesses
sys.sysprotects
sys.sysservers
sys.system_columns
sys.system_components_surface_area_configuration
sys.system_internals_allocation_units
sys.system_internals_partition_columns
sys.system_internals_partitions
sys.system_objects
sys.system_parameters
sys.system_sql_modules
sys.system_views
sys.systypes
sys.sysusers
sys.tables
sys.tcp_endpoints
sys.time_zone_info
sys.trace_categories
sys.trace_columns
sys.trace_event_bindings
sys.trace_events
sys.trace_subclass_values
sys.trigger_event_types
sys.type_assembly_usages
sys.types
sys.user_token
sys.via_endpoints
sys.views
sys.xml_schema_attributes
sys.xml_schema_collections
sys.xml_schema_component_placements
sys.xml_schema_components
sys.xml_schema_facets
sys.xml_schema_model_groups
sys.xml_schema_namespaces
sys.xml_schema_types
sys.xml_schema_wildcards

Notes:
1) As can be seen, also the INFORMATION_SCHEMA views don't seem to be fully supprted.
2) "(*)" in description marks the views that can be accessed by the current user in the current database.
3) I removed the number of records as they are instance specific.
4) The code should work also on a dedicated SQL Server pool.
5) I hope to come back and showcase the usage of some of the most important views. 
6) The script can be used for the Microsoft Fabric Warehouse, however each record will be shown in a different panel! One can use an additional temporary table to save the results or extend the views table and update the table with the result, like in the following script:

-- retrieving the data management views in use with the number of records they held
DECLARE @view_name nvarchar(150)
DECLARE @sql nvarchar(250)
DECLARE @number_records bigint 
DECLARE @number_views int, @iterator int

DROP TABLE IF EXISTS dbo.#views;

CREATE TABLE dbo.#views (
  ranking int NOT NULL
, view_name nvarchar(150) NOT NULL
, record_count bigint NULL
)

INSERT INTO #views
SELECT row_number() OVER(ORDER BY object_id) ranking
, concat(schema_name(schema_id),'.', name) view_name
, NULL record_count
FROM sys.all_views obj
WHERE obj.Type = 'V'
  AND obj.is_ms_shipped = 1
  --AND obj.name LIKE 'dm_exec_requests%'
ORDER BY view_name

SET @iterator = 1
SET @number_views = IsNull((SELECT count(*) FROM #views), 0)

WHILE (@iterator <= @number_views)
BEGIN 
    SET @view_name = (SELECT view_name FROM #views WHERE ranking = @iterator)
    SET @sql = CONCAT(N'SELECT @NumberRecords = count(*) FROM ', @view_name)

	BEGIN TRY
		--get the number of records
		EXEC sp_executesql @Query = @sql
		, @params = N'@NumberRecords bigint OUTPUT'
		, @NumberRecords = @number_records OUTPUT

		IF IsNull(@number_records, 0)>= 0  
		BEGIN
		  UPDATE #views
                  SET record_count = @number_records
                  WHERE view_name = @view_name
		END 
	END TRY
	BEGIN CATCH  
	 -- no action needed in case of error
    END CATCH;

	SET @iterator = @iterator + 1
END

SELECT *
FROM dbo.#views;

DROP TABLE IF EXISTS dbo.#views;

Happy coding!

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.