SQL Server Performance

sqlwb.exe High memory

Discussion in 'SQL Server 2005 General DBA Questions' started by lancer, May 1, 2007.

  1. lancer New Member

    Hi,

    When running a simple query with a large result set on a SQL 2005 server (via Management Studio), the sqlwb.exe uses large amounts of memory (presently consuming 1.5 Gb) especially when 'results to grid' is selected. Eventually the server runs out of virtual memory (as it runs out of disk space) and the query aborts.

    I have also experienced an issue when a small results set (say 20 rows) is returned and it takes an eternity to display these results (maybe 20 seconds to paint each row).

    Running SQL 2005 SP2 (9.00.3042.00)

    Has anyone experienced this issue? Is there a fix?
  2. MohammedU New Member

  3. madhuottapalam New Member

  4. satya Moderator

  5. lancer New Member

    Thanks Satya- No Certificates (and that certificate issue is much better with SP2).

    What I realised is that SSMC uses large temp files in your Windows profile (to keep XML grid results I'm guessing). My query is huge- returns over 100,000,000 rows and this requires 15Gb of space via a temp file in C:Documents and Settingsprofile nameLocal SettingsTemp1 directory. The server has obly 10Gb free on the C drive and as a result the query fails.

    Interesting behaviour- don't remember SQL 2000 behaving this way....
  6. satya Moderator

    Appreciate your feedback, I will have to see whether this is the case to reproduce at my end.
    You might try feedback this to MS Connect athttp://connect.microsoft.com fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  7. MohammedU New Member

    quote:Originally posted by lancer

    Thanks Satya- No Certificates (and that certificate issue is much better with SP2).

    What I realised is that SSMC uses large temp files in your Windows profile (to keep XML grid results I'm guessing). My query is huge- returns over 100,000,000 rows and this requires 15Gb of space via a temp file in C:Documents and Settingsprofile nameLocal SettingsTemp1 directory. The server has obly 10Gb free on the C drive and as a result the query fails.

    Interesting behaviour- don't remember SQL 2000 behaving this way....
    Thanks for sharing...
    But did you try the same in Query Analyser connecting to sql 2005?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. ndinakar Member

    The bigger question here is, why do you need to "see" 100 mill rows in the grid?

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  9. satya Moderator

    Thats a good point and confirm whether you are trying to achieve the display with query editor or SSMS --> open a table.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page