November 6, 2016 at 2:57 pm
Hi,
I recently discovered that when I query a table (anyone I've tested) and the results are more than 1000 rows, Management Studio keeps hanging/lagging.
The more rows the result shows the longer the hangs are. And the behaviour starts after a thousand rows.
First I thought that it was something with SQL Server 2014, but then I uninstalled everything, reinstalled it and upgraded Management Studio to version 2016 (16.5), but the problem is still there.
I've tried disable Intellisense but it doesn't matter.
This behavior i new so it shouldn't be a hardware issue.
Any ideas what can cause this?
November 6, 2016 at 10:44 pm
alfred.h.hernandez (11/6/2016)
Hi,I recently discovered that when I query a table (anyone I've tested) and the results are more than 1000 rows, Management Studio keeps hanging/lagging.
The more rows the result shows the longer the hangs are. And the behaviour starts after a thousand rows.
First I thought that it was something with SQL Server 2014, but then I uninstalled everything, reinstalled it and upgraded Management Studio to version 2016 (16.5), but the problem is still there.
I've tried disable Intellisense but it doesn't matter.
This behavior i new so it shouldn't be a hardware issue.
Any ideas what can cause this?
Looks to me like the operating system is paging memory for processing/displaying the results, have you checked memory and disk IO when the SSMS is hanging?
😎
November 7, 2016 at 12:46 am
Thank you for your reply!
Unfortunally I can't see wierd with memory och I/O.
But I just saw that if I display the results to text instead of grid there is no problem. So it's the grid display that is causing the hangs/lagging.
November 7, 2016 at 12:57 am
alfred.h.hernandez (11/7/2016)
Thank you for your reply!Unfortunally I can't see wierd with memory och I/O.
But I just saw that if I display the results to text instead of grid there is no problem. So it's the grid display that is causing the hangs/lagging.
The grid display is quite inefficient but also the text results are limited in length which of course makes it lighter.
😎
I just ran this query and the grid display consumed 20Mb+ for only 10K rows.
DECLARE @SAMPLE_SIZE INT = 100000;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
DATEADD(SECOND,NM.N,GETDATE())
,REPLICATE('S',4000)
,REPLICATE('A',4000)
,REPLICATE('M',4000)
,REPLICATE('P',4000)
,REPLICATE('L',4000)
,REPLICATE('E',4000)
,REPLICATE('D',4000)
,REPLICATE('A',4000)
,REPLICATE('T',4000)
,REPLICATE('A',4000)
FROM NUMS NM;
November 7, 2016 at 1:09 am
When I run that query and display the results as text I goes on and on for over a minute, then I stopped. Displaying over 40K rows.
If I run it with display to grid i runs for 7 sec (with successfully execution) and then nothing is shown.
What I find very wierd is that it didn't use to be like this...:(
November 7, 2016 at 1:21 am
alfred.h.hernandez (11/7/2016)
When I run that query and display the results as text I goes on and on for over a minute, then I stopped. Displaying over 40K rows.If I run it with display to grid i runs for 7 sec (with successfully execution) and then nothing is shown.
What I find very wierd is that it didn't use to be like this...:(
SSMS has never been efficient in displaying the query results, in fact one disables it when doing tasks such as performance testing etc.
😎
Even if SSMS (default settings) caps each column at 256 characters in text mode, the output of the query will still be almost 3K characters p. row.
November 7, 2016 at 7:56 am
alfred.h.hernandez (11/7/2016)
What I find very wierd is that it didn't use to be like this...:(
Perhaps you have a memory chip that's gone bad, you've loaded something extra into memory that's taking a lot of room, you haven't rebooted your machine in an eternity, or SSMS has gone corrupt. It could also be that you have some strange data in a large datatype where trailing spaces have been preserved. Use DATALENGTH on the various columns to find that out.
If it's not something like the latter above, then the next thing I'd do is a reboot and see if that fixes things. If that doesn't fix it, max sure that nothing is consuming an unusual amount of memory. It that's not it, uninstall SSMS, reboot, reinstall.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2016 at 4:41 am
Thank you all for the tips and insights.
When I discovered this I started by unistall everything that had to do with SQL Server, and installed it again. All previous instances gone, and one new fresh one.
I then restore Adventureworks 2014 DW and ran a query on the table FactInternetSales. It contains absout 60 thousand rows.
The results shows almost instantly and the query is executed successfully. But then starts the lagging. It all dissapears if I close the tab.
I then created a new database with just one table with one rows, containing dates starting from 1990-01-01 to 2029-12-31. Thats when I realised that the lagging start at about 1000 rows and is only a problem when I display the result as a grid.
Maybe I have a bad memory chip, I don't know. I do know ive rebooted my computer and SSMS is working fine except the grid display.
I find this kind of annoying I think I might even format my hard drive and start with a clean slate. Haven't really decided yet since its kind of time consuming... 🙂
Regards,
Alfred
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply