July 3, 2008 at 10:43 pm
Dear all,
Why both "Target Server Memory" & "Total Server Memory" in my server are 0 ?
I think it is totally unreasonable as it is a production server and its average CPU% is 22%.
Configuration : SQL Server 2005 Standard Edition 9.0.3054, Windows 2003 R2, Standard x64 Edition, physical memory : 16 GB.
Have you ever got such an abnormal Performance Monitor result before ?
July 4, 2008 at 12:10 am
Check some of the other SQL counters. I have seen cases before where all of the SQL-related perfmon counters read 0.
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
July 4, 2008 at 12:34 am
Yes, all memory related counters are zero :
"Time", "Available KBytes", "Connection Memory (KB)", "Granted Workspace Memory (KB)", "Optimizer Memory (KB)", "Target Server Memory (KB)", "Total Server Memory (KB)"
"07/04/2008 13:37:20.605","9043136","0","0","0","0","0"
"07/04/2008 13:37:35.605","9045300","0","0","0","0","0"
"07/04/2008 13:37:50.605","9045432","0","0","0","0","0"
"07/04/2008 13:38:05.605","9045728","0","0","0","0","0"
"07/04/2008 13:38:20.605","9045652","0","0","0","0","0"
"07/04/2008 13:38:35.605","9045628","0","0","0","0","0"
"07/04/2008 13:38:50.605","9045628","0","0","0","0","0"
"07/04/2008 13:39:05.605","9044676","0","0","0","0","0"
"07/04/2008 13:39:20.605","9044736","0","0","0","0","0"
July 4, 2008 at 12:36 am
Check some of the other SQL objects and counters.
I know there's a way to fix this, but I can't remember offhand.
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
July 4, 2008 at 12:39 am
They are OK, eg. % Processor Time, % Disk Time, Physical Average Disk Queue Length, all of them are normal.
July 4, 2008 at 12:43 am
Not those. Those are OS-level counters. What about other SQL-related counters?
(SQL Server:Access Methods, SQL Serveer: General Statistics, ....)
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
July 4, 2008 at 2:04 am
Yes, you are right, all SQL-related counters are also zero :
"Time", "Available KBytes", "Full Scans/sec", "Index Searches/sec",
"Logical Connections", "Logins/sec", "Transactions", "User Connections",
"Average Latch Wait Time (ms)", "Total Latch Wait Time (ms)",
"Connection Memory (KB)", "Granted Workspace Memory (KB)",
"Optimizer Memory (KB)", "Target Server Memory (KB)", "Total Server Memory (KB)"
"07/04/2008 15:13:53.864","9031184"," "," ","0"," ","0","0"," "," ","0","0","0","0","0"
"07/04/2008 15:14:08.864","9031692","0","0","0","0","0","0","0","0","0","0","0","0","0"
"07/04/2008 15:14:23.864","9031736","0","0","0","0","0","0","0","0","0","0","0","0","0"
"07/04/2008 15:14:38.864","9032092","0","0","0","0","0","0","0","0","0","0","0","0","0"
"07/04/2008 15:14:53.864","9031608","0","0","0","0","0","0","0","0","0","0","0","0","0"
"07/04/2008 15:15:08.864","9029080","0","0","0","0","0","0","0","0","0","0","0","0","0"
"07/04/2008 15:15:23.864","9024852","0","0","0","0","0","0","0","0","0","0","0","0","0"
July 4, 2008 at 3:02 am
Have a look at this kb article, see if it solves your problem
http://support.microsoft.com/kb/300956/en-us
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
July 5, 2008 at 12:45 am
gail cnt we reinstal sqlserver
July 5, 2008 at 7:25 am
Can you post the result of ::
select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
from master..sysperfinfo
where object_name = 'SQLServer:Memory Manager'
and counter_name in ('Target Server Memory (KB)', 'Total
Server Memory (KB)')
Also check the TOP Line in the Error Log and Post the info:
SQL Server error log, defaults to C:\Program Files\Microsoft SQL Server\MSSQL\LOG
top line should be something like:
2006-03-29 08:59:09.21 server Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Else GILA might be on the Right TRACK.. rather is on the right track.
Maninder
www.dbanation.com
July 5, 2008 at 10:39 am
maruf24 (7/5/2008)
gail cnt we reinstal sqlserver
That's a little extreme as a fix for missing counters.
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
July 11, 2008 at 12:32 am
i think for this we hav to execute a disk -ye or y command to get the perfect counter sorry i dnt no the full commnad
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply