MSSQL - Primary Key Clustered specifying multiple columnsBest solution to fixing database design with GUID as...
What is going on with 'gets(stdin)' on the site coderbyte?
What exact color does ozone gas have?
How can I write humor as character trait?
Plot of a tornado-shaped surface
Quoting Keynes in a lecture
What features enable the Su-25 Frogfoot to operate with such a wide variety of fuels?
How can "mimic phobia" be cured or prevented?
Extract more than nine arguments that occur periodically in a sentence to use in macros in order to typset
Fear of getting stuck on one programming language / technology that is not used in my country
Store Credit Card Information in Password Manager?
How does a computer interpret real numbers?
Does malloc reserve more space while allocating memory?
Can I say "fingers" when referring to toes?
Can the US President recognize Israel’s sovereignty over the Golan Heights for the USA or does that need an act of Congress?
On a tidally locked planet, would time be quantized?
Can a stoichiometric mixture of oxygen and methane exist as a liquid at standard pressure and some (low) temperature?
How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?
How to fade a semiplane defined by line?
Calculating total slots
Does Doodling or Improvising on the Piano Have Any Benefits?
Recommended PCB layout understanding - ADM2572 datasheet
What happens if you are holding an Iron Flask with a demon inside and walk into an Antimagic Field?
Hero deduces identity of a killer
Why should universal income be universal?
MSSQL - Primary Key Clustered specifying multiple columns
Best solution to fixing database design with GUID as primary keyParent-Child Tree Hierarchical ORDERReindexing Clustered Primary Keydeteriorating stored procedure running timeswhere to add a column with low cardinality, decimal type into an index?Primary keys, clustered indexes and partitioningShould I remove this clustered index?Fact table Primary Key: Surrogate Key or multi-column Natural Key?Clustered Primary Key that is never used vs. Non-Clustered Primary Key on Multiple ColumnsError 666 on clustered primary key
I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this:
ALTER TABLE [dbo].[Distribution_Batch] ADD CONSTRAINT
[PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED
(
[BATCH_ID] ASC,
[DATE_CREATED] ASC,
[CONTACT_ID] ASC,
[LAB_CODE] ASC,
[DISTRIBUTION_TYPE] ASC,
[CREATOR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here is the table itself, these are the columns:
SELECT TOP (1000) [BATCH_ID] --- this is a varchar(100) field
,[LAB_CODE]
,[DISTRIBUTION_TYPE]
,[CONTACT_ID]
,[CREATOR]
,[DESCRIPTION]
,[DATE_CREATED]
,[DATE_COMPLETED]
,[DATE_DEADLINE]
,[DATE_CONFIRMED]
,[ERROR_CODE]
,[CONTENT]
,[ADDED_BY]
,[SOURCE]
,[STATUS]
,[DELIVERY_METHOD]
,[FILE_SIZE]
,[DISTRIBUTION_FORMAT]
,[CONTACT_ID_ORIGINAL]
FROM [dbo].[Distribution_Batch]
All this table does is queue up distribution jobs to be run by our application. Nothing fancy.
They have indexes on numerous columns:
[DistributionBatch-ContactId-DistributionType-20161108-121538]
[idx_date_completed]
[idx_date_created]
[idx_DistribBatch_BatchId]
[idx_Distribution_Contacts]
[IX_Distribution_Batch]
[IX_Distribution_Batch_LAB_CODE_DISTRIBUTION_TYPE_STATUS_DATE_COMPLETED]
[IX_Distribution_Batch_LAB_CODE_STATUS]
[IX_Distribution_Batch_STATUS]
[IX_Distribution_Batch_STATUS_DELIVERY_METHOD]
[PK_Distribution_Batch_1__23]
The system overall is slow and the admins response has always been to add more indexes to each table. This table spits out data sequentially based on whether it is completed or not. I can't understand why they would have so many indexes on columns that are never sorted on. Am I missing something?
My question is 2 parts:
Part 1) Does that primary key make any sense? Shouldn't the primary key just be an ID (int), starting at 1 with identity spec and auto incrementing?
Part 2) I need confirmation that none of those indexes make sense and are not necessary. There are a lot more tables that have this same issue.
This database has 190 GB of data and 101 GB of indexes.
All comments and opinions greatly appreciated.
sql-server t-sql
asked 9 mins ago
Michael FeverMichael Fever
1011
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this:
ALTER TABLE [dbo].[Distribution_Batch] ADD CONSTRAINT
[PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED
(
[BATCH_ID] ASC,
[DATE_CREATED] ASC,
[CONTACT_ID] ASC,
[LAB_CODE] ASC,
[DISTRIBUTION_TYPE] ASC,
[CREATOR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here is the table itself, these are the columns:
SELECT TOP (1000) [BATCH_ID] --- this is a varchar(100) field
,[LAB_CODE]
,[DISTRIBUTION_TYPE]
,[CONTACT_ID]
,[CREATOR]
,[DESCRIPTION]
,[DATE_CREATED]
,[DATE_COMPLETED]
,[DATE_DEADLINE]
,[DATE_CONFIRMED]
,[ERROR_CODE]
,[CONTENT]
,[ADDED_BY]
,[SOURCE]
,[STATUS]
,[DELIVERY_METHOD]
,[FILE_SIZE]
,[DISTRIBUTION_FORMAT]
,[CONTACT_ID_ORIGINAL]
FROM [dbo].[Distribution_Batch]
All this table does is queue up distribution jobs to be run by our application. Nothing fancy.
They have indexes on numerous columns:
[DistributionBatch-ContactId-DistributionType-20161108-121538]
[idx_date_completed]
[idx_date_created]
[idx_DistribBatch_BatchId]
[idx_Distribution_Contacts]
[IX_Distribution_Batch]
[IX_Distribution_Batch_LAB_CODE_DISTRIBUTION_TYPE_STATUS_DATE_COMPLETED]
[IX_Distribution_Batch_LAB_CODE_STATUS]
[IX_Distribution_Batch_STATUS]
[IX_Distribution_Batch_STATUS_DELIVERY_METHOD]
[PK_Distribution_Batch_1__23]
The system overall is slow and the admins response has always been to add more indexes to each table. This table spits out data sequentially based on whether it is completed or not. I can't understand why they would have so many indexes on columns that are never sorted on. Am I missing something?
My question is 2 parts:
Part 1) Does that primary key make any sense? Shouldn't the primary key just be an ID (int), starting at 1 with identity spec and auto incrementing?
Part 2) I need confirmation that none of those indexes make sense and are not necessary. There are a lot more tables that have this same issue.
This database has 190 GB of data and 101 GB of indexes.
All comments and opinions greatly appreciated.
sql-server t-sql
asked 9 mins ago
Michael FeverMichael Fever
1011
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this:
ALTER TABLE [dbo].[Distribution_Batch] ADD CONSTRAINT
[PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED
(
[BATCH_ID] ASC,
[DATE_CREATED] ASC,
[CONTACT_ID] ASC,
[LAB_CODE] ASC,
[DISTRIBUTION_TYPE] ASC,
[CREATOR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here is the table itself, these are the columns:
SELECT TOP (1000) [BATCH_ID] --- this is a varchar(100) field
,[LAB_CODE]
,[DISTRIBUTION_TYPE]
,[CONTACT_ID]
,[CREATOR]
,[DESCRIPTION]
,[DATE_CREATED]
,[DATE_COMPLETED]
,[DATE_DEADLINE]
,[DATE_CONFIRMED]
,[ERROR_CODE]
,[CONTENT]
,[ADDED_BY]
,[SOURCE]
,[STATUS]
,[DELIVERY_METHOD]
,[FILE_SIZE]
,[DISTRIBUTION_FORMAT]
,[CONTACT_ID_ORIGINAL]
FROM [dbo].[Distribution_Batch]
All this table does is queue up distribution jobs to be run by our application. Nothing fancy.
They have indexes on numerous columns:
[DistributionBatch-ContactId-DistributionType-20161108-121538]
[idx_date_completed]
[idx_date_created]
[idx_DistribBatch_BatchId]
[idx_Distribution_Contacts]
[IX_Distribution_Batch]
[IX_Distribution_Batch_LAB_CODE_DISTRIBUTION_TYPE_STATUS_DATE_COMPLETED]
[IX_Distribution_Batch_LAB_CODE_STATUS]
[IX_Distribution_Batch_STATUS]
[IX_Distribution_Batch_STATUS_DELIVERY_METHOD]
[PK_Distribution_Batch_1__23]
The system overall is slow and the admins response has always been to add more indexes to each table. This table spits out data sequentially based on whether it is completed or not. I can't understand why they would have so many indexes on columns that are never sorted on. Am I missing something?
My question is 2 parts:
Part 1) Does that primary key make any sense? Shouldn't the primary key just be an ID (int), starting at 1 with identity spec and auto incrementing?
Part 2) I need confirmation that none of those indexes make sense and are not necessary. There are a lot more tables that have this same issue.
This database has 190 GB of data and 101 GB of indexes.
All comments and opinions greatly appreciated.
sql-server t-sql
asked 9 mins ago
Michael FeverMichael Fever
1011
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this:
ALTER TABLE [dbo].[Distribution_Batch] ADD CONSTRAINT
[PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED
(
[BATCH_ID] ASC,
[DATE_CREATED] ASC,
[CONTACT_ID] ASC,
[LAB_CODE] ASC,
[DISTRIBUTION_TYPE] ASC,
[CREATOR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here is the table itself, these are the columns:
SELECT TOP (1000) [BATCH_ID] --- this is a varchar(100) field
,[LAB_CODE]
,[DISTRIBUTION_TYPE]
,[CONTACT_ID]
,[CREATOR]
,[DESCRIPTION]
,[DATE_CREATED]
,[DATE_COMPLETED]
,[DATE_DEADLINE]
,[DATE_CONFIRMED]
,[ERROR_CODE]
,[CONTENT]
,[ADDED_BY]
,[SOURCE]
,[STATUS]
,[DELIVERY_METHOD]
,[FILE_SIZE]
,[DISTRIBUTION_FORMAT]
,[CONTACT_ID_ORIGINAL]
FROM [dbo].[Distribution_Batch]
All this table does is queue up distribution jobs to be run by our application. Nothing fancy.
They have indexes on numerous columns:
[DistributionBatch-ContactId-DistributionType-20161108-121538]
[idx_date_completed]
[idx_date_created]
[idx_DistribBatch_BatchId]
[idx_Distribution_Contacts]
[IX_Distribution_Batch]
[IX_Distribution_Batch_LAB_CODE_DISTRIBUTION_TYPE_STATUS_DATE_COMPLETED]
[IX_Distribution_Batch_LAB_CODE_STATUS]
[IX_Distribution_Batch_STATUS]
[IX_Distribution_Batch_STATUS_DELIVERY_METHOD]
[PK_Distribution_Batch_1__23]
The system overall is slow and the admins response has always been to add more indexes to each table. This table spits out data sequentially based on whether it is completed or not. I can't understand why they would have so many indexes on columns that are never sorted on. Am I missing something?
My question is 2 parts:
Part 1) Does that primary key make any sense? Shouldn't the primary key just be an ID (int), starting at 1 with identity spec and auto incrementing?
Part 2) I need confirmation that none of those indexes make sense and are not necessary. There are a lot more tables that have this same issue.
This database has 190 GB of data and 101 GB of indexes.
All comments and opinions greatly appreciated.
sql-server t-sql
sql-server t-sql
asked 9 mins ago
Michael FeverMichael Fever
1011
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 9 mins ago
Michael FeverMichael Fever
1011
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 9 mins ago
Michael FeverMichael Fever
1011
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 9 mins ago
Michael FeverMichael Fever
1011
asked 9 mins ago
Michael FeverMichael Fever
1011
1011
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Michael Fever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Michael Fever is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232885%2fmssql-primary-key-clustered-specifying-multiple-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Michael Fever is a new contributor. Be nice, and check out our Code of Conduct.
Michael Fever is a new contributor. Be nice, and check out our Code of Conduct.
Michael Fever is a new contributor. Be nice, and check out our Code of Conduct.
Michael Fever is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232885%2fmssql-primary-key-clustered-specifying-multiple-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
