February 9, 2016 at 9:01 am
Hi,
I have 10 CTAS statements creating temp tables. Once they are created, they are LEFT OUTER JOINED.
Is there a way, a parallel execution of these 10 CTAS statements be enforced programmatically or does the Query optimiser engine take care of this automatically?
thank you
February 9, 2016 at 9:05 am
etl2016 (2/9/2016)
Hi,I have 10 CTAS statements creating temp tables. Once they are created, they are LEFT OUTER JOINED.
Is there a way, a parallel execution of these 10 CTAS statements be enforced programmatically or does the Query optimiser engine take care of this automatically?
thank you
What are CTAS?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 9, 2016 at 9:08 am
hi, CTAS is create table as SELECT
thank you
February 9, 2016 at 9:21 am
You could try a combo of this function:
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx
Or using powershell
https://technet.microsoft.com/en-us/library/jj713711.aspx
gsc_dba
February 9, 2016 at 9:56 am
etl2016 (2/9/2016)
hi, CTAS is create table as SELECTthank you
The simplest way I can think of doing this is using an SSIS package to submit the queries in parallel via multiple ExecuteSQL tasks.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 9, 2016 at 12:32 pm
That sounds like Oracle not SQL Server. SS doesn't have CTAS syntax.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 9, 2016 at 12:41 pm
ScottPletcher (2/9/2016)
That sounds like Oracle not SQL Server. SS doesn't have CTAS syntax.
Aha, I just assumed it was an unorthodox way of referring to SELECT INTO.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 9, 2016 at 1:51 pm
ScottPletcher (2/9/2016)
That sounds like Oracle not SQL Server. SS doesn't have CTAS syntax.
It's SQL Server Parallel Data Warehouse aka APS, or the Azure version of it, Azure DWH. CTAS replaces SELECT ... INTO in the PDW.
PDW is not a standard SQL Server, SSIS is not a good option for working on data within the PDW, because it doesn't take advantage of the distributed nature of the data, and it requires pulling the data out and putting it back in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2016 at 1:53 pm
etl2016 (2/9/2016)
Is there a way, a parallel execution of these 10 CTAS statements be enforced programmatically or does the Query optimiser engine take care of this automatically?
The CTAS statements will be executed one after each other. T-SQL is synchronous in terms of statement execution. Each one may run in parallel depending on the distribution of the data.
The only way to make the CTAS statements run at the same time as each other would be multiple sessions each with one or more of the CTAS statements, but then you risk running into the concurrent query limit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2016 at 2:36 pm
GilaMonster (2/9/2016)
The CTAS statements will be executed one after each other. T-SQL is synchronous in terms of statement execution. Each one may run in parallel depending on the distribution of the data.
CTAS needs to apply exclusive locks on system objects.
While the first one is not finished no other ones would be able to start.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply