August 10, 2010 at 10:57 am
So, I have a production SQL Server being spammed 25 to 50 times a second with a DBCC SHOW_STATISTICS with HISTOGRAM_STEPS. In our environment, we have linked servers, and look at the attached screenshot, where you see that the transactions in the MASTER database are MORE than the activity for our user databases. In other words, master is generating 650 transactions a second, and the user database is generating half that. I tracked down, that it appears that the DBCC SHOW_STATISTICS command is actually being instantiated from this stored proc.
sp_table_statistics2_rowset
hardly NOTHING online about this undocumented system command. This link, http://msdn.microsoft.com/en-us/library/ms187961.aspx , indicates this:
------------------------------------------------------------------------------------------
Users that run SQL Server Profiler against ADO, OLE DB, and ODBC applications may notice these applications using system stored procedures that are not covered in the Transact-SQL Reference. These stored procedures are used by the Microsoft SQL Server Native Client OLE DB Provider and the SQL Server Native Client ODBC driver to implement the functionality of a database API. These stored procedures are just the mechanism the provider or driver uses to communicate user requests to an instance of SQL Server. They are intended only for the internal use of the provider or the driver. Calling them explicitly from a SQL Server-based application is not supported.
The sp_createorphan and sp_droporphans stored procedures are used for ODBC ntext, text, and image processing.
The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.
The system stored procedures in the following tables are used only within an instance of SQL Server or through client APIs and are not intended for general customer use. They are subject to change and compatibility is not guaranteed.
The following stored procedures are documented in SQL Server Books Online:
------------------------------------------------------------------------------------------
And then it lists the sp_table_statistics2_rowset stored proc. It is not in BOL however like it says that it is.
What in the world is calling this 50 times a second causing all of these transactions that in my opinion are completely unneccesary. Why in the world would it be important that any API call from any of the items listed above CARE about how the statistics look on a particular table/item.
What is causing this? How do I find out? the profiler tells me the host machine, but not WHAT is instantiating this, or causing it. It says Microsoft SQL Server is the application name. But, I have NEVER seen sql server run this many DBCC SHOWSTATS with histogram_steps like this in 11 years of doing this.
Any ideas at all on how to stop it, track it down? curb it?, anything?
August 10, 2010 at 12:09 pm
I've never seen this, either. Does a profiler trace show any other interesting SQL being issued by that spid? Is there a cross-server join goin on?
August 10, 2010 at 12:37 pm
Let me post how this looks in profiler. The DBCC SHOW_STATS appears to be instantiated by the main sp, sp_table_statistics2_rowset . Here's an example of how it looks in profiler. See screenshot.
August 10, 2010 at 1:41 pm
And no other SQL shows up on that spid? Just that sp execution and the DBCCs it spawns? Can you see the processes running on the SQL Server on the box it's coming from? One of them may be issuing this mess.
August 11, 2010 at 2:57 pm
Actually, very good questions.
Yes, other SQL shows on this spid, it is constantly issueing SP_RESET_CONNECTION.
This is an item, that if my memory serves correct, is used by an instantiating connection pool.
In other words, a client connection utilizing connection pools creates the pool, and when the current executing action is done, it issues the reset, so that the next executing action can be run. Might be wrong on that though. If that's the case, If I can find the other code that I am seeing on that SPID, and track down THAT code, I may have found the origination point of where these show_statistics are coming from. I was wrong on my estimate though. It is run DBCC SHOW_STATISTICS with HISTOGRAM_STEPS, 212 times a second. I have never seen this before, ever.
What I am worried about, is that this is somehow the optimizer using this command to determine what indexes to use or something. Typically, the optimizer does all of it's thinking internally. You won't see that activity in profiler, but I am wondering if because this is linked servers, that it's different or something. That's not what this looks like though. This looks like EXPLICITLY called show stats, and if that's true, then it is causing 2/3's of my boxes load, all by itself. And this is a server doing 1,500 tps.. A good sized, heavily hit, server.
August 12, 2010 at 8:42 am
The "spamming" you describe is from LINKED SERVER activity. Bad stuff there. I have had several clients get several orders of magnitude improvement from replicating out stuff that used to be acquired via linked servers...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 28, 2010 at 10:15 pm
I am having similar issues with my Production SQl 2005 SP3 version.
The system SP (sp_table_statistics2_rowset) is being called from another DB server via the linked server connection using the sa account.
This is causing issues with the performance of the destination server.
I am wondering if this is some sort of a spam? This has all started recently on our servers. Any idea as to what is the source of this SP being called via a Linked Server???
November 28, 2010 at 10:45 pm
It's the other SQL Server trying to get statistics info so that it can correctly estimate rows for query optimisation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 12:03 pm
I didn't know that the SQL engine runs update statistics over the linked server?! Never seen this happen before. It sound a little off the wall.
SQL engine is responsible for its own optimization. That is what I know. To run statistics over the linked server doesn't make sense. Anyone else is accepting what Gail is suggesting?
nn
November 29, 2010 at 12:20 pm
I didn't say it was trying to update statistics. I said it was trying to get statistics. As in read the existing statistics that are there. As in asking the linked SQL Server to please send the statistics that it has for that column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 12:27 pm
Haven't seen that either. Why would it want to do it cross the linked server? Is this normal to you?
November 29, 2010 at 12:42 pm
How else is the optimiser going to get cardinality estimate for remote tables so that it can generate a good execution plan?
It's not something that people see often, and that's a bad thing. It's because of the insane security requirements of that proc - db_owner. Very few people (validly) set up linked servers with dbo permissions on the remote database, hence the request for that proc fails, the optimiser makes do with a bad guess as to the cardinality and the execution plan suffers,
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply