Got approached with the below error message recently and thought it might be an interesting problem to look into.
ProgrammingError((\’42000\’, 651, \'[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot use the ROW granularity hint on the table “XXXXXX” because locking at the specified granularity is inhibited.\’, 11159)
The client faces this issue when they implement a clustered Columnstore index. So first let’s try to recreate the problem and then we can explore the reasons for it. To start off I am going to create a table with the Clustered Columnstore index only.
Once the Clustered Columnstore index is created we apply the below query against the table and we see the error message as shown. The error message clearly indicates it’s because on a hint being applied to the table. But is this the only way the error can appear?
What if we replaced the clustered Columnstore index with a non-clustered Columnstore index against all columns?
As you can see the issue still persists because we ask the optimizer to acquire row level locks. So what happens when we remove the hint?
Notice that without the hint the query executes fine and the results show that Columnstore indexes are stored in LOB pages not traditional IN row data pages. As a result there is not possibility for the optimizer to acquire specific row level locks within the LOB pages especially considering the compressed format the data is stored within these pages.
So what is the solution? Simply remove the query hint and execute the query as needed. Add traditional non clustered indexes as needed to acquire seek behavior on specific rows on a query by query basis.
Please Consider Subscribing
