Blocking Issue...
-
Hi All
,I have an issue in my application, sometimes users reported that system is slow, we checked with the DBA guys and found blocking on the database which causing interruption in query execution.
In normal scenario, DBA remove the blocking and send the query which causing blocking. But it cause a huge impact in process.
I have some query:
#1. Is it any way to find out the Root Cause of blocking? As we have the queries which causing the blocking but all are OK
.#2. Is it any way to prevent it?
#3. How to monitor it without run Tracing as we dont have Rights for that
.Regards,
Manu Raj
Thanks & Regards,
Raj -
I'd talk to your DBAs and ask them the very same questions you're asking here. That should typically come under their responsibility (I would say).
If you don't have rights to run a trace then it's probably because the DBAs have reason to restrict that right.
-
Hi SQLZ
,Is tracing is only the way to monitor the blocking???
Other thing is, in my 1st question, I asking for the Root cause. Do you have any idea, its an indexing problem or space problem or database architecture or something else...
Regards,
Manu Raj
Thanks & Regards,
Raj -
Hi Manu,
You monitor blocking in various ways. I'd start with looking at sp_who2 and sp_lock - check them out in BOL. I'd also read up on locking and blocking in BOL.
It's impossible to say what the root cause of your blocking is without having a good idea of what the design (logical and physical) of your system looks like and without knowing what transactions are running against it. This is why I said you should ask your DBA - that's what they're there for. Secondly, it sounds like you've already been told what the root cause is - didn't you say that the DBA sends you the offending transaction? That would be a good place to start with regards to the root cause.
Something you have to realise about locking/blocking is that it is a fundamental and essential part of all transactional databases. Some process updates data in a table and locks that data so that other processes are blocked from being able to read/update the same data. It's a fact of life. There are ways around this - some of which are applicable in certain situations and some of which aren't applicable.
The point is that you will never be able to completely avoid blocking so your aim should be to maximise the performance of transactions such that resources are locked for the least amount of time possible - thereby minimizing the length of time for which other processes are blocked. How you maxmise the performance of your transactions depends on a multitude of factors. Proper coding practices, proper use of transactions, proper database design, hardware, architecture, indexes, etc, etc. I could go on and on.
In a single isolated case what you're interested in (at a very very basic level) is:
1. What process(es) are being blocked
2. What process is causing the blocking
3. Why is it blocking
4. How can you improve the performance of the process causing the blocking (item 2) such that you minimize item 1.
It sounds like your DBA has already given you 1. and 2. So with your DBAs help you should be able to find out the answers to questions 3. and 4.
Hope that helps,
-
Based on my experiences, most blocks originate from the scripts/queries and database designs. Being a DBA, he can only tell you what blocks what and what scripts may be criminals.
As a developer, he should write good scripts/queries. He should try to avoid writing a long transaction in the sense of time to execute. He can include different isolation level and no lock clauses in his code.
Of course, DBA should provide a good database design. To avoid locks, developers and DBAs should cooperate. A good design for a database needs information on the scripts/queries. In the same token, good scripts/queries are based on a good design for a database. Without good cooperations between developers and DBAs, what we could do are finger-pointing at each other.
-
Hi,
I agree with all the inputs, but the thing is that the scripts and query which DBA sent were all OK.
I want to know the Root Causes which can creates blocking in the database, may it not possible but I desperately want to know the list of cases which can cause the Blocking.
According to this list I will proceeds with my end, as per my previous experience I know that DBA will not help as much as I can get here...
Hope a positive response
Thanks & Regards,
Raj -
Hi,
What do you think means by blocking? What the people in this thread has already said is that blocking is normal in a RDBMS system. Even if you have two 'normal' queries, they can Block eachother.
I.e: Query 1 is inserting into table A for 10 minutes (by one single insert statement)
Query 2 tries to do a SELECT * from table A.
If Query 2 is executed just after Query 1, Query 2 will be Blocked for 10 minutes.
This is normal and both queries are 'good'.
What kind of Root Cause are you looking for?
Regards,
Hanslindgren
-
Have you lookd at the front end code that calls the offending stored procedure? At various times I have found front end developers having put transaction around a call to a stored procedure, and leave it open for too long. Or even worse, transaction that is open while waiting for user input.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply