SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strange performance behaviour


Strange performance behaviour

Author
Message
lenamagnus.frisk
lenamagnus.frisk
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 10
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

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121404 Visits: 18756
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
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: General Forum Members
Points: 147126 Visits: 22090
Can you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
lenamagnus.frisk
lenamagnus.frisk
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 10
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
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121404 Visits: 18756
lenamagnus.frisk - Wednesday, June 13, 2018 7:50 AM
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)?

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

Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5779 Visits: 1112
Or a staging table
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: General Forum Members
Points: 147126 Visits: 22090
Are you aware that you'll get 2 result sets when @opMode is NULL?


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
lenamagnus.frisk
lenamagnus.frisk
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 10
John Mitchell-245523 - Wednesday, June 13, 2018 8:03 AM
lenamagnus.frisk - Wednesday, June 13, 2018 7:50 AM
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)?

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

Attachments
EP_fast.sqlplan (6 views, 196.00 KB)
EP_slow.sqlplan (8 views, 226.00 KB)
lenamagnus.frisk
lenamagnus.frisk
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 10
Luis Cazares - Wednesday, June 13, 2018 7:39 AM
Can 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

lenamagnus.frisk
lenamagnus.frisk
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 10
lenamagnus.frisk - Thursday, June 14, 2018 1:50 AM
Luis Cazares - Wednesday, June 13, 2018 7:39 AM
Can 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

Attachments
SampleData.xlsx (4 views, 659.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search