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
70 votes
4 answers
92k 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?
Alireza's user avatar
  • 3,636
32 votes
2 answers
4k 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 ...
Erik Darling's user avatar
  • 40.5k
25 votes
2 answers
36k 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 ...
Lumpy's user avatar
  • 2,109
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 ...
Tom V's user avatar
  • 15.7k
15 votes
3 answers
41k 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 ...
stumct's user avatar
  • 191
15 votes
2 answers
6k 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 ...
Mark Sinkinson's user avatar
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'...
SQL Learner's user avatar
  • 3,011
12 votes
6 answers
56k 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 ...
user124569's user avatar
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 ...
marc_s's user avatar
  • 8,922
12 votes
1 answer
911 views

Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?

I'm testing against SQL Server 2019 CU14. I have a pure row-mode query that selects the top 50 rows from a complicated view. The full query takes 25426 ms of CPU time at MAXDOP 1 and 19068 ms of CPU ...
Joe Obbish's user avatar
  • 32.1k
12 votes
2 answers
965 views

Can I refactor this query to get it to run in parallel?

I have a query that takes about 3 hours to run on our server--and it doesn't take advantage of parallel processing. (about 1.15 million records in dbo.Deidentified, 300 records in dbo.NamesMultiWord). ...
rsjaffe's user avatar
  • 383
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 ...
Rachel's user avatar
  • 8,457
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 ...
crokusek's user avatar
  • 2,120
11 votes
1 answer
10k 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 ...
thanby's user avatar
  • 211
11 votes
2 answers
5k views

MAXDOP = 1, Query Hints and Cost Threshold For Parallelism

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually ...
Martin Bansey's user avatar
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, ...
Thomas's user avatar
  • 307
11 votes
2 answers
899 views

Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?

Setup DROP TABLE IF EXISTS #EmptyTable, #BigTable CREATE TABLE #EmptyTable(A int); CREATE TABLE #BigTable(A int); INSERT INTO #BigTable SELECT TOP 10000000 CRYPT_GEN_RANDOM(3) FROM sys....
Martin Smith's user avatar
  • 84.4k
10 votes
3 answers
1k 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 ...
user9516827's user avatar
  • 1,305
10 votes
1 answer
15k views

How can I disable parallel queries in PostgreSQL?

I want to benchmark some things in PostgreSQL. When I do this I normally turn off some of the features the database is currently using, just for my session, to see what the second-best plan is. ...
Evan Carroll's user avatar
  • 62.7k
9 votes
2 answers
1k 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 ...
WorldStar SQL's user avatar
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 ...
user avatar
9 votes
2 answers
508 views

How can I get rid of an unhelpful parallel branch when unpivoting a single row?

Consider the following query that unpivots a few handfuls of scalar aggregates: SELECT A, B FROM ( SELECT MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1 , MAX(CASE WHEN ID = 2 THEN 1 ...
Joe Obbish's user avatar
  • 32.1k
9 votes
1 answer
619 views

In SQL Server, How Does Parallelism Change Memory Grants?

I've heard conflicting things about memory grants for parallel select queries: Memory grants get multiplied by DOP Memory grants get divided by DOP Which is it?
Erik Darling's user avatar
  • 40.5k
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 ...
David's user avatar
  • 1,185
8 votes
1 answer
707 views

Did Microsoft change query optimizer regarding number of files and parallelism

Did Microsoft change query optimizer regarding number of files and parallelism? Does the optimizer no longer factor in the number of files to determine the degree of parallelism for a query? If so, ...
Will Davis's user avatar
8 votes
2 answers
2k views

Round robin vs Demand Parallelism partitioning

This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have ...
Gokhan's user avatar
  • 304
8 votes
1 answer
3k 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 ...
Erwin Brandstetter's user avatar
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 ...
user3469285's user avatar
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, ...
atommc's user avatar
  • 71
7 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 ...
Roberto's user avatar
  • 128
7 votes
2 answers
10k 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 ...
J.D.'s user avatar
  • 37.1k
7 votes
2 answers
1k views

Non parallel plan only in Azure when SESSION_CONTEXT is used

I'm observing strange difference between query plans that I'm getting on my local machine and on Azure SQL. I'm trying to implement row level security, where I read user identifier from ...
Lukas.Navratil's user avatar
7 votes
1 answer
502 views

STATISTICS IO for parallel index scan

Assume that there is a table with clustered index create table [a_table] ([key] binary(900) unique clustered); and some data insert into [a_table] ([key]) select top (1000000) row_number() over (...
i-one's user avatar
  • 2,354
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 ...
Beth Lang's user avatar
  • 952
7 votes
1 answer
2k views

In SQL Server, what is "Partitioned Table Parallelism" that is only available in Enterprise Edition?

I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I ...
ruskind's user avatar
  • 253
7 votes
1 answer
684 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 ...
Michoel's user avatar
  • 171
7 votes
2 answers
229 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 ...
jn025's user avatar
  • 273
6 votes
1 answer
416 views

SQL Server Scalar UDF Parallelism Mystery!

Referencing this blog post by Erik Darling on the website of my favorite SQL Server guru - Brent Ozar: When you select from that table alone, it shows "CouldNotGenerateValidParallelPlan". ...
Anil Raghavan's user avatar
6 votes
2 answers
647 views

Can a server wide setting of 'Max Degree of Parallelism' = 1 cause Brent Ozar's sp_BlitzCache to flag execution plan as 'forced-serialization'?

I am using Brent Ozar's sp_BlitzCache store procedure and I'm attempting to nail down why it is reporting: "Something in your plan is forcing a serial query. Further investigation is needed if ...
D-K's user avatar
  • 503
6 votes
3 answers
653 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 ...
Supercyberal's user avatar
5 votes
3 answers
16k 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 (...
matskm's user avatar
  • 53
5 votes
1 answer
289 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, ...
u23432534's user avatar
  • 1,535
5 votes
1 answer
1k 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 ...
user45867's user avatar
  • 1,683
5 votes
1 answer
349 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....
WorldStar SQL's user avatar
5 votes
1 answer
299 views

SQL Server generates a query plan with parallelism and TOP statement

I have two simple queries: SELECT TOP(20) * FROM Clients ORDER BY City SELECT TOP(20) Id, Name, City FROM Clients ORDER BY City In the first case I get a result like this Id Name City many more ...
Bert de Saffel's user avatar
5 votes
1 answer
304 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?
usr's user avatar
  • 7,330
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)....
Andrew Carmichael's user avatar
5 votes
1 answer
4k views

SQL SERVER 2016 high CPU

I have a brand new server with SQL Server 2016. It has 24 CPU, about 80go of RAM. The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all. If I look at the 'execute ...
Guillaume Philipp's user avatar
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 ...
Reaces's user avatar
  • 2,581
4 votes
2 answers
2k views

Is SQL Server able to use internal parallelism for an update statement?

I am struggling to find the best way to migrate some "varchar" columns to "nvarchar". One of the options i am using is to add new nvarchar column(s) then update the values from the ...
Edward Hayrabedian's user avatar

1
2 3 4 5