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
66 votes
4 answers
79k 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?
user avatar
  • 3,546
31 votes
2 answers
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 ...
user avatar
  • 33.4k
24 votes
2 answers
33k 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 ...
user avatar
  • 2,069
15 votes
1 answer
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 ...
user avatar
  • 15.4k
15 votes
2 answers
5k 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 ...
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'...
user avatar
  • 2,961
14 votes
3 answers
37k 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 ...
user avatar
  • 181
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 ...
user avatar
  • 8,643
12 votes
2 answers
864 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). ...
user avatar
  • 383
12 votes
3 answers
29k 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 ...
user avatar
  • 8,259
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 ...
user avatar
  • 1,966
11 votes
2 answers
4k 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 ...
user avatar
11 votes
2 answers
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, ...
user avatar
  • 307
11 votes
2 answers
730 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....
user avatar
  • 77.9k
10 votes
3 answers
685 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 ...
user avatar
  • 1,285
10 votes
1 answer
8k 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 ...
user avatar
  • 201
10 votes
6 answers
44k 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 ...
user avatar
10 votes
1 answer
729 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 ...
user avatar
  • 30.3k
9 votes
5 answers
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 ...
user avatar
9 votes
2 answers
489 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 ...
user avatar
  • 30.3k
9 votes
1 answer
352 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?
user avatar
  • 33.4k
8 votes
4 answers
6k 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 ...
user avatar
  • 1,145
8 votes
1 answer
706 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, ...
user avatar
8 votes
2 answers
1k 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 ...
user avatar
  • 304
7 votes
2 answers
806 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 ...
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 ...
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, ...
user avatar
  • 71
7 votes
2 answers
8k 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 ...
user avatar
  • 24.2k
7 votes
1 answer
440 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 (...
user avatar
  • 2,294
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 ...
user avatar
  • 952
7 votes
1 answer
610 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 ...
user avatar
  • 171
7 votes
2 answers
223 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 ...
user avatar
  • 273
6 votes
2 answers
444 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 ...
user avatar
6 votes
1 answer
9k 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. ...
user avatar
  • 55.7k
6 votes
1 answer
262 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". ...
user avatar
6 votes
2 answers
567 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 ...
user avatar
  • 443
6 votes
3 answers
635 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 ...
user avatar
6 votes
1 answer
1k 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 ...
user avatar
6 votes
1 answer
1k 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 ...
user avatar
  • 243
5 votes
3 answers
14k 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 (...
user avatar
  • 53
5 votes
1 answer
243 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, ...
user avatar
  • 1,475
5 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 ...
user avatar
  • 108
5 votes
1 answer
333 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 ...
user avatar
  • 1,493
5 votes
1 answer
291 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....
user avatar
5 votes
1 answer
294 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?
user avatar
  • 7,140
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)....
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 ...
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 ...
user avatar
  • 2,501
4 votes
2 answers
661 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 ...
user avatar
  • 669
4 votes
1 answer
406 views

No parallelism in Express Edition of SQL Server

I have SQL Server 2019 Express Edition (CU8) on Windows Server 2019 (1809 version) and all of my queries go serial with NoParallelPlansInDesktopOrExpressEdition in NoparallelPlanReason property. Is it ...
user avatar
  • 935