SQL Server Rowset Trace

SQL Server Rowset Trace
from my MSDN blog - September 3, 2016

SQL Server Trace分為2種
1.Server-Side Trace with file provider(File Provider)
2.Rowset Trace(Rowset Provider)

Server-Side Trace with file provider(File Provider)

如果是Server-Side Trace是使用file provider

select * from sys.traces where is_rowset = 0 and path is not NULL
PS. The file provider is designed with a guarantee that no event data will be lost.

Rowset Trace(Rowset Provider)

SQL Server Profiler連線到SQL Server啟動的就是Rowset Trace (使用rowset provider)

select * from sys.traces where is_rowset = 1 and path is NULL
PS. The rowset provider, on the other hand, is not designed to make any data loss guarantees.
If data is not being consumed quickly enough and its internal buffers fill, it waits up to 20 seconds before it begins jettisoning events in order to free buffers to get things moving. The SQL Server Profiler client tool will send a special error message if events are getting dropped, but you can also find out if you’re headed in that direction by monitoring SQL Server’s TRACEWRITE wait type, which is incremented as threads are waiting for buffers to free up.
SQL Server Profiler pulls these events from the rowset provider via a call to sp_trace_getdata and performs a “pivot” to produce the human-readable output we’re used to seeing. This is yet another reason that the rowset provider can be less efficient than the file provider—sending so many rows can produce a huge amount of network traffic.
If you do require rowset provider-like behavior for your monitoring needs, you luckily will not need to figure out how to manipulate this data. SQL Server 2005 ships with a series of managed classes in the Microsoft.SqlServer.Management.Trace namespace, designed to help with setting up and consuming rowset traces. The use of these classes is beyond the scope of this chapter, but they are well documented in the SQL Server TechCenter on TechNet, and readers should have no trouble figuring out how to exploit what they offer.

Reference:
Server-Side Tracing and Collection
https://msdn.microsoft.com/en-us/library/cc293613.aspx
SQL Trace Architecture and Terminology
https://msdn.microsoft.com/en-us/library/cc293610.aspx
SQL SERVER – Server Side and Client Side Trace
http://blog.sqlauthority.com/2015/12/15/sql-server-server-side-and-client-side-trace/