Too sp_columns in activity of SQL Server

[xfreeman89x]

Hi all,

I started profiler in my SQL Server 2008 R2 and I see a lot of calls to sp_columns. This is a procedure that retrieve table informations. I guess that ColdFusion is calling this function when I execute cfquery. Is there any way to reduce this overload?

Thanks!!!

[haxtbh]

What makes you think it is Coldfusion? The call to sp_columns will only appear if something is calling it , and Coldfusion wont do this without someone writing code to specifically call it.

How often is it happening and why do you think its overloading things?

[xfreeman89x]

Hi haxtbh,

this occurs on 50% of queries

[haxtbh]

What filters do you have set on the profiler and how are you seeing it on the profiler?

Are you sure that the queries being run are not just other stored procs that call sp_columns or even hard coded into the CFML itself?

Running the profiler on two of my servers never shows any calls to sp_columns.

[xfreeman89x]

I set only SQL:BatchCompleted. Here is what I see...

[WolfShade]

Which of the following examples represents a majority of the SQL contained within your queries?

SELECT userID, userName, lastLogin FROM tableUsers

SELECT * FROM tableUsers

V/r,

^_^

[xfreeman89x]

Hi Wolf,

the first example, I'd say, but it continue with where clause.

What are you thinking about this problem?

[haxtbh]

I assume the table names it is using to lookup are names you are familiar with? Those tables exist?

[WolfShade]

sp_columns makes me think that it's looking up column names for tables (the image you provided also makes me think this.)

If you use SELECT *, then the database has to refer to a system table in order to know what column names to select from.  If you provide the column names in the SQL, then this step is skipped.  You indicated that the sp_columns (stored procedure?) was being called in about 50% of your queries, so I assumed that approximately half of your queries use the asterisk instead of column names.

V/r,

^_^

[haxtbh]

Neither Coldfusion nor SQL would need to run a SP to find out column names. Running * will just parse on the SQL server and the server itself will figure out what columns it needs.

We have tons of queries with and without * and never have I seen the sp_columns.

It has to be called directly from a stored proc being called or be actually written in the Coldfusion code.

For example, SPID 70 & 451 in the list you provided shows no SELECT * but shows two calls to sp_columns. Have you checked this code in the codebase to see if there is a stored proc related to it or if it explicitly calls for sp_columns after.

Do you have any form of maintenance task scheduled to run that might cause this proc to be called?

Alternatively it could potentially be an attempt on compromising the database by a third party.

[xfreeman89x]

I don't know why but the problem seems resolved when I use semicolon at end of the query.

<cfquery name="abc" datasource="test">

     select a, b, c

     from table;

</cfquery>

[xfreeman89x]

When I omit semicolon then some field appears in the sql profiler as sp_columns

[xfreeman89x]

Furthermore, if I write a select with top 10 statemente in profiler appears this row:

sp_columns @table_name = richieste, @table_owner = null, @table_qualifier = null, @column_name = 10

Inserting semicolon magically sp_columns disappered.

[haxtbh]

What version of Coldfusion are you using?

[xfreeman89x]

Coldfusion 9 with latest update

[BKBK]

Could it just be that SQL Server executes the procedure sp_columns when you run the profiler?