Clustered index when using GUID as PK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Clustered index when using GUID as PK

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2015-01-13 : 11:35:03

We are using GUIDS for primary keys on our tables. Consequently, we have made the "rowid" the clustered index (makes no sense to have random values as clustered index). We never query using rowid but I've noticed that SQL Server is heavily doing SCANS on the rowid clustered index. Should I modify the clustered indexes to be...

ROWID,CLIENTID

Since we often query using clientid. Or should I just ensure that there are relevant non-clustered indexes to handle CLIENTID?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-13 : 11:43:30

Why are you using GUIDs for a PK? Generally this is a bad idea: it's wide (16 bytes) and random -- both contra-indicators for CIs, especially the last.

What is the query that is doing SCANs? Do you have NCIs that cover the columns in the WHERE and JOIN ON clauses?

Consider adding a NCI on ClientId, or even making it the PK, or the first column in the PK

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2015-01-13 : 11:50:15

quote:
Originally posted by gbritton

Why are you using GUIDs for a PK? Generally this is a bad idea: it's wide (16 bytes) and random -- both contra-indicators for CIs, especially the last.

What is the query that is doing SCANs? Do you have NCIs that cover the columns in the WHERE and JOIN ON clauses?

Consider adding a NCI on ClientId, or even making it the PK, or the first column in the PK

We opted to use GUIDS for PK to give us the flexibility to extract clients from one database and put them into another one (already populated with other clients).

I'm starting to regret the decision because that use case is fairly marginal and there seems to be some negative performance implications.

To clarify, it is a hybrid implementation. The GUID is the PK but NOT the clustered index.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-13 : 11:55:16

OK, I understand your use of GUID as PK better.

Could you post your DDL for the table along with your INSERT logic? What is in the ROWID column?

Note that you undoubtedly need an index on the CLIENTID column, and possibly others.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2015-01-13 : 12:12:07

quote:
Originally posted by gbritton

OK, I understand your use of GUID as PK better.

Could you post your DDL for the table along with your INSERT logic? What is in the ROWID column?

Note that you undoubtedly need an index on the CLIENTID column, and possibly others.

I'm not actually a DBA for my apologies if what I posted below isn't a DDL.

I do have some NCI for clientid. Is there any benefit to changing the clustered index from simply rowid to rowid,clientid or is it just as good to have the relevant NCIs handle it?

All INSERTS are done through the "entity framework" but using profiler I get this...

quote:
exec sp_executesql N'INSERT [dbo].[Users]([Id], [ClientId], [DepartmentId], [LanguageId], [EnrollmentKeyId], [Username], [Password], [PasswordSalt], [FirstName], [MiddleName], [LastName], [FullName], [Gender], [Address], [Address2], [City], [ProvinceId], [CountryId], [PostalCode], [Phone], [EmployeeNumber], [Location], [JobTitle], [InactiveMessage], [ExternalId], [Token], [PasswordResetToken], [AccountActivationToken], [ActiveStatus], [DateInactivated], [IsAdmin], [IsLearner], [IsInstructor], [PasswordResetExpiry], [PasswordChangeRequired], [AcceptedTermsAndConditions], [ReferenceNumber], [Notes], [EmailAddress], [ManagedGroupId], [DateHired], [DateTerminated], [CustomFields_Decimal1], [CustomFields_Decimal2], [CustomFields_Decimal3], [CustomFields_Decimal4], [CustomFields_Decimal5], [CustomFields_String1], [CustomFields_String2], [CustomFields_String3], [CustomFields_String4], [CustomFields_String5], [CustomFields_String6], [CustomFields_String7], [CustomFields_String8], [CustomFields_String9], [CustomFields_String10], [CustomFields_String11], [CustomFields_String12], [CustomFields_String13], [CustomFields_String14], [CustomFields_String15], [CustomFields_String16], [CustomFields_String17], [CustomFields_String18], [CustomFields_String19], [CustomFields_String20], [CustomFields_String21], [CustomFields_String22], [CustomFields_String23], [CustomFields_String24], [CustomFields_String25], [CustomFields_String26], [CustomFields_String27], [CustomFields_String28], [CustomFields_String29], [CustomFields_String30], [CustomFields_DateTime1], [CustomFields_DateTime2], [CustomFields_DateTime3], [CustomFields_DateTime4], [CustomFields_DateTime5], [CustomFields_Bool1], [CustomFields_Bool2], [CustomFields_Bool3], [CustomFields_Bool4], [CustomFields_Bool5], [Avatar], [DateAdded], [AddedBy], [DateEdited], [EditedBy], [IsDeleted], [DateDeleted], [Supervisor_Id])
VALUES (@0, @1, @2, @3, NULL, @4, @5, @6, @7, NULL, @8, @9, @10, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @11, NULL, @12, NULL, @13, @14, @15, @16, @17, NULL, NULL, NULL, @18, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @19, @20, @21, @22, @23, NULL, NULL)
',N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier,@3 int,@4 nvarchar(255),@5 nvarchar(255),@6 nvarchar(255),@7 nvarchar(255),@8 nvarchar(255),@9 nvarchar(255),@10 int,@11 nvarchar(255),@12 int,@13 bit,@14 bit,@15 bit,@16 datetime2(7),@17 bit,@18 nvarchar(255),@19 datetime2(7),@20 uniqueidentifier,@21 datetime2(7),@22 uniqueidentifier,@23 bit',@0='7B80AA77-B66E-47CB-BB7C-A6BDE1E7BCCF',@1='00000000-AAAA-BBBB-5555-00001212BBBB',@2='21264E4B-7952-42F0-BB1D-9319F4CE8529',@3=1,@4=N'craig.smith@cccc.comTEST',@5=N'olqaS/hHmMcnGSMeflzC/Z2HyrDvRmdH',@6=N'evND3BHxsX7bDRbZQT0vP/8DZdMqbjdy',@7=N'CraigTest',@8=N'User',@9=N'CraigTest User',@10=0,@11=N'eAa9Z0IOlwyuIPbgb0D3NtTvHFFDWTqZiBZO96KsRQcJGECkm5Tlu9aDAPRXIblmn3IurO3p2ot33R8DVSsd_g2',@12=0,@13=0,@14=1,@15=0,@16='2015-01-20 17:09:30.5075678',@17=1,@18=N'craig.smith@cccc.com',@19='2015-01-13 17:09:30.5075678',@20='7ECE5C23-15DD-4B0F-882A-24B1F8D89C78',@21='2015-01-13 17:09:30.5075678',@22='7ECE5C23-15DD-4B0F-882A-24B1F8D89C78',@23=0

DDL???


CREATE TABLE [dbo].[Users](
	[Id] [uniqueidentifier] NOT NULL,
	[ClientId] [uniqueidentifier] NOT NULL,
	[DepartmentId] [uniqueidentifier] NOT NULL,
	[LanguageId] [int] NULL,
	[EnrollmentKeyId] [uniqueidentifier] NULL,
	[Username] [nvarchar](255) NULL,
	[Password] [nvarchar](255) NULL,
	[PasswordSalt] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[MiddleName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[FullName] [nvarchar](255) NULL,
	[Gender] [int] NOT NULL,
	[Address] [nvarchar](4000) NULL,
	[Address2] [nvarchar](4000) NULL,
	[City] [nvarchar](255) NULL,
	[ProvinceId] [uniqueidentifier] NULL,
	[CountryId] [uniqueidentifier] NULL,
	[PostalCode] [nvarchar](255) NULL,
	[Phone] [nvarchar](255) NULL,
	[EmployeeNumber] [nvarchar](255) NULL,
	[Location] [nvarchar](255) NULL,
	[JobTitle] [nvarchar](255) NULL,
	[InactiveMessage] [nvarchar](max) NULL,
	[ExternalId] [nvarchar](255) NULL,
	[Token] [nvarchar](255) NULL,
	[PasswordResetToken] [nvarchar](255) NULL,
	[AccountActivationToken] [nvarchar](255) NULL,
	[IsAdmin] [bit] NOT NULL,
	[IsLearner] [bit] NOT NULL,
	[PasswordResetExpiry] [datetime] NULL,
	[PasswordChangeRequired] [bit] NOT NULL,
	[ReferenceNumber] [nvarchar](255) NULL,
	[Notes] [nvarchar](max) NULL,
	[EmailAddress] [nvarchar](255) NULL,
	[ManagedGroupId] [uniqueidentifier] NULL,
	[DateHired] [datetime] NULL,
	[DateTerminated] [datetime] NULL,
	[CustomFields_Decimal1] [decimal](18, 2) NULL,
	[CustomFields_Decimal2] [decimal](18, 2) NULL,
	[CustomFields_Decimal3] [decimal](18, 2) NULL,
	[CustomFields_Decimal4] [decimal](18, 2) NULL,
	[CustomFields_Decimal5] [decimal](18, 2) NULL,
	[CustomFields_String1] [nvarchar](255) NULL,
	[CustomFields_String2] [nvarchar](255) NULL,
	[CustomFields_String3] [nvarchar](255) NULL,
	[CustomFields_String4] [nvarchar](255) NULL,
	[CustomFields_String5] [nvarchar](255) NULL,
	[CustomFields_String6] [nvarchar](255) NULL,
	[CustomFields_String7] [nvarchar](255) NULL,
	[CustomFields_String8] [nvarchar](255) NULL,
	[CustomFields_String9] [nvarchar](255) NULL,
	[CustomFields_String10] [nvarchar](255) NULL,
	[CustomFields_String11] [nvarchar](255) NULL,
	[CustomFields_String12] [nvarchar](255) NULL,
	[CustomFields_String13] [nvarchar](255) NULL,
	[CustomFields_String14] [nvarchar](255) NULL,
	[CustomFields_String15] [nvarchar](255) NULL,
	[CustomFields_String16] [nvarchar](255) NULL,
	[CustomFields_String17] [nvarchar](255) NULL,
	[CustomFields_String18] [nvarchar](255) NULL,
	[CustomFields_String19] [nvarchar](255) NULL,
	[CustomFields_String20] [nvarchar](255) NULL,
	[CustomFields_String21] [nvarchar](255) NULL,
	[CustomFields_String22] [nvarchar](255) NULL,
	[CustomFields_String23] [nvarchar](255) NULL,
	[CustomFields_String24] [nvarchar](255) NULL,
	[CustomFields_String25] [nvarchar](255) NULL,
	[CustomFields_String26] [nvarchar](255) NULL,
	[CustomFields_String27] [nvarchar](255) NULL,
	[CustomFields_String28] [nvarchar](255) NULL,
	[CustomFields_String29] [nvarchar](255) NULL,
	[CustomFields_String30] [nvarchar](255) NULL,
	[CustomFields_DateTime1] [datetime] NULL,
	[CustomFields_DateTime2] [datetime] NULL,
	[CustomFields_DateTime3] [datetime] NULL,
	[CustomFields_DateTime4] [datetime] NULL,
	[CustomFields_DateTime5] [datetime] NULL,
	[CustomFields_Bool1] [bit] NULL,
	[CustomFields_Bool2] [bit] NULL,
	[CustomFields_Bool3] [bit] NULL,
	[CustomFields_Bool4] [bit] NULL,
	[CustomFields_Bool5] [bit] NULL,
	[DateAdded] [datetime] NOT NULL,
	[AddedBy] [uniqueidentifier] NULL,
	[DateEdited] [datetime] NOT NULL,
	[EditedBy] [uniqueidentifier] NULL,
	[IsDeleted] [bit] NOT NULL,
	[Supervisor_Id] [uniqueidentifier] NULL,
	[RowId] [int] IDENTITY(1,1) NOT NULL,
	[Avatar] [nvarchar](255) NULL,
	[AcceptedTermsAndConditions] [bit] NULL,
	[DateInactivated] [datetime] NULL,
	[DateDeleted] [datetime] NULL,
	[ActiveStatus] [int] NOT NULL DEFAULT ((0)),
	[IsInstructor] [bit] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_dbo.Users] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.Clients_ClientId] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.Clients_ClientId]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.Countries_CountryId] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Countries] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.Countries_CountryId]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.Departments_DepartmentId] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Departments] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.Departments_DepartmentId]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.EnrollmentKeys_EnrollmentKeyId] FOREIGN KEY([EnrollmentKeyId])
REFERENCES [dbo].[EnrollmentKeys] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.EnrollmentKeys_EnrollmentKeyId]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.Groups_ManagedGroupId] FOREIGN KEY([ManagedGroupId])
REFERENCES [dbo].[Groups] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.Groups_ManagedGroupId]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.Languages_LanguageId] FOREIGN KEY([LanguageId])
REFERENCES [dbo].[Languages] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.Languages_LanguageId]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.Provinces_ProvinceId] FOREIGN KEY([ProvinceId])
REFERENCES [dbo].[Provinces] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.Provinces_ProvinceId]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Users_dbo.Users_Supervisor_Id] FOREIGN KEY([Supervisor_Id])
REFERENCES [dbo].[Users] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_dbo.Users_dbo.Users_Supervisor_Id]
GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-13 : 12:26:43

OK -- you didn't post the create index for the ROwId, but that's OK. I get it. Now we need to dig into the query that is producing SCANS in the execution plan. Can you post an example of one?

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2015-01-13 : 12:47:35

quote:
Originally posted by gbritton

OK -- you didn't post the create index for the ROwId, but that's OK. I get it. Now we need to dig into the query that is producing SCANS in the execution plan. Can you post an example of one?

Not sure which queries are doing the SCANS. This is what prompted me to make this post (see image below). Notice that there are almost 50000 SCANS against the clustered index. I guess I just need to determine what indexes it wants (using a "missing indexes" query) until it stops doing SCANS against the clustered index...

https://drive.google.com/file/d/0B1KHaskv8072UjFWZkU5YV8yc0U/view?usp=sharing

Note: you can see that I have already created a couple of "clientid" NCIs. I also created a NCI that is "rowid" but includes all other fields. It is now being used (seeks) and the clustered index is no longer being used. This is a good thing I think.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-13 : 14:00:44

Find the longest running queries (e.g. profiler, extended events, activity monitor) then look at the execution plans for those. that will tell you where the scans are. Note that unless the queries are hours long or blocking other queries or killing the CPU or thrashing the disk, the whole exercise may not be worth the trouble. Try Data Collector if you like

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources