Oct 302012
 

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.

Continue Reading »
Oct 232012
 

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 »
Oct 172012
 

One of the things that's simultaneously great and horrible about the Internet is that, once something gets posted out in the ether, it basically never goes away. (Some day, politicians will realize this. We can easily fact check their consistency.) Because of longevity of content posted to the Internet, a lot of performance tuning topics become "zombies." We shoot 'em dead, but they keep coming back!

Continue Reading »
Oct 152012
 

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 usp_ or 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?

Continue Reading »