June 13, 2018 at 12:47 am
Hi,
I have a strange performance behaviour. I have a backup from a database (SQL Server 2012) that is restored on a different machine (SQL Server 2016 SP2). One specific query is running very slow on this newly restored db (that takes no time at all on SQL 2012). Maybe not so strange? We have an other version of SQL, maybe related to that? So I start testing around. I begin by changing Compability level to 130 (SQL 2016) on the db. When I run the specific query from my client I still have the slow performance. Maybe the change did not kick in? I restart the SQL service and I restart the client - still no improvement. After a lot of searching and testing I found one scenario that got the query to execute fast again. I change compability level (it does not matter to what value I put there - the important thing is that I change it) AND then I run the slow query in SQL Management Studio. Now the query runs fast again, both in SQL management Studio and from my client. Does anyone have an idea what the problem can be? An important notice is that I have the exact same problem when I restore the database on the same machine where I have SQL Server 2016 SP2) but on an SQL 2102 instance. The database was originally a SQL 2008 R2 version and the compability level is 100 (SQL 2008) on the database.
(Fast = 0 sek, Slow = 3-4 sek)
Here is the query:
(In my sample I have about 2000 thousands inserts...)
declare @p3 dbo.DataIdentifierGuidType
insert into @p3 values(N'A','BCCC8B8B-A30B-4A70-9293-0007E4A60A4F')
insert into @p3 values(N'A','0AEC3D71-ABBB-4E48-9DD0-0066DEEB42ED')
insert into @p3 values(N'A','0F39995E-59F3-4BD4-9A82-F69FE55CD2A6')
insert into @p3 values(N'A','500FABE0-9715-4824-9A9E-F7AD3D7A29BC')
insert into @p3 values(N'A','1A7662A1-280D-472E-9F2B-F7F6926172E4')
insert into @p3 values(N'A','824A8286-57EF-4AA4-B256-F880173EF611')
insert into @p3 values(N'A','43E81353-60C0-42F8-B261-FA653DFE9D9A')
insert into @p3 values(N'A','B615196E-6804-4834-812E-FAFF52BB8043')
insert into @p3 values(N'A','2BA5B0B7-0D99-4A29-92DD-FBAC76E384F0')
insert into @p3 values(N'A','F53E9474-AB08-4466-8AEE-FDDCA16F6A3D')
insert into @p3 values(N'A','87F27F8B-B0DB-4D66-B611-FE899E338A65')
insert into @p3 values(N'A','C99A1A23-6EA8-4776-A5BD-FF8C4E64D3B9')
exec HasPermissionToObjects @UserAccountGuid='8014C576-BB22-4337-B31F-1CF13D13D42A',@opMode=N'P',@BusinessObjectDataGuids=@p3
-- HERE IS THE SP THAT IS CALLED --
USE [xxxxxxxx]
GO
/****** Object: StoredProcedure [dbo].[HasPermissionToObjects] Script Date: 2018-06-13 08:10:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description: Allows batch fetching of object permissions
-- =============================================
-- MJN - 2015-09-30 - Does not always return correct result. Fixed!
-- MJN - 2017-01-20 - Added function to the criteria if a user has access to an object .
-- MJN - 2017-05-05 - Added permission XML option via opmode P
-- MJN - 2017-09-15 - Added read mode based on read permission
ALTER PROCEDURE [dbo].[HasPermissionToObjects]
(
@UserAccountGuid uniqueidentifier
, @BusinessObjectDataGuids DataIdentifierGuidType READONLY
, @opMode nvarchar(1) = 'S' -- Simple
)
WITH EXEC AS CALLER
AS
BEGIN
-- MJN New code to also take into consideration if the role has assigned objects to the usergroup ...
if (@opMode = 'S' or @opMode is null)
BEGIN
;with AccessCte (BusinessObject, HasAccess)
as (
select AC.BusinessObjectDataGuid, cast( case when count(SF.SystemFunctionGuid) > 0 then 1 else 0 end as bit) from AccessControl AC
inner JOIN UserGroupAccessControlRelation AS UGAC ON UGAC.AccessControlGuid = AC.AccessControlGuid
inner join UserGroupRole AS UGR ON UGR.UserGroupGuid = UGAC.UserGroupGuid
inner JOIN UserAccountUserGroupRoleRelation AS UAUCR ON UGR.UserGroupRoleGuid = UAUCR.UserGroupRoleGuid and UAUCR.UserAccountGuid = @UserAccountGuid
inner JOIN FunctionPermission AS FP ON UGR.UserGroupRoleGuid = FP.UserGroupRoleGuid
inner JOIN SystemFunction AS SF ON FP.SystemFunctionGuid = SF.SystemFunctionGuid and SF.FunctionID = REPLACE(AC.BusinessObjectType, 'AF.Tekdok.Business.', '')
group by AC.BusinessObjectDataGuid
)
select bo.DataIdentity as BusinessObjectGuid, isnull(access.HasAccess,0) as HasAccess
from @BusinessObjectDataGuids bo
left join AccessCte access on bo.DataIdentity = access.BusinessObject
END
if (@opMode = 'R' or @opMode is null)
BEGIN
;with AccessCte (BusinessObject, HasAccess)
as (
select AC.BusinessObjectDataGuid, CAST(MAX(CAST(FP.ReadPermission AS int)) AS bit) from AccessControl AC
inner JOIN UserGroupAccessControlRelation AS UGAC ON UGAC.AccessControlGuid = AC.AccessControlGuid
inner join UserGroupRole AS UGR ON UGR.UserGroupGuid = UGAC.UserGroupGuid
inner JOIN UserAccountUserGroupRoleRelation AS UAUCR ON UGR.UserGroupRoleGuid = UAUCR.UserGroupRoleGuid and UAUCR.UserAccountGuid = @UserAccountGuid
inner JOIN FunctionPermission AS FP ON UGR.UserGroupRoleGuid = FP.UserGroupRoleGuid
inner JOIN SystemFunction AS SF ON FP.SystemFunctionGuid = SF.SystemFunctionGuid and SF.FunctionID = REPLACE(AC.BusinessObjectType, 'AF.Tekdok.Business.', '')
group by AC.BusinessObjectDataGuid
)
select bo.DataIdentity as BusinessObjectGuid, isnull(access.HasAccess,0) as HasAccess
from @BusinessObjectDataGuids bo
left join AccessCte access on bo.DataIdentity = access.BusinessObject
END
if (@opMode = 'P')
BEGIN
;WITH OriginalQuery AS (
select bo.DataIdentity as BusinessObjectGuid
from @BusinessObjectDataGuids bo
)
select oq.*, (select isnull(R, 0) as R,isnull(C, 0) as C,isnull(U, 0) as U,isnull(D, 0) as D,isnull(F, 0) as F,isnull(E, 0) as E,isnull(P, 0) as P,isnull(RA, 0) as RA,isnull(T, 0) as T for xml path('Permission'),type,elements absent) as PermissionXml
from
OriginalQuery oq
left join (
SELECT
BusinessObjectDataGuid as BO,
CAST(MAX(CAST(FP.ReadPermission AS int)) AS bit) R,
CAST(MAX(CAST(FP.CreatePermission AS int)) AS bit) C,
CAST(MAX(CAST(FP.UpdatePermission AS int)) AS bit) U,
CAST(MAX(CAST(FP.DeletePermission AS int)) AS bit) D,
CAST(MAX(CAST(FP.FilterPermission AS int)) AS bit) F,
CAST(MAX(CAST(FP.ExportPermission AS int)) AS bit) E,
CAST(MAX(CAST(FP.PrintPermission AS int)) AS bit) P,
CAST(MAX(CAST(FP.RightsAssignmentPermission AS int)) AS bit) RA,
CAST(MAX(CAST(FP.TranslatePermission AS int)) AS bit) T
FROM
AccessControl AS AC INNER JOIN
UserGroupAccessControlRelation AS UGACR ON AC.AccessControlGuid = UGACR.AccessControlGuid INNER JOIN
UserGroup AS UG ON UGACR.UserGroupGuid = UG.UserGroupGuid INNER JOIN
UserGroupRole AS UGR ON UG.UserGroupGuid = UGR.UserGroupGuid INNER JOIN
UserAccountUserGroupRoleRelation AS UAUGRR ON UGR.UserGroupRoleGuid = UAUGRR.UserGroupRoleGuid INNER JOIN
FunctionPermission AS FP ON UGR.UserGroupRoleGuid = FP.UserGroupRoleGuid INNER JOIN
SystemFunction AS SF ON FP.SystemFunctionGuid = SF.SystemFunctionGuid
INNER JOIN OriginalQuery bo on bo.BusinessObjectGuid = AC.BusinessObjectDataGuid
WHERE
(UAUGRR.UserAccountGuid = @UserAccountGuid)
AND
(REPLACE(AC.BusinessObjectType, 'AF.Tekdok.Business.', '') = SF.FunctionID)
GROUP BY
AC.BusinessObjectDataGuid,
AC.BusinessObjectType) tmpTbl on oq.BusinessObjectGuid = tmpTbl.BO
END
END
Here is the structure of the user defined table type:
CREATE TYPE [dbo].[DataIdentifierGuidType] AS TABLE(
[DataType] [varchar](10) NULL,
[DataIdentity] [uniqueidentifier] NULL
)
GO
/ A confused one
June 13, 2018 at 2:07 am
Please post the actual execution plans (not estimated) as .sqlplan files for each scenario. If you're inserting 2000 rows into @p3 and then passing that as the argument for @BusinessObjectDataGuids then that's a red flag - there are no statistics on table variables and so the query optimizer will just assume there is one row in the table. (That number may have changed in a more recent version - I'm not sure).
John
June 13, 2018 at 7:39 am
Can you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.
June 13, 2018 at 7:50 am
Hi John and thank you for your answer,
I will post the actual execution plans. I understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?
/ A confused one
June 13, 2018 at 8:03 am
lenamagnus.frisk - Wednesday, June 13, 2018 7:50 AMI understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?
Impossible to say for sure without seeing the execution plans. It may involve inserting the @BusinessObjectDataGuids list into a temp table at the beginning of your stored procedure, though.
John
June 13, 2018 at 10:12 am
Or a staging table
June 13, 2018 at 10:42 am
Are you aware that you'll get 2 result sets when @opMode is NULL?
June 14, 2018 at 1:39 am
John Mitchell-245523 - Wednesday, June 13, 2018 8:03 AMlenamagnus.frisk - Wednesday, June 13, 2018 7:50 AMI understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?Impossible to say for sure without seeing the execution plans. It may involve inserting the @BusinessObjectDataGuids list into a temp table at the beginning of your stored procedure, though.
John
Hi John,
Here a the two execution plans.
/A confused one
June 14, 2018 at 1:50 am
Luis Cazares - Wednesday, June 13, 2018 7:39 AMCan you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.
Hi Luis and thank you for your answer,
Here are two files (to create table FunctionPermission and some sample data)
/A confused one
June 14, 2018 at 1:53 am
lenamagnus.frisk - Thursday, June 14, 2018 1:50 AMLuis Cazares - Wednesday, June 13, 2018 7:39 AMCan you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.Hi Luis and thank you for your answer,
Here are two files (to create table FunctionPermission and some sample data)
/A confused one
Sorry - forgot the files...
create table:/****** Object: Table [dbo].[FunctionPermission] Script Date: 2018-06-14 09:51:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FunctionPermission](
[FunctionPermissionGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[SystemFunctionGuid] [uniqueidentifier] NOT NULL,
[PermissionTypeGuid] [uniqueidentifier] NOT NULL,
[UserGroupRoleGuid] [uniqueidentifier] NULL,
[UserAccountGuid] [uniqueidentifier] NULL,
[Name] [nvarchar](50) NOT NULL,
[ReadPermission] [bit] NOT NULL,
[CreatePermission] [bit] NOT NULL,
[UpdatePermission] [bit] NOT NULL,
[DeletePermission] [bit] NOT NULL,
[FilterPermission] [bit] NOT NULL,
[ExportPermission] [bit] NOT NULL,
[PrintPermission] [bit] NOT NULL,
[RightsAssignmentPermission] [bit] NOT NULL,
[TranslatePermission] [bit] NOT NULL,
CONSTRAINT [PK_FunctionPermission] PRIMARY KEY NONCLUSTERED
(
[FunctionPermissionGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_FunctionPermissionGuid] DEFAULT (newid()) FOR [FunctionPermissionGuid]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_ReadPermission] DEFAULT ((1)) FOR [ReadPermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_CreatePermission] DEFAULT ((0)) FOR [CreatePermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_UpdatePermission] DEFAULT ((0)) FOR [UpdatePermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_DeletePermission] DEFAULT ((0)) FOR [DeletePermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_FilterPermission] DEFAULT ((1)) FOR [FilterPermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_ExportPermission] DEFAULT ((0)) FOR [ExportPermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_PrintPermission] DEFAULT ((1)) FOR [PrintPermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_RightsAssignmentPermission] DEFAULT ((0)) FOR [RightsAssignmentPermission]
GO
ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_TranslatePermission] DEFAULT ((0)) FOR [TranslatePermission]
GO
ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_PermissionType] FOREIGN KEY([PermissionTypeGuid])
REFERENCES [dbo].[PermissionType] ([PermissionTypeGuid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_PermissionType]
GO
ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_SystemFunction] FOREIGN KEY([SystemFunctionGuid])
REFERENCES [dbo].[SystemFunction] ([SystemFunctionGuid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_SystemFunction]
GO
ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_UserAccount] FOREIGN KEY([UserAccountGuid])
REFERENCES [dbo].[UserAccount] ([UserAccountGuid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_UserAccount]
GO
ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_UserGroupRole] FOREIGN KEY([UserGroupRoleGuid])
REFERENCES [dbo].[UserGroupRole] ([UserGroupRoleGuid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_UserGroupRole]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The internal key of the user. Automatically generated.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FunctionPermission', @level2type=N'COLUMN',@level2name=N'UserAccountGuid'
GO
June 14, 2018 at 2:08 am
lenamagnus.frisk - Thursday, June 14, 2018 1:39 AMJohn Mitchell-245523 - Wednesday, June 13, 2018 8:03 AMlenamagnus.frisk - Wednesday, June 13, 2018 7:50 AMI understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?Impossible to say for sure without seeing the execution plans. It may involve inserting the @BusinessObjectDataGuids list into a temp table at the beginning of your stored procedure, though.
John
Hi John,
Here a the two execution plans.
/A confused one
Well, in the fast one, the number of rows in the table variable is being correctly estimated, whereas in the slow one it isn't. And you are scanning the table variable twice, so the effect is magnified. There doesn't appear to be a good reason to have a CTE (just use the table variable name) and there certainly doesn't seem to be a good reason to join the CTE to itself, causing the two scans. I don't know why the cardinality is being estimated correctly in one plan but not in the other, but you might be interested to read through this.
John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply