- \n
- Details<\/li>\n\n\n\n
- Indexes Present\n
- \n
- Dbo.Harvest Primary Key<\/li>\n\n\n\n
- All Indexes in DB<\/li>\n<\/ol>\n<\/li>\n\n\n\n
- How to Trace It<\/li>\n<\/ol>\n\n\n\n
____________________________________________________________<\/p>\n\n\n\n
0__Details<\/strong><\/p>\n\n\n\n
Just Confirming I was in the Right Place,<\/p>\n\n\n\n
Was Looking @<\/p>\n\n\n\n
- \n
- DB\n
- \n
- Dbo.Harvest<\/li>\n\n\n\n
- Dbo.Setting<\/li>\n\n\n\n
- Dbo.SettingSnapshot<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
Files: Anything I link or show below can be found here<\/p>\n\n\n\n
\u00a0xx.0 – GMDB Database Locks in Settings Table<\/p>\n\n\n\n
____________________________________________________________<\/p>\n\n\n\n
1_Indexes Present<\/strong><\/p>\n\n\n\n
- \n
- Indexes = Dbo.Harvest\n
- \n
- \u2018cnstHarvest\u2019\n
- \n
- This is one due to the Primary Key on the table, and is a non-locking index and *should<\/strong>* not be causing the issue<\/li>\n\n\n\n
- Removing this index will remove the ability to ensure the Primary Key is Unique<\/li>\n\n\n\n
- Confirmed via a re-creation: \u00a0Index Creation _ Harvest _ Private Key.sql\n
- \n
- Suggestion:<\/strong> we leave it there, the \u2018risk vs reward\u2019 gut check isn\u2019t in our favour.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- \n
- Indexes = Entire Database\n
- \n
- I have extracted all of the potential locking indexes via the below script, results attached:\n
- \n
- Script: \u00a0Grab All Indexes _ Non Key.sql<\/li>\n\n\n\n
- Results: \u00a0All Indexes in DB_MG.xlsx<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- \n
- Did a second pass, to ensure there wasn\u2019t anything hiding:\n
- \n
- Script: \u00a0Return Index Info for Table.sql<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- \n
- To be honest, I found little of interest in the above w\/ our current issues<\/li>\n<\/ul>\n\n\n\n
____________________________________________________________<\/p>\n\n\n\n
2_Trace It Manually<\/strong><\/p>\n\n\n\n
See below to set the mood.<\/p>\n\n\n\n
![\"\"](\"https:\/\/blog.lapthorne.com.au\/wp-content\/uploads\/2025\/09\/image-4.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
- \n
- Best way to handle this is to run a SQL Activity trace, but it needs both of us online and with time to kill.\n
- \n
- I can also lend Shah to the process if I am not around.\n
- \n
- Some of the below is for his benefit, just in case.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- \n
- I have installed a couple toys to help,\n
- \n
- Sp_BlitzLock<\/strong>\n
- \n
- Handy proc for diagnosing active locks, is now in Master of SQL1 (and SQL0)<\/li>\n\n\n\n
- Simple as\n
- \n
- Execute sp_BlitzLock on DB;<\/li>\n\n\n\n
- If there was anything to show:<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
![\"\"](\"https:\/\/blog.lapthorne.com.au\/wp-content\/uploads\/2025\/09\/image.png\")
<\/figure>\n\n\n\n- \n
- SQL Activity Monitor & SSIS Automation Suggestions<\/strong>\n
- \n
- Helpful in the moment, but useless after its already happened.\n
- \n
- Using \u2018Recent Expensive Queries\u2019 records, you should be able to ID any queries clashing and generating locks and \u2018lock_wait\u2019 or \u2018deadlock\u2019 status flags.<\/li>\n\n\n\n
- By right clicking on expensive queries that affect those locks, you can select \u201cShow Execution Plan\u201d\n
- \n
- This delivers the below screenshot, where it shows you where the wait and lag are of the execution,<\/li>\n\n\n\n
- I regularly use this to ID a Stat or Index that is making the process slow and removing \/ updating it.
See below, it is showing the table scan is taking 45% of the effort (by design with an index, no fix needed)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n![\"\"](\"https:\/\/blog.lapthorne.com.au\/wp-content\/uploads\/2025\/09\/image-1.png\")
<\/figure>\n\n\n\n- \n
- SQL Profiler<\/strong>\n
- \n
- Full Admin Access Req<\/li>\n\n\n\n
- Will Run a Profiler against active connections as they run,\n
- \n
- Will Slow the DB and Server, down while the trace is running, so we cant leave it running for ages.\n
- \n
- Example of what I would usually focus on
<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n![\"\"](\"https:\/\/blog.lapthorne.com.au\/wp-content\/uploads\/2025\/09\/image-2.png\")
<\/figure>\n\n\n\n- \n
- What the Output looks like:<\/li>\n<\/ul>\n\n\n\n
![\"\"](\"https:\/\/blog.lapthorne.com.au\/wp-content\/uploads\/2025\/09\/image-3.png\")
<\/figure>\n","protected":false},"excerpt":{"rendered":"____________________________________________________________ Table of Contents ____________________________________________________________ 0__Details Just Confirming I was in the Right Place, Was Looking @ Files: Anything I link or show below can be found here \u00a0xx.0 – GMDB Database Locks in Settings Table ____________________________________________________________ 1_Indexes Present ____________________________________________________________ 2_Trace It Manually See below to set the mood.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-115","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=\/wp\/v2\/posts\/115"}],"collection":[{"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=115"}],"version-history":[{"count":1,"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=\/wp\/v2\/posts\/115\/revisions"}],"predecessor-version":[{"id":121,"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=\/wp\/v2\/posts\/115\/revisions\/121"}],"wp:attachment":[{"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=115"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=115"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.lapthorne.com.au\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=115"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- What the Output looks like:<\/li>\n<\/ul>\n\n\n\n
- Example of what I would usually focus on
- Will Slow the DB and Server, down while the trace is running, so we cant leave it running for ages.\n
- SQL Profiler<\/strong>\n
- Helpful in the moment, but useless after its already happened.\n
- SQL Activity Monitor & SSIS Automation Suggestions<\/strong>\n
- Sp_BlitzLock<\/strong>\n
- I have installed a couple toys to help,\n
- Some of the below is for his benefit, just in case.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- I can also lend Shah to the process if I am not around.\n
- Best way to handle this is to run a SQL Activity trace, but it needs both of us online and with time to kill.\n
- To be honest, I found little of interest in the above w\/ our current issues<\/li>\n<\/ul>\n\n\n\n
- Script: \u00a0Return Index Info for Table.sql<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- Did a second pass, to ensure there wasn\u2019t anything hiding:\n
- I have extracted all of the potential locking indexes via the below script, results attached:\n
- Indexes = Entire Database\n
- This is one due to the Primary Key on the table, and is a non-locking index and *should<\/strong>* not be causing the issue<\/li>\n\n\n\n
- \u2018cnstHarvest\u2019\n
- Indexes = Dbo.Harvest\n
- DB\n