Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: I think I just reproduced a SQL Memory Leak

    I have this query:


    SELECT 'SELECT '''+NAME+''', COUNT(*), 0 FROM ' + NAME FROM SYSOBJECTS WHERE TYPE = 'U'
    AND name LIKE 'foo_%'
    ORDER BY 1

    I then took the results, put it into Excel and then added a column before it with the numbers:
    1
    3
    5
    and filled down

    Then on a separate worksheet I put:
    2 UNION
    4 UNION
    6 UNION

    and then filled down to match the depth of my other sheet, copied the results over to the first tab and then sorted by column 1. Took column 2 and pasted it into a new Query window and ran the query while watching performance monitor. The memory slowly grew to about 98% so I killed the query (clicked stop in SSMS). Memory did not move down, so I stopped the Agent (no jobs running, new server but just habit), no change. So I killed any open connections, no change. Memory was still at 98% usage. Is there anything short of bouncing SQL Server service to reset the memory? Just asking for future use, if a checkpoint or something similar would reset the memory it would be good to know.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This is normal. In effect, SQL Server will take as much memory as it needs to do whatever you ask it to. It will hold that memory, until something else needs it.

    Try this. Install a second instance of SQL Server on the box. mark down what the PIDs are for each executable (Task Manager does not show instance name), then run your query on instance 1. Watch the memory go up on one of the PIDs. After you have had enough of that, run the query on instance 2. Any bets on what will happen?

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    I assume the memory shifts to a different PID!

    fwiw I was able to solve my problem using this dmv query:

    Code:
    SELECT o.name,
     ddps.row_count
    FROM sys.indexes AS i
     INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
     INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
     AND i.index_id = ddps.index_id
    WHERE i.index_id < 2
     AND o.is_ms_shipped = 0
    ORDER BY o.NAME

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by MCrowley View Post
    This is normal. In effect, SQL Server will take as much memory as it needs to do whatever you ask it to. It will hold that memory, until something else needs it.

    Try this. Install a second instance of SQL Server on the box. mark down what the PIDs are for each executable (Task Manager does not show instance name), then run your query on instance 1. Watch the memory go up on one of the PIDs. After you have had enough of that, run the query on instance 2. Any bets on what will happen?
    We had a problem on one of our servers that we had IIS and SQL running on the same box (not my decision to put them both on the server). And we had set SQL Memory (properties off of Server in SSMS) for the Min and Max to be like 11GB (out of a possible 15GB on the server). When I checked how much sqlserver.exe was using I think it was like 13.5 GB so we took the server out of rotation and bounced the service to bring the memory back to normal. There were other errors we were receiving which prompted us to investigate the memory usage. Anyway, I am sure this is not common, but was curious if there were any 'online' steps I could have taken instead of bouncing SQL Server service to free up the memory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •