I have been saving the contents of the default trance into a table in my
The table is called
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:
- Sort Warnings
- Missing Join Predicate
- Missing Column Statistics
- Hash Warning
as you can see on the picture below:
Missing Join Predicate
Missing Column Statistics
I have fixed it, or at least got rid of the warning by simply running
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.
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).
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
How can I find out what statements were executed by
mycompany\myuserin order to get those
warningsor better saying
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?