Boston is not nearby for me to attend Aaron’s presentation on Management Studio Tips & Tricks. But I did enjoy reading his presentation deck, and that reminded me of an SSMS trap I fell into not too long ago. Here I’d like to bring it to your attention so that you don’t have to be as frustrated as I was. And I was mighty frustrated!
So if you use SQL Agent jobs, you must have gone through the following motion numerous times in SSMS Object Explorer:
Click on the plus sign to expand “SQL Server Agent”
Click on the plus sign to expand “Jobs”
Right click on a job to select “View History”
This works very well no matter how many jobs you have as long as you have a reasonable limit on the size of the job history log.
Now, one of these days when you are in a hurry, you may right click on “Jobs” under “SQL Server Agent” (instead of right clicking on a specific individual job), and select “View History” just as I did. What happens next depends on how large your job history log is.
In my case, the job history log was not excessively large on a per job basis. But we did have a lot of jobs. Unfortunately, the “View History” item on the “Job” rich click menu basically causes SSMS to load the entire job history log for all the jobs and sort them in chronological desc order. The end result in my case was that the workstation became extremely slow in responding to anything – mouse clicks, keyboard entries, etc. I even had trouble bringing up Task Manager. The workstation wasn’t dead, but nothing was responsive. I had to kill SSMS, an act that otherwise should be quick but required a great deal of patience on my part to accomplish.
As soon as SSMS was killed, the responsiveness of my workstation was restored and all was well.
Obviously, the feature was not designed or programmed properly. You just don’t want to populate user interface with a resultset all at once if that resultset can be very large.