Table hint on UPDATE to optimize query 1

[TheBugSlayer]

Happy New Year all.

I have a few queries in the form

update accession
set clientkey=c.id
from accession a
join client c WITH (NOLOCK)
on a.facilityid=c.facilityid
and a.clientid=c.clientid 
where a.clientkey is null

in a stored procedures. Very often the execution of this SP, which runs every ten minutes, creates deadlock with either the SP or the other programs being chosen as the victim. These tables are heavily used by the most important applications. The SP is the only one that updates the field it does. I with to be able to read the table rows, even if they are being locked by other applications, and I would like to allow the other applications to read the rows that this SP is updating.
I have modified the query by adding hints like this:

update accession WITH (READPAST)
	set clientkey=c.id
	from accession a
	join client c WITH (NOLOCK)
		on a.facilityid=c.facilityid
		and a.clientid=c.clientid
	where a.clientkey is null

My intention is to read all records that I need to modify, even is at the time they were locked by another transaction (that may complete by the time mine is ready to update them), and since I am not modifying the joining table and no other process will modify the same field as the SP, AND to select all rows that I need from the joining table.

Do you know a better way to achieve what I want?

Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Curious... how long does it take for the update query to run?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Looking at the job history, the whole SP takes an average of 30 seconds to run. It goes up to 2 minutes when it' s failed. It has run only one time since I made the change and it took 29 seconds...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Have you looked at the execution plan for the update query. Most people only look at execution plans for selects. Anyway, since it is slow, I suspect you are getting index scans instead of seeks. If this is the case, then I suggest you think about adding an index.

Since you are running this query every 10 minutes, I assume it's not updating too many rows. I would suggest you check the indexes and then consider putting this code in to a trigger so that it updates even less rows each time.

Another possibility is page splits. Do you know what the fill factors are for any indexes that include the column that is getting updated.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Hi George.

The execution plans are very similar. The costliest operation is an update on Accession table' s non-clustered index update (ClientKey + FacilityID + ClientID) at 63%. This is the same for both execution plans. Also there is a 4% index seek on the same index, and a 7% index scan on Client' s PK index, in both plans. The SP has not deadlocked since I made the change, but it' s too early for it to be conclusive.

I do like the idea of an INSERT trigger to update the keys, since at the time the rows are being created...well no other process is using them. Only problem I have to create a trigger on every table updated by the SP; there are 13 updates in the same SP, more than once on some tables. So in terms of maintenance one will have to be aware of all places to look as opposed to the one-stop SP...Will keep watching the job to see...

Thanks for your input.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

The costliest operation is an update on Accession table' s non-clustered index update (ClientKey + FacilityID + ClientID) at 63%.

Do you know what the fill factor is for this index?

Look at it this way, indexes are sorted by the keys. In this case, it's sorted first by the ClientKey, then FacilityId, and Finally by ClientId. Since you are updating the ClientKey which is the first column listed in the index, you are guaranteed to be modifying the index a lot. Specifically, since data is stored in 8K pages, the data for this row in the index is going to be removed from one page and added to another page. If that other data page doesn't have room for the data (because the 8K of data space is full), then SQL Server will do a page split. This is relatively costly. Bottom line, if the fill factor for this index is either 0 or 100 percent (which means the same thing), then you are pretty much guaranteed to have lots of page splits. I would suggest you set the fill factor for this index to something lower than 100% (like 90% or 80%). This will require more storage space, but you should get better performance. Then, make sure you rebuild this index on a regular basis. The frequency at which you build the index depends on many factors like fragmentation and such. I would suggest that you rebuild the index nightly during a slow period.

As far as the triggers are concerned... you could implement a few triggers that update the table without necessarily worrying about all occurances of an update. In this situation, you would want to continue using your "every 10 minute" execution in case you missed any triggers. When the SP runs, if there are less rows to update, it will do it faster. The quicker the code runs, the less problems you will have with deadlocks.

Which brings me to my main point. By speeding up the code, the frequency in which you have problems will be less, which is certainly a good thing.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Hi George.

I had forgotten to mention that Fullness = 3.3% (fill factor is 3%, strange value, isn' t it?). Total fragmentation is 0.05%. I have a nightly maintenance plan that rebuilds or reorganizes indexes based on fragmentation.
I undestand the page split process, but 3% is kind of weird...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

3% is very weird. If I had to guess, I would say that someone meant to use 30% but fat fingered it at 3. If you can, I would encourage you to change it. If you can't, then bring it to the attention of someone who can.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

I can and will. Maybe 70%. Thanks much George.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

I'm curious to know the affect on performance this change will make. Can you please post back with before & after execution times?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Take a look also at Little known index facts

PluralSight Learning Library

 

[markros]

I like that Brad's blog so much, that I allow myself to quote a bit
-----------------------------------------
You decide to set up this task to rebuild all your indexes with a fill factor of 80 and so you type the number 80 in the Percent box. And you set the task to run tonight at midnight.

The next morning you come in and everything has slowed to a crawl.

Why?

Look more closely at the dialog. It’s not asking for a Fill Factor Percent… it’s asking for a Free Space Percent! It’s the exact opposite of everything you’ve learned! You’ve just rebuilt all your indexes with 80% free space and only 20% filled… and therefore all your indexes are 4 times bigger than they should be because most of the space in their leaf pages is empty.

Don’t laugh. I saw this happen. I was called into a company that had poor performance in their system, and, thanks to my utility for generating DDL Hyperlinks, I happened to noticed that every one of their indexes had a fill factor of 20. Apparently an external contract DBA had come in to set up some admin-related tasks and he entered the number 80 in the Rebuild Index Task Dialog of a Maintenance Plan (presumably thinking he was entering a fill factor value), thereby bloating the database with tons of empty space.

So beware of that Rebuild Index Task Dialog. Whoever put that backwards dialog together must have been evil. Microsoft should check their payroll for a person whose name is Lucifer or Beelzebub or Satan.



PluralSight Learning Library