Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.

learn more… | top users | synonyms

5
votes
1answer
171 views

sp_who returns more rows than the value of “max degree of parallelism”

sp_configure returns the following values on a server. name minimum maximum config_value run_value max degree of parallelism 0 32767 8 8 However, ...
4
votes
1answer
59 views

Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED

Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be ...
1
vote
1answer
83 views

SQL not engaging parallelism for extremely large query

I have a very large query (~630 lines) that involves a lot of nested SELECT statements and pulls from multiple views. Our SQL server has parallelism set to 2 with a threshold of 95 (set that way ...
1
vote
1answer
85 views

Long running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?

Firstly 1 thread selecting a huge amount of data from a table. Then, before the SELECT query is not yet finished, another thread deletes data from that table. It causes SELECT query to failed with ...
0
votes
1answer
63 views

Mysqli error “Empty row Packet body” during reap_async_query

I am executing 50 similar large SELECT queries in parallel with mysqlnd/ mysqli_async in the query-poll-reap fashion. The database holds around 300M records. Recently I migrated the database from ...
0
votes
1answer
37 views

Why is my query using parallelism, even with MAXDOP = 0?

I'm having some headaches with a procedure. Long story short, I'm using now a hint to force an index ( now, the query runs in 0.1 secs, after this, it was running in 2min ) but the question is, why ...
0
votes
1answer
37 views

DB with column store and parallel query execution?

I am looking for open-source alternatives to SQL Server Enterprise Edition for a mostly-read analytics workload. The main key features that I'm looking for are Column-oriented storage Parallel ...
2
votes
0answers
49 views

Max Degree of Parallelism for SQL Server 2008

Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and ...
4
votes
2answers
1k views

How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?

Problem We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year. The SQL-Server instance in question has very high CPU usage (...
0
votes
0answers
73 views

Turn on multi-threading in Oracle for ETL purposes

As you can see from this question, I was able to configure multi-threading in SQL Server and build a workflow in Informatica to multi-thread a job to move data faster into SQL Server. Multi-thread ...
5
votes
1answer
68 views

Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server)

I'm just wondering if there are common scenarios where an insert/ delete combination is faster than an update else insert function. Here is my specific example. I have to update a database with ...
4
votes
1answer
272 views

“Lock request time out period exceeded” when publishing SqlServer databases in parallel

I am working on a continuous integration environment using SqlServer2014. There I need to publish many SqlServer databases simultaneously on the same instance. Our publish process uses a .sqlproj file ...
0
votes
1answer
130 views

What's the biggest bottleneck (time) in writing data to a SQL database?

I'm running an ETL process that is writing about 2 million rows to a SQL Server database. I'm trying to optimize the time it takes for pure inserts (I guess updates is a different story). I'm ...
7
votes
1answer
90 views

Parallel option performing worse on cluster

I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not ...
1
vote
0answers
21 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 ...
2
votes
0answers
77 views

How to get past Windows Server rate limiting repeated SQL Server connections over network? [closed]

Currently I have ETL software installed/ running on a machine separate from the destination data warehouse database (for security purposes apparently). So it must send data/ write records across a ...
4
votes
3answers
346 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
votes
3answers
300 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 = ...
8
votes
1answer
425 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 ...
3
votes
2answers
154 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-...
2
votes
0answers
69 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
5
votes
1answer
493 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 ...
2
votes
1answer
1k 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 ...
3
votes
1answer
317 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 ...
1
vote
0answers
90 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. ...
4
votes
3answers
277 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 ...
1
vote
1answer
52 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 ...
4
votes
2answers
312 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 ...
5
votes
3answers
440 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 ...
0
votes
0answers
313 views

Sql Server, a single threaded query spawns multiple threads (or internal parellelizaion during execution)

In Sql Server, when I run a procedure or a query that takes multiple minutes, sometimes spawns multiple threads. Even though there is no MAXDOP hint or necessity in the query, I see multiple instances ...
5
votes
1answer
203 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?
0
votes
0answers
430 views

MySQL Cluster lock wait timeout exceeded when trying to deal with huge amount of queries

I'm trying to deal with many connections and sometime I get following error: ERROR : Lock wait timeout exceeded; try restarting transaction The bigger number of queries the least number of ...
4
votes
1answer
561 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 ...
3
votes
2answers
816 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 ...
2
votes
0answers
2k 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 ...
8
votes
2answers
18k 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 ...
0
votes
1answer
65 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 ...
32
votes
4answers
19k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
1
vote
1answer
307 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-...
3
votes
0answers
179 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 ...
1
vote
1answer
846 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#...
12
votes
2answers
13k 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 ...
3
votes
1answer
1k 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 ...
1
vote
1answer
280 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 ...
3
votes
0answers
329 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. ...
5
votes
1answer
2k 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
1answer
263 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 ...
3
votes
1answer
258 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....
7
votes
3answers
2k 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
votes
2answers
2k 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'...