October 27, 2005 at 10:11 am
There are so many Locks (TAB,PAG,KEY) are creating in my production database. How can I know which SQL Stalemates cause the problem of the lock?
Is SQL Profiler is good for find the SQL Statemets that casuses the locks?
Is sp_who2 is good for find the locks?
How can I remove the locks?
Is LOCK_TIMEOUT is a good option to avoid locking?
Please advice
October 27, 2005 at 11:31 am
You can use SQL Profiler, sp_who, sp_who2, and sp_lock to monitor this activity.
Locking is a normal activity within a database and is used to maintain consistency and integrety of data. Why are locks and issue to you? Are you seeing performance issues in an application? You can use isolation levels and lock hints to modify locks during sql operations. Using custom isolation levels and lock hints can cause problems as well as fix them if you are not carefull!
October 27, 2005 at 12:46 pm
Thanks for u r reply
Yes .. We have some performance issue in application. Some of the users complain that before the application was fast Now they feel it is slower.
What are the procedure that I have to include my maintenance plan to make the system more faster.
Regards
October 27, 2005 at 1:48 pm
60% of the improvement can normally found in the application itself,how it handles its data and general design of your database (3NF?)
If possible, check what has changed in the application. (since when the application got slower?)
Does it use stored procedures? Does it retrieves all rows asked? Is there proper indexing (avoiding tablescans)? What is it main function transaction processing or reporting? Cursors are avoided if possible?
Below is a link how to read the query plan and general suggestions how to optimize them
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
To find locks you can use sp_lock, sp_who2.
Microsoft statement how to solve blocking statements:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453
Troubleshooting application performance:
October 27, 2005 at 1:58 pm
I like to insure basic maintenanace takes place before I look at the applications. Do you have a nightly/weekly/monthly window that you can introduce maintenenace to the MS SQL Server? The length of the window needed depends on the size and amount of tables/indexes a system has. I have a 200G database that takes about 90 minutes to run the below processes in a serial fashion so they do not overlap.
Basic maintenenace example:
Full database backup: Daily (Recovery to point in tiime within 24hrs)
Transaction Log backups (Recovery to point in tiime from full backup)
Database Consistency checks (Check for corruption)
Rebuilding Indexes (Updates stats and Reorganizes/defragments clustered index data and secondary index pointers)
Update statistics (Builds staistics on tables, columns, and indexes for use by the optimizer)
Recompiling objects (sp_recompile on all tables which recompiles stored procedures and triggers the next time they are used. This allows query plans to be rebuilt and stored based on updated indexes and statistics)
NOTE: db_options auto update stats, auto create stats, and torn page detection accomplish DBCC's and maintaining statistics, but at an overhead to the application). In the end if you don't rebuild query plans this only help in-line sql.
If this is already in place I would go to Jo's plan if not I would implement proper maintenance, then monitor and tune the maintenanace. Then start working the application as Jo recommended above.
October 27, 2005 at 2:18 pm
I do not have daily/weekly /monthly mantenance plan
But I am taking full DB Backup every 12 hrs and Differental Backup every 2 Hrs and Log Backup every 15 miniuts.My database size is 15 GB .
Every week I am doing rebuild all the index and shrink the log file and data files.
What are the other procedure that I have to do in my have daily/weekly /monthly maintenance plan? Pls explain in detail.
October 27, 2005 at 2:33 pm
Are users able to query the database ? When we first switched to SQL Server, power users were able to run Query Analyzer selects against huge production tables that caused problems for users of our order entry application. Initially, the selects were changed to use (NOLOCK) that solverd the problem temporarily until we got the replicated reporting database up & running.
October 27, 2005 at 2:33 pm
What is the OS and MS SQL Server version you are running? What utility are you using to do the backups? Is the application using stored procedures or in-line SQL?
Can you get an hour a night to dedicate to maintenance? During this hour the application would be usable but performance would be degredated.
October 28, 2005 at 9:09 am
Manually removing locks in a production database would be extremely hazardous. If you figured out how to do it you will probably cause massive database corruption. And if you use LOCK_TIMEOUT to force lock expiration you will cause the transactions that created them to fail.
The number of locks is a symptom, the actual problem is the duration of the locks. Any large operation is going to generate a large number of locks, but they should be released as quickly as possible.
You can reduce the number of locks, but usually at the expense of concurrency and scalability. You can cut down on read locks in reporting queries by using WITH(NOLOCK) (IF YOU UNDERSTAND WHAT THIS DOES AND IS ACCEPTABLE IN YOUR APPLICATION). You can reduce the number of locks generated by inserts/updates by using WITH(PAGLOCK) or WITH(TABLOCK) to avoid a large number of row locks, but this will severely impact anything else trying to access the same tables.
Reducing the duration of locks may be more important. All transactions should be as short as possilble, and should not be waiting on outside events. Are rows being locked while the application is waiting for the user to enter changes? A large operation such as updating a million rows will run faster as a loop that updates (and commits) 5,000 rows at a time, but you lose the ability to rollback the entire transaction if there is a problem.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply