Hot answers tagged

25

No. Please see the PostgresSQL FAQ: How does PostgreSQL use CPU resources? The PostgreSQL server is process-based (not threaded), and uses one operating system process per database session. A single database session (connection) cannot utilize more than one CPU. Of course, multiple sessions are automatically spread across all available CPUs by ...


14

The query is a non-parallel (serial) plan so it is definitely executing from start to finish on a single SQL Server worker thread, associated with a single SQLOS scheduler. From an operating system perspective, this single thread is still subject to Windows' normal scheduling algorithms by default. I say "by default" because the behaviour depends on the ...


13

Few months ago, I faced similar situation wherein the MAXDOP setting was default and a run away query exhausted all the worker threads. As Remus pointed out this is called worker thread starvation. There will be a memory dump created on your server when this condition occured. If you are on 2008R2+SP1 and up then sys.dm_server_memory_dumps will give you ...


11

Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005": Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form: SELECT StatMan([SC0]) FROM ...


11

It is mostly a license issue. These developments end up patching the code quite heavily, so if you were to deal with MySQL, you'd either have to open-source your code or be at the mercy of MySQL's corporate owner for the life of your business. Some offers for MySQL get around that by implementing their work as a storage engine, but that doesn't offer all ...


10

There is a lot of misunderstanding about CXPACKET. CXPACKET isn't the cause of your problems, it is a side effect. What CXPACKET means when you see it is that that thread of a parallel query is waiting for another thread of that query to do something. Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that ...


10

FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML). When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you. The provided execution plan shows the optimizer choosing a static-like plan. Because the ...


8

It is fairly well documented that UDFs force an overall serial plan. I'm not certain it is all that well documented :) A scalar T-SQL function indeed prevents parallelism anywhere in the plan. A scalar CLR function can be executed in parallel, so long as it does not access the database. A multi-statement table-valued T-SQL function forces a serial ...


8

When you issue a WAITFOR command, the task will start having a wait_type of WAITFOR. This is a benign wait type and can be ignored. Likewise, when this task enters the suspended state it will get off of the scheduler (processor) so it won't be taking up worker time. We can see this with a simple example: use AdventureWorks2012; go waitfor delay ...


8

You usually don't want to disable parallelism as that will also disable it for admin tasks. Your best bet is to fix the queries that are causing the parallelism through adding or fixing indexes or through making full on schema changes. Based on your updated questions... Some people will change MAXDOP to 1 for vendor built applications because they can't ...


8

You may find this surprising, but you should set the innodb_thread_concurrency to 0 (which is infinite concurrency). This will allow the InnoDB Storage Engine to decide how many concurrency tickets to issue. I wrote a post about InnoDB's multicore engagement (MySQL 5.5, also MySQL 5.1.38 InnoDB Plugin) back on May 26, 2011. According to the MySQL ...


8

I can see two reasons: 1) historically, PostgreSQL had better query planner and statistics analyzer. This might be not true now, but few years ago PostgreSQL was much better then MySQL on complex queries, which is OLAP ones. 2) PostgreSQL have better functions/triggers/etc programming support.


8

There could be several reasons. Most likely is that you were out of workers. See max_worker_threads. The condition is called 'worker stravation'. The workers could be stolen by any one of multiple means (none of which would result in high CPU utilization, btw), like having many requests blocked or doing stupid things in CLR (eg. HTTP requests). The symptom ...


7

It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate. When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly ...


7

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of ...


7

There are many reasons that an insert and delete can be faster in practice than an single update that achieves the same end result. I am not even going to attempt to list all the considerations, but for example: Updates that affect an index key might appear to do an in-place update from the execution plan, but this is not the case at the lowest level. An ...


6

I wrote about six potential issues with parallelism here: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx That was three years ago, and I'm sure other equally dangerous bugs have come up since I wrote that. I haven't gone through and validated any that have been reported as fixed, but the ...


6

You're not seeing the benefits of parallel execution because both insert methods use single-row inserts, hence parallel does not kick in ! Parallel execution is only available for bulk operations. Lots of tiny operations doesn't qualify as a set operation. You're inserting rows one by one here, you should try it with a bulk operation (INSERT /*+APPEND*/ ...


5

This looks like a failed assertion in SQL Server code. Basically, you've uncovered a bug in SQL Server. You could submit it to Microsoft. A quick solution is to add option (maxdop 1) at the end of your query; this instructs SQL Server not to spread the query over multiple processors (aka parallelizing.)


5

Two reasons can cause improvement of the same query over time: The data is changing The query plan is changing The data you can't do much about. To verify that the query plan is improving, try running the query manually during a time when you know the query is running at it's fastest. SAVE the fast query plan Run the same query during a time when you ...


5

You need to go an read: The Data Loading Performance Guide How to analyse SQL Server performance Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server) bcp.exe Transferring Data Efficiently by Using Partition Switching Operations That Can Be Minimally Logged We Loaded 1TB in 30 Minutes with SSIS, and So Can You SqlBulkCopy Class SSIS ...


5

Anyone ever see Cost Threshold for Parallelism being ignored? It is not being ignored. During the compilation process, the optimizer first considers a serial plan. If the estimated cost of that plan exceeds the Threshold, the optimizer goes on to look for a parallel plan. If the resulting parallel plan is costed below the best serial one, it will be ...


5

Based on what I see in SQL Sentry Plan Explorer PRO*, looks like two different jobs are fighting over who is going to get to delete some rows (click to enlarge): Maybe there is some overlap because Microsoft does some not-so-optimal things in msdb.dbo.sp_syscollector_purge_collection_logs, for instance letting each invocation of the procedure grab ...


5

There is an easy solution if you have Enterprise (expensive) Edition in use: Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.


4

In SQL Server 2014 parallel DML for the SELECT INTO statement requires compatibility level 120. 110: A SELECT INTO statement always creates a single-threaded insert operation. 120: A SELECT INTO statement can create a parallel insert operation. When inserting a large numbers of rows, the parallel operation can improve performance. Note, that ...


4

Postgres currently does not use parallelism when executing a single query. The entire execution engine is single threaded for each query (you can of course still execute multiple queries in parallel). This lack of "intra query parallelism" is one of the great drawbacks of running Postgres or MySQL. Basically, it makes those engines unfit for purpose on large ...


4

There is no hidden impact in raising the cost threshold for parallelism. As low as it is, SQL Server ships with a default cost threshold of 5, which is always taken into account when the optimizer compiles query plans. So, in this regard, the amount of work for the optimizer is the same. Whether raising the CTFP to a very high value is a good idea or not, ...


4

Chances are the CXPACKET waits are actually disproportionately represented if you have a lot of threads waiting on something that is I/O bound. You can check this by setting MAXDOP=1 and re-running the query. See if the proportion of wait time from PAGEIOLATCH waits increases significantly. If your PAGEIOLATCH waits are a large proportion of the wait time ...


4

SQL Server makes this decision based upon cost. If you execute 'sp_configure' (make sure advanced options are enabled) you will see an entry for 'cost threshold for parallelism'. If the optimizer estimates the cost to be greater than the run_value then your query will be executed in parallel. If the ORDER BY clause in your example results in an increased ...


4

You should find explanations for parallelism in SQL Server in these two questions: A query submitted from different applications has differing DOP and What is the meaning of DOP in the context of sql server? To enable the use of parallelism in first server you have two options: enable it at query level ( use option OPTION (MAXDOP 8) to enable the ...



Only top voted, non community-wiki answers of a minimum length are eligible