August 16, 2021 at 9:22 pm
I have a simple proc that I suspect is causing intermittent blocking. It takes a customer ID (int) as a param and returns an output param FeatureFlag (bit) indicating whether a feature in enabled. The customer ID is the PK and clustered. The problem is that the proc gets called 100s of times a minute, during certain times of the day. My idea is to create a covering index on customer ID and FeatureFlag and specify that index with a table hint in the proc. There are lots of other procs that hit that table for other reasons (mostly reads) and I'm thinking that this would relieve the pressure on the clustered index and help with the blocking issue.
Am I crazy?
TIA
John Deupree
August 17, 2021 at 1:53 am
No.
If it's doing a lookup only (not modifying data, just reading it), you could also use WITH (NOLOCK) on the table to reduce overhead of the lookups. I would argue that you should, although some people reflexively attack any use of NOLOCK.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 17, 2021 at 7:04 am
I don't think that creating a covering index will help you. Since you are using the clustered primary key as your search argument, you are not doing any lookup. Are you sure that this procedure is causing the blocking? When you look at the procedure's query plan do you see that it does an index seek? If the answer for both questions is yes, you can consider using Scott Pletcher's advice and specify with (nolock) hint, but you have to understand that you'll might get a dirty read that was not committed to the database and on rare occasions you'll might not find a record that exists in the table.
Adi
August 17, 2021 at 12:09 pm
Is the query scanning the existing index or seeking against it? If it's a seek, and it's the cluster, I'm not entirely sure you'll see much of an improvement with a covering index. It's likely to behave exactly the same way... maybe. Testing is your buddy in these circumstances.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 17, 2021 at 12:19 pm
I have a simple proc that I suspect is causing intermittent blocking. It takes a customer ID (int) as a param and returns an output param FeatureFlag (bit) indicating whether a feature in enabled. The customer ID is the PK and clustered. The problem is that the proc gets called 100s of times a minute, during certain times of the day. My idea is to create a covering index on customer ID and FeatureFlag and specify that index with a table hint in the proc. There are lots of other procs that hit that table for other reasons (mostly reads) and I'm thinking that this would relieve the pressure on the clustered index and help with the blocking issue.
Am I crazy?
TIA
How have you proven that the proc has an issue? Have you looked at the actual execution plan?
And new nonclustered index may help in that it would be much more narrow than the clustered index and so could take a lot less memory and IO and maybe speed. The only way to know for sure is to first make sure you know how to measure it without changing it and then try it. Depending on the nature of the query, a filtered index may work even more of a treat.
But, again, we're only speculating. You're going to have to try things in-situ and under-fire to know for sure because, right now, I'm not sure that you actually have a problem to begin with and neither are you. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2021 at 12:21 pm
... maybe. Testing is your buddy in these circumstances.
Preach it Brother! One good test result is worth a thousand expert opinions.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2021 at 1:09 pm
Some monitoring softwares will show blocking in the engine and may get you on track detangling such issues.
If you think (b)locking is the issue, you may even want to try snapshot isolation if you do not want to experience the potential down sides of using nolock.
But then again, snapshot isolation may also need some attention in tempdb.
Check your alternatives and TEST IT !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 17, 2021 at 3:31 pm
Thanks for the replies. I don't want to use NOLOCK because I want to be sure that the feature is enabled before kicking off the job. A customer might have multiple jobs in a short time period and if one fails the flag gets set to disabled so that we don't have multiple failures stacking up.
And yes we do have output from WhoIsActive showing that this is the blocking code. the actual query plan (in dev) shows a clustered index seek.
Unfortunately this is not reproducible in our dev or QA environments (not enough volume and other activity), and testing is frowned upon in production :). I'll see about getting some way of reproducing the frequency and number of the calls to see if I can reproduce.
Thanks again.
John Deupree
August 18, 2021 at 5:38 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply