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


ADVERTISEMENT

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

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 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

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

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 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 :: 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

Size Of Log Life

Dec 3, 2007

I am using SQL 2005. I have one very big database. The size of the DB is 1500986.88 MB and space available is 149359.48 MB. There are log files in this DB. The size of my log files are growing so big. The currently allocated space of one log file is 799874.50 MB and available free space is 38281.67 MB (4%). And the currently allocated space of another log file is 1500 MB and available free space for this log file is -760092.83 MB (-50672%)

I am not sure how come available free space for the 2nd log file becomes –ve. What does it mean? If I shrink log file, will I get any extra space? If not, what is the solution?

View 4 Replies View Related

SQL 2005 Screwed My Life

Sep 14, 2007

My one VB Exe used to connect SQL 2000 using windows SQL Server Driver. But same exe is giving problem of SQL Connection Timed out error in SQL 2005 - SP1.
As this exe refers 5 tables from database and insert as well as update the data. Also client pc and server pc's ping test is around 30 to 40 ms.
Is there any way where we can increase timed out level at SQL 2005.

One more thing which i noticed in 2005. its really very heavy software. even my IBM Xeon_346 server 3GB Dual CPU, 3 GB RAM also not able to handle.

Thanks in Advance
Dino


Dina Satam

View 14 Replies View Related

