Recently, we ran into some SQL Server performance issues. At some point, the maximum RAM cap that was set on our servers reset itself to the full amount of RAM that was available. The effect was that SQL Server ballooned to gigantic sizes and left less than 100MB of RAM for the rest of the OS. At that point, the server started paging which brings performance to a screeching halt. We quickly identified this, reset the cap and SQL Server went back to behaving as it should. This got me thinking though... what kind of stuff was sitting in that 7.9 gig sqlserver.exe process. I knew the answer was cached queries but I wanted to see those queries. That's where a blog entry from a guy named Glenn Berry comes in (http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!366.entry)... he had some very useful queries that allowed you view the cache and see how well it was being utilized. I quickly realized that I had a lot of SQL to cleanup... instead of using prepared SQL commands with ADO.Net which will allow the cache to be used effectively I had a lot of SQL that wasn't prepared which resulted in a cached entry that would never be reused unless the exact same query verbatim with criteria was used again. Preparing the statement allows the execution plan to be reused regardless of the queries parameters. To make a long story short, Glenn's queries came in so handy that I decided to wrap them in a nice GUI that developers could use (I figured DBA's would probably scoff at not just running the queries as queries, but I'm sure there are those out there that might find this useful also). I created a setup program for it and version 1 now available. I may include the source code with it in the future if there is a demand for it (It was written in Vb.Net for the Framework 2.0). Below I've included a download link to the setup application and a screenshot of the program (since the SQL isn't very readable from the grid, I made it so any cell you click on will open up in a built in text editor... I may just have this shell out to the default system text editor since mine has no bells and whistles). The grid data itself can also be saved to a flat file and the SQL that the utility uses based off of your parameters can be viewed through the Database menu. Note, by default this encrypts and saves your connection information locally, I put a menu option under the Database menu to disable this functionality for those that don't want to save passwords, even encrypted. I use a lot of trusted connections in development so I don't mind saving the connection string information since I hate typing it in everytime.
Code Plex link for source and install: http://sqlservercache.codeplex.com/