This procedure will get all the objects performing committed reads. It will fetch all the objects (Procedures, Functions, Triggers, View etc.) that doesn't uses the NOLOCK and SET ISOLATION LEVEL READ UNCOMMITTED. It may be helpful in performance tuning assignments and to resolve / minimize the frequent blockage and deadlock issues.
Category: Database Administration
usp_SQLServerCarpenter_Tools_Get_Procedures_Triggers_Missing_SET_NOCOUNT_ON
This procedure will get all the stored procedures and triggers missing the use of SET NOCOUNT ON. It may be helpful in performance tuning assignments.
Game changer settings # MAXDOP and Cost threshold for parallelism
I got a performance tuning assignment for an esteemed customer in the Financial Services domain. I went through the wait stats of SQL Server using the built-in SQL Server Report, called Performance Dashboard. I found CPU – Parallelism referred to as CXPACKET was leading the list, followed by Buffer IO, Lock, and Latch. Parallelism in SQL Server refers to two settings – MAXDOP and Cost threshold … Continue reading Game changer settings # MAXDOP and Cost threshold for parallelism
Storage savings with Table Compression
In one of my recent assignments, my client asked me for a solution, to reduce the disk space requirement, of the staging database of an ETL workload. It made me study and compare the Table Compression feature of SQL Server. This article will not explain Compression but will compare the storage and performance aspects of … Continue reading Storage savings with Table Compression
Estimating the storage savings by removing columns with NULL value across the table or marking them as SPARSE
In the previous article Find columns with NULL values across the table we discussed that storage space can be saved by removing columns with NULL value across the table or marking them as SPARSE. We also learnt about the query to find all such columns across the tables of a database. In this article we'll … Continue reading Estimating the storage savings by removing columns with NULL value across the table or marking them as SPARSE
SQL Server Carpenter 