Archive

Archive for the ‘Best Practices’ Category

SQL Server Patches and Hotfix Notification

December 1, 2014 3 comments

I often get asked, if there is an easy way to get notifications for Patches and Hotfixes for SQL Server.  Generally I recommend people keep an eye on http://sqlserverbuilds.blogspot.com.  However I just came across another site that can be used, http://blogs.msdn.com/b/sqlreleaseservices/default.aspx.  Unlike the SQL Server Builds website, you can subscribe to RSS feed on MSDN site, this way whenever Microsoft releases update you can review it right away.  I really give kudos to who ever is maintain SQL Server Builds website; by far the most compressive and easy to use site for SQL Server build details.  So both sites in conjunction, I think are beneficial links to have in your favorites for patching and hotfix release information.

As Microsoft best practices we recommend you keep your servers up-to-date on latest service pack level.  It can be challenging in my scenarios; however, if you know what fixes are being released and what are their impact.  Then you can have an proactive discussion with business owner and management on why certain patch level is required.

For example, lets say the patch required because of memory leak, or corruption fix, etc.  These kind of patches you don’t want to wait for until you run into issue, we need to be proactive about addressing this to maximum the availability and uptime for service provided by SQL Server.

How to calculate Max Memory for SQL Server?

March 4, 2013 Leave a comment

SQL Server is designed to manage the memory on a server dynamically.  That is, it will acquire memory as needed and it will release it when asked to (by OS).  Its the good kid on the play ground, making sure now other processes on OS don’t starve.  Unlike 32-bit (x86), where the SQL Server process was capped to 1.7GB.  On 64-bit (x64) we do not have this limitation, as the virtual address space (VAS) for user applications is 8TB.  Therefore for SQL Servers running on x64, I often recommend to set Max Memory to control the amount of memory each instance can use.  Until SQL Server 2008 R2, the Max Memory stated the amount of memory assigned to Buffer Pool only.  However starting SQL 2012, it is almost all the memory SQL can consume.  (How memory architecture changed in SQL 2012 is beyond this post, maybe a future post?).  However even with new SQL 2012, we still should reserve some memory for SQL threads and external components.  So I am still using similar formula for all versions until I have reason to change it.

* Please note all calculations are for x64 bit system.

Max Memory - Main Formula

Where …

Max Physical Memory = Total Physical Memory Available on Server.  If you are calculating this for Clustered Environment, then I would use the Available Physical Memory from the Node with Least Amount of Memory.

Memory Reserved For OS = Really its not operating system I am referring to, any other applications running on server that is not SQL Server Engine.  I have used the guidelines below for initial configuration.  I will adjust this value for further monitoring and tuning.  Namely I will monitor Memory\Available Mbytes and Page File\% Usage(_Total).  On x64 I want Available Mbytes to be 1024MB or above and % Page File Usage <= 20%.

Physical Ram Ram Left for OS
4096MB (4GB) 1024MB (1GB)
8192MB (8GB) 2048MB (2GB)
16384MB (16GB) 3072MB (3GB)
24576MB (24GB) 4096MB (4GB)
32768MB (32GB) 5120MB (5GB)
40960MB+ (40GB+) 6144MB (6GB)

Memory Reserved for SQL Server: This formula is borrowed from really SQL 2005/2008 days, but I am still using it in SQL 2012 with positive results.

Max Memory - Memory Reserved for SQL OS

Where ….

# of Max Threads: This is referring to maximum number of worker threads SQL Server can create.  By default this value is “0”, meaning SQL calculates the maximum number based on number of CPUs as below.  CPUs in this context referrers to all visible CPU to operating system. For example, if you have 2 Physical CPUs with 4 Physical Cores each and Hyper-Threading; for the purpose of this formula the # Of CPU = (2 * 4 * 2) = 16.

Max Memory - Max Number of Worker Threads

This formula by all mean not absolute, please feel free to adjust it to your needs and environment.  After you configure SQL Server with Max Memory as listed above monitor your Available Memory (Memory\Available MBytes), Page File (Page File\% Usage (_Total), SQL Server Memory Pressure (SQLServer:Buffer Manager\Page Life Expectancy) and adjust the memory you reserve for the OS and SQLOS.

Lets work through an example …

  • Operating System: Windows 2008 R2 (x64)
  • # of Physical Sockets (CPUS): 4
  • # of Physical Cores per CPU: 6
  • Hyper-Threading Enabled: Yes
  • Number of SQL Server Instances: 2
  • Physical Ram Available: 16GB (16384MB)

First Step: Calculate the maximum number of worker threads…

# of Max Threads = 512 + {[(4*6*2)-4)*16} = 1216

Second Step: Memory Reserved for each SQL OS

SQLOS = 1216 Threads * 2MB = 2432MB

Third Step: Memory Reserved for Operating System

Memory Reserved for OS = 3072MB (Based on my reference table above)

Forth Step: Total Memory Reserved for all Instances

Total Memory Reserved for SQLOS = 2 * 2432 = 4864MB

Final Step: Putting it All together

Max Memory Per Instance = [16384MB (Physical) – 3072MB (Reserved for OS) – 4864MB (Reserved for SQLOS)] / 2 (Number of Instances)

Therefore Max Memory Per Instance = 4224MB

WOW? Even though server has 16GB, we are leaving SQL server with only 4.125GB per instance.  Please note this is extreme value, that is you have a SQL Server that is going to use all 1216 threads.  Therefore if you monitor your SQL Server and actually see your Available Memory is ALWAYS above 1024MB, maybe you want to give more to your SQL instances.  In this example lets suppose we know Instance 2 only requires 2048MB.  Then we can assign 6400MB to instance #1 without working about performance.

I hope I explained this topic a bit, generally troubleshooting performance issues with Memory is lots of work. But I usually start here for dealing with memory related issues.

PS Please note I only use MB in my calculations, because we set our max memory in SQL Server to MB not GB or KB.

July 23, 2013 – Small update for calculating the # of CPU formula in # of Max Threads section.

Granting Execute Permissions

November 10, 2008 Leave a comment

Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.

EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO

EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO

 SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
   INTO #NewPermissions
   FROM sysobjects
  WHERE xtype = 'P'
    AND ( name LIKE 'sp[_]%' OR
          name LIKE 'up[_]%')
ORDER BY name
GO

DECLARE @SQLStatement VARCHAR(512)

DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
  FROM #NewPermissions

OPEN SPPermissions

  FETCH NEXT
   FROM SPPermissions
   INTO @SQLStatement

  WHILE @@FETCH_STATUS = 0
  BEGIN

     PRINT 'Executing: ' + @SQLStatement
     EXEC (@SQLStatement)

     FETCH NEXT
      FROM SPPermissions
      INTO @SQLStatement

  END

CLOSE SPPermissions
DEALLOCATE SPPermissions
GO 
%d bloggers like this: