Page Life Expectancy is 222 in SQL Server 2005

Chandra Mohan Kanithi
Chandra Mohan Kanithi used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
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 Administrator

Commented:
... and sizes of database/datafiles ...
Commented:
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/
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Chandra Mohan KanithiSenior Principal Consultant - Database

Author

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 Administrator

Commented:
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
IT Engineer
Distinguished Expert 2017
Commented:
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."

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial