Tag Info

New answers tagged

3

Apparently, you want to see constraint exclusion at doing its work. As in similar setups the parent table has no constraint (and, just to mention it, usually no rows), it will be always visited by queries on the parent table. Using your example schema, see the output: test=# EXPLAIN SELECT count(1) FROM p; QUERY PLAN ...


0

Based on your question and other discussion comments, I didn't see any clue for network and blocking issues. Here, I'd like to give following approaches to try which can help in your situation: Approach#1: Suspecting performance issue because of indexes in STG_TRANSIT table. In the sutuation of big STG_TRANSIT table and there are indexes available, what ...


0

I believe i have discovered the problem. One TXT type column as appeared with 67285Bytes and every time i select them with an where clause the SQL Server Engine lock the entire Table because the data volume on the select.


0

I don't know SQLServer specifics, but usually this sort of thing is due to table statistics being different in the two databases. Look at the query plans to see if they are different. Run the SQLServer version of "analyze table" or "analyze schema" commands. If these things don't help, check how the databases are set up. Is it possible that the data is ...


4

There are doubtless many blogs that discuss this, but this post by Kendra Little (at BrentOzar.com) discussed the issues that concern you. See: http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Kendra discusses some of the problems that arise and how you can test for potential ...


0

OBSERVATIONS In your question, you said I read from the documentation that the behaviour of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined. You are overlooking something far more basic MySQL Documentation clear says the following on GET_LOCK() A negative timeout ...


8

Is there a way to prevent the deadlock while maintaining the same queries? The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case): As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in ...


0

I have a similar issue that occurs occasionally and here is the approach I take. Add set deadlock priority low; to the select. This will cause this query to be the deadlock victim when a deadlock occurs. Setup retry logic within your application to automatically retry the select if it fails because of deadlock (or timeout), after waiting / sleeping for ...


2

The title says blocking, but the question says locking. I'll deal with the former since the latter should be a given: yes, locks need to be taken in order to perform a delete. To check for blocking, in the window where you're performing the deletes: SELECT @@SPID; -- make note of this In another window: SELECT blocking_session_id, wait_type FROM ...


1

3 minutes for 50k records using a 'batch style' delete is actually not too bad. It's also a great way to minimize locking/blocking. Any time you run a DELETE operation, there will be locks. What you have done here is minimize that by only doing 100 records at a time. So, to answer your question, yes, there are locks taken on the table while the delete ...



Top 50 recent answers are included