Who's bad?

"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.

What's stopping you?

So what's holding people back from fixing these issues even when they know they exist? Well, applications have grown so complex that developers and dba's are overwhelmed by the prospect of changing hundreds or even thousands of stored procedures. It would take a herculean effort to fix every procedure in a large, complex database. That's where the age-old concept of KISS comes in to play. Keep It Simple, Stupid!!

Have you KISS'ed your database today?

Ironically, the way to keep it simple is a little complex. As Scrooge McDuck said, "work smarter, not harder". I can come up with a short list of procedures to look at by querying the query plans in cache and finding the ones with Select * statements. Not only will my list be shorter, the work I do will have a bigger impact as it will be on procedures that I know are being actively used.

As a comparison I did a search on one of my large active databases to see how many procedures have a pattern matching Select * and how many of them are in the procedure cache. There were 72 procedures in total found, but only 1 of them was found in cache. Now, instead of having to spend hours slogging through procedures, I can spend some real quality time with a single procedure doing some meaningful analysis on whether or not the Select * can be alleviated.

The bread and butter:

My approach here is to find all of the procedures in cache that contain Select statements by shredding the query plan XML nodes to find the statement types of each batch. I apply a pattern search on the text of the individual Select statements that have an asterisk ( * ) after the Select keyword.

Declare @QuerySearch nvarchar(10)

Set @QuerySearch = '%Select%*%';

with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
Select object_name(st.objectid, st.dbid) As ProcedureName
From sys.dm_exec_cached_plans cp
Cross Apply sys.dm_exec_query_plan(cp.plan_handle) qp
Cross Apply qp.query_plan.nodes(N'//sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"]') As sel(StmtSimple)
Cross Apply sys.dm_exec_sql_text(cp.plan_handle) st
Where PatIndex(@QuerySearch, sel.StmtSimple.value('@StatementText', 'nvarchar(max)')) > 0
And cp.objtype = 'Proc'
And qp.dbid = db_id();