Performance metrics on SQL Server Profiler and sp_trace commands
Are there any metrics available that indicate the varying performance of using MS SQL Profiler against the sp_trace commands on SQL Server 2008. Most articles and writers seem to suggest that using sp_trace commands would be more efficient but is their any empirical evidence for this under开发者_开发问答 varying workloads.
I believe the reason you hear this suggestion is because SQL Server Profiler is merely the GUI front end that runs on top of SQL Trace
From Brad McGeHee's Mastering SQL Server Profiler:
What may be a surprise to many DBAs and developers is that SQL Server Profiler is only a GUI designed to work with another feature of SQL Server called SQL Trace. It is SQL Trace that is actually doing most of the work when it comes to capturing SQL Server events and storing them for later use. SQL Trace is a feature of SQL Server that can be accessed indirectly with the Profiler GUI, system stored procedures, or programmatically using Server Management Objects (SMO).
In essence, SQL Trace is a very simple tool. Its job is just to capture SQL Server-related communication between a client and SQL Server. It acts similarly to a specialized network sniffer that captures traffic on the network related to SQL Server and allows you to see exactly which events are being sent from the client to SQL Server.
精彩评论