CODEWITHSUNDEEP
sql-serversql-server-2008
SQL Learner
SQL Learner

Reputation: 601

SQL Server parallelism and OLTP systems?

Many online blogs I see a suggestion that says parallelism is meant for a data warehouse sysetms and not for OLTP systems. Many experts even suggests to turn off parallelism by setting max degree of parallelism to 1 in an OLTP environment. I am trying to find basis for such suggestions and my guess is as OLTP systems are subjected to many concurrent requests keeping CPUs busy; parallelism used by query optimizer can be a overhead.

However, isn't parallelims can be a boon. By allowing a request to be processed by multiple threads are we reducing query processing time.

Trying to delve deeper into parallelism. Any suggestiosn on parallelism in OLTP systems.

Upvotes: 0

Views: 926

Answers (2)

Mike
Mike

Reputation: 3311

In OLTP, your inserts are typically small, fast transactions, maybe even non-logged operations in some cases.

Again in OLTP, queries are typical point queries meaning that the query will bring back a single row or very small set of rows. OLTP databases can usually be contained in memory (both table and index) as older data is not something an OLTP system is interested in.

Most folks don't do any DSS type queries in an OLTP database. Additional indexes, disk I/O, etc. mess with the cache / task switching and can hurt OLTP performance, which is more based on latency.

When your doing Data Warehouse type processing, your looking at ranges of data, the course of a day, the average over a week/month/year/etc. This is where parallel queries make sense. Break the problem down, hand them off to worker threads and merge the results.

It takes some setting up to get any real benefit out of it, partitioning, multi-core / multi-processor CPU's, appropriate disk controllers / paths, raid arrays or cache fronted disk storage (like EMC). They are designed for two completely problem sets.

Upvotes: 1

usr
usr

Reputation: 171178

SQL Server will only introduce parallelism for expensive queries. Typical OLTP queries are not affected at all.

Be careful playing with the global MaxDOP setting without understanding exactly what workload is running and what the setting does to it.

Upvotes: 1

Related Questions