Managing Memory


Let us discuss on some on some aspects of performance tuning and optimization of sql queries. The following terms are quiet relevant in this regard. Although we do not always think about these counters unlesss we are seriously having issues with the sql server memory or the performance of a strong query.
Buffer Cache Hit Ratio
 The buffer cache is a syndicate or collection of buffer pages. The data pages occupies the biggest part of the memory and pages are mostly read into this cache. In OLTP systems this counter value should be as high as possible. Ideally, 99 percent in most cases. It is exactly the reverse in OLAP or DW environment where cache requirement although is there but as most of it is bulk data (billions of data records), cache requirement is less. Also frequency of requests coming in can be much lower than e.g. transactions in bank. If Buffer cache hit ratio value is low that means, only a few requests could be served out of the buffer cache, with the rest of the requests being served from disk.
 If this truly happens, then definitely the SQL Server is warming up or the memory requirement of the buffer cache is more than the maximum memory available.
Lazy Writes/Sec
The Lazy writes/sec counter records the number of buffers written each second by the buffer manager's lazy write process. In this process the aged buffers are removed from the memory and the memory is freed up for other usage. The Lazy writes/sec values should consistently be less than 20 for the average system. A dirty, aged buffer is one that has all the changes and should be written to the disk. Higher values on this counter possibly indicate I/O issues or even memory problems.
Page Life Expectancy
 Page Life Expectancy indicates how long a page will stay in the buffer syndicate without being referenced. A low number for this counter means that pages are being removed from the buffer. This lowers the efficiency of the cache and indicates the possibility of memory stress. On reporting systems, as opposed to OLTP systems, this number may remain at a lower value since more data is accessed from reporting systems. A reasonable value to expect to see here is 250 - 300 seconds or more.
 Checkpoint Pages/Sec
 The Checkpoint Pages/sec counter represents the number of pages that are moved to the disk by the checkpoint operation. These numbers should be low e.g. less than 30 per second for most systems. A higher number means more pages are being marked as dirty in the cache. When a page is modified in the memory it gets treated as a dirty page and is written into the disk by the next checkpoint operation. I/O problems might be a result of high values on this counter (which indicates a larger number of writes occurring within the system).


You can refer to the following thread or read more on how to fetch these values/counters

Comments

Post a Comment

Popular posts from this blog

Automatic Replenishment ~ Warehouse Management (WM) in SAP

Image
This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock. We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.   Step 1 : Material Master has to be maintained with Warehouse Views. Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained. Configuration Changes: Step 2 : Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types. Movement type 320 is configured as the following
Read more

Teradata - Month End Date

Sql Server: Calculating the last day of the month in SQL Server is pretty simple. Run the following piece of code and its done. SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) CurrentMonthEnd_Day ResultSet: CurrentMonthEnd_Day ———————– 2010-08-31 23:59:59.000 Teradata: However, with reference to Teradata SQL, in our day to day queries in a datawarehouse environment very frequently we need to play with the month end dates that are mostly kept in the date dimension table with one field, i.e. the month end date field. You might require to fetch the month end date which is 4 years back. usually the simplest way in this case will be to calculate 48 months back from the current month end date (in case the monthe end date that long is not stored into the database). This is fair. The piece of code required to do is simple. SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE),-48)) Result: 2008/08/31 Now trying this simple thing will not work if its a leap year a
Read more

Cross Domain Webix Post

Recently, we were working with Spring Boot and Webix framework using Ajax. We had quite some trouble to POST data back to the Server with Spring Boot.  Error: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:7100' is therefore not allowed access. We realized the problem was because of our AJAX cross-domain requests that are forbidden by default by the same-origin security policy. Since browsers need to “preflight” any asynchronous or rather HTTP request methods that modify data and also fetch an “approval” the problem had to be addressed both way i.e. Server side and Browser i)                     We had to enable the CORS assertion in Spring Boot i.e. @CrossOrigin(*) ii)                    Enable Cross-origin resource sharing (CORS) from the Chrome browser. You need to add an extension or CORS enabler from the Chrome Web Store for this. Thereafter using the following code the JSON data wa
Read more