Category Archives: INTERVIEW Q&A


I got this question in an interview where the interviewer asked me to Define any three locks I used in my career and their practical purpose.

I answered him, I have used UPDLOCK,HOLDLOCK AND NOLOCK in my career and started elaborating as follows,

UPDLOCKS are associated with DML statements like Update. It is a shared lock and needs to be converted to an Exclusive lock before the original operation occurs. Also the lock occurs at row level. When a user executes an Update statement it assigns a Shared lock on all the scanned rows in the table, when it finds the row to be updated it converts it to an Exclusive lock and update operation occurs.

HOLDLOCKS are used at table level it prevents the table from being updated by any other DML transactions like an insert or an update. HOLDLOCKS are preferable to use in situations where we need only COMMITTED READS.

NOLOCKS are opposite of HOLDLOCKS and when they are issued at a table level, it allows the user to access/read the data from a table whilst some update/ insert operations are running at the back. NOLOCKS may produce UNCOMMITTED READS and are preferred to use at the Production Environments so that the real time update is not hindered.

The Interviewer asked, How do you see the current locks in a Server?

I told him I will use the Stored proc SP_LOCK

Then he asked me how will you kill the locks?

I answered, I will use KILL Session_ID command.

Then he asked me, Is there a report you can see the status of the Locks?

I answered him, There is one in the SSMS at database level, when you right click the database and view Reports.

He asked me, Whats its name?

I told him, I can’t think the name on top of my head but will be able to tell it when I open SSMS.

He said, Its “Top Transactions By Lock Count”

I was like Gosh!


Difference between Report Server, Report Server DB and Report Manager:

Report Server: Report Server defines the nature of installation of SQL Server Reporting Services in your organization. It comes in two modes namely native and share point mode. Native mode is the default and used in 80% of the industry and in SharePoint mode it is integrated with SharePoint. Report Server is made up of two processing engines and a group of extensions responsible for authentication, data processing, rendering and delivery options.

Report ServerDB:  Report Server database contains all properties, objects and metadata related to an SSRS unit. It can be accessed using SSMS. Most used tables from this database are Catalog, Execution Log Storage, Subscriptions and Users.

Report Manager: Report Manager is a web based tool built using ASP.NET application to view/access SSRS reports. It’s not available in SharePoint mode.

Commentary: I faced this question in one of my interview and confused the Report Server and Report Manager terminologies. Technically we will be deploying the .rdl file to the Report Manager and what’s the use of Report Server then? After digging into it came to know Report Server is implemented as a Windows Service and will run in the background as SQL Server Reporting Services. With regards to Report Server DB, Had an opportunity to work with it for an audit purpose and it has tons of data related to SSRS and we can generate useful insights from it.For further reading on this topic visit the MSDN site.

Hope it helps someone:)