February 2, 2011 at 8:50 am
I read somewhere thats its possible to specify in an SP which index to use (not sure of the terminology sorry).
I am seeing high number of KEY LOCKS.
Was wondering
TABLE A
has INDEX A
and INDEX B
SP1 used index A
SP2 used index B
Would that in anyway reduce the KEY LOCKS ?
The overhead of having an additional index i guess is another place to INSERT / UPDATE when a row is added to the table.
Thank you
Scott
February 2, 2011 at 9:03 am
It is possible.
The question is, do you know better than the query optimiser for every and all possible parameter values what index is best?
Have you exhausted every other possible fix?
Have you tested whether another index will have an adverse effect on updates?
Hinting indexes most commonly degrades performance because people hint indexes that they think are appropriate but actually aren't.
If you want some advice on reducing locking, improving performance, please post actual table definitions, index definitions and the execution plans (saved as .sqlplan files)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 9:35 am
Thank you again for the reply.
1)"The question is, do you know better than the query optimiser for every and all possible parameter values what index is best?"
The answer is most definitely no 🙂
2)"Have you exhausted every other possible fix?"
Finding it difficult to locate and understand other fixes.
3)"Have you tested whether another index will have an adverse effect on updates?"
Lacking adequate testing environment to simulate the same load, plans a foot to install a framework but recreating a load is tricky.
4)
table definitions (using script as ?)
index definitions (using script as ?)
execution plans (how ?)
Thanks again for your time
Scott
February 2, 2011 at 9:55 am
Just quick point, my issue is to do with INSERT/UPDATE more than SELECT lock, wont adding an additional index simply mean the INSERT/UPDATE will take longer as there is one more index to populate ?
The data is currently located using the PRIMARY KEY prior to update , dont see how adding an index will help that.
Will post the definitions and update SP which i think are causing the issue.
February 2, 2011 at 9:55 am
See http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
For table and indexes, just the create scripts. If you have management studio, right click the table/index script-> Create to -> clipboard then paste into reply.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 9:59 am
scott_lotus (2/2/2011)
Just quick point, my issue is to do with INSERT/UPDATE more than SELECT lock, wont adding an additional index simply mean the INSERT/UPDATE will take longer as there is one more index to populate ?
Yes, but the question is 'will it be significant' and 'will it outweigh the good the index will do' Please don't ignore the possibility of a good index because you fear the insert overhead. A small number of good indexes will do far more good than harm
That's why testing these is essential
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2011 at 2:38 am
Hi Gail,
Was in the process of creating the query plan to post , as part of this i had to run the query past the optimiser. As part of this i created an index on the table in question , the KEYLOCKS disappeared after apply it , unbelievable performance enhancement.
Turns out there was only the clustered index on table. Adding a non-culster PK index solved the issue.
I will post tables and execution plans next time i have an issue but will try the optimiser first.
Thank you again for your help, im starting too (slowly) understand the SQL architecture and my monitoring tool a little better.
Scott
February 3, 2011 at 3:05 am
Optimiser? Database tuning adviser?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2011 at 3:28 am
Yes sorry , the Database Engine Tuning Advisor. The barrier to using it for me has always been the need to amend the SP to DECLARE the parameter and SET the values , can be a bit daunting at first. However will not shy away from it again as the first place to solve problems. Just starting to get my head around some of the concepts in Quest Spotlight too to help highlight the cause of these issues in the first place.
- Lock type waits have disappeared to almost nothing. RID (row waits i believe) are the most prominent now at 0 - 9 secs but performance ok.
- I'm still seeing 1000 - 6000 ms wait times on latches but performance ok. Not sure how to investigate these yet.
- Biggest performance issue now 700 + connections to SQL server per second but this is being dealt with by the dev team using a new service.
All in all looking a million times better thank you 🙂
February 3, 2011 at 3:54 am
Seriously cannot believe the difference that one index made.
-The table contained 20 columns (int, bi value, decimals, datetime) and only 30,000 rows.
-PK clustered index only.
-The INSERT SP examined this table to find deviceID.
-The DB Tunning Advisor suggested adding an index with deviceid.
-SP uses this new index and locks disappeared.
What happened ? the speed of lookup on the new index with so fast that the KEYLOCKS were no longer an issue ?
February 3, 2011 at 4:11 am
Be careful of DTA. It over-recommends, it's not always right. Test any recommendation and don't hesitate to ignore any that don't help.
If you have RID appearing anywhere it means that the table doesn't have a clustered index.
Debugging latch waits depends on what the are. Are they LATCH_**, PAGELATCH_** or PAGEIOLATCH_**?
What happened ? the speed of lookup on the new index with so fast that the KEYLOCKS were no longer an issue ?
Pretty much.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2011 at 8:18 am
GilaMonster (2/3/2011)
Debugging latch waits depends on what the are. Are they LATCH_**, PAGELATCH_** or PAGEIOLATCH_**?
LATCH_EX appears to be the one. Looking at the SP involved (NOLOCK) was added to it sometime ago.
RID appears to be a STATS table with no clustered index, simply counts hits from a service.
February 3, 2011 at 8:22 am
Latches are not locks. Nolock (which quite honestly you shouldn't be using unless you're happy with the side effects) stops shared locks, nothing else.
Latch_ex is not even related to tables or pages. It's used to synchronise access to shared memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply