find deadlock in sql server

If you are in charge of a SQL Server-based project or you have just been in touch with SQL Server, you may have to face some database performance problems, this article will provide you with some useful guidance (most of which can also be used for other DBMS ). Here, I am not going to introduce the tips for using

SQL server databases are the most widely used relational databases. However, in actual application, many databases need to be optimized. When the data volume is particularly large, we must rely on the optimization of our team's database. Such as creating indexes, table partitions, database shards, and table shards. This article summarizes some optimization methods. Address of this Article There are many rea

isolation level. 4) use the table-level locking prompt for select, insert, update, and delete statements. 5) configure the index lock Granularity You can use the sp_indexoption system stored procedure to set the locking granularity for indexes. 6. view the lock Information 1. Execute exec sp_lock to report lock information. 2. Press Ctrl + 2 in the query analyzer to view the lock information. Seven precautions How to avoid deadlocks 1. When using a transaction, try to shorten t

cursor. If a large result is returned, the stored procedure is used.42. Do not use the same function repeatedly in one sentence, waste resources, and put the result in a variable before calling it faster.43. The efficiency of select count (*) is low. Try to change his writing style as much as possible, while exists is fast. note the difference: the return values of select count (field of null) from table and select count (field of not null) from table are different !!!44. When the

45. Access your table in a certain order. If you lock table A and table B first, lock them in this order in all stored procedures. If you first lock table B in A stored procedure and then lock Table A, this may lead to A deadlock. If the lock sequence is not designed in detail in advance, it is difficult to find deadlocks. 46. Monitor the load of the corresponding hardware through SQL

Original: 2-year summary of SQL Server DBA tuning2 years SQL Server DBA Summary of tuningWhen I was 2 DBA I felt that there was something that needed to be shared with everyone to discuss the book first.Book List1. "In-depth analysis of SQL

Label:1. Four properties of a transaction Atomic Atomicity, consistency consistency, isolation isolation, persistent durability, acid properties. atomicity : A transaction must be a unit of complete work, either fully executed or not executed at all. consistency : At the end of the transaction, all internal data is correct. isolation : When multiple transactions are concurrent, each transaction does not interfere with internal data, and it processes data that is before or after another transacti

2YearSQL Server DBAOptimization Summary When I was a dBA for two years, I felt that some things needed to be shared and discussed with you. Book order 1. "in-depth analysis of SQL Server 2008 series" is the technical insider series of MSSQL 2005. I am also interested in version 2012. The technical insider series is my first book. It covers a large amount of conte

existence of a deadlock, if any, one of the transactions to be revoked, so that the other transaction can be completed successfully. In general, it is a matter of undoing the small amount of modified data so that the rollback is less expensive. A database with row-level locks rarely has this problem because two users are less likely to modify the same record at the same time, and have fewer locks due to the very accidental order of data modification.

efficiency of Select COUNT (*) is low. Try to change his writing style as much as possible, while EXISTS is fast. note the difference: the return values of select count (Field of null) from Table and select count (Field of NOT null) from Table are different !!!44. When the server has enough memory, the number of preparation threads = the maximum number of connections + 5, which can maximize the efficiency; otherwise, use the number of prepared thread

. If you lock table A and table B first, lock them in this order in all stored procedures. If you first lock table B in a stored procedure and then lock Table A, this may lead to a deadlock. If the lock sequence is not designed in detail in advance, it is difficult to find deadlocks. 46. Monitor the load of the corresponding hardware through SQL server performanc

Label:Original: SQL Server optimizer features-dynamic retrievalSome time ago I wrote the article SQL Server implicit conversion caused by lying gun deadlock in some friends commented back said that in the SQL2008R2 test did not appear