How do I find a disabled index on SQL server 2008
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
A while back when I was performing some bulk inserts of data into my SQL Server database, I disabled a number of indexes to improve the insert performance. I now need to go back and rebuild/re-enable them.
Unfortunately, I'm not sure exactly which indexes I disabled.
Is there a way I can query to identify which indexes are disabled and should be re-enabled?
sql-server sql-server-2008 asked Aug 16 '10 at 17:04
starstar
213134
add a comment |
A while back when I was performing some bulk inserts of data into my SQL Server database, I disabled a number of indexes to improve the insert performance. I now need to go back and rebuild/re-enable them.
Unfortunately, I'm not sure exactly which indexes I disabled.
Is there a way I can query to identify which indexes are disabled and should be re-enabled?
sql-server sql-server-2008 asked Aug 16 '10 at 17:04
starstar
213134
5
Why is this not in the IDE!! How hard is it to gray something out?
– Simon_Weaver
Nov 16 '14 at 0:41
1
Here I am exactly four years to the day later with the same question :-/
– Simon_Weaver
Nov 17 '18 at 6:29
add a comment |
A while back when I was performing some bulk inserts of data into my SQL Server database, I disabled a number of indexes to improve the insert performance. I now need to go back and rebuild/re-enable them.
Unfortunately, I'm not sure exactly which indexes I disabled.
Is there a way I can query to identify which indexes are disabled and should be re-enabled?
sql-server sql-server-2008 asked Aug 16 '10 at 17:04
starstar
213134
A while back when I was performing some bulk inserts of data into my SQL Server database, I disabled a number of indexes to improve the insert performance. I now need to go back and rebuild/re-enable them.
Unfortunately, I'm not sure exactly which indexes I disabled.
Is there a way I can query to identify which indexes are disabled and should be re-enabled?
sql-server sql-server-2008 sql-server sql-server-2008 asked Aug 16 '10 at 17:04
starstar
213134
asked Aug 16 '10 at 17:04
starstar
213134
asked Aug 16 '10 at 17:04
starstar
213134
asked Aug 16 '10 at 17:04
starstar
213134
asked Aug 16 '10 at 17:04
starstar
213134
213134
5
Why is this not in the IDE!! How hard is it to gray something out?
– Simon_Weaver
Nov 16 '14 at 0:41
1
Here I am exactly four years to the day later with the same question :-/
– Simon_Weaver
Nov 17 '18 at 6:29
add a comment |
5
Why is this not in the IDE!! How hard is it to gray something out?
– Simon_Weaver
Nov 16 '14 at 0:41
1
Here I am exactly four years to the day later with the same question :-/
– Simon_Weaver
Nov 17 '18 at 6:29
5
5
Why is this not in the IDE!! How hard is it to gray something out?
– Simon_Weaver
Nov 16 '14 at 0:41
Why is this not in the IDE!! How hard is it to gray something out?
– Simon_Weaver
Nov 16 '14 at 0:41
1
1
Here I am exactly four years to the day later with the same question :-/
– Simon_Weaver
Nov 17 '18 at 6:29
Here I am exactly four years to the day later with the same question :-/
– Simon_Weaver
Nov 17 '18 at 6:29
add a comment |
1 Answer
1
active
oldest
votes
select
sys.objects.name as table_name,
sys.indexes.name as index_name
from sys.indexes
inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
sys.objects.name,
sys.indexes.name
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f3495446%2fhow-do-i-find-a-disabled-index-on-sql-server-2008%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
select
sys.objects.name as table_name,
sys.indexes.name as index_name
from sys.indexes
inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
sys.objects.name,
sys.indexes.name
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
add a comment |
select
sys.objects.name as table_name,
sys.indexes.name as index_name
from sys.indexes
inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
sys.objects.name,
sys.indexes.name
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
add a comment |
select
sys.objects.name as table_name,
sys.indexes.name as index_name
from sys.indexes
inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
sys.objects.name,
sys.indexes.name
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
select
sys.objects.name as table_name,
sys.indexes.name as index_name
from sys.indexes
inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
sys.objects.name,
sys.indexes.name
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
edited Nov 17 '18 at 6:32
Simon_Weaver
74.8k63461533
74.8k63461533
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
answered Aug 16 '10 at 17:07
Daniel RenshawDaniel Renshaw
28.6k66587
28.6k66587
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f3495446%2fhow-do-i-find-a-disabled-index-on-sql-server-2008%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
5
Why is this not in the IDE!! How hard is it to gray something out?
– Simon_Weaver
Nov 16 '14 at 0:41
1
Here I am exactly four years to the day later with the same question :-/
– Simon_Weaver
Nov 17 '18 at 6:29