Why both "Target Server Memory" & "Total Server Memory" 0 ?

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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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"

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • They are OK, eg. % Processor Time, % Disk Time, Physical Average Disk Queue Length, all of them are normal.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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"

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • gail cnt we reinstal sqlserver

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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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