locked
SSMS Slow query / Out of memory RRS feed

  • Question

  • I was hoping the latest update would fix this but still happening

    I use to be able to quickly select top 1000 rows but now only about 10 or I get System.OutOfMemoryException, sometimes it succeeds but takes close to 4 minutes while

    SELECT TOP (100) [QBBridgeID]
          ,[QBBridgeIDfk]
          ,[QBXML]
          ,[DateRun]
          ,[QBFile]
          ,[PageName]
          ,[FirstResponseCode]
          ,[FirstMessageType]
          ,[RunStatus]
          ,[rowguid]
      FROM [QBBridge]
      order by [QBBridgeID] desc

    If I remove QBXML (ntext) from the query all queries run as fast as expected almost instantaneous

    While I can work around this issue it just began with last 2 Microsoft SQL Server Management Studio updates

    Currently installed

    Microsoft SQL Server Management Studio      13.0.15900.1
    Microsoft Analysis Services Client Tools      13.0.1700.281
    Microsoft Data Access Components (MDAC)      10.0.14393.0
    Microsoft MSXML      3.0 4.0 6.0
    Microsoft Internet Explorer      9.11.14393.0
    Microsoft .NET Framework      4.0.30319.42000
    Operating System      6.3.14393

    Monday, October 3, 2016 6:54 PM

Answers

  • Lowering SET TEXTSIZE under options -> Query Execution -> SQL Server -> General allows me to query now without error
    Monday, October 3, 2016 7:45 PM

All replies

  • How much memory does your server have? How much memory is allocated to SQL Server?

    Maybe one of you ntext columns got really big? Run this to see the size of the ntext column.

    SELECT TOP 1000 DATALENGTH(CAST(QBXML AS NVARCHAR(MAX)))
    FROM [QBBridge]
       order by [QBBridgeID] desc




    • Edited by TheBrenda Monday, October 3, 2016 7:09 PM edit
    Monday, October 3, 2016 7:08 PM
  • There is a larger one (79143895) while most are less than 10000, excluding the longer records I can query fine.

    There are no issues with all other programs interacting with the server which has 64 gigs ram / not sure about allocated would have to look further into.

    Logging onto the server itself which has an older copy of SSMS and all queries run fine even including larger xml fields.

    Running from my laptop which has 16 gigs of ram and using latest I get the failures. The lengths have been consistent in the past and did not start getting this issue until latest updates

    For now I'll query without including the xml field and if I need to look at a larger one will have to remotely use older version of SSMS

    Monday, October 3, 2016 7:30 PM
  • Lowering SET TEXTSIZE under options -> Query Execution -> SQL Server -> General allows me to query now without error
    Monday, October 3, 2016 7:45 PM