Questions tagged [parallelism]
Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.
197
questions
1
vote
0
answers
58
views
Oracle parallel hint on cluster [duplicate]
I wanted to use the parallel hint that would give the best performance in my query which is a equi-join query where the two tables are in a cluster and the join column is a cluster index. I wasn't ...
- 273
4
votes
3
answers
1k
views
Setting maxdop 1 vs setting the threshold for parallelism high
I am looking at a possible fringe case for parallelism settings.
I've long since followed the credence that maxdop 1 is a viable option for Microsoft Dynamics CRM databases. As the nature of the ...
- 2,561
4
votes
3
answers
2k
views
Are parallel queries with mysqlnd actually possible?
So I have been playing with asynchronous database requests in PHP using mysqlnd in an attempt to achieve parallel database requests and cut down total execution time i.e. rather than:
total time = ...
- 141
15
votes
1
answer
3k
views
sp_cursoropen and parallelism
I'm running into a performance problem with a query that I can't seem to get my head around.
I pulled the query out of a cursor definition.
This query takes seconds to execute
SELECT A.JOBTYPE
FROM ...
- 15.6k
3
votes
2
answers
571
views
How can I monitor my server’s status before changing the “Cost threshold for parallelism” settings
Our production server is running SQL Server 2008 SP3.
I’ve read a post on “cost threshold for parallelism” - http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-...
- 575
7
votes
1
answer
2k
views
MAXDOP not working?
I wanted to test out MAXDOP on my computer. So I set MAXDOP to 2 for a specific query. However, when I looked at my logical processors in Task Manager as I ran the query, it looked like they were all ...
- 661
4
votes
2
answers
6k
views
Amazon Redshift - The difference between Query Slots, Concurrency and Queues?
We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and ...
- 141
3
votes
1
answer
1k
views
Are CTEs multithreaded?
Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the ...
- 555
0
votes
0
answers
2k
views
EXEC Multiple procedures in parallel? [duplicate]
SQL Server 2008.
I have several stored procedures. I would like to execute them all in parallel, not serially. How can I do this?
- 45
1
vote
0
answers
354
views
Parallel insert in db2
I am trying to import from 8 files and insert into a single table in db2. I am running 8 .sql files that will do the task, but only one of them actually seem to be running and inserting into table.
...
6
votes
3
answers
1k
views
Why is cost threshold for parallelism ignored?
SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is CXPACKET.
Instance settings
MAXDOP: 8
Cost Threshold for Parallelism: 175
Ran ...
- 118
2
votes
1
answer
98
views
Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit
We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
- 121
4
votes
2
answers
699
views
Can WAITFOR increase CXPACKET waits?
If I have some T-SQL code like this:
While @done<1
Begin
WAITFOR DELAY '00:00:30';
Set @done=<some select statement>
End
Is this likely to increase waits for CXPACKET? Presumably the other ...
- 669
6
votes
3
answers
641
views
Parallel Plan Selection
I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple ...
- 101
5
votes
1
answer
302
views
What is the minimum required compatibility level to use parallel SELECT INTO?
What is the minimum required compatibility level to use parallel SELECT INTO in SQL Server 2014?
- 7,250
11
votes
2
answers
3k
views
Is support for Parallel Scalar UDF a reasonable feature request?
It is fairly well documented that scalar UDF's force an overall serial plan.
Running functions in parallel
Given a large number of rows coming into a point in the pipeline where a UDF must be ...
- 2,036
3
votes
2
answers
2k
views
Deadlock with simple queries in sql server 2008
I have an application that must delete and insert registries from a table, the table is storing the historic registries for an access control and is stored with the datetime for the check in the ...
- 35
2
votes
0
answers
5k
views
Oracle parallel query: finding out which Producer a Consumer is waiting for
One of our materialized view refresh's (part of a daily ETL routine) was hanging today; it usually completes within 5 minutes, but this time, I had to kill it after 23 hours.
V$SESSION contained this ...
- 376
15
votes
3
answers
39k
views
High CXPACKET and LATCH_EX waits
I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...
- 191
0
votes
1
answer
104
views
Oracle Autodop disabled at the table level
I have a question related to Oracle auto DOP and parallel DML concepts. I have a scenario, if I set the Auto DOP set to 1 on the table and if I run parallel DML statements on that table through ...
- 101
70
votes
4
answers
87k
views
Can a single PostgreSQL query use multiple cores?
In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
- 3,596
1
vote
1
answer
770
views
How is parallelism threshold (seconds) used if query CPU cost is actually unitless
How does the optimizer decide whether or not to use parallelism when the parallelism threshold is specified in seconds (on specific hardware, see below) but the estimated CPU costs are supposedly unit-...
- 2,036
3
votes
0
answers
242
views
Why does removing a RID by creating a clustered index lose parallelism and slow down execution by 2X
If I remove a RID lookup by replacing 2 non-clustered indices (non unique) with 2 identical clustered indicies (non-unique) where there weren't any before, why does this query slow down by a factor ...
- 2,036
1
vote
1
answer
2k
views
SSIS 2012 Ftp parallel/asynchronous multiple file download
Any help would be appreciated with either scenario.
if
Are there any settings available to download multiple files in parallel from an FTP server?
else
If not, I guess I'll just roll my own via C# ...
- 460
25
votes
2
answers
35k
views
Need to understand parallel query execution error
Today we experienced a degradation in performance on our production sql server. Durring the time this occurred we logged several "The query processor could not start the necessary thread resources ...
- 2,109
3
votes
1
answer
2k
views
PLSQL : DBMS Jobs - parallelization?
I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs?
for rec in select column from table1
loop
execute stored_procedure( rec.column );
end ...
- 151
1
vote
1
answer
1k
views
MySQL: parallel restores at same time from backup files?
Slightly related to this question, but for MySQL: Would it be possible/wise to execute 2 parallel restores from a single backup?
Is it possible/wise to do a parallel restore of two independent ...
- 218
3
votes
0
answers
464
views
Are the following statements concerning sys.dm_exec_requests true?
Parallel plans mean that cpu_time can be greater than total_elapsed time.
Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread.
...
- 4,238
5
votes
1
answer
3k
views
Executing SPs in parallel (maybe with a cursor) [closed]
We have an 3rd party accounting system that runs on SQL Server 2008 R2. To insert invoices into this accounting system, we have to use the API that comes with it (which is just a set of encrypted SPs)....
0
votes
1
answer
1k
views
Oracle scheduler max_jobs_Slave_processes is set to null, how many will be allocated?
Executing the following query against my database:
select value
from dba_scheduler_global_attribute
where attribute_name='MAX_JOB_SLAVE_PROCESSES';
yields a value of NULL. My question is what is ...
- 201
3
votes
1
answer
337
views
SQL Server parallelism issue
I am rather new to SQL Server administrations, I am running an SSIS that never ends...
A) looking at the processes using
SELECT r.*, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys....
- 31
12
votes
4
answers
3k
views
Dealing with CXPACKET waits - setting cost threshold for parallelism
As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits.
I know the knee-jerk solution is to turn off all ...
- 8,772
14
votes
3
answers
6k
views
Parallel Statistics Update
In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don'...
- 3,001
1
vote
1
answer
475
views
What is the "Data partitioned, Shared-Nothing, Parallel Database" solution for PostgreSQL ? [closed]
I was looking for a open source parallel database solution. It seems Postgres-XC is a viable solution. However, I don't need the replication. In this case, is there any better solution towards read ...
- 111
2
votes
2
answers
3k
views
Are problems with parallelism more common today? What issues can arise from parallelism?
This is a very general question so I don't give much specific information. I am more interested in parallelism and its possible drawbacks. Generally I am all for parallelism as something useful and ...
- 673
12
votes
3
answers
30k
views
Why is an aggregate query significantly faster with a GROUP BY clause than without one?
I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.
For example, this query takes almost 10 seconds to run
SELECT MIN(CreatedDate)
FROM MyTable
WHERE ...
- 8,407
11
votes
2
answers
22k
views
Using multiple cores for single MySQL queries on Debian
I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four.
I am doing expensive joins on large tables, ...
- 307
2
votes
1
answer
8k
views
In Oracle, if parallel_max_servers is set to be significantly less than the DOP can the explain plan be sub-optimal?
For tables that have DEGREE DEFAULT, the degree of parallelism (DOP) is calculated by Oracle using number of CPUs and number of threads per CPU amongst other things. As I understand it, when costing ...
- 261
7
votes
6
answers
11k
views
CXPACKET Waits performance tune for SQL Server 2008
I have a SQL Server 2008 query that is working on millions of records. The query is w/in a proc that is run nightly by a job. The query can take a full day to run when I first put it on the server, ...
- 71
9
votes
5
answers
11k
views
Parallelism Best Practices
What are the best practices with setting parallelism in general? I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default ...
anon
3
votes
1
answer
1k
views
Does the pgpool-II extension allow for parallel execution of any query?
I read about pgpool-II here and it states that:
Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to ...
- 1,155
3
votes
1
answer
736
views
Query Parallelism
I have a long running query that behaves differently between two different machines.
I have an 8 core machine running Microsoft SQL Server Developer Edition (64-bit) that does not use parallelism and ...
- 33
4
votes
2
answers
1k
views
A query submitted from different applications has differing DOP
I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 ...
- 639
2
votes
1
answer
261
views
Why am I not seeing any benefits of parallel execution when parallel clause is used?
I have these two methods of creating a table and populating with rows. The first one uses a
parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) ...
- 3,361
8
votes
4
answers
7k
views
Why are so many MPP solutions based on PostgreSQL instead of MySQL?
Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
- 1,155
7
votes
1
answer
1k
views
How does MS SQL Server decide when it can use "Parallelism" when executing queries?
How does MS SQL Server decide when it can use "Parallelism" when executing queries? I recently posted another question here that, when looking at the actual execution plan used parallelism when I had ...
- 952
4
votes
2
answers
698
views
Bizarre crash in T-SQL - what is it?
What would be a typical situation under which the following error would occur, and what does it mean?
Also note that I find myself disconnected from the server when the query fails (but I have not ...
Alexander