My co-worker Steve Wright (blog | @SQL_Steve) prodded me with a question recently on a strange result he was seeing. In order to test some functionality in our latest tool, SQL Sentry Plan Explorer PRO, he had manufactured a wide and large table, and was running a variety of queries against it. In one case he was returning a lot of data, but
STATISTICS IO was showing that very few reads were taking place.
SQL Server offers two methods of collecting diagnostic and troubleshooting data about the workload executed against the server: SQL Trace and Extended Events. Starting in SQL Server 2012, the Extended Events implementation provides comparable data collection capabilities to SQL Trace and can be used for comparisons of the overhead incurred by these two features. In this article we'll take a look at comparing the "observer overhead" that occurs when using SQL Trace and Extended Events in various configurations in order to determine the performance impact that data collection may have on our workload through the use of a replay workload capture and Distributed Replay.Continue Reading »
In the SQL Server world, there are two types of people: those who like all of their objects to be prefixed, and those who don't. The former group is further divided into two categories: those who prefix stored procedures with
sp_, and those who choose other prefixes (such as
proc_). A long-standing recommendation has been to avoid the
sp_ prefix, both for performance reasons, and to avoid ambiguity or collisions if you happen to choose a name that already exists in master. Collisions are certainly still an issue, but assuming you've vetted your object name, is it still a performance issue?