How to resolve SQL query takes 30 min on one server and same will takes 3 hours on another server? | Query slowness due to Maxdop and CPU issues on SQL Server

by

This article is about,

How to resolve SQL query takes 30 min on one server and same will takes 3 hours on another server? or Query slowness due to Maxdop and CPU issues on SQL Server

Sometime customer raise ticket that they are facing query slowness issue on one server and same query executing quickly on another server. Query execute in 30 min on DEV server and same query takes 3 hours to execute on PROD Server.

What to check to troubleshoot such type of issue on SQL Server?

When you see such type of issue , query execute faster on one server and slow on other. Check resource utilization of both SQL server VM. If query is retrieving too much of data and performing a lot operation then check if those operations are running parallelly.

For running query operation parallelly you need to configure MAXDOP i.e. max degree of parallelism. This configuration allow to limit SQL server to use existing CPU power for query processing parallelly.

Most of the cases if query runs faster on one SQL server and slow on another in that case total number of CPU power present is different and also MAXDOP configured value is also different which limit SQL server to perform parallelly processing and that’s why same query runs very slow on that server.

How to resolve SQL query takes 30 min on one server and same will takes 3 hours on another server? OR Query slowness due to Maxdop and CPU issues on SQL Server?

Step 1 : Check CPU configuration of server on which you are facing query slowness. Sometime on production you have SQL Server Always On configured and there is reporting query node where all select queries runs. But CPU configuration on reporting query node is lesser than primary node. Upgrade CPU configuration same as primary replica which help to run query faster.

Step 2 : If CPU configuration is slow then check MAXDOP setting on Server. To check MAXDOP configuration run

-- If advance option not showing run

 sp_configure 'show advanced options',1
go
reconfigure
go

Step 3 : If MAXDOP configured 0 then SQL server can use all CPU power for parallel SQL query processing. But if we have used limit value of maxdop on SQL server then SQL server only use limited number of CPU for query processing but if query process huge data then maxdop configured value not sufficient to perform that query processing parallelly which slow down query and it takes time to execute.

To resolve this you can use maxdop(0) query hint which can help to run query faster.(Note : You can try different value in query hint for maxdop as per available CPU core and test query)

-- maxdop query hint

 SELECT *
  FROM [AdventureWorks2019].[Sales].[Currency] option(maxdop 0)

Leave a Comment