Get The Most Recent In A Group (was: For The Life Of Me I Can't Write ...)

Jul 3, 2007

Hi. I have a table called "Maxes" with three fields: Exercise_ID, weight, and date. This is for journaling my weightlifting progress. What I want my query to do is this:
Return just one record for each Exercise_ID, and only the one with the most recent date.

I tried this:

Code:

SELECT DISTINCT Maxes.Exercise_ID, Maxes.Date_Maxed, Maxes.[Max Weight]
FROM Maxes ORDER BY Maxes.Date_Maxed DESC;

but it doesn't quite work. Can someone show me how to do this?

View 4 Replies View Related

Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page

Nov 11, 2015

I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.

View 3 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

SQL SERVER 2000 End Of Life ? Support Issue.

Aug 2, 2007

Hello,

This is my first foray into SQL Server and i am coming from an Oracle background.

We are currently looking to upgrade a COTS package from a third party supplier. I would like to know is there any end of life announcement for SQL Server 2000 ? Is there an End of Support date announced for SQL Server 2000 ?

In Oracle there is a product life cycle announcement screen, I would like to know if there is a similar page in SQL SERVER and where ?

thanks


View 3 Replies View Related

SQL Server 2000/2005 Tutorial For Complex And Real Life Queries

Dec 13, 2007

I am not very good in queries. Could you please suggest me some web site/Tutorial/Artical where i can get Study Material for complex and real life queries. I know the syntexes, I just need to practice queries to enhance my skills

View 1 Replies View Related

Unable To Connect Remotelly To Mdf.life On Sql Server 2005 Express Edition

Apr 17, 2007

I have a c# application running on my machine. I want to point to a sql server data base express edition running on other machine. By default, from server explorer Ionly acces data base files of the local machine. I dont have acces to remote servers.

I used the file explorer and opened the mdf file on remote server. I tried to acces the mdf file remotely but i got excepcion.



My questios are:

- Is sql server 2005 express edition supporting remote connection to mdf data base file?

- In other words, I can remotelty conncect to mdf file from remote machine ?



So, I how can point my application to remote sql server 2005 express edition data base running on other machine?



Please, Help

View 2 Replies View Related

SQL 7 Poor Performance

Apr 4, 2000

Hi,
I have just upgrade my sql 6.5 to 7.0 version. This sql box (compaq proliant 5500) has 1 gig ram and 4 pentium pro processors and smart array controller. There are about 200 users hit on that server constantly. I configured this server according compaq white paper and Microsoft recommendation; however, I am still suffering huge performance hit. Then I setup performance monitor to see what is happening. What I see is all of 4 processors are at 70% processor time constantly. What I heard is sql 7.0 runs much better on pentium III processors. Is that true? Or you have any recommendations??


Help!!
Thanks!!

Jim Zhong

View 6 Replies View Related

Poor Performance

Jul 23, 2005

Hello Gurus,SQL Server 2000Windows 2003 Server, Standard Edition.Firstly I'm not a SQL server DBA but have a little experience withOracle 9i and Oracle Rdb.An application that I've inherited has started performing very veryslowly over the last few days, as far as I know there have been nomodifications or changes in the volume of data the db is holding. Thedatabase is in simple logging mode and it's updated twice per day fromand Oracle Rdb database.The SQL database is all in the primary filegroup and is sat on fourdisks (RAID mirrored) which form one logical disk of 128gb. Itconsists of approx 120 tables, each with a primary key and a number ofunique indicies per table.Apart from a twice a day update in the small hours the only access tothis database is read only. Users are connecting via the web andusing predefined Crystal reports to retrieve data.Questions then:Should this db have more than one filegroup and should I put theindicies in a different group? Is this relevant when the underlyingstorage is RAID?Should it be using mirrored RAID or should it be striped or should Isteer clear of RAID for a database?The indicies have not been rebuilt for some time (probably one year).Is this something I should be doing once per day/week as I do have thedowntime window?Should I be doing a database/table reorg at fairly regular intervals?I'm going to generate a workload file from SQL profiler and see what,in conjunction with the Index tuning wizard it suggests.Appreciate that poor performance & tuning is a huge subject and not anexact science but any tips or comments would be gratefully received.RegardsDave.

View 2 Replies View Related

Poor Performance

Sep 24, 2007

Hi

I have the following structure:

At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.

This is my problem:

The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.

I would like to know what problem can be cause this delay.

Thanks

View 3 Replies View Related

Poor Performance

Feb 4, 2008

Is there a way to improve this query?
The execution plan states that it's ,aking use of the Indexes as 'Clustered Index Seeks'
but the query takes 30 minutes to complete.
The Index statistics are also up to date!
If I use just an INNER JOIN the query completes in 2 seconds!
How can I make the LEFT OUTER JOIN more optimal?
SELECT A.MeterSerial AS 'PeaceMeter', B.MeterFrom, B.MeterTo,
CASE WHEN A.MeterSerial = B.MeterFrom
OR A.MeterSerial = B.MeterTo
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
THEN 'Y' ELSE 'N' END AS 'ComplexMeter', B.ComplexMeterType
FROM Peace.MeterData_DR1 A LEFT OUTER JOIN
(SELECT MeterFrom, MeterTo, ComplexMeterType
FROM Peace.ComplexMeters2) B ON A.MeterSerial = B.MeterFrom
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
WHERE A.MeterSerial != ''

View 3 Replies View Related

Poor Performance From Web Server

Dec 18, 2007

Hi,I am having a problem with one of my stored procedures in SQL Server 2005.  Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET. I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it.  More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig.  I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.If anyone has got any ideas on this then please let me know as I am completely stuck.  I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...Thanks in advance Tom    

View 1 Replies View Related

Is This A Poor Database Design?

Dec 29, 2004

Setup
I am using a simple aspx page to calculate a person’s tax filing status and capturing statistical information. Most of the items are drop down boxes or radio buttons but a few are checkboxes.

Question
My plan is to give each option in the check boxes its own column in the database this will lead to a table that is 70 columns wide. Is that to many? Most of the info will be very small like “yes� or “no�. I will be using a stored procedure to insert the info. This will be coming down a VPN

Goal
I would like to give the admin staff here real time access to the data via link tables and such. I would like there to be only one table so it will be easy for them to pars the info quickly without needing to join tables or write unions. Ideally this would get me out of the business of creating one off reports.

View 2 Replies View Related

How To Select The Poor Indexes?

Jul 28, 2004

hi,
I have a dba with more than 500 tables and I want to drop the indexes on the least selective columns. How do I know which are the poor indexes?
Please help.........thanks

View 1 Replies View Related

SSAS Poor Performance

Jul 9, 2007

Hi,

I created a cube in my development box, tested, performance is great, now if I try to deploy it into the production server from VS, the screen freezes without any error msg... after some time playing around with security I gave up and created a backup of the cube, and sent it over to the IT department( I have no direct control over the production server so they restored the cube ). Now with the cube in production, it was a matter of just processing it... well that didn't go well either... it would take forever from my development box to see results... then I tried to browse any dimension using the data already on the backup... a simple 4 values dimension would take 2-4 minutes to load on screen...

I can't understand why browsing the cube its so slow in the production server, IT admin even reported that when trying to browse locally it would be slow too... The server has 16gbs of RAM and its a dual processor, he didnt notice any lack of CPU or memory while browsing the cube...

Have you experienced this or can you help me troubleshoot whats going on?

View 1 Replies View Related

Poor Man's Trace Replay?

Apr 23, 2008

A while back I captured a large trace but only the SQL:BatchCompleted event and not the usual list of events so I'm not able to use the RML Utilities or Profiler to play it back. Really though all the queries are from the same login so there's no issue just playing them straight through I would think and using the stored duration to back calculate a start time and just play them all through. Are there any tools to do this or I am on my own having to whip something up?

View 6 Replies View Related

Poor Query Optimizing

Nov 4, 2007

Hi there,

I've got a SQL 2005 table(DataTable) with 5.5 million rows and a clustered index on an integer column. The column is a foreign key pointing to another table (LookupTable) that associates the integer with an nvarchar(50) column. Both integer and nvarchar(50) columns are in a clustered index.

I'm running equivalent select statements with slightly different where clauses, and am not seeing the performance similarities I'd expect.

select * from DataTable join LookupTable on DataTable.Integer = LookupTable.Integer

1. Setting the where clause to the nvarchar(50) value on the LookupTable takes 64 seconds.
2. Setting the where clause to Integer = (select integer from LookupTable where nvarchar(50) = 'mysearchstring') takes 61 seconds.
3. Setting the where clause to Integer = 526 takes 1 second.

Is this poor design in SQL, or is it a misconfiguration? I would have expected SQL to modify my query to essentially be #3 in all cases before touching the DataTable, which would cause all three versions to take 1 second.

Thanks,
Jason

View 2 Replies View Related

Poor Performance Due To LCK_M_S?

Nov 13, 2007

We just converted from SQL Server 2000 to SQL SERVER 2005 and it seems as though we are having trouble with our performance. Sets of queries that used to take about 15 seconds now take almost 2 minutes. We used a utility to find out what was taking so long and found that almost all of the wait time belonged to LCK_M_S. Does anyone have any suggestions?

Here is a snippet of code I used to test the speed against 2000:

declare @counter int
set @counter = 1

while @counter < 200
BEGIN
update UPR40500 set actindx = 96 where actindx = 96
set @counter = @counter+1
END

This takes 15 seconds when it used to take virtually no time at all.

View 3 Replies View Related

Poor Index Performance

Mar 24, 2007

Hey guys,

Having some trouble with indexes on sql server 2005. I'll explain it with a simplified example.
I have a customers table, and a sp to list customers :


create table Customers(
CusID int not null,
Name varchar(50) null,
Surname varchar(50) null,
CusNo int not null,
Deleted bit not null
)


create proc spCusLs (
@CusID int = null,
@Name varchar(50) = null,
@Surname varchar(50) = null,
@CusNo int = null
)
as

select
CusID,
Name,
Surname,
CusNo
from
Customers
where
Deleted = 0
and CusID <> 1000
and (@CusID is null or CusID = @CusID)
and (@CusNo is null or CusNo = @CusNo)
and (@Name is null or Name like @Name)
and (@Surname is null or Surname like @Surname)
order by
Name,
Surname


create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary



I've recently noticed that some tables, including 'Customers' don't have indexes except primary keys. And I have added indexes to "name", "surname" and "cusno" columns. This has dropped the number of IO reads. But the strange thing is; one time it works with name / surname searches like ('joh%' '%') but when CusNo is included, it does a full scan. And vice versa when the SP is recompiled using 'alter', works ok with CusNo, but not with name/surname. Recompile it, and it's reversed again. When run as a single query, the execution plan looks different.

What's happening? Perhaps something to do with statistics? This doesn't have a big payload on the server, but there are some other procs suffering from this on heavy queries, making server performance worse than before...

View 1 Replies View Related

Poor Performance On Oracle

Aug 3, 2006

Attention: To all SSIS gods like Donald Farmer, Jamie Thompson, Michael Entin

I am so very disappointed with the performance of SSIS on Oracle. When I am doing a simple lookup, it is caching the entire lookup table that is killing my performance. What is worst? When I try to change from Full to Partial or None caching mode, the component throws an error (x) sign.

I am also so frustrated with the way I have warnings all over the place. The warnings are saying code page value for the column cannot be determined. But the code page value is defaulted to 1252. I looked around everywhere to find what the damn code page value of my Oracle database is without any luck.

The damn package that I created takes 10 minutes to process 10,000 records ONLY which is slower than a legacy Cognos Decision Stream!!! Unacceptable!!! My lookup tables on an average have 200,000 records and I DO NOT WANT TO CACHE THEM all for 10,000 records. Something seems messed up!!!

PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!

View 4 Replies View Related

Linked Server = Poor Performance

Jan 10, 2006

Using a query through a linked server is giving tremendously reduced performance.
Is part of the problem linking from a SQL 2000 to a SQL 7.0 database?
Are there any other tips out there?
Thanks.

View 1 Replies View Related

Poor Peformance On Pulling Data

Nov 12, 2007

So this could be a long story.

Server1 = Previous "Test box", Windows Server 2003 Standard, SQL 2005 Standard SP1 with hotfixes, 2 processor 4 gig RAM
Server2 = New "Box for production", Windows Server 2003 Enterprise, SQL 2005 Standard SP1 with hotfixes, 2 Dual Core Processor, 16 gig Ram

This is for a datawarehouse environment. I pull a lot of data every night from an Oracle database. On Server1 the process took about an hour to pull all my data accross, on Server2 it takes more than 3 hours. I use SSIS packages to pull the data accross.
Even just running an OPENQUERY statement takes a lot longer. On Server1 returned about 150K record in 1.5 minutes and now on Server2 it only return 40K.
Have I missed a setting on my re-install. It the same SQL build number, SP_Configure has exactly the same settings and everything seems the same. I ftp'ed a file from the oracle box to each of my boxes and had very similar results, so it doesn't seem to be a network issue.

Any help would be much appreciated, is there anything that would cause SQL to pull data 3 times slower from an external datasource?

Thanks in advance guys,

Regards.

View 5 Replies View Related

Poor Sql Performance In Oledb Source?

Mar 31, 2008

i need to select data by using a very complex sql statement. when i use a ole db source componente and choose SQL command as data access mode the process never ends. but when i put the sql statement in an sql task component it works fine and fast. isn't an oledb source always based on an sql statement (select *)? so how is it possible that this component becomes so slow?

View 11 Replies View Related







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