Questions tagged [parallelism]

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

Filter by
Sorted by
Tagged with
10
votes
2answers
21k 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, ...
24
votes
2answers
30k 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 ...
31
votes
2answers
3k views

Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results. There are some places where a Scalar UDF is the only option, though. As an ...
8
votes
1answer
7k 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 ...
64
votes
4answers
71k 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?
15
votes
1answer
2k 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 ...
14
votes
3answers
5k 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'...
9
votes
6answers
35k views

Run stored procedures in parallel

I'm looking to try and run the same stored procedure multiple times with different parameters but at the same time. I'm using SQL 2014 The reason for this is that the procedure takes around 7 hours ...
5
votes
3answers
13k 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 (...
12
votes
4answers
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 ...
7
votes
1answer
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 ...
7
votes
2answers
655 views

In SQL Server, is parallelism per operator, or something else?

I work with a really old DBA who says a lot of weird stuff. Dude has an O'Reilly book that only has an amoeba on the cover. At lunch we were talking about parallelism, because our new server has 24 ...
14
votes
2answers
4k views

If a Parallelism Exchange Event deadlock is victim-less, is it a problem?

We're seeing a lot of these Intra-Query Parallel Thread Deadlocks in our Production environment (SQL Server 2012 SP2 - yes...I know...), however when looking at the Deadlock XML that has been captured ...
6
votes
2answers
7k views

MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?

If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)? If the first SELECT ...
2
votes
2answers
427 views

Insert result of a parallellized select query into a table, in Postgresql

According to https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html, "Even when it is in general possible for parallel query plans to be generated, the planner will ...
10
votes
2answers
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 ...
5
votes
1answer
232 views

What's the easiest and most accurate way to visualize parallel thread usage in SQL Server?

Following up on my other question here, I'd like an easy way to visualize the answer. I've tried using a query like this, but the number of "running" threads can exceed DOP sometimes. SELECT ost....
9
votes
5answers
10k 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 ...
7
votes
6answers
10k 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, ...
7
votes
2answers
218 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 ...
4
votes
1answer
535 views

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

The manual for Postgres 12 says: The following operations are always parallel restricted. Scans of common table expressions (CTEs). Scans of temporary tables. ... Further down on ...
10
votes
3answers
531 views

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
5
votes
1answer
285 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?
4
votes
1answer
134 views

Degree of parallelism and scheduler usage

Is it ever possible for a SQL Server query running with an effective degree of parallelism x to have parallel workers assigned to more than x distinct schedulers? Even if the execution plan has many ...
4
votes
2answers
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 ...
1
vote
1answer
32 views

how to sum times in EXPLAIN for Postgres parallel query

I'm having trouble understanding this EXPLAIN on Postgres 12.3: EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo'; This is a 22GB table with 30 million rows, on a ...