SQL Server Management Studio (SSMS) 2012 slow to show results in Grid only

Question

I generally use Microsoft SQL Server Management Studio 2012 (11.0.3000.0) with query results being displayed in a grid. As of 3 or 4 days ago query results have been quite slow, but only intermittently. As an example, a simple query such as

SELECT GETDATE() 

will take 7 seconds (according to SSMS) to display the current date/time. If I run the query with tracing/profiler turned on, I can see that the query is done executing almost immediately even though the SSMS timer continues to tick and no results are displayed for some time. The resulting date/time value is the same as the one that the trace/profiler shows for the "StarTime" column. Usually, the query will returns in 1 second or less, but if I execute 5 or 6 times, I will catch the issue and it will take a while to finish.

When this happens, my quad core laptop will spike to 25% CPU (full core being used for the entire time period) until the grid is drawn up.

I'm connecting to a local server (on my LAN) which is under very little load and nobody else in my company seems to be having any similar issues. I've installed SSMS 2014 to see if that helped (it didn't). Thinking it was an issue with drawing up the DataGrid itself, I installed .NET 4.6 which didn't help either.

When I run the results to Text they show up in under a second, every single time.

It doesn't seem to be a networking issue:

Reply from 192.168.10.47: bytes=32 time<1ms TTL=128

Ping statistics for 192.168.10.47:
    Packets: Sent = 24, Received = 24, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

Does anyone have any suggestions on things I should try?

I'm on Windows 7 (x64).

Answer 1

I was able to find the issue by running the query while monitoring the process with Process Monitor from the SysInternalSuite. When displaying query results to a grid, SQL Server Management Studio creates a .tmp file in C:\Users\username\AppData\Local\Temp\ named tmp####.tmp (where the #'s are randomly generated characters).

For whatever reason, my temp directory had been filled with 40,000+ these files (all of them empty) and Process Monitor was showing that when my query was not displaying results it was throwing thousands of "NAME COLLISION" errors trying to come up with a new name for the temp file it was trying to create.

Displaying Query Results to Text does not create a temp file which explained why that wasn't having an issue.

Deleting all the .tmp files from that temp directory fixed my issue immediately.

Hope this helps someone else.