What are these sys.sp_* Stored Procedures doing?

Question

I'm tracking down an odd and massive performance problem in my SQL server installation. On my system, a particular stored procedure takes 2 minutes to execute; on a colleague's system it takes less than 1 second. We have similar databases/data and configurations, but there's obviously something very different.

I ran the SP in question through the Profiler on both systems and noticed something odd. On My system, I see 9 entries with the following properties:

• The Duration is way high relative to other rows. I have values as high as 37,698 and as low as 1734. On the "fast" system the maximum duration (for the entire SP call) is 259.
• They are executed for two databases related to the one that contains the SP I'm running. (This SP makes calls via Linked Servers to these two databases).
• They are executions of one of the following system SPs:
  • sp_tables_info_90_rowset
  • sp_check_constbytable_rowset
  • sp_columns_90_rowset
  • sp_table_statistics2_rowset
  • sp_indexes_90_rowset

I can't find any Googleable documentation on what these are, why they would be so slow, or why they would run on one system but not the other. Does anyone know what they're all about?

Answer 1

I'm not familiar with those specific procedures, but you can try running:

SELECT object_definition(object_id('Procedure Name'))

To get a better idea of what's going on under the hood.

Answer 2

I don't know the answer to your question. But to try to fix the problem you're having (which, I assume, is what you're actually interested in), the first thing I'd do is run a re-index on the tables you're querying. This frequently will fix any kind of slowness when the conditions are as you described (same database structure, different data/database, same query).

Answer 3

Last index rebuild? Last statistics update?

Otherwise, these stored procs are used by the SQL Server client too... no? And probably won't cause these errors

Answer 4

Try manually updating statistics on that table.

UPDATE STATISTICS [TableName]

Then double check that the database option to AutoUpdateStatistics is TRUE. Even if it is, though, I've seen cases where adding large amounts of data to a table doesn't always cause the statistics to update in a timely way, and queries can be slow.