At the SNI how does SQLprofiler captures trace data?

Question

---

yup

Answer 1

You know the difference between subject and body of a post? A concrete or at last more elaborate question is also welcome..

Answer 2

Sincere apologies, the last one didn’t posted well guess it was because of some browser issues.

If you will look at chapter 1 of the book Professional SQL Server 2008 Internals & Trouble Shooting or view the video of Life of a query by Christian Bolton there is a part where SNI is being talked about.

It was in one of the session recordings that I saw an year ago where it was talked about the process how SQL Server captures messages / TDS packets for the profiler trace output.

Now here I’ve lost my papers & can’t find those details anymore. Anyone who can throw some light?

---

yup

Answer 3

Your welcome. Really. While I can anticipate your problem, can you elborate your problem?

Answer 4

Hi Yup,

Hope this is what you want:

A Basic Select Query
The details of the query used in this example aren’t important — it’s a simple SELECT statement with
no joins, so you’re just issuing a basic read request. Start at the client, where the fi rst component you
touch is the SQL Server Network Interface (SNI).
SQL Server Network Interface
The SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection between the client and the server. It consists of a set of APIs that are used by both the database engine and the SQL Server Native Client (SNAC). SNI replaces the net-libraries found in SQL Server 2000 and the Microsoft Data Access Components (MDAC), which are included with Windows.

Late in the SQL Server 2005 development cycle, the SQL Server team decided to eliminate their dependence on MDAC to provide client connectivity. MDAC is owned by the SQL Server team but ships in the box with Windows, which means its shipped ‘out-of-band’ with SQL Server. With so many new features being added in SQL Server 2005, it became cumbersome to coordinate updates to MDAC with Windows releases, and SNI and SNAC were the solutions created. This meant that the SQL Server team could add support for new features and release the new code in-line with SQL Server releases. SNI isn’t confi gurable directly; you just need to confi gure a network protocol on the client and the server. SQL Server has support for the following protocols:

➤ Shared memory: Simple and fast, shared memory is the default protocol used to connect from
a client running on the same computer as SQL Server. It can only be used locally, has no confi
gurable properties, and is always tried fi rst when connecting from the local machine.
➤ TCP/IP: TCP/IP is the most commonly used access protocol for SQL Server. It enables you
to connect to SQL Server by specifying an IP address and a port number. Typically, this happens
automatically when you specify an instance to connect to. Your internal name resolution
system resolves the hostname part of the instance name to an IP address, and either you
connect to the default TCP port number 1433 for default instances or the SQL Browser service
will fi nd the right port for a named instance using UDP port 1434.
➤ Named Pipes: TCP/IP and Named Pipes are comparable protocols in the architectures in
which they can be used. Named Pipes was developed for local area networks (LANs) but it
can be ineffi cient across slower networks such as wide area networks (WANs).

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

This is from the book, I'm looking for how the Profiler / Server side Trace is captured and the point where its done.

---

yup

Answer 6

Yup,

This content is how to trace in that book, are you finding the components from the book?

Profiler is a graphical user interface (GUI) which you can use to interactively build and execute a tracing session. You can select any combination of events and SQL Trace will return them to the GUI so you can see what is happening in real time. Whilst the basics of setting up a trace in Profiler won’t be covered in this book, it does also offer lots of extended functionality, and these more advanced aspects of Profiler are indeed covered in this book.

The server-side trace however is the main event, in my opinion, when it comes to tracing SQL Server, especially in production environments. You create server-side trace by executing a number of system stored procedures against SQL Server. The server-side trace then writes the events that you have asked it to capture to a file on the file system. For auditing style traces you should always use a server-side trace. You will discover why when you look at the architecture of SQL Trace.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

My question is simple to the point, how is this data captured & given to the trace?

---

yup

Answer 8

Yup,

In SQL Trace, events are gathered if they are instances of event classes listed in the trace definition. SQL Server Profiler lets you record events as they occur in an instance of the Microsoft SQL Server Database Engine. The recorded events are instances of the event classes in the trace definition.

So, we can simply think that the change of the data will be captured by the events and the events will send the information to trace.

For more information, please see:

http://technet.microsoft.com/en-us/library/hh245121.aspx

http://msdn.microsoft.com/en-us/library/ms175481.aspx

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.