Lock

Error: Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited

I recently encountered this error message on our SQL server 2008: Error: Cannot use the ROW granularity hint on the table “xxx” because locking at the specified granularity is inhibited Cause: This is usually caused by indexes created on a database table where ALLOW_PAGE_LOCKS is set to OFF. This behaviour means that all access to the index will be via a table lock, not a row lock. Run following TSQL command against the affected database table: [sourcecode language=”sql”]

SQL Server 2008 R2 Post Configuration Changes/Optimization

In this post I will be looking at some useful tools and configuration changes that can be implemented to ensure that you get the best out of SQL: – Run the Microsoft SQL Server 2008 R2 Best Practices Analyzer (Click Here) – Split SQL TempDB per number of Processor Core (Click Here) – Lock Pages in Memory – Set SQL max memory limit – Enable paging file – Prevent Cluster Failover if SQL Server Agent resource fails