SQL SERVER – Applying NOLOCK to Every Single Table in Select Statement – SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The other day, during SQL Server Performance Tuning Practical Workshop, I walked into a very interesting situation. While tuning a query we had a situation when we had to check if the query is locked during the execution of not. To test our theory out we had to put a nolock hint for every single table of the long query. However, it was no way possible to do so as the Stored Procedure was super huge and involved over 90 tables and 14 views in multiple SQL Statements. It was indeed a huge challenge for a developer team to modify that SP. If you ever face such situations, you should not stress out. There is a much simpler way to read uncommitted data.

Read More

SQL SERVER – Observation: ROWLOCK Hint and Slow Performance

During my recent Comprehensive Database Performance Health Check Developer showed me a slow query which used ROWLOCK hint. The common understanding is that with the help of ROWLOCK hint SQL Server only locks a single row which eventually leads to performance but in their case, it was actually very much opposite. Let us see this scenario with a simple example.

Read More

SQL SERVER – Puzzle – Usage of New Index Hints for Denali – ForceSeek and ForceScan

Today is Friday and tomorrow is the weekend. I just thought, let us explore something new but additionally give you all a quick puzzle to explore about index hints.

SQL Server Denali has new Query Hint – FORCESCAN. In earlier version of SQL Server we already have Query Hint FORCESEEK but now the counter part also exists. The quick understanding is there will be cases when FORCESEEK or FORCESCAN will be helpful and improve the performance of the query.

Read More
Menu