Parallel plan

A few days ago, I was call to see about a query that was some strange behavior.

The query was  composed by two queries with a UNION ALL .

Q1 UNION All Q2

and was taking around 15-20 sec to execute.

Q1,Q2 have like 15-20 tables and views joining together.  There were filtering for this month and year. Q1 has the result data, Q2 – hold the old data, fox data so, no result here.

Taking each on separately and execute them, they were finishing in less then 1 sec.

Q1 – was able to produce a parallel plan , Q2 – was unable to produce a parallel plan.

Reunion them, QO -was unable to produce a parallel plan because of Q2.

MaxDOP = 0  (this setting should be change , but I’m not in control) –

Threshold for Parallel  = 5 (this setting should also be change , but I’m not in control)

No  Resource Governor limitation in place…

From this point , they should trigger parallel plan. Looking at the execution plan , in xml output, here there was  NonParallelPlanReason = CouldNotGenerateValidParallelPlan .

Button line, in Q2 , was a view, that had an scalar function , this was disable parallel option. Rewrite it , and then all when well.

 

Conclusion:

Look at execution plan, also in xml version, you will find good information.

 

S

 

AT TIME Zone

New in sql server, starting with version 2016, it’s AT TIME ZONE, used to convert some date values to a specific zone.

 

Here is a sample :

declare @dt2_myDate as datetime2(0) = ‘20190216 08:10:12’ ;

select CONVERT(datetime2(0) , @dt2_myDate , 126)
AT TIME ZONE ‘Central European Standard Time’
AT TIME ZONE ‘UTC’ as newDateTime

 

newDateTime
16/02/2019 07:10:12 +00:00

 

S

Check database roles

If you have a new user/login and want to grant some permission base on an existing user. For this , I  use a script like this :

USE myDatabase
GO

set transaction isolation level read uncommitted;

;WITH RoleMembers (member_principal_id, role_principal_id)
AS
(
SELECT
rm1.member_principal_id,
rm1.role_principal_id
FROM sys.database_role_members rm1 (NOLOCK)

UNION ALL

SELECT
d.member_principal_id,
rm.role_principal_id
FROM sys.database_role_members rm (NOLOCK)
INNER JOIN RoleMembers AS d
ON rm.member_principal_id = d.role_principal_id
)
,lstUsers AS
(SELECT 5 as iIndex,’newUser’ as userFinal, ‘baseUser’ as userBase
)
select
CA.database_role as finalUserRoles
,u.userFinal as database_finalUser

,u.userBase as userBase
,OA.database_role as database_role_base__need_to_be_granted

,’IF IS_ROLEMEMBER(”’ + OA.database_role +”’ , ”’ + u.userFinal +”’) = 0 ‘ +
‘ BEGIN ‘ + CHAR(13) + CHAR(10) +
‘ ALTER ROLE [‘ + OA.database_role + ‘] ADD MEMBER ‘ + ‘[‘+ u.userFinal +’]’ + ‘;’ + + CHAR(13) + CHAR(10) +
‘ RAISERROR (” Rolul ‘ + OA.database_role + ‘ for user ‘ + u.userFinal + ‘ was granted;”,10,1) WITH NOWAIT;’ + CHAR(13) + CHAR(10) +
‘END ‘ + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
as stmtToExec
from
lstUsers as u
OUTER APPLY
(
SELECT distinct
rp.name
FROM
RoleMembers as drm
inner join sys.database_principals as rp on drm.role_principal_id = rp.principal_id
inner join sys.database_principals as mp on drm.member_principal_id = mp.principal_id
WHERE
mp.name = u.userBase
)OA(database_role) –base Role

OUTER APPLY
(
SELECT DISTINCT
rp.name
FROM
RoleMembers as drm
inner join sys.database_principals as rp on drm.role_principal_id = rp.principal_id
inner join sys.database_principals as mp on drm.member_principal_id = mp.principal_id
WHERE
mp.name = u.userFinal
and OA.database_role = rp.name
)CA(database_role) — for finalUser
order by u.iIndex,u.userFinal

S