SQL 2012 :: Buffer Cache And PLE (Page Life Expectancy) Query

Aug 17, 2015

I'm getting an alert which states that both my Buffer Cache Hit Ratio and PLE are low on one of my SQL Servers though I'm not sure how to correctly check this.

I ran:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

Which gives me the Buffer Cache Hit Ratio, cntr_Value of 9 though its constantly dipping between 3-3000 and is never steady and I'm unsure if this is normal.

I also ran:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'

Which gives me the Page life expectancy of 209061.

If these values would cause concern and if this is a normal Buffer Cache Hit Ratio? It's constantly dropping from high or low from what I can see. These scripts were pulled from another forum and I'm assuming they're showing the correct values.

View 1 Replies


ADVERTISEMENT

SQL 2012 :: DB Engine Page Life Expectancy And Buffer Cache Hit Ratio Is Too Low

Sep 12, 2014

Our server administrator forwarded some messages from SCOM that indicate:

SQL DB Engine 2012 Page Life Expectancy and Buffer Cache Hit Ratio is too low

When I logged into the offending server, I could not find anything in the SQL Log File that indicates this.

I was wondering how did SCOM identify this issue - where in SQL Server would this have been reported to SCOM?

View 3 Replies View Related

SQL 2012 :: Page Life Expectancy Is Too Low?

Feb 18, 2014

I started receiving these alert messages, and after doing some re-search still can't figure out how to totally resolve it. From what I gather the value Microsoft stipulates 300 for PLE is not accurate if you running a 64 bit OS and dependent of the amount of RAM you allocate to SQL.

If I allocate 20 Gig of RAM to SQL, The PLE should not drop below 1500 - (PLE should be 300 for every 4 GB of RAM) (20/4)*300

During the course of the day it sometimes drops below 1500, so my question is how can I further see why and what query is causing this to happen???

I setup a monitoring job as mentioned by Steve Hood to capture results for me every 20 min.

View 6 Replies View Related

Page Life Expectancy=0

Feb 28, 2006

In my MSSQL server 2000 sp4, in performanece monito, buffer memory, pagelife expectancy is equal to 0.00000 (average for 10 sec, auto update). Ithink sth is wrong configured, but what?Marek

View 1 Replies View Related

Page Life Expectancy Issue

Mar 17, 2008

STUCK! I have a consistent PLE value of 0. The server has 8GB RAM, I/O-CPU counters under 30%, various DMV's results show the buffer and memory cache do not calculate up to it's AWE (32-bit) allocation 7.5GB, waits don't show any problems, no locks, no blocks.

There are improvements to indexes that have to be made but where can I identify the root cause to the low PLE? The same process runs x6 quicker on 2 other much lower spec SQL servers exact copies from a data prospective, however there PLE is very high and not production.

Any ideas?

View 1 Replies View Related

Page Life Expectancy Is POOR

Jan 16, 2008

Page Life Expectancy (PLE) is pretty bad on my server. PLE is hovering around 3 minutes "sometimes" but is usally around 20-30 seconds.

Total memory allocated to SQL ( a fixed amount ) is set at 3GB.

Of the total memory allocated, SQL Server is using 2.52GB ... so there is room if needed.

The Buffer Cache is sitting at 2.09GB with a hit rate around 99.8%.

The Procedure Cache is sitting at 378MB with a hit rate around 90.5%.


CPU is hovering around 10-20%


Free System Page Table Entries is LOW ... at 22343

