Page Life Expectancy is 222 in SQL Server 2005

Hi,

My Application users getting slowness for web search queries. I have checked the server and found no blocking issues.

No high cpu utilization processes and Disk IO utilization is normal.

Server Configuration:
Version : MS SQL Server 2005
Dedicated LUNS for MDF, NDF , INDEX and LDF Files.
Memory : 49GB
Processors: 24.
Integrity Check, Update stats and Re-indexing jobs configures on weekly bases and Latest status is successful

As per my observation found PLE is 222 and Users getting the following error also.

"Could not continue scan with NOLOCk due to data movement"

Please provide steps for trouble shooting this issue.

Thanks,
Chandra
Chandra Mohan KanithiSenior Principal Consultant - DatabaseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to provide us more information.

What operation was been performed when the error occur?
Which MSSQL edition are you using?
What's the server configuration?
How much memory is configured to the SQL Server instance?
There's any other application installed in the server beside MSSQL?
How many SQL Server instances are installed in the server?
If more than one, which versions are they?
...
David ToddSenior Database AdministratorCommented:
... and sizes of database/datafiles ...
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well PLE of 222 is an indication that you have a lot of data that is transient in the buffer pool.  

Troubleshooting for the NOLOCK error can start here : https://www.mssqltips.com/sqlservertip/3289/error-601-could-not-continue-scan-with-nolock-due-to-sql-server-data-movement/
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Chandra Mohan KanithiSenior Principal Consultant - DatabaseAuthor Commented:
Edition: Microsoft SQL Server 2005 - 9.00.5000.00 (X64)   Dec 10 2010 10:38:40   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

max degree of parallelism: Config Value:1 Run Value:1
Max Memory : 44032
Min Memory : 44302
Total Memory: 49142
Dedicated Database Server and Single Instance

Database Size : 3.1 TB

Please review above details and waiting for response.

Thanks,
Chandra
David ToddSenior Database AdministratorCommented:
Hi,

Some simple performance rules of thumb - scans are bad, seeks are good.

When things are big, they need to be well tuned to work acceptably. When things are small then almost anything works brilliantly.

My questions are why is DOP set to 1? When was it last tested?

Are you doing index maintenance? When was it done last?

Regards
  David
Vitor MontalvãoMSSQL Senior EngineerCommented:
Focusing only on the error 601 you should review your queries.
I saw many times people using NOLOCK hint without knowing the impact that it may have. They just want to avoid locks but can't the reverse side of this option.

"Action
This error aborts the query. Either resubmit the query or remove the NOLOCK locking hint."

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.