MAXDOP Settings When Exceeding the Recommendation
I have an Sql Server 2014 Enterprise running on VMWare with 32 logical processors assigned. Based on Microsoft recommendation the MAXDOP should be set to 8. However when I run a large query against multiple large tables:
With MAXDOP at 8: 7 Minutes With MAXDOP at 16: 3 Minutes
My questions is what issue I could encounter if I to set the MAXDOP to 16 which is half the number of logical processors?
Thanks in advance
See also questions close to this topic
SQL Server get yesterday start and end datestamp and convert to Unix epoch
I have a Stored Procedure that will run within a time period (between 2-3 am), but not always at the exact same time during that period.
As part of this procedure I need to do 3 steps:
1: Get the start of yesterday's date
So if today is 13/08/2020 13:51:02 I need the query to return 12/08/2020 00:00:00 and do this dynamically
2: Get the end of yesterday's date
In the above this would return 12/08/2020 23:59:59
3: Convert both values into Unix EPOCH timestamps
I have used in the past on a similar issue (that was less time-sensitive) the below bit of Code:
declare @yesterday date set @yesterday = (SELECT convert(datetime, DATEADD(day, -1 ,getdate()), 23 ))
The Problem here is that this gives a value that is exactly 24 hours in the past, so if the SP is run at 2:15 am - it's time stamp will be different when it's run at 2:23 am or 2:53 am.
Once I've got a method of getting the start and end date to always be correct - I'll then use something like this solution to convert the Datetime into Epoch timestamps, unless someone who answers this question has a snazzy method of doing it all in one (for which I would be eternally grateful)
View count is not same as paging total
Any idea why
SELECT COUNT(*) FROM vwOrganizationContactsvalue is 15% more than the paging snippet below?
DECLARE @startDate DATETIME2 = '2000-01-01', @startID VARCHAR(100) = NULL, @count INT = 5000, @totalCount INT = 0, @more BIT = 1 SELECT COUNT(1) FROM vwOrganizationContacts WHERE [Contact.ModifiedAt] >= @startDate CREATE TABLE #mapping ( [Organization.ID] BIGINT, [Contact.ID] VARCHAR(100), [ModifiedAt] DATETIME2 ) WHILE (@more = 1) BEGIN PRINT 'StartDate: ' + CONVERT(VARCHAR, @startDate) PRINT 'StartId: ' + CONVERT(VARCHAR, @startId) PRINT 'TotalCount: ' + CONVERT(VARCHAR, @totalCount) RAISERROR(N'', 0, 1) WITH NOWAIT INSERT INTO #mapping ( [Organization.ID], [Contact.ID], [ModifiedAt] ) SELECT TOP (@count) orgContacts.[Contact.OrgId], orgContacts.[Contact.ID], orgContacts.[Contact.ModifiedAt] FROM vwOrganizationContacts orgContacts WHERE ( ( orgContacts.[Contact.ModifiedAt] = @startDate AND orgContacts.[Contact.ID] > ISNULL(@startID, '') ) OR orgContacts.[Contact.ModifiedAt] > @startDate ) ORDER BY orgContacts.[Contact.ModifiedAt], orgContacts.[Contact.ID] SELECT @totalCount = @totalCount + COUNT(1), @more = CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END FROM #mapping SELECT @startDate = MAX(v.[ModifiedAt]), @startId = MAX(v.[Contact.ID]) FROM #mapping v TRUNCATE TABLE #mapping END DROP TABLE #mapping SELECT @totalCount
Query to roll over weekends (saturday and Sunday) to next business Day (Monday) if it is same month and
So if we start a month on a weekend the first day becomes the weekend and it should roll over to weekday and similarly Holidays should roll back to previous day.
Date RecognizedBusinessDateKey RecognizedBusinessDateNumber 7/28/2020 20200728 19 7/29/2020 20200729 20 7/30/2020 20200730 21 7/31/2020 20200731 22 8/1/2020 20200803 1 8/2/2020 20200803 1 8/3/2020 20200803 1 8/4/2020 20200804 2 Date RecognizedBusinessDateKey RecognizedBusinessDateNumber 5/28/2020 20200528 19 5/29/2020 20200529 20 5/30/2020 20200529 20 5/31/2020 20200529 20 6/1/2020 20200601 1 6/2/2020 20200602 2 6/3/2020 20200603 3 6/4/2020 20200604 4 Date RecognizedBusinessDateKey RecognizedBusinessDateNumber 6/4/2020 20200604 4 6/5/2020 20200605 5 6/6/2020 20200605 5 6/7/2020 20200608 6 6/8/2020 20200608 6 6/9/2020 20200609 7
Data is looking like this in table
I tried the below query
SELECT d.Date ,d.DateKey ,d.IsBusinessDay ,d.MonthKey ,ISNULL(MAX(d2.DateKey),d.DateKey) AS RecognizedBusinessDateKey ,DENSE_RANK() OVER(PARTITION BY d.MonthKey ORDER BY ISNULL(MAX(d2.DateKey),d.DateKey)) AS RecognizedBusinessDateNumber FROM dbo.DimDate d LEFT JOIN dbo.DimDate d2 ON d2.MonthKey <= d.DateKey WHERE d2.IsBusinessDay = 'Business Day' AND d2.DateKey > 0 AND d2.Date BETWEEN '8/1/2020' AND '8/31/2020' GROUP BY d.Date ,d.DateKey ,d.IsBusinessDay ,d.MonthKey