sp_statistics and sp_columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sp_statistics and sp_columns

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-01-05 : 15:32:46

Hi,

I think my question is more related to dba instead of developer.

On anylysing a recent trace log, I found out these two system SPs have been called out quite a bit from an application server box.

exec sp_statistics N'table_name', N'dbo', NULL, N'%', N'N', N'Q'
exec sp_columns N'table_name', NULL, NULL, NULL

The programmers do not remember using them in that app, and did not find them in the source code. (All of the current programmers, including me, don't even know how to use them

)

What could cause them to run? Who is in need of those statistic data?
Will running them help the performance of the app/app server or sql server?

Thanks for any help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-05 : 16:15:41

Are you sure that these aren't from someone using EM or QA on the application server? Could you post the entire two rows of the trace?

Tara Kizer
aka tduggan

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-01-05 : 17:59:07

Here they are. Thanks!

TextData/duration_in_seconds/LoginName/NTUSerName/HostName/ApplicationName/SPID/StartTime/EndTime
exec sp_statistics N'stsJobTracking', N'dbo', NULL, N'%', N'N', N'Q' .42 system NULL STOBUS2 VisiBar Server 60 2006-01-05 09:46:51.797 2006-01-05 09:46:52.220
exec sp_columns N'stsLocTypes', NULL, NULL, NULL .19 system NULL STOBUS2 VisiBar Server 60 2006-01-05 09:52:42.533 2006-01-05 09:52:42.720

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-05 : 19:12:42

It looks like these are being generated by SQL Server itself as I think system is the key in your rows. I always exclude system processes from my trace. You can find this option on the last tab.

Tara Kizer
aka tduggan

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-01-06 : 08:14:37

"System" is the login_name. They created that for most their console apps running from the servers.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 12:42:08

Well that might be confusing then in the trace results as I believe you'll see system for SQL Server system stuff. I'll check on that later.

Tara Kizer
aka tduggan

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-01-06 : 13:29:57

These could be generated by opening recordsets for updates. Something like rs.open(tablename), rather than rs.open(query). A little experimentation on a test box should prove that out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources