Thursday, October 11, 2012

Trace SQL Server using Scripting and No Client for Better Performance

There is a very good article on creating server side traces in SQL Server here:
SQL Server Pedia - Server Side Traces

The essence of the whole thing is this:
Monitoring using the SQL Server Profiler creates considerable overhead, whether you run it on the server that hosts the database itself, or on a client machine.
Monitoring using a script of the sort generated in the linked article, on the other hand, creates nearly zero performance degradation (per Linchi Shea's fine article, found here: trace-profiler-test).  I have linked to Linchi's test results as well as the article.  Test Results

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal addres...