Aggressive Under-Indexing and no data for missing indexWhy doesn't SQL Server have any missing index requests in the DMVs or Query Plans?Query Plan showing missing index warning on trivial statementsWhy are certain objects loaded into Buffer?How to investigate “aggressive indexes”Lots of short term blockings on SQL Server 2008-R2 databaseHow to find the query that is still holding a lock?Database BlockingExample of Tree Indexing StructuresSQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndexOracle Database troubleshooting enq: TX - row lock contentionSuggestion on Missing Index Creation
Mathematica command that allows it to read my intentions
Does the Idaho Potato Commission associate potato skins with healthy eating?
Is it a bad idea to plug the other end of ESD strap to wall ground?
ssTTsSTtRrriinInnnnNNNIiinngg
Can compressed videos be decoded back to their uncompresed original format?
Notepad++ delete until colon for every line with replace all
OP Amp not amplifying audio signal
What are the G forces leaving Earth orbit?
Is it "common practice in Fourier transform spectroscopy to multiply the measured interferogram by an apodizing function"? If so, why?
How to show a landlord what we have in savings?
In Bayesian inference, why are some terms dropped from the posterior predictive?
Rotate ASCII Art by 45 Degrees
Implication of namely
Why were 5.25" floppy drives cheaper than 8"?
Getting extremely large arrows with tikzcd
Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?
How to stretch the corners of this image so that it looks like a perfect rectangle?
Why is the sentence "Das ist eine Nase" correct?
What is an equivalently powerful replacement spell for the Yuan-Ti's Suggestion spell?
How to coordinate airplane tickets?
Why are UK visa biometrics appointments suspended at USCIS Application Support Centers?
Did 'Cinema Songs' exist during Hiranyakshipu's time?
What reasons are there for a Capitalist to oppose a 100% inheritance tax?
My ex-girlfriend uses my Apple ID to log in to her iPad. Do I have to give her my Apple ID password to reset it?
Aggressive Under-Indexing and no data for missing index
Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?Query Plan showing missing index warning on trivial statementsWhy are certain objects loaded into Buffer?How to investigate “aggressive indexes”Lots of short term blockings on SQL Server 2008-R2 databaseHow to find the query that is still holding a lock?Database BlockingExample of Tree Indexing StructuresSQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndexOracle Database troubleshooting enq: TX - row lock contentionSuggestion on Missing Index Creation
I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every now and then, we get issue of blocking and this blocking gets so severe and their design is so poor that the whole portal goes down unless and until I kill few SPIDs which is holding exclusive lock(mostly).
I have been using sp_blitzindex from almost an year now and a big fan of First Responder Kit provided by Mr. Brent Ozar and Team. When I execute sp_blitzindex against this database where blocking takes place, it says - "Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits" with the priority of 10. I checked the URL column and also checked other related pages however couldn't get much assistance.
I know that the tables listed here are under indexed and some more indexes need to be created however when I run the same procedure i.e. sp_blitzindex for these objects individually at table level by using below command:
EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';
I don't get any missing index details at all. All the existing indexes are utilized and read count is lesser than write count, only issue which is highlighted here are in the "Lock Waits" column for Primary key.
I have no clue on which column index needs to be created. I also checked sp_blitzlock to see if I could gather some more info which would help however all I see there is lot of deadlocks and same objects are listed which is figured in aggressive under-indexing.
Also checked output of sp_blitzcache by passing sortorder as "reads" and "duration" in this particular database however no missing index request. There are warnings saying "Unparameterized Query" and "Unparameterized Query, non-SARGables" and these plans involve different set of tables.
Any help is highly appreciated.
Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index blocking sp-blitzindex
add a comment |
I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every now and then, we get issue of blocking and this blocking gets so severe and their design is so poor that the whole portal goes down unless and until I kill few SPIDs which is holding exclusive lock(mostly).
I have been using sp_blitzindex from almost an year now and a big fan of First Responder Kit provided by Mr. Brent Ozar and Team. When I execute sp_blitzindex against this database where blocking takes place, it says - "Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits" with the priority of 10. I checked the URL column and also checked other related pages however couldn't get much assistance.
I know that the tables listed here are under indexed and some more indexes need to be created however when I run the same procedure i.e. sp_blitzindex for these objects individually at table level by using below command:
EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';
I don't get any missing index details at all. All the existing indexes are utilized and read count is lesser than write count, only issue which is highlighted here are in the "Lock Waits" column for Primary key.
I have no clue on which column index needs to be created. I also checked sp_blitzlock to see if I could gather some more info which would help however all I see there is lot of deadlocks and same objects are listed which is figured in aggressive under-indexing.
Also checked output of sp_blitzcache by passing sortorder as "reads" and "duration" in this particular database however no missing index request. There are warnings saying "Unparameterized Query" and "Unparameterized Query, non-SARGables" and these plans involve different set of tables.
Any help is highly appreciated.
Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index blocking sp-blitzindex
add a comment |
I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every now and then, we get issue of blocking and this blocking gets so severe and their design is so poor that the whole portal goes down unless and until I kill few SPIDs which is holding exclusive lock(mostly).
I have been using sp_blitzindex from almost an year now and a big fan of First Responder Kit provided by Mr. Brent Ozar and Team. When I execute sp_blitzindex against this database where blocking takes place, it says - "Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits" with the priority of 10. I checked the URL column and also checked other related pages however couldn't get much assistance.
I know that the tables listed here are under indexed and some more indexes need to be created however when I run the same procedure i.e. sp_blitzindex for these objects individually at table level by using below command:
EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';
I don't get any missing index details at all. All the existing indexes are utilized and read count is lesser than write count, only issue which is highlighted here are in the "Lock Waits" column for Primary key.
I have no clue on which column index needs to be created. I also checked sp_blitzlock to see if I could gather some more info which would help however all I see there is lot of deadlocks and same objects are listed which is figured in aggressive under-indexing.
Also checked output of sp_blitzcache by passing sortorder as "reads" and "duration" in this particular database however no missing index request. There are warnings saying "Unparameterized Query" and "Unparameterized Query, non-SARGables" and these plans involve different set of tables.
Any help is highly appreciated.
Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index blocking sp-blitzindex
I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every now and then, we get issue of blocking and this blocking gets so severe and their design is so poor that the whole portal goes down unless and until I kill few SPIDs which is holding exclusive lock(mostly).
I have been using sp_blitzindex from almost an year now and a big fan of First Responder Kit provided by Mr. Brent Ozar and Team. When I execute sp_blitzindex against this database where blocking takes place, it says - "Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits" with the priority of 10. I checked the URL column and also checked other related pages however couldn't get much assistance.
I know that the tables listed here are under indexed and some more indexes need to be created however when I run the same procedure i.e. sp_blitzindex for these objects individually at table level by using below command:
EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';
I don't get any missing index details at all. All the existing indexes are utilized and read count is lesser than write count, only issue which is highlighted here are in the "Lock Waits" column for Primary key.
I have no clue on which column index needs to be created. I also checked sp_blitzlock to see if I could gather some more info which would help however all I see there is lot of deadlocks and same objects are listed which is figured in aggressive under-indexing.
Also checked output of sp_blitzcache by passing sortorder as "reads" and "duration" in this particular database however no missing index request. There are warnings saying "Unparameterized Query" and "Unparameterized Query, non-SARGables" and these plans involve different set of tables.
Any help is highly appreciated.
Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index blocking sp-blitzindex
sql-server index blocking sp-blitzindex
edited 2 days ago
Learning_DBAdmin
asked 2 days ago
Learning_DBAdminLearning_DBAdmin
441214
441214
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Free Unit
Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.
No Missing Indexes?
Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?
First, Wait
Use sp_BlitzFirst @SinceStartup = 1;
, and check out your wait stats.
If LCK_
waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait
column isn't showing values over 1-2 seconds for the LCK_
waits, the situation might not be so dire. I'd probably switch my focus elsewhere.
Modifiers
Rather than using sp_BlitzCache
to look at Reads or Duration, use it to look at these:
EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';
You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.
The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.
Since you're running sp_BlitzCache
, pay attention to the line in the Warnings rollup (second output);
Common Problems
Long locks can come from places that end users may not notice, like index maintenance.
If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).
Look for code patterns that use BEGIN TRAN
, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.
Look in sp_BlitzIndex
for foreign keys with cascading actions. These can cause a dramatic amount of locking.
Hope this helps!
@Learning_DBAdmin I'm not trying to be dismissive, but this really isn't a good use of site comments. If you have a new question, please ask it as one.
– Erik Darling
yesterday
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%2f233601%2faggressive-under-indexing-and-no-data-for-missing-index%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
Free Unit
Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.
No Missing Indexes?
Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?
First, Wait
Use sp_BlitzFirst @SinceStartup = 1;
, and check out your wait stats.
If LCK_
waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait
column isn't showing values over 1-2 seconds for the LCK_
waits, the situation might not be so dire. I'd probably switch my focus elsewhere.
Modifiers
Rather than using sp_BlitzCache
to look at Reads or Duration, use it to look at these:
EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';
You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.
The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.
Since you're running sp_BlitzCache
, pay attention to the line in the Warnings rollup (second output);
Common Problems
Long locks can come from places that end users may not notice, like index maintenance.
If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).
Look for code patterns that use BEGIN TRAN
, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.
Look in sp_BlitzIndex
for foreign keys with cascading actions. These can cause a dramatic amount of locking.
Hope this helps!
@Learning_DBAdmin I'm not trying to be dismissive, but this really isn't a good use of site comments. If you have a new question, please ask it as one.
– Erik Darling
yesterday
add a comment |
Free Unit
Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.
No Missing Indexes?
Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?
First, Wait
Use sp_BlitzFirst @SinceStartup = 1;
, and check out your wait stats.
If LCK_
waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait
column isn't showing values over 1-2 seconds for the LCK_
waits, the situation might not be so dire. I'd probably switch my focus elsewhere.
Modifiers
Rather than using sp_BlitzCache
to look at Reads or Duration, use it to look at these:
EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';
You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.
The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.
Since you're running sp_BlitzCache
, pay attention to the line in the Warnings rollup (second output);
Common Problems
Long locks can come from places that end users may not notice, like index maintenance.
If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).
Look for code patterns that use BEGIN TRAN
, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.
Look in sp_BlitzIndex
for foreign keys with cascading actions. These can cause a dramatic amount of locking.
Hope this helps!
@Learning_DBAdmin I'm not trying to be dismissive, but this really isn't a good use of site comments. If you have a new question, please ask it as one.
– Erik Darling
yesterday
add a comment |
Free Unit
Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.
No Missing Indexes?
Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?
First, Wait
Use sp_BlitzFirst @SinceStartup = 1;
, and check out your wait stats.
If LCK_
waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait
column isn't showing values over 1-2 seconds for the LCK_
waits, the situation might not be so dire. I'd probably switch my focus elsewhere.
Modifiers
Rather than using sp_BlitzCache
to look at Reads or Duration, use it to look at these:
EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';
You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.
The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.
Since you're running sp_BlitzCache
, pay attention to the line in the Warnings rollup (second output);
Common Problems
Long locks can come from places that end users may not notice, like index maintenance.
If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).
Look for code patterns that use BEGIN TRAN
, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.
Look in sp_BlitzIndex
for foreign keys with cascading actions. These can cause a dramatic amount of locking.
Hope this helps!
Free Unit
Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.
No Missing Indexes?
Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?
First, Wait
Use sp_BlitzFirst @SinceStartup = 1;
, and check out your wait stats.
If LCK_
waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait
column isn't showing values over 1-2 seconds for the LCK_
waits, the situation might not be so dire. I'd probably switch my focus elsewhere.
Modifiers
Rather than using sp_BlitzCache
to look at Reads or Duration, use it to look at these:
EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';
You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.
The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.
Since you're running sp_BlitzCache
, pay attention to the line in the Warnings rollup (second output);
Common Problems
Long locks can come from places that end users may not notice, like index maintenance.
If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).
Look for code patterns that use BEGIN TRAN
, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.
Look in sp_BlitzIndex
for foreign keys with cascading actions. These can cause a dramatic amount of locking.
Hope this helps!
answered 2 days ago
Erik DarlingErik Darling
22.6k1269112
22.6k1269112
@Learning_DBAdmin I'm not trying to be dismissive, but this really isn't a good use of site comments. If you have a new question, please ask it as one.
– Erik Darling
yesterday
add a comment |
@Learning_DBAdmin I'm not trying to be dismissive, but this really isn't a good use of site comments. If you have a new question, please ask it as one.
– Erik Darling
yesterday
@Learning_DBAdmin I'm not trying to be dismissive, but this really isn't a good use of site comments. If you have a new question, please ask it as one.
– Erik Darling
yesterday
@Learning_DBAdmin I'm not trying to be dismissive, but this really isn't a good use of site comments. If you have a new question, please ask it as one.
– Erik Darling
yesterday
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%2f233601%2faggressive-under-indexing-and-no-data-for-missing-index%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