“Select *” is bad. Everyone knows it, but everyone still uses it. I use it. Most of the time it is fairly innocuous. No harm, no foul, right? But what about those precious milliseconds lost sending data across the network to client applications? That’s where you start to notice the effect of a Select *. This effect is amplified when we deal with tables with large data types such as XML and the new max data types.
How do you know what procedures are cached in SQL Server? Simple, just ask, and SQL Server will tell you. You can query the SQL Server dynamic management views to get a list of procedures in cache. In this example, I query sys.dm_exec_cached_plans and sys.dm_exec_sql_text: