Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Too sp_columns in activity of SQL Server

Explorer ,
Jul 07, 2016 Jul 07, 2016

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

Salvatore Cerruto
1.4K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 07, 2016 Jul 07, 2016

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?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 07, 2016 Jul 07, 2016

Hi haxtbh,

this occurs on 50% of queries

Salvatore Cerruto
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 07, 2016 Jul 07, 2016

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 07, 2016 Jul 07, 2016

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

Salvatore Cerruto
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 07, 2016 Jul 07, 2016

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,

^_^

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 07, 2016 Jul 07, 2016

Hi Wolf,

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

What are you thinking about this problem?

Salvatore Cerruto
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 07, 2016 Jul 07, 2016

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 07, 2016 Jul 07, 2016

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,

^_^

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

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 07, 2016 Jul 07, 2016

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>

Salvatore Cerruto
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 07, 2016 Jul 07, 2016

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

Salvatore Cerruto
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 07, 2016 Jul 07, 2016

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.
Salvatore Cerruto
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 07, 2016 Jul 07, 2016

What version of Coldfusion are you using?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 07, 2016 Jul 07, 2016

Coldfusion 9 with latest update

Salvatore Cerruto
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 09, 2016 Jul 09, 2016
LATEST

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

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