Highlighted

Too sp_columns in activity of SQL Server

Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

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!!!

Views

504

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Too sp_columns in activity of SQL Server

Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

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!!!

Views

505

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 07, 2016 0
Advocate ,
Jul 07, 2016

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

Hi haxtbh,

this occurs on 50% of queries

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Advocate ,
Jul 07, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

I set only SQL:BatchCompleted. Here is what I see...Schermata 2016-07-07 alle 14.57.04 (2).jpg

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
LEGEND ,
Jul 07, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

Hi Wolf,

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

What are you thinking about this problem?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Advocate ,
Jul 07, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
LEGEND ,
Jul 07, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Advocate ,
Jul 07, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Advocate ,
Jul 07, 2016

Copy link to clipboard

Copied

What version of Coldfusion are you using?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
Community Beginner ,
Jul 07, 2016

Copy link to clipboard

Copied

Coldfusion 9 with latest update

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 07, 2016 0
BKBK LATEST
Adobe Community Professional ,
Jul 09, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 09, 2016 0