Disc Queue Lengths spike quite often to above 5 and sometimes as high as 36. Usually sitting at .05 to 1.0 (and there are times when the DQLs are great and not measureable.

What I need to find out is how to get PLE above the recommended 5 minute mark???

Please let me know if there are any other items I need to note.

Thanks!

=========================================
Here are some hardware/Software/Implementation stats:
=========================================

SQL Server 2005 Standard w/ latest patch of 3152

Windows Sever 2003 R2 Standard w/ latest patches applied (says PAE is enabled in the System Properties.. General tab

4 Intel Xeon X5355 @266GHz

4GB RAM with 3GB dedicated to SQL Server via the /3GB switch


System Disc ( C: ) is 136GB (free space is 122GB) and is RAID10
Data/SQL Disc ( G: ) is 408GB (free space is 347GB) and is RAID10
The SQL files (MDFs/LDFs, TempDB, DB & TLog backups, SQL application and all that is SQL related) are all on the single array (G (** which I must note is NOT how I configured the SQL environment but aquired the setup when I started the position).

View 6 Replies View Related

DB Engine :: Page Life Expectancy Above 100K

May 27, 2015

I have few servers that are VM and different version of SQL Severs (2008 Express/Standard, 2012, or 2014 Enterprise) and seems all of them have same issue that when I am querying "sys.dm_os_performance_counters" for "[object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Page life expectancy'" I am getting very big number (Ex, above 100K or some servers 1M).Is this number seems fine or acceptable? 

View 6 Replies View Related

Setup And Upgrade :: Page Life Expectancy Less Than 300 Seconds

Jun 9, 2015

Every day, at same hour, my SQL Server is showing error of PLE less than 300 seconds.

So after this, the server works properly.

View 5 Replies View Related

DB Engine :: Page Life Expectancy Increases Dramatically From A Minute To Another

May 22, 2015

I measure PLE on my server and insert them every minute into a table. Now, when I look into the table I just dont know how to interpret the following data. I dont understand how is that possible. It's an sql server bug? or? How to interpret that data? 

View 10 Replies View Related

SQL 2012 :: Value Of Buffer Cache Hit Ratio

Jul 16, 2015

What is better values for Buffer Cache Hot Ratio in SQL Server 2012. Is the values changed from 2008r2 to 2012?

View 2 Replies View Related

SQL 2012 :: When Does Database Clear Buffer Cache

Sep 28, 2015

We are troubleshooting a performance problem and the test result is slow the 1st time but the subsequent runs are faster.. Logging out of application and log back in ( connecting to a new database session) did not clear the buffer cache as I thought it would.. When does the database clear the buffer cache? Is it not per database session?

I can issue CHECKPOINT and then run DBCC DROPCLEANBUFFERS to clear the buffers in the disk. But since we are testing from the application,do we need to run these commands via application code to clear buffer/per database session OR can we run these commands from a management studio session?

View 8 Replies View Related

SQL 2012 :: Buffer Cache Size Much Lower Than Max Memory Config And Low PLE?

May 22, 2014

I have a virtual server (VMware ESX) with 64GB RAM running a single instance of SQL 2012 SP1. The max memory config is set to 59392 (58GB).

The Page Life Expectancy for this server has been averaging well under 10 mins for the last few days, according to our monitoring.

I have been checking the amount of data in the buffer cache periodically during the day with the below query, which seems to show that there is never more than about 10GB of data at any one time, frequently dropping below 5GB:

SELECT COUNT(*) AS BufferPages,
CONVERT(decimal(10, 2), COUNT(*) / 128.0) AS BufferMB
FROM sys.dm_os_buffer_descriptorsWhy would the amount of cached data be so low (and cause so much churn)?

I am aware that other things will require some of that memory (plan cache etc.) but with Max Mem of 58GB, I would expect there to be a much higher amount of actual cached data at any one time. I did the same checks on another VM with the same amount of RAM/Max Mem setting, and there was 50GB of data in the cache, with PLE measured in hours.

View 9 Replies View Related

SQL 2012 :: Find Buffer Cache Usage By DB Objects Within Particular Database

Jun 22, 2015

I am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.

I am running the following query

select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb

[Code] ....

Following are the results:-

DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....

Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.

View 0 Replies View Related

DB Engine :: How To Use Particular Query In Buffer Cache Permanently

Aug 3, 2015

I've a spatial (GIS) Data which is used frequently insertion, updation.

5 lakh records insertion in daily basis. when I trying to generate reports last 3 days or one weak, it takes 20-30 minute.

very disappointing while playing with clients. how to boostup and perform fast.

I think as so once we set query plan in buffer permanently then i would be faster than ever.

View 4 Replies View Related

SQL2K - Clean Buffer Cache And Procedure Cache.

May 31, 2007

Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €œDBCC FLUSHPROCINDB (@dbid)€??
Is there a workaround for €œdbo€? to be able to flush procedure and data cache without being elevated to €œsysadmin€? server role?

PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.

View 1 Replies View Related

Buffer Cache

Sep 30, 1999

Help, have recently upgraded from 6.5 to 7.0 and have come across a problem with performance. The problem appears to relate to the buffer cache being flushed, the buffer cache hit ratio drops from 98% to 0% in a matter of a second. It then very slowly grows, then is flushed again, then increase slowly upto 30%.

Does any one have any ideas as to what would flush the buffer cache?

Any comments would be much appreciated - cheers

View 1 Replies View Related

Can't Clean Buffer Cache

May 16, 2007

Hi,



My problem is that I cannot completely clean buffer cache on SQL Server 2005 version 9.00.2047.00 (probably SP1).



Right after I run DBCC DROPCLEANBUFFERS in the context of my database (this is development server, and so far I am only the one who is working with a particular database), I run a script that quetries sys.dm_os_buffer_descriptors view also from the context of my database to make sure that the buffer cache is really clean. However it shows large number of entries totalling 42 MB.



I ran both DBCC an the script in the past too, and it always showed nothing in the results, that means that buffers were really clean. The reason why I am running this is for benchmarking of existing and new application.



Does anybody have any idea, suggestions, how to troubleshoot this issue ? I already closed all connections to this database, but rebooting the server is not an option since other people are also working on it.



Thanks

View 2 Replies View Related

Buffer Cache Hit Ratio...how To Measure?

Jan 2, 2004

Does anyone know how to measure the buffer cache hit ratio? I have been reading a lot about it but can't find this measurement in Performance Monitor.

Thanks

View 1 Replies View Related

100% Buffer Cache Hit Ratio Not Always Optimal?

Jun 19, 2008

I am wondering if 100% buffer cache hit ratio is considered not good in general?

Are there instances that it is actually bad and can contribute to server performance degradation?

Any thoughts on the topic most welcome :)


--------------------
keeping it simple...

View 11 Replies View Related

Troubleshooting SQL Buffer Cache Hit Ratio

Jul 23, 2005

This issue just happen recently. The buffer cache ratio went from > 90%to 50% and has slowly been climbing back up over 8 hours or so. Itscurrently @ 76%. Is this something I should take action on immediately?It seems to be coming back to normal...

View 1 Replies View Related

Estimation The Buffer Cache Hit Ratio

Sep 1, 2006

HiI have trouble with MSSQL2000 SP4 (without any hotfixes). During last twoweeks it start works anormally. After last optimalization (about few monthsago) it works good (fast, without blocks). Its buffer cache hit ratio wasabout 99.7-99.8. Last day it starts work slow, there was many blocks anddedlocks. There are no any queries, jobs and applications was added. Nowbuffer cache hit ratio oscilate about 95-98. I try update statistics andreindex some hard used tables, but there is no effect or effect is weryshort (after few hours problem return).Mayby somene know what it could be?Is it possible to estimate how each table (using DBCC SHOW_STATISTICS orDBCC SHOWCONTIG or others) how the table affect on total buffer cache hitratio?Marek---www.programowanieobiektowe.pl

View 1 Replies View Related

Performance Monitor -- Buffer Cache Object

Jul 2, 2007

Hello,
Does the performance counter Buffer cache object refer to Data cache and procedure cache or just refers to Data cache???

I was of the opinion that Buffer cache means data cache and Plan cache means procedure cache. Can someone throw some light???

Regards

Jaideep

View 4 Replies View Related

SQL 2012 :: Query Notifications For More Than Just Cache?

Jan 13, 2015

All the recommendations I see from Microsoft docs is to limit the use of Query Notifications (QNs) to notifying connected clients when changes to mostly-static reference or configuration data occur, and to keep the number of overall query forms in play and connected clients to a minimum. Any way regarding a more integral use of QNs and Service Broker from a web app to notify n-web servers (farm) of an update to data that could be updated concurrently and quite frequently, or with a system where the technique is used extensively with lots of different query forms?

View 0 Replies View Related

SQL Server 2012 :: Query Tuning With Each Run Of Fresh / Blank CACHE

Jun 4, 2015

I am using SQL Server 2012 Express.

I am doing performance tuning of SP/Query in Dev-Test environment.

I found that SQL Server caches plan between successive executions.

So if I test/execute SP 10 times, after 1st or 2nd execution, SQL server will pull-up plan-info from CACHE...Not from SQL SERVER Or Database...

Means i am not getting correct answer...

I found this 2 commands:

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

But they say that executing above command might interfere/bother other people executing other query/sp on this server.

They also say that: Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.

Part of query was using Dynamic-SQL executed with EXEC command.

I replaced that with SP_EXECUTESQL.

How can I start testing of each SP-run with Fresh/Blank CACHE ?

View 1 Replies View Related

SQL Server 2008 :: Identify Size Of Data Cache Allocated From Buffer Pool

Oct 20, 2011

How do i check the size of the datacache allocated from the buffer pool by sql server?

DMV or anything to show me the pool allocation sizes for the various pools in sql server i think i may be able to work from there.

View 9 Replies View Related

Query To Show Life Cycle Revenue?

Jan 2, 2007

I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table  and a sales table joined by custno.
SELECT     Customer.LastName, Sales.InvDate, Sales.AmtChargeFROM         Customer INNER JOIN                      Sales ON Customer.CustNo = Sales.CustNo
 The output I'd like is
CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount
Getting the first inv date seems straight forward
SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo
However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head.
 Can anyone point me in the right direction?
 

View 2 Replies View Related

Cache Only A Dropdown In A Page, With Data Returned By Database

Dec 12, 2006

Dear Friends,
I want your help to know the best way to cache the data of a dropdownbox returned by a SQL Query.(SELECT FIELD FROM DBO.TABLE)
This data returned by database is used in more than one dropdown on multiple pages. I want to cache only the dropdown control, not the entire page. Which is the best way to do it? The data isn't refreshed often, probably once per month.
 Could you tell me the best way? 
THANKS 

View 4 Replies View Related

SQL 2012 :: Log Buffer Waits

Sep 1, 2015

Last Sunday on our Primary server I saw some blocking and the First spid that blocked everything was waiting on LogBuffer wait. At that time the server was not running hot and our DB is on SSD's. There was no memory pressure(1 TB of memory on the server) It is strange why we should get LogBuffer wait when we are running on the fastest disks possible and there was not much action going on the DB.

View 4 Replies View Related

SQL 2012 :: Backups And Buffer Pool

Jan 23, 2015

I am sure I have seen in the past in a monitoring tool that PLE drops off to 0 whenever we do a backup. I was doing some reading around this however and found something that said backups use a different portion of memory external to the buffer pool (minmax settings).

Is this correct and how can I tell how much memory will be required for a backup?

View 2 Replies View Related

SQL Server 2012 :: Force Page Footer At Bottom Of The Page

Mar 12, 2015

I have a report with tablix. when tablix returns no rows Footer is coming all the way up . How to display the footer at the bottom of the page all time.

View 0 Replies View Related

SQL 2012 :: Why Does SHRINKFILE Page Latch On A Page In Different File

Sep 28, 2015

I am running a DBCC SHRINKFILE on "FILE1" of a database (it has fileid = 1)...intent is to remove 70GB of file space:

DBCC SHRINKFILE (N'FILE1' , 400000).

For the SPID that's doing the shrink, In activity monitor you can see:

Waittype: PAGEIOLATCH_EX on resource: 9:3:15411328
(the DB is dbid=9)

But why does it need a page from fileid=3? Are there page dependencies between files that prevent moving a page within a given file? Does it need that fileid3 page to come along?

Its just sitting there in the SUSPENDED state for the last hour....I am going to leave it another 5 hours or so before cancelling.

The dm_exec_requests has an estimated percent complete at 83% and holding....not sure if I can believe that.

View 6 Replies View Related

SQL 2012 :: Plans Not Staying In Proc Cache

Feb 25, 2015

On one server I'm having an issue with and it having such a small procedure cache.

Server has 60GB of RAM assigned to its min and max server memory settings, optimise for ad hoc workloads is disabled.

Procedure cache at the moment on the server is 2.41MB with only 6 objects in side all related to mssqlsystemresource database, I can see stuff dropping in for user databases, but as soon as the proc has finished the plan is removed from the cache.

Buffer cache is in the 17GB mark, free pages around the 42GB mark so around 60GB used with a bit in stolen pages, but no proc cache.

All other servers in the environment are reporting over 8GB of proc cache in use which is more healthy.

Using Spotlight to monitor all of this.

Whats wrong with this one server and it not keeping the plans in cache.

View 9 Replies View Related

SSDT Error - Size Necessary To Buffer XML Content Exceeded Buffer Quota

Apr 18, 2012

I encountered the following error while attempting to preview an RDL report I was developing in VS2010 using SSDT:"The size necessary to buffer the XML content exceeded the buffer quota"

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved