1

Here you can see how to save the contents of the default trace into a table.

I have been saving the contents of the default trance into a table in my DBA database.

The table is called [DBA].[dbo].[DefaultTrace_History].

when I query the trace table to find out about the user mycompany\myuser, some his queries seem to be running slow.

SELECT [TextData]
      ,[Database]='my database' --db_name([DatabaseID])
      ,[HostName]='my server'
      ,[ApplicationName]
      ,[LoginName]='mycompany\myuser'
      --,[SPID]
      ,[StartTime]=MAX([StartTime])
      --,[EndTime]
      --,[Duration]
      --,[ObjectID]
      --,[ObjectType]
      --,[IndexID]
      ,[EventClass]
      ,[EventClass-Name]=te.name
      --,[FileName]
      --,[RowCounts]
      --,[IsSystem]
      ,[SqlHandle]
  FROM [DBA].[dbo].[DefaultTrace_History] dth
  inner join sys.trace_events te
          on dth.EventClass = te.trace_event_id
  where LoginName = 'mycompany\myuser'
  GROUP BY
       [TextData]
      ,[DatabaseID]
      ,[HostName]
      ,[ApplicationName]
      ,[LoginName]
      --,[SPID]
      --,[StartTime]=MAX([StartTime])
      --,[EndTime]
      --,[Duration]
      --,[ObjectID]
      --,[ObjectType]
      --,[IndexID]
      ,[EventClass]
      ,te.name
      --,[FileName]
      --,[RowCounts]
      --,[IsSystem]
      ,[SqlHandle]
  order by StartTime desc

I get the following EvenClasses, that I am concerned about:

  1. Sort Warnings
  2. Missing Join Predicate
  3. Missing Column Statistics
  4. Hash Warning

as you can see on the picture below:

enter image description here

Missing Join Predicate

the Missing Join Predicate has been discussed here and Here too.

Missing Column Statistics

I have fixed it, or at least got rid of the warning by simply running

sp_updatestats

(although the database in question has auto-create stats on) Otherwise it has been discussed here and scripting statistics here

Sort Warning

The sort warning has been discussed here which includes a script to find the stored procedures with sort warnings plus the below by Paul White:

Cached plans are always pre-execution (estimated) plans. Sort warnings are only present in post-execution (actual) plans. Collecting data using Extended Events or Event Notifications is indeed the way to go.

Hash Warning

The Hash Warning and Sort warnings too have been discussed here. where Paul and again by Paul White:

you can monitor the spills using Extended Events or Profiler Sort Warnings and Hash Warnings. Memory is reserved for sorts and hashes based on cardinality estimates before execution starts, and cannot be increased during execution regardless of how much spare memory your SQL Server may have. Accurate row count estimates are therefore crucial for any execution plan that involves workspace memory consuming operations.

Your query is also parameterized. You should consider adding OPTION (RECOMPILE) to the query if different parameter values affect the query plan. You should probably consider using it anyway, so the optimizer can see the value of @Param1 at compilation time. If nothing else, this may help the optimizer produce a more reasonable estimate for the index seek shown above, given that the table is very large, and partitioned. It may also enable static partition elimination.

Try the query again with a temporary table instead of the table variable and OPTION (RECOMPILE).

QUESTION:

The default trace does not give me the sql statements (either ad hoc or stored procedures) that have been run by mycompany\myuser in order to get those warnings or better saying EvenClasses.

How can I find out what statements were executed by mycompany\myuser in order to get those warnings or better saying EvenClasses?

I have thought about maybe adding SQL:BatchCompleted to the default trace, if at all possible, but then I get concerned about the extra cost on the default trace.

Another idea that came from he comments by Paul White is using Extended Events, but would I be able to link both (the default trace with the tracks of mycompany\myuser and the Extended Events data?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.