- About us
- Services
- Skills
- Web Development
- ColdFusion Development
- SharePoint Development
- Classic ASP Development
- ASP.NET Development
- Modern ASP .Net Development
- DotNetNuke Development
- Java/JSP/servlets Development
- Python Development
- PHP Development
- Perl Development
- Ruby on Rails (RoR) Development
- Lasso Development
- Real Basic Development
- Google API Integration Services
- Mobile Development
- Database & Client server Development
- Plug-in Development
- Reports/BI Development
- Automated Testing
- QA/Testing Skills
- Domain-based Testing
- E-Commerce Development
- CMS Development
- Desktop Development
- RIA Development
- App-based development
- Salesforce Development
- SugarCRM Development
- Dynamics GP GreatPlains development
- Dynamics SL Solomon development
- Microsoft Dynamics CRM Consulting Development
- Dynamics RMS development
- LiveCycle development
- QuickBooks development
- Drupal development
- Joomla development
- Typo3 development
- Magento development
- Dolphin social development
- Others
- Web Development
- Industries
- Knowledge
- Process
- Models
- Clients
- Career
| |
Software Technology Tips
Max Degree of Parallelism is an important setting in a multi processor environment. A value of 0 indicates that all processors will be involved in the query execution process of large queries which consume a lot of cpu. This can sometimes cause issues on the server and result in degraded performance.
|
This setting can be changed at the server level by setting the value to 1, which will prevent parallel execution of all queries fired.
Parallel execution does result in execution plans which cause queries to run faster. So ,turning it off at the server is not advisable in most scenarios. Parallel Execution also covers up for poorly indexed databases.
The main disadvantage of parallel execution is it increases cpu utilization.
However we can always control parallel execution for specific queries by using the MAXDOP hint . For example :
SELECT pk_id, employee_name from employees ORDER BY pk_id, employee_name OPTION (MAXDOP 1) Recently there was a scenario where we migrated from windows 2003 x32 server to windows 2008 x64 server. Although the later is a powerful machine we had performance problems for a sql job which was running on it. This job was basically executing a stored procedure which had a complex query with large number of UNIONs.
The query was somehow taking abnormal amount of time to execute on the new server. The Activity Monitor inside SQL server showed high wait time during the execution of this job.
The work around for this was using the MAXDOP hint. Using OPTION (MAXDOP 1) the execution time was drastically reduced on the new server and the wait time problem was eliminated. |
Related Tags:
Database, SQL Server
Author: Arijit Rath
Database
- Importance of Max Degree of Parallelism in SQL Server
- How to display tabular data in CLR proc
- MySQL: Ordering values according to a predefined pattern
- Use of cross apply to show ',' separated values(SQL Server)
- MYSQL:Ordering A TextField Numerically
- How to select Random rows in SQL Server using TABLESAMPLE
- How to add dynamic data in to the SQL Report header and footer
- How To Set Page Break in a report Conditionally
- MySQL: Filter or Search Records Using Regular Expression
- MSSQL-Insert records for columns where IDENTITY is set
- Forming Comma Separated String in SQL Server
- How to create folders in SQLReport server dynamically ?
- Get database connection string using UDL files
- How to INSERT, UPDATE or DELETE record in one statement
- SQL Server : Multiple record insert
- Making Inserts into from one table to another table excluding duplicates - without using cursor / temp table/ loop
- New Line character and Forming a comma Separated String in SQL Server
- SqlServer Query Optimization Tips
- MySQL : Exporting The Resultset of a Table into CSV File
- MySQL: How to get the Particular Data Range
- More Related Tips
