Sql-server – Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql

dynamic-sqloptimizationparallelismsql serversql-server-2016

I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will execute it with sp_ExecuteSql.

Is SQL Server able to paraellelize each batch of queries and is there a way I can tell when it will parallelize them? (I guess I could print my dynamic SQL, and copy the query batches into a new worksheet and look at the execution plan to verify if the optimizer is planning on parallelizing them.)

Typically I'm trying to aggregate the data from each database into one temp table I declare outside of the dynamic SQL. Here's an example:

DECLARE @DynamicSql AS NVARCHAR(MAX) = ''

DROP TABLE IF EXISTS #Results
SELECT TOP 0 -1 AS DatabaseId, -1 AS ResultId, -1 AS ResultValue
INTO #Results

SELECT @DynamicSql = @DynamicSql +
'   
    USE [' + T.DatabaseName + '];

    INSERT INTO #Results
    SELECT ' + CAST(T.DatabaseId AS VARCHAR(10)) + ' AS DatabaseId, T.ResultId, T.ResultValue
    FROM SomeTable AS T
    WHERE T.SomeCondition = ''SomeValue'';
'
FROM MyToolkit.dbo.vwDatabaseInstances

--PRINT @DynamicSql
EXEC sp_ExecuteSql @DynamicSql 

SELECT *
FROM #Results

Best Answer

Looking at the execution plan, you can see that every query is done as a different query. Again, if you want to see if those query are using parallelism, you will see it in the execution plan.

If each of the query can go parallel, then they will.

Running this query agains StackOverFlow2013 DB:

create table #tbl (tbl varchar(30)); insert into #tbl values ('Comments'),('Comments')

DECLARE @DynamicSql AS NVARCHAR(MAX) = ''

DROP TABLE IF EXISTS #Results SELECT TOP 0 -1 AS DatabaseId, -1 AS ResultId, -1 AS ResultValue INTO #Results

SELECT @DynamicSql = @DynamicSql + 'select top 5000 * from [dbo].['+tbl+'] where text like ''%err%''' FROM #tbl t

EXEC sp_ExecuteSql @DynamicSql

SELECT *FROM #Results

drop table #Results drop table #tbl

you get an execution plan that does both select with parallelism.

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL

with

if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.

You could also use RAISERROR in a similar fashion:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)

HTH

Sql-server – sp_ExecuteSQL, performance and table variables

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T 
  ( 
     x INT 
  ) 

INSERT INTO #T 
            (x) 
SELECT TOP 10 number 
FROM   master..spt_values 

SELECT * 
FROM   #T 
UNION 
SELECT * 
FROM   #T 

DROP TABLE #T
'

Best Answer

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

Sql-server – sp_ExecuteSQL, performance and table variables

Related Question