Why parallelism is not desired in OLTP database enviornment.

 a) Many time out of date statistics or missing indexes can lead to parallelism in OLTP environment. Contrarily to OALP in most OLTP environment a transaction touches few numbers of rows so large table sca n is not desired which generally uses parallel query executions.

 b) Say you have highly scalable OLTP database with more than 1000 transactions per second. If a query or stored procedure uses parallelism it can spawn multiple threads per request this in turn increase CPU and amount of threads that can be handled per seconds (1000 threads x 3 = 3000 threads).  

 In certain situation you can limit max degree of parallelism server configuration to 1 which in turn disables parallelism.

 Keep in mind if you set Max Degree of Parallelism to 1, if you use query hint OPTION MAXDOP it would override server level parallelism settings.

 

 

Advertisements
This entry was posted in SQL Tricks and Tips. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s