Skip to main content

What is MAXDOP in SQL Server

As we every one know we every application we are using run on top of an OS. Typically Windows operating system or may be Linux. SQL Server is running on top of Windows OS, now Linux as well. SQL Server has its own OS, which do the lot of tasks like , scheduling threads for CPU, memory managing, synchronizing, deadlocks detection, and many more.. Normally in our machine  has multiple CPUs or logical processors which allows to do our tasks fast and parallelly.

We can check the number of CPUs our machine has in many ways.

1. Right-click task bar -> Task Manager -> Performance tab


2. We can write a SELECT query and retrieve the number of logical processors or cores belongs to our computer

SELECT cpu_count

FROM sys.dm_os_sys_info;

GO



 Now lets see what is MAXDOP

MAXDOP or Maximum number of Degree Of Parallelism means we can allow or limit by set the value for MAXDOP as a query hint or else configure in sp_configure global configurations and execute the query parellely. Actually when we write a query as a query hint what it does, its override the value we have configured in sp_configure. The default configuration value is 0 (Which means unlimited)



In the below query maximum number of parallel threads which can be used limit to one.

SELECT *
FROM dbo.FactFinance f
INNER JOIN dbo.DimDate d ON f.DateKey = d.DateKey
OPTION (MAXDOP 1);
GO

Labels:

Comments

Post a Comment

Popular posts from this blog

Run T-SQL Script Files Using Command Line

Most of the time when we need to execute a SQL script or statement, then we go for SQL Server Management Studio to execute our T-SQL scripts. But the same result we can achieve using Command Prompt. This is very simple way to do that just a matter of write a command and pass few arguments. You can see the process status like as in when you execute a query in Management studio. Use-case : There may be having some situations you won't be able to execute T-SQL script using Management Studio. Most of the time when doing data population. In my case, I had a situation I needed to populate a Database without using backup/restore. Once I generated script with all the schemas and related data it took around 400+Mb sql file.  SQL Server Management Studio will not allow you to run the script unless you execute the query in high-end box. Because, when the file loaded to the memory it will too hard to cater our requirement. You may probably get  memory ...
Post a Comment
Read more

How to Create a Date Table in Few Steps Using Power BI

Calendar Dimension or Calendar Table is one of the crucial table in a Power BI model. I never have seen any data warehouse data model which does not have a Calendar table so far in my development. Because, when it comes to data warehousing or dimension modeling you may storing various business processes or events as Facts. So those events anyway occur in particular date or time. So simply there wouldn’t be a Power BI data model without a date table. In this post, I’m going to share with you how to create a date table within few steps. Actually, there are two DAX measures which we can use to create a date table. Calendar(DAX) and CalendarAuto(Auto). Calendar(DAX) = You can pass start date and End date as parameters you need to create date table. CalendarAuto(DAX) = You can use this function without passing any parameter. Then it will generate the dates based on your data model dates.  You can simply copy the below DAX code and paste in your Power BI Desktop DAX e...
7 comments
Read more