Month: December 2024

Overusing ISNULL

ISNULL() is a function, and therefore has an overhead. Here is a pattern I have seen recently …

WHERE ISNULL(CustomerID, 0) = 1

… which harms performance in three ways.

1 Changing NULL to a value that will never equal ‘1’ (EG: ‘0’), is the same as leaving it as NULL (but with the additional overhead of using a function).

2 Indexes will have to be scanned (not seeked), as nesting CustomerID inside a function like this makes it non SARGable.

3 Every single CustomerID has to go through this comparison test – not just the NULL’s. Which may have a profound impact on performance, if -say- a million CustomerID’s have to funnel through!

To fix, you can just remove the ISNULL function, or maybe investigate the option of changing the column definition within the table to ‘not null’.

SQL Server: Setting Min & Max memory

-- MinMaxMemory.sql


-- 1 get the physical memory on the server

 DECLARE @PhyMem INT =
 (
   SELECT total_physical_memory_kb / 1024 
   FROM sys.dm_os_sys_memory
 );


-- 2 get the current min/max-memory settings

 DECLARE @MinMem INT =
 (
   SELECT CONVERT(INT, c.value)
   FROM sys.configurations c
   WHERE c.[name] = 'min server memory (MB)'
 );

 DECLARE @MaxMem INT =
 (
   SELECT CONVERT(INT, c.value)
   FROM sys.configurations c
   WHERE c.[name] = 'max server memory (MB)'
 );


-- 3 calculate the new settings (min = 20%, max = 90% 
--   rounded down to nearest 1024)

 DECLARE @NewMinMem INT = @PhyMem / 100 * 20
 / 1024 * 1024;

 DECLARE @NewMaxMem INT = @PhyMem / 100 * 90 
 / 1024 * 1024;

 SELECT @PhyMem PhysicalMemory,
        @MinMem MinMemory,
        @NewMinMem NewMinMemory,
        @MaxMem MaxMemory,
        @NewMaxMem NewMaxMemory; -- save 4 rollback


-- 4 change min/max-memory settings

 EXEC sys.sp_configure N'show advanced options',N'1';
 RECONFIGURE WITH OVERRIDE;

   EXEC sys.sp_configure N'min server memory (MB)', 
   @NewMinMem;
   RECONFIGURE WITH OVERRIDE;

   EXEC sys.sp_configure N'max server memory (MB)', 
   @NewMaxMem;
   RECONFIGURE WITH OVERRIDE;

 EXEC sys.sp_configure N'show advanced options',N'0';
 RECONFIGURE WITH OVERRIDE;
 GO