a work victory
I have consistant issues with blocking on my VLDB at work... enough blocking that it effects app performance on the user end.
Because of that I have a job that checks for blocking every couple of seconds and records all kinds of details about the blocking spids and the blocked spids for later analysis.
Last week I was able to find a single update statement that seemed to cause the vast majority of the blocking...
after some very detailed analysis and consulting with MS Business Solutions (the application authors) we were able to come up with the what we thought was the missing index... And it was a long shot since it was on a table not even related to the update statement.
Anyways, I created the index last night, and today I've had 87% fewer records written to my blocking info table....
meaning, an 87% decrease in blocking instances, meaning, some very very very happy users!
Additionally, my disk read time is down 6.5% on my data volume.
I rock!
Because of that I have a job that checks for blocking every couple of seconds and records all kinds of details about the blocking spids and the blocked spids for later analysis.
Last week I was able to find a single update statement that seemed to cause the vast majority of the blocking...
after some very detailed analysis and consulting with MS Business Solutions (the application authors) we were able to come up with the what we thought was the missing index... And it was a long shot since it was on a table not even related to the update statement.
Anyways, I created the index last night, and today I've had 87% fewer records written to my blocking info table....
meaning, an 87% decrease in blocking instances, meaning, some very very very happy users!
Additionally, my disk read time is down 6.5% on my data volume.
I rock!