Google
 

Saturday, May 20, 2017

Detecting applications causing SQL Server locks

On one of our testing environments, login attempts to a legacy web application that uses MS SQL Server were timing out and failing. I suspected that the reason might be that another process is locking one of the table needed in the login process.
I ran a query similar to this:

SELECT request_mode,
 request_type,
 request_status,
 request_session_id,
 resource_type,
 resource_associated_entity_id,
 CASE resource_associated_entity_id 
  WHEN 0 THEN ''
  ELSE OBJECT_NAME(resource_associated_entity_id)
 END AS Name,
 host_name,
 host_process_id,
 client_interface_name,
 program_name,
 login_name
FROM sys.dm_tran_locks
JOIN sys.dm_exec_sessions
 ON sys.dm_tran_locks.request_session_id = sys.dm_exec_sessions.session_id
WHERE resource_database_id = DB_ID('AdventureWorks2014')


Which produces a result similar to:



It shows that an application is granted exclusive lock on the table EmailAddress, and another query is waiting for a shared lock to read from the table. But who is holding this lock? In my case, by checking the client_interface_name and program_name columns from the result we could identify that a long running VBScript import job was locking the table. I created a simple application that simulates a similar condition which you can check on Github. You can run the application and run the query to see the results.

It's a good practice to include "Application Name" property in your connection strings (as in the provided application source code) to make diagnosing this kind of errors easier.

Saturday, February 18, 2017

Abuse of Story Points

Relative estimates are usually recommended in Agile teams. However nothing mandates a specific sizing units like story points or T-shirt sizing. I believe that - used correctly - relative estimation is a powerful and flexible tool.
I usually prefer T-shirt sizing for road-mapping to determine which features will be included in which releases. When epics are too large and subject to may changes, it makes sense to use an estimation technique that is quick and fun and doesn't give a false indication of accuracy.
On the release level, estimating backlog items using story points helps planning and creating a shared understanding between all team members. However used incorrectly, the team can get really frustrated and might try to avoid story points in favor of another estimation technique.

In a team I'm working with, one of the team members suggested during a sprint retrospective to change the estimation technique from story points to T-shirt sizing. The reasons were:
  • Velocity (measured by story points achieved in a sprint) are sometimes used to compare the performance of different teams.
  • Story points are used as a tool to force the team to do a specific amount of work during a sprint.
Both reasons make a good case against the use of story points.

The first one clearly contradicts with the relative nature of story points as each team has different capacity and baseline for their estimates. Also the fact that some teams use velocity as a primary success metric is a sign of a crappy agile implementation.
The second point is also a bad indicator. The reason is that you simply get what you ask for: If the PO/SM/Manager wants higher velocity then inflated estimates is what (s)he gets. Quite similar to the Observer effect.

Fortunately in our case both of these concerns were based on observations from other teams. Both the Product Owner and Scrum Master were knowledgeable enough to avoid these pitfalls and they explained how our team is using velocity just as a planning tool. However, the fact that some team members might get affected by the surrounding atmosphere in the organization is interesting and brings into attention the importance of having consistent level of maturity and education.

What is your experience with using story points or any other estimation technique? What worked for you and what didn’t? Share your thoughts in a comment below.