April 17, 2012 at 4:13 am
Hi Experts,
Application team got error from their end that system database is overloaded.
We have run some initial set of counters under the assumption that its memory bottleneck. Please find the counter values below
User connections are below 100,CPU utilisation is below 30% most of the times
Page lookups : High
Free Pages: Low
Page Life expectancy: Low value most of the times
Database page: High
Buffer cache hit ratio: High
Pages\sec(Server):Low
But we are not able to come to a conslusion.
Please help
April 17, 2012 at 4:25 am
Chapter 1, chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 4:48 am
Thanks Gail.
Can you please guide me what exactly i need to do i ruled out CPU bottleneck as i found the CPU usage below 30% most of the times.
User connection is below 100
The system is 32bit with AWE enabled 6GB RAM and SQL Server uses 5GB.
April 17, 2012 at 5:05 am
Did you read the two chapters I indicated?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 5:40 am
I saw only one page and went through that.
"We spend much of our working life helping solve SQL Server-related performance problems, hands-on, during consulting, or on online forums. We've seen a few weird-and-wonderful issues but, mainly, it’s the same problems and misconceptions time-and-again. This is our attempt to describe, diagnose, and solve the most common problems with SQL Server 2005, 2008, and 2008 R2.
First, we explain a basic approach to troubleshooting, the essential tools, and how rare it is that a problem can be diagnosed by looking at a single data point. The art of troubleshooting SQL Server is the art of first gathering various pieces of information and then assembling the "puzzle" to reveal a complete picture of what is going on inside a server, and so the root of the problem. Next, we explore the areas in which problems arise with alarming regularity:
•High Disk I/O –RAID misconfiguration, inadequate I/O throughput, poor workload distribution, SAN issues, disk partition misalignment and more
•High CPU usage –insufficient memory, poorly written queries, inadequate indexing, inappropriate configuration option settings, and so on
•Memory mismanagement – the advent of 64-bit SQL Server removes the memory allocation "shackles" placed on its 32-bit predecessors, but issues arising from incorrect memory configuration are still common
•Missing indexes – arguably the number one cause of wasteful resource usage in SQL Server
•Blocking – caused mainly by poorly designed databases that lack proper keys and indexing, and applications that apply needlessly restrictive transaction isolation levels
•Deadlocking – covering the Bookmark Lookup deadlock, the Serializable Range Scan deadlock, the Cascading Constraint deadlock and more
•Full transaction logs – lack of log backups, hefty index maintenance operations, long running transaction, problems with replication and mirroring environments, and more.
•Accidentally-lost data – "oops, wrong database!" Let's hope you've got backups%hellip;
In each case, the book describes the most common problems, why they occur, and how they can be diagnosed using tools such as the Performance Monitor, Dynamic Management Views, server-side tracing, and more. Finally, it provides practical solutions for removing root causes, rather than "papering over the cracks".
The steps and techniques described are ones we use day-to-day to troubleshoot real SQL Server performance problems. With them, we hope you can solve performance problems quickly and accurately, and tame your unruly SQL Servers."
April 17, 2012 at 6:57 am
Ratheesh.K.Nair (4/17/2012)
I saw only one page and went through that.
Err, no, that's a full book of 350 pages. What you quoted was the blurb. Read chapters 1 and 4.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 7:45 am
Sorry Gail,
Cant download from here..
Will read those and get back to you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply