SQL Server puts used data into the cache to speed up access to the data. If there are no other processes competing, SQL Server uses almost all of the memory until another process needs memory to free up memory. In the case of a small concurrency, there is no problem with setting up SQL Server's memory. However, in the case of high concurrency/large data volumes, this can result in a large number of page exchanges. The recommended practice is to set SQL Server to use 75% of the memory, such as 64G memory servers, we generally set the maximum memory of SQL 48G, and then observe the SQL buffer Cache hit Ratio, if less than 99%, and then increase the 2G memory until buffer Cache hit ratio higher than 99% query buffer Cache hit ratio code
SQL code SELECT (CAST (LTRIM (RTRIM (counter_name)) when ' Buffer Cache hits ratio ' then CAST (Cntr_value as Intege R) ELSE NULL END) as FLOAT)/cast (SUM (Case LTRIM (RTRIM (counter_name)) when ' Buffer cache hits ratio base ' then CAST (c Ntr_value as INTEGER) ELSE NULL END) as FLOAT) * as Buffercachehitratio from Sys.dm_os_performance_counters WHE RE LTRIM (RTRIM ([object_name])) like '%:buffer Manager ' and [counter_name] like ' Buffer Cache hit ratio% '
SQL Server memory settings
