0

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
3
  • 1
    this would be treated as a batch with each insert into the temp table it's own statement. It might parallelize the individual statement but it's going to process the statements sequentially. If you want this to go parallel (as in query multiple databases simultaneously) you could change it to a UNION ALL (just use three part naming instead of the USE statement), or write something that opens a new connection for each query subset. Jan 28, 2020 at 16:13
  • @JonathanFite Ah gotcha. Any ideas off the top of your head why when I re-write my query to do a UNION ALL it actually runs a lot slower? Literally the only change is instead of 500 sequential INSERT INTOs it's now 500 UNION ALLs. Is it possible that running the query as one batch sequentially faster because each INSERT INTO statement runs one at a time (very quickly) and UNION ALLs start to degrade in performance after X number being used?...could it be a bottleneck of the optimizer while trying to generate an execution plan?
    – J.D.
    Jan 28, 2020 at 21:05
  • It's just a guess, but maybe saturated your drive IO? (Assuming all those databases are on the same drive). Is 'SomeCondition' indexed? Check for plan regression from seek+keylookup to scan. Jan 29, 2020 at 14:03

1 Answer 1

1

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.