Columnstore index and low selective columns
I have a table with almost 1T rows.
create table bigtable (
K1 int, K2 date, -- PK columns
C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
primary key (K1, K2)
)
I want to use columnstore to increase the query performance. There are the following options.
- Create a cluster columnstore index
- Create a noncluster columnstore index for all the columns
- Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?
The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?
sql-server sql-server-2014 columnstore
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a table with almost 1T rows.
create table bigtable (
K1 int, K2 date, -- PK columns
C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
primary key (K1, K2)
)
I want to use columnstore to increase the query performance. There are the following options.
- Create a cluster columnstore index
- Create a noncluster columnstore index for all the columns
- Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?
The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?
sql-server sql-server-2014 columnstore
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you using SQL Server 2014? If you are using 2014 your data will have to be static if you want to use Non clustered columnstore. Only the Clustered Columnstore index is updateable in SQL Server 2014
– Stijn Wynants
Jun 8 '16 at 7:51
Yes, I'm using 2014. I can drop/recreate the nonclustered columnindex periodically in my case because the data is not updated frequently.
– u23432534
Jun 8 '16 at 14:27
Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly.
– Geoff Patterson
Jun 9 '16 at 18:22
add a comment |
I have a table with almost 1T rows.
create table bigtable (
K1 int, K2 date, -- PK columns
C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
primary key (K1, K2)
)
I want to use columnstore to increase the query performance. There are the following options.
- Create a cluster columnstore index
- Create a noncluster columnstore index for all the columns
- Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?
The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?
sql-server sql-server-2014 columnstore
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
I have a table with almost 1T rows.
create table bigtable (
K1 int, K2 date, -- PK columns
C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
primary key (K1, K2)
)
I want to use columnstore to increase the query performance. There are the following options.
- Create a cluster columnstore index
- Create a noncluster columnstore index for all the columns
- Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?
The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?
sql-server sql-server-2014 columnstore
sql-server sql-server-2014 columnstore
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
edited Jun 16 '16 at 6:30
Paul White♦
50.2k14269435
50.2k14269435
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
asked Jun 8 '16 at 3:25
u23432534u23432534
72231229
72231229
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you using SQL Server 2014? If you are using 2014 your data will have to be static if you want to use Non clustered columnstore. Only the Clustered Columnstore index is updateable in SQL Server 2014
– Stijn Wynants
Jun 8 '16 at 7:51
Yes, I'm using 2014. I can drop/recreate the nonclustered columnindex periodically in my case because the data is not updated frequently.
– u23432534
Jun 8 '16 at 14:27
Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly.
– Geoff Patterson
Jun 9 '16 at 18:22
add a comment |
Are you using SQL Server 2014? If you are using 2014 your data will have to be static if you want to use Non clustered columnstore. Only the Clustered Columnstore index is updateable in SQL Server 2014
– Stijn Wynants
Jun 8 '16 at 7:51
Yes, I'm using 2014. I can drop/recreate the nonclustered columnindex periodically in my case because the data is not updated frequently.
– u23432534
Jun 8 '16 at 14:27
Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly.
– Geoff Patterson
Jun 9 '16 at 18:22
Are you using SQL Server 2014? If you are using 2014 your data will have to be static if you want to use Non clustered columnstore. Only the Clustered Columnstore index is updateable in SQL Server 2014
– Stijn Wynants
Jun 8 '16 at 7:51
Are you using SQL Server 2014? If you are using 2014 your data will have to be static if you want to use Non clustered columnstore. Only the Clustered Columnstore index is updateable in SQL Server 2014
– Stijn Wynants
Jun 8 '16 at 7:51
Yes, I'm using 2014. I can drop/recreate the nonclustered columnindex periodically in my case because the data is not updated frequently.
– u23432534
Jun 8 '16 at 14:27
Yes, I'm using 2014. I can drop/recreate the nonclustered columnindex periodically in my case because the data is not updated frequently.
– u23432534
Jun 8 '16 at 14:27
Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly.
– Geoff Patterson
Jun 9 '16 at 18:22
Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly.
– Geoff Patterson
Jun 9 '16 at 18:22
add a comment |
1 Answer
1
active
oldest
votes
One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).
So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
add a comment |
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
});
}
});
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%2f140681%2fcolumnstore-index-and-low-selective-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).
So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
add a comment |
One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).
So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
add a comment |
One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).
So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).
So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
answered Oct 31 '16 at 2:56
mendosimendosi
1,974520
1,974520
add a comment |
add a comment |
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%2f140681%2fcolumnstore-index-and-low-selective-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
Are you using SQL Server 2014? If you are using 2014 your data will have to be static if you want to use Non clustered columnstore. Only the Clustered Columnstore index is updateable in SQL Server 2014
– Stijn Wynants
Jun 8 '16 at 7:51
Yes, I'm using 2014. I can drop/recreate the nonclustered columnindex periodically in my case because the data is not updated frequently.
– u23432534
Jun 8 '16 at 14:27
Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly.
– Geoff Patterson
Jun 9 '16 at 18:22