March 23, 2010 at 12:17 pm
I found a task in a query execution plan of type "Clustered Index Insert" where the object is tempdb.dbo.CWT_PrimaryKey.
The output list is fields of a table valued function.
The definition of the table-valued function has no indexes or primary keys.
Is this some behind-the-scenes work that tempdb is having to do when populating the function table?
The query that causes this task is in the form
DECLARE curs CURSOR FORWARD_ONLY FOR
SELECT...
FROM dbo.TableFunction() JOIN dbo.ActualTable
WHERE...
ORDER BY...
The following query in the exec plan's batch is
FETCH NEXT FROM curs INTO...
and this has a task type of Clustered Index Seek on tempdb.dbo.CWT_PrimaryKey
I don't recall ever having seen tempdb being explicitly mentioned like this in execution plans, and a google of CWT_PrimaryKey was unfruitful.
If anyone could enlighten me on what is going on here I would very much appreciate it
Thanks
March 24, 2010 at 8:41 am
Hmmm, that's an interesting one. I'm just guessing here, but the application is probably creating that table somewhere. Is that table referenced in the function?
I'd do a trace and try to see if you see that table created. Strike that. First I'd check the default trace for the creation of that table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 24, 2010 at 9:08 am
Thanks Jack
It's a 3rd party application that runs this query, and we think this process was the one that caused tempdb to grow to 50GB all of a sudden, so I am of course suspicious of what could be happening here!
Edit: the function does not mention the mystery table at all.
I had a look in the DEFAULT TRACE (http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/ for anyone not very familiar with the default trace, like myself).
I see lots of objects created with names like _WA_Sys_...... which must be legit/system generated. Also lots of objects PK__#pre______________786AD428 which I presume are also system generated
There are also lots of creations of an object called PK_ids, one called i1 and one called index_key
I can't see any reference to CWT_PrimaryKey
March 24, 2010 at 9:23 am
mazzz (3/24/2010)
I see lots of objects created with names like _WA_Sys_...... which must be legit/system generated.
Auto-generated statistics
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
March 24, 2010 at 9:25 am
All the WA_SYS stuff is system generated column statistics.
This query provides a bit more information than you need, but will help you query the Default Trace better:
With cteObjectTypes AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'ObjectType'
),
cteEventSubClasses AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'EventSubClass'
)
SELECT
TE.[name],
I.ApplicationName,
I.BigintData1,
I.ClientProcessID,
I.ColumnPermissions,
I.DatabaseID,
I.DatabaseName,
I.DBUserName,
I.Duration,
I.EndTime,
I.Error,
I.EventSequence,
Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
I.FileName,
I.HostName,
I.IndexID,
I.IntegerData,
I.IsSystem,
I.LineNumber,
I.LoginName,
I.LoginSid,
I.NestLevel,
I.NTDomainName,
I.NTUserName,
I.ObjectID,
I.ObjectID2,
I.ObjectName,
Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
I.OwnerName,
I.ParentName,
I.Permissions,
I.RequestID,
I.RoleName,
I.ServerName,
I.SessionLoginName,
I.Severity,
I.SPID,
I.StartTime,
I.State,
I.Success,
I.TargetLoginName,
I.TargetLoginSid,
I.TargetUserName,
I.TextData,
I.TransactionID,
I.Type,
I.XactSequence
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id LEFT JOIN
cteEventSubClasses AS ESC ON
TE.trace_event_id = ESC.trace_event_id And
I.EventSubClass = ESC.subclass_value LEFT JOIN
cteObjectTypes AS OT ON
TE.trace_event_id = OT.trace_event_id AND
I.ObjectType = OT.subclass_value
WHERE
T.is_default = 1 AND
I.DatabaseID = 2 AND /* TempDB always 2*/
I.ObjectType = 8277 /* User object */
When was your last reboot/SQL restart? The default trace may have rolled ever since the table was created.
SELECT
D.create_date
FROM
sys.databases AS D
WHERE
database_id = 2
Can you query the table directly?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 24, 2010 at 9:54 am
Thanks Gail.
Jack, thanks for the mammoth query!
The server was last rebooted a couple of weeks ago (funnily enough when the same process caused the tempdb logfile to grow out of control!), so it has definitely not been restarted since the query was run.
I've run the query you provided me, and all the created objects in the relevant resultset have NULL ObjectName (although non-null objectId)
I appear to have a phantom table on my hands.
April 28, 2010 at 9:45 pm
The CWT_PrimaryKey clustered index is a temporary table created by the processing of the CURSOR.
When you OPENed the cursor, the system created a temp table (inaccessible by anything else) and populated it to house the data that you are going to FETCH from the cursor.
All the FETCHes are retrieved from that temp table.
--Brad
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply