SSMS Causes High CPU Utilization
Question
Monday, August 29, 2011 1:02 PM
I am writing a stored procedure and when I open the .sql file containing the code for the procedure, the CPU utilization for SSMS hits the ceiling and essentially locks up SSMS. Closing the query window makes this go away. The stored proc has not yet been created. Looking at profiler during this problem is unrevealing. Anyone have any thoughts on this?
All replies (6)
Monday, August 29, 2011 2:26 PM âś…Answered
As a first test I would try from an SSMS whiich is as updated as possible (as new version/service pack as possible). Might be some bug/inefficient code in SSMS.
I would check of the database has autoclose turned on. This is just a wild giess and to be honest I doubt this would change things as SSMS sould have its connection open and not open/close connection all the time.
Is your disk I/O on the server or client? If it currently is the same machine, then I would test running SSMS on a different machine. Will not tell you why it is slow, but it will tell you where the resources are used. Finally, do you have lots and lots of objects in the database? If so, I can imagine Intellisense not comping (reading all that meta-data and cachine it) so turning off would probably be the best ting...
Tibor Karaszi, SQL Server MVP | web | blog
Monday, August 29, 2011 1:19 PM
Intellisense?Tibor Karaszi, SQL Server MVP | web | blog
Monday, August 29, 2011 1:52 PM
I don't think so. Turning intellisense off didn't have any effect. A few more details - the stored proce has not yet been created becase the coding is incomplete. While the CPU is running away the disk I/O light is flashing indicating read write activity.
I am running SQL Server Developer Edition 2008 R2.
Monday, August 29, 2011 2:00 PM
I forgot to bounce SSMS agter turning off intellisense so I did that and now the problem is gone. So why would intellisense cause this problem and is there anything I can do t correct it short of leaving intellisense turned off?
Friday, September 9, 2011 1:06 PM | 5 votes
As it has turned out, there is a setting under Tools-Options-Text Editor-Transact SQL-Intellisense called maximum script size. The default was 2MB increasing this to 5 MB has solved the issue.
Friday, May 19, 2017 3:41 PM
5/19/17 - sorry to say, didn't help with my issue...
Wayne Erfling (aka Cornan The Iowan)