Setup and Shrink SQL Server Memory

 In some cases, we don't know how our database program/application has developed. Sometime, the developer don't consider how to create/make query for the best hardware performance. 

For example, we use an windows application/program than use Sql Server as database storage. When we check our server, it looks a higher memory usages, what will we do?

First thing that you should know is, every program or service must consume a lot of memory. You can check through windows task manager, how much memory for each program/service.

In this case, assume the sqlserv.exe process consumes highest server memory (RAM), so we can adjust memory maximum / allocation for the sql server database service/process with this step:

1. Log in to SQL Server Management Studio

2. On the Server Instance, right click, select Properties

 

3. Set the maximum memory according to memory capacity of the server

 4. We can set to 75% from maximum installed server memory (in MB), ie. 12,000 MB

 5. If while this processes the memory usages higher than 12,000 MB, then this process will automatic shrink / adjust to 12,000 MB.

SQL Server Database Memory Usages Check

If we check performance of our windows server, and if we saw that the memory consumption is higher,  70% upper, check the sql server memory usages, by:

  * Open Task Manager

  * Open Tab Processes

  * Find sqlservr.exe process, by sort it by memory
  * If you see there is top highest memory usages, please check through sql server management studio
 
Below some queries to check or monitor sql server database memory usages:

To get total memory usages:
    SELECT  
    (physical_memory_in_use_kb/1024) AS Mem_usedby_Sqlserver_MB,  
    (locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
    (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
    process_physical_memory_low,  
    process_virtual_memory_low  
    FROM sys.dm_os_process_memory;  
 

 

To get status memory usages:
    select
          total_physical_memory_kb/1024 AS total_physical_mem_mb,
          available_physical_memory_kb/1024 AS available_physical_mem_mb,
          total_page_file_kb/1024 AS total_page_file_mb,
          available_page_file_kb/1024 AS available_page_file_mb,
          100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3)))
          AS 'Percent_Used',
          system_memory_state_desc
    from  sys.dm_os_sys_memory;

To get memory usages on each database:
    DECLARE @tot_buffer INT;
    SELECT  @tot_buffer = cntr_value
    FROM   sys.dm_os_performance_counters
    WHERE  RTRIM([object_name]) LIKE '%Buffer Manager'
           AND counter_name = 'Database Pages';
    
    ;WITH DBBuffer AS
    (
    SELECT  database_id,
            COUNT_BIG(*) AS db_buffer_pages,
            SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
    FROM    sys.dm_os_buffer_descriptors
    GROUP BY database_id
    )
    SELECT
           CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS 'db_name',
           db_buffer_pages AS 'db_buffer_pages',
           db_buffer_pages / 128 AS 'db_buffer_Used_MB',
           [mbempty] AS 'db_buffer_Free_MB',
           CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @tot_buffer) AS 'db_buffer_percent'
    FROM   DBBuffer
    ORDER BY db_buffer_Used_MB DESC;        

Electric Control - Help you to Calculate, Estimate and Monitor your Electric Power Usages & Bills

 Download Android Apps - Electric Control : 

https://play.google.com/store/apps/details?id=com.yoslines.meteranku

Electric Control (EC) will help you Calculate, Estimate and Monitor the Electric Power Consumption and Bill of some electrical devices (home appliances) in your home or office from your android phone.  

In addition, with Electric Control, you can record all electrical devices equipped with power data, other information like brand, location, life time, and how many days of life in a month.  

Electric Control will calculate the amount of power in Kwh and with data rates per Kwh, will be able to calculate how much the operational costs of a device.  

You can also check how much power is at present time and how many active devices. Electric Control will also display how much devices at an location.

With the estimation usages of power, you will able to estimate how much cost and money you have to provide for next days.

You should try the electric control app to manage, monitor and estimate your electric power usage.

EC Team
Electric Power Usage Efficiency

#electricpower #electriccost #efficiency #electricpowercalculator #electriccostcalculator #howtocalculateelectricpowercost

 

Download Electric Control : 

https://play.google.com/store/apps/details?id=com.yoslines.meteranku

#electric control #kontrol listrik #hemat listrik