Mac users' indifference toward security 'worrying' http://t.co/MBTwqilk
7 minutes ago by OnlineWebTips on twitter
Breaking News: Yahoo announces Alibaba stake sale
ZDNet / Techguide / Database
Diagnose performance issues via sp_lock
Summary
A common misconception among IT pros is that "locks are bad", and you must do everything possible to ensure database locking doesn't prevent processes from running.
Events
IBM Technology Conference & Expo 2012
May 22, 2012
One World Hotel, First Avenue, Bandar Utama City Centre, 47800 Petaling Jaya, Selangor
Echelon 2012
June 11 and 12, 2012
University Cultural Centre, National University of Singapore
Startup Asia Jakarta 2012
June 7 and 8, 2012
12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia
MMA Forum Singapore
April 23-25, 2012
Grand Hyatt Singapore
In order to ensure a consistent database environment, the database engine must use a mechanism to acquire exclusive use of a resource when that resource is being modified.
SQL Server uses locks to achieve this consistency. Locks are objects that the database engine uses to ensure that only one thread can access a resource at a time.
Without the use of locks, concurrent data modifications would be possible by separate processes, which could potentially leave the database in an inconsistent state.
Locks are a good thing, but you should plan your applications in such a way to minimize the number of database locks involved.
Here are details about a stored procedure that enables you to diagnose your database locking issues.
Finding out what's getting locked
A sluggish running system means that it is time for you to do some investigation. A great place to start your search is to determine the number and frequency of locks occurring on your system. If your environment is highly transactional, it is very common for separate applications to contend for resources, which will cause locks.
The key to resolving these issues is having the ability to determine the resources that are being locked and the processes that are contending for resources.
sp_lock
The sp_lock system stored procedure is packaged with SQL Server 2000 and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslockinfo in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests.
Let's take a look at executing the sp_lock procedure to see what information it provides:
EXECUTE sp_lock
On my system, here's what the procedure returns. The information returned from sp_lock isn't self explanatory and will require some investigation to get to the meaningful data. You have the luxury to copy the text of this stored procedure and create a new one that will give you a better explanation as to what is going on in the system. (I will only focus on the data returned by sp_lock.)
Looking at the results above, you see these fields: spid, dbid, objid, indid, type, resource, mode, and status. The spid is the process identification number, which identifies your connection to SQL Server. To find out which user is associated with that spid, execute the stored procedure sp_who and pass the spid as a parameter to the procedure. The dbid is the database the lock is occurring in; you can find it in the sysdatabases table in the master database. The objid field indicates what object the lock is occurring on in the database. To view this object, you can query the sysobjects table in the master database for that specific objid.
The single record produced in the screenshot above will not necessarily be indicative of what is transpiring in your production environment. When you run this procedure, you want to look for result sets of 500 to 1,000 or more. You will probably get a different result set each time you execute sp_lock because of the new locks that have been acquired and the older locks that have been released. If you find that a large number of records returned by sp_lock have the same spid, it is likely the process is inside of a large transaction, and these locks may begin to block other transactions from occurring.
When you notice a spid that has acquired a large number of database locks, it is helpful to determine what stored procedure or statement is being run. To do this, run the following DBCC command:
DBCC INPUTBUFFER(spid)
This DBCC command will return information regarding the statement running in the EventInfo field.
A solid starting point
A slow running system could be indicative of a high number of locks on your tables. These locks may be caused by processes such as: a user running a really long query on your system; a process that is a resource hog; or two critical processes that compete for the same resources and frequently deadlock.
Once you find the process that you think is slowing down your system, in most cases, your next step should simplys be to monitor the system. It's not a good idea to kill a process because it has a large number of locks in the system, unless you are absolutely certain that nothing else is adversely affected; instead, you should think of ways to automate the analyzation of your locking situation. Another idea is to figure out a way to receive notification when the system locks reach a certain threshold during certain times of the day.
The more information you can gather about your system, the greater advantage you will have regarding its resolution.
Tim Chapman is an SQL Server database administrator and consultant who works for a bank. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.
Resource Centre Useful content from our premier sponsors
Database TechGuides
Audit data using SQL Server 2005's COLUMNS_UPDATED function
Physically destroy your hard disk for security
Simplify queries with a Dates table
Document SOX compliance with SQL Server 2005
Build a file location on the fly
Generate dynamic SQL statements in SQL Server
Write a common sorting algorithm
Bulk importing data into SQL Server
Develop an SQL Server Job from scratch
Correct data casing in SQL Server
ZDNet Asia Live
RT @Bilafer: 25% of top VC firms investing in China, India or both. Good news for innovation, #SAPAPJ http://t.co/wkCXKkxU
7 minutes ago by businessobjects on twitter
Mac users' indifference toward security 'worrying' http://t.co/AkSlHrCH #cyber #infosec
7 minutes ago by Sec_Cyber on twitter
RT @zdnetasia: Gartner: Mobile CRM gives better ROI than social. http://t.co/nTgj44H8
37 minutes ago by Oystor_Tweets on twitter
China hits back at Pentagon report on spy claims. http://t.co/CccR4SBM
37 minutes ago by zdnetasia on twitter
China hits back at Pentagon report on spy claims http://t.co/YP380BYQ http://t.co/erFX4aVv #arcavir
37 minutes ago by V_RaV on twitter
http://t.co/VNaZtseV China hits back at Pentagon report on spy claims: Annual report by Pent... http://t.co/TvgCi5RE http://t.co/wiqY9ktt
37 minutes ago by RavtachSolution on twitter
#AntiVirus News: Mac users' indifference toward security 'worrying' http://t.co/spWS0CpU #AdAware
37 minutes ago by AdAwareFree on twitter
Mac users' indifference toward security 'worrying' http://t.co/BtVn1BAk
> expected! They still remember Mac vs PC ads
#infosec #news #apple
Pentagon report says China exploit US tech, conduct cyberespionage, China says it has been "unjustly criticized" http://t.co/P5wgqy6I #in
52 minutes ago by EllyZDNetAsia on twitter
Mac users' indifference toward security 'worrying': 59 Jakarta 10350, Indonesia In light of the recent spate of ... http://t.co/Lxgnc1wM
1 hour ago by GoodCodeBadCode on twitter
Pakistan lifts block on Twitter - ZDNet Asia: Pakistan lifts block on TwitterZDNet Asia59 Jakarta 10350, Indones... http://t.co/61n85ajh
1 hour ago by semarang_photo on twitter
Pakistan lifts block on Twitter http://t.co/WHqoJOqm http://t.co/erFX4aVv #arcavir
1 hour ago by V_RaV on twitter
http://t.co/VNaZtseV Pakistan lifts block on Twitter: Country restores access after briefly ... http://t.co/5gqegFWK http://t.co/wiqY9ktt
1 hour ago by RavtachSolution on twitterI reckon your view: "CRM is strategy, not software", if a company replicating the approach uses in ERP implementation into CRM, what they...
3 hours ago by wykoong on Gartner: Mobile CRM gives better ROI than socialThis video will teach you about the Excel fill handle but also provide you with a workook to download... http://www.youtube.com/watch?v=...
20 hours ago by TradeBrother on A quick fill handle trick for Microsoft Excelwaiting...
2 days ago by eapete on What should count in a company's market value?Boy, you've opened a can of worms now.
Wait for the rants & raves.
I was puzzling before this whether to replicate the success formula we executed for a financial institute, and come out with a standard s...
3 days ago by wykoong on Drop the egos, copy ideas, then innovateKeep up with ZDNet Asia
Facebook 
RSS Feeds 
Newsletters 
Twitter Whitepapers
Downloads
Database News
Inside ZDNet Asia
Sponsored
Echelon 2012 - The Awesomer Tech Event in Asia
Echelon 2012 – SEA’s longest running tech startup event goes Awesomer. Catch 50 of Asia’s most promising startups & over 40 international speakers on June 11-12.
Startup Asia Jakarta showcases new product-ready tech startups. Plus: hackathon, exhibition, and speakers. Use promo code CBSi50 for 50% discount.
ZDNet Asia Intelligent Singapore video series
Featuring inteviews with CXOs who define "intelligence" in their markets and reveal how their companies drive business efficiencies through ICT.
