Question about Trace query being run
Just wondered if someone could explain what the following query has to do with source code control in a Database.
SET XACTABORT ON ;
DECLARE @defaultTraceFileName NVARCHAR(256);
SELECT @defaultTraceFileName = CONVERT(NVARCHAR(256), value)
FROM fntrace_getinfo(DEFAULT)
WHERE [property] = 2
AND value IS NOT NULL;
SELECT
dt.StartTime
, dt.DatabaseID
, dt.TransactionID
, dt.EventSubClass
, dt.EventClass
, dt.ObjectID
, sysTSV.subclassname AS ObjectType
, CONVERT(NVARCHAR(256), NULL) AS SchemaName
, dt.ObjectName
, dt.LoginName
, dt.ApplicationName
FROM ::fntracegettable(@defaultTraceFileName, default) AS dt
LEFT JOIN sys.tracesubclassvalues AS sysTSV
ON sysTSV.traceeventid = dt.EventClass
AND sysTSV.subclassvalue = dt.ObjectType
WHERE dt.EventSubClass IN ( 0, 1 )
AND dt.EventClass IN ( 46, 47, 164 )
AND sysTSV.subclass_name IN ('ST', 'AS', 'BN', 'CT', 'EN', 'IX', 'MT', 'PF', 'PS', 'RT', 'SC', 'SV', 'SX', 'TA', 'TR', 'U' );
This was part of a lot more convoluted and overly complex query that was captured while profiling a server that I'm testing SQL Source Control on.
The resultset from this query generated over 30K rows and only a very few related to the database that was under source control.
philcart
shared this idea
I think the procedure has changed slightly, but it’s used to identify changes to your database to put blue indicators on your Object Explorer.
-
[Deleted User]
commented
So we are running this query in order to find out about modifications to the database. This information is then displayed in the SSMS UI as blue blobs in the Object Explorer.
The filtering stage is currently being run at the very end of the query, and as far as I can tell you are correct that it could be hoisted up to the initial select, which would improve performance in the case where there is a lot of activity in other databases. We are currently looking into this, and our internal bug number is SOC-667.