April 30, 2008 at 12:07 am
Hello.
I have a table that over 3000 user insert row in it. and some other users use SELECT on this table. I have an update query that run on new inserted rows every 2houres. and when this query is running, the Select query can't run and get Timeout.
I want to my users can work with SELECT when the Update Query is running
please help me.
April 30, 2008 at 6:24 am
Are the updates and the reads against the same data? Before you do something drastic (the very next post is probably going to suggest using a query hint on the reads), you should examine the indexes on that table. Specifically the clustered index. Is it distributing the data such that incoming reads can get at the pages of data that they want while incoming updates & inserts are affecting other sets of data. That's where I'd suggest starting.
"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
April 30, 2008 at 6:51 am
No query hints in my post either.
I agree - examine the process. Are the users selecting the data that is being updated? How long is the update taking?
If they are not viewing the exact same records, it could be that your update is escalating locks because of page splitting and you just need to adjust your fillfactor on your indexes.
If they are trying to view the records being updated, I would suggest you look at snapshot isolation - this would allow your users to see the previous committed version of a record during your update (which is better than viewing the dirty records). Of course, this may require some application changes.
April 30, 2008 at 6:55 am
Good suggestions and I'm happy to be wrong about the query hint.
"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
April 30, 2008 at 6:57 am
I hate the hint you are referring to.
It shall remain un-named for a bit longer.
April 30, 2008 at 7:34 am
I'd have to agree with Michael and Grant. What is it that's being done to take so long?
Like Mr Myagi would say: "Best defence - no be there". In other words - your best course of action is to attack the update, and not mess with the SELECT's.What is the update doing? can it be done in a series of smaller chunks? Why the heck is that taking so long?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 30, 2008 at 10:19 pm
what do I examine the clustered index?
yes. the users selecting the data that is being updated.
the update take about 15min.
snapshot isolation is good idea. I think it's good but what do I change in application?
May 1, 2008 at 5:30 am
The first thing you need to do is examine the update. I would be very concerned about any single transaction that takes 15 minutes on a live production system that people are actively querying.
If the users really do need to see the records being updated, you need to really consider what they should be seeing. You have a few options.
The first is what you have - they wait until the update is done or rolled back and see the committed information.
The second is snapshot isolation - the users would see the before picture of all of the data until the update is committed. You should look this up in BOL and spend some time reading a few articles on it before you decide it is a good option in your situation.
The third is dirty reads - the users would see the partially updated data and could be looking at something that will be rolled back. This is usually a bad idea and I rarely recommend it.
Like I said at the beginning - the problem is most likely the process that takes 15 minutes on your production system every 2 hours - a full 1/8 of the time your system is running it is unavailable to the users. That is probably something that needs fixing.
May 1, 2008 at 6:03 am
twity61 (4/30/2008)
what do I examine the clustered index?yes. the users selecting the data that is being updated.
the update take about 15min.
snapshot isolation is good idea. I think it's good but what do I change in application?
First, is your update a set-based operation or is it processing everything row-by-row? If it's row-by-row, that's where you need to spend your time. Turn it into a set-based operation.
Assuming it's already a set-based operation, examine the execution plan. As Michael says above, your system is offline too much of the time. You might not have an effecient clustered index or indexes period. The execution plan is where you can see that. Is it doing a table scan or index scan? Then you need to examine the indexes on the table and change them such that it's performing seeks.
Regardless, it really sounds like the main issue is the update process, not the reads (although they may have problems too).
"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
May 1, 2008 at 1:28 pm
Another option to look at with the update, is can it be done in pieces? Perhaps in a temp table, even. With complex updates with a lot of math in them, and a lot of joins, etc., it can be good to break it down into smaller, less complex parts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply