Performance Enhancement and Optimization in SQL Server

This is one particular thing that at least once in the development life cycle we all encounter. In subsequent articles, we will go over one once by one over different ways by which we can enhance the performance of data driven websites, database, queries etc...

In this article, we will see how we can increase the memory of SQL Server that it can use. Usually, default setting is 2147483647 bytes i.e. approximately 2.15 GB that SQL Server can use, but it may be required to increase the ram utilization as some of the query operations can be memory intensive and we want to avoid any Timeouts.

To see the current setting, run below

select name,

            convert(int, minimum) as minimum,

            convert(int, maximum) as maximum,

            convert(int, isnull(value, value_in_use)) as config_value,

            convert(int, value_in_use) as run_value

        from sys.configurations where name like '%server memory%'

--Above query will show you current settings, if config_value and run value are 10 digit value then it is in default --settings and probably, your Sql Server will not utilize more than 2 GB of memory. In order to change that, run --below

 

sp_configure 'show advanced options', 1

RECONFIGURE

GO

--Awe enable must be set to true for change to take place

sp_configure 'awe enabled', 1

RECONFIGURE

GO

 

--This will set the memory utilization for 6 GB. If you want to increase only to 4 GB's then replace 6144 to 4096

 

sp_configure 'max server memory', 6144

RECONFIGURE