https://cloud.google.com/compute/docs/instances/sql-server/best-practices?hl=pt-br
https://www.devmedia.com.br/paralelismo-do-sql-server-com-o-cxpacket/31608
select * from sys.dm_os_waiting_tasks
select * from sys.dm_exec_requests
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
query_plan AS CompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
n.query('.') AS ParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
-- analise Query
set statistics time on
SELECT SE2.E2_NUM , E2_PREFIXO, E2_PARCELA
FROM SE2010 (nolock) SE2
WHERE SE2.E2_FILIAL = '01' AND SE2.D_E_L_E_T_= ' '
ORDER BY REPLICATE('0', 9 - LEN(E2_NUM ) ) + RTRIM(E2_NUM ) DESC OPTION(MAXDOP 3)
GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'cost threshold for parallelism', 500 ;
GO
RECONFIGURE
GO
-- Quantidade de CPUS
SELECT (cpu_count / hyperthread_ratio) AS PhysicalCPUs,
cpu_count AS logicalCPUs
FROM sys.dm_os_sys_info