July 14, 2008 at 12:04 pm
Hello,
This started out as a performance issue but led me to an administration question. We are seeing some occasions when our web site hangs and it appears that SQL Server (2005 Standard Edition 64-bit) runs slowly.
In process of trying to identify if particular queries might be the cause, I checked the Default Trace files. I did not see anything in the trace files pointing to a query taking a long time to run (perhaps that data is not even captured by the default trace?), but I did see this statement from one of our linked servers appearing again and again:
dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector
Can anyone tell me why this statement would be running so often? (The default trace is logging it at something like 180 times per second.)
Is it because this is a linked server? What purpose does this statement have, and how can I tell whether it is causing the periodic performance bottlenecks?
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 14, 2008 at 2:35 pm
Did you check the setting of the update statistics for each database?
July 14, 2008 at 2:48 pm
SQL ORACLE (7/14/2008)
Did you check the setting of the update statistics for each database?
Thank you for your response.
Do you mean the 'Auto Update Statistics' option in the Options tab of the Database properties? If so, then yes - this setting is enabled for the databases I checked.
Does this setting result in all of those statements in Profiler?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 15, 2008 at 1:35 am
Shouldn't do.
What user and application are you seeing those statements from? What SPID?
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
July 15, 2008 at 7:43 am
GilaMonster (7/15/2008)
Shouldn't do.What user and application are you seeing those statements from? What SPID?
The SPID was 206. The user was the user we set up for the linked server connection. The user name was displayed in the SessionLoginName column of the trace.
For some reason, there is also a LineNumber column, which says 105 for each of these trace records.
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 15, 2008 at 7:47 am
Then someone's running a proc/sql batch on the other server that's checking your statistics.
Have a look at the server that's the source of this. See what you can see running
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
July 15, 2008 at 7:50 am
GilaMonster (7/15/2008)
Then someone's running a proc/sql batch on the other server that's checking your statistics.Have a look at the server that's the source of this. See what you can see running
OK, will do.
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 16, 2008 at 8:42 am
I've seen this with linked server connections before. in general linked servers are "bad" (they have a time and place sure)
you avoid using linked servers?
relicate the data or some other method? dts? ssis?
MVDBA
August 19, 2008 at 1:19 pm
We also use a linked server and have seen the same errors in a trace log today. I was not looking for it, but we moved a database to another server and were looking at a deadlock issue.
Perhaps source SQL Server is trying to optimize the query plan? Perhaps we should grant the login the rights to execute this DBCC SHOW_STATISTICS? However, it appears the rights required are too much.
"User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."
It seems like the original server should have a separate login for this rather than use the application login. Does anybody know how to handle this?
BTW, I don't like linked server myself, but I have to work with what I have.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 19, 2008 at 1:49 pm
webrunner (7/14/2008)
In process of trying to identify if particular queries might be the cause, I checked the Default Trace files. I did not see anything in the trace files pointing to a query taking a long time to run (perhaps that data is not even captured by the default trace?)
But to answer your other question, the information about slow running queries will not reside in the default trace. You should examine the DMVs for queries using large amounts of CPU or IO. Run this to see the list of events to be found in the default trace:
SELECT DISTINCT cat.name AS CategoryName, e.name AS EventName
FROM fn_trace_geteventinfo(1) AS rt
INNER JOIN sys.trace_events AS e
ON rt.eventid = e.trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
ORDER BY CategoryName, EventName
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply