The Heap™ – Consultancy ©®

12:03 AM

The issue is, even if I switched it to an INT PK, and made the NVARCHAR(64) PK a non-PK, and kept it, I would still not really gain much (I don't think) because literally every query has a WHERE Name = @Name and that Name is the PK now, and even if I made a new PK as an INT it would still always be WHERE Name = @Name in the query, because I reference all these items by their name, and they're frequently referenced.

Joe Obbish

@sp_BlitzErik I can, but a link to what?

the chat message here?

EBrown

Also, rare inserts. Should have mentioned that. Mostly SELECT, few UPDATE, no DELETE, and rare INSERT.

Joe Obbish

ok, I think I can give a real answer on the subject

looks like for the query in row mode we end up with a repartition streams operator that partitions by the PARTITION BY column

this happens even when the sort isn't needed

so if we're partitioning by a constant value then all of the rows will be assigned to one thread

so having that query go parallel doesn't seem helpful at all

with the batch mode operator we can get distribution over multiple parallel threads, even when partitioning by a constant value

of course, that's not an answer for why it won't go parallel. just that it wouldn't be beneficial to do so

Michael Green

12:25 AM

@EBrown If that column is referenced in every query having an index on it makes sense. If the column is a candidate key then defining that index as UNIQUE is the right thing to do. Having it as the clustered index, however, will have implications for all other indexes, as the clustering column(s) are duplicated in non-clustered indexes. So other indexes (if there are any?) will have fewer rows per page, require more pages per scan, have greater depth -- all making reads slightly slower.

EBrown

@MichaelGreen There will not be any other indexes, period. It makes no sense, really, because the table has a low cardinality and few columns.

Michael Green

You could have the best of both worlds by hashing Name and using the hash (an integer) as the PK at the small additional cost of calculating the hash each query.

EBrown

I don't know if you're familiar with EF/C#, but I can post the actual object if it helps you.

Michael Green

@EBrown only a little bit. Not enough to offer advice.

EBrown

@MichaelGreen I could do that, but these values are literally referenced hundreds of times per page load, by name.

I cannot do them by ID either, even if they had one.

CREATE TABLE [dbo].[Settings](
	[Name] [nvarchar](64) NOT NULL,
	[Value] [nvarchar](max) NULL,
	[Type] [nvarchar](max) NULL,
	[DisplayName] [nvarchar](max) NULL,
	[Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.Settings] PRIMARY KEY CLUSTERED
(
	[Name] 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]

There's the DDL.

Since you're not an EF person. ;) The MAX size qualifiers on each NVARCHAR will eventually be replaced with hard values, but that should tell you what's going on.

Literally the only column that's not requested by every query is Description, which is only used to inform editors of the value what it is all about.

Everything else is almost always needed, DisplayName is also less frequently used but much more-so than Description.

Basically, the C# API is viewed like: var smtpServer = DefaultSettings.SmtpServer.GetValue(context);, which will query the DB for Name, Value, Type where Name = 'SmtpServer', then return Value formatted to Type, depending on what system type it is.

Michael Green

12:37 AM

With a single pathway in to the data (Name = @Name), no other indexes, no referencing FKs, no secondary indexes, column is unique and not nullable .. can't think of a good reason not to define it as a PK.

EBrown

Alright, I appreciate your input. :) I wanted to make sure this was not going to become a major problem in the future, and, as I said, cardinality should be low - I currently have 20 whole records in this table, and I expect it to grow no larger than 200-300, eventually I'm going to add an INT which is an FK to a different table but that shouldn't be pulled except on a few queries with Description - that's when it'll most likely be needed.

Michael Green

1:04 AM

In the future, when you add that new column and make it a FK referencing a different table, you'll likely put an index on that new column. If you make Name the clustering column, that new index on the new column will be a little bit larger because, under the covers, it will drag in Name, too, since Name is the clustering column. With the number of rows you mention the difference will be tiny and not worth worrying about.

EBrown

@MichaelGreen Yeah, I'm not worried about the very small space taken. (Over 1k rows the max extra consumption is 25kB, which is negligible.) What I did just realize is I do have a table that FK's this one, I totally forgot because it's a history-log, and I don't count it. (Since I never query it, really.) I'm starting to think I might want to ID this table with an INT and index Id, Name, Value, Type, since those will be pulled most frequently. I'm still not sure yet.

McNets

6:56 AM

Morning

Transcript for

The Heap™ – Consultancy ©®