October 2012 - SQLPerformance.com

Beware misleading data from SET STATISTICS IO

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.

Aaron Bertrand ◆ October 30, 2012

11

Trimming time from datetime – a follow-up

Aaron Bertrand follows up on an earlier post about trimming time from datetime, this time examining the performance characteristics of various methods without data access.

Aaron Bertrand ◆ October 25, 2012

8

Measuring “Observer Overhead” of SQL Trace vs. Extended Events

SQL Server offers two methods of collecting diagnostic and troubleshooting data about the workload executed against the server: SQL Trace and Extended Events. Read on to compare.

Jonathan Kehayias ◆ October 23, 2012

13

The Zombie PerfMon Counters That Never Die!

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!

Kevin Kline ◆ October 17, 2012

0

Is the sp_ prefix still a no-no?

SQL Sentry's @AaronBertrand shows several reasons why you shouldn't use the sp_ prefix on stored procedures in SQL Server, including a performance hit.

Aaron Bertrand ◆ October 15, 2012

17

Configuring a Dedicated Network for Availability Group Communication

SQL Server 2012 AlwaysOn Availability Groups require a database mirroring endpoint for each SQL Server instance that will be hosting an availability group replica and/or database mirroring session. This SQL Server instance endpoint is then shared by one or more availability group replicas and/or database mirroring sessions and is the mechanism for communication between the primary replica and the associated secondary replicas.

Depending on the data modification workloads on the primary replica, the availability group messaging throughput requirements can be non-trivial. This activity is also sensitive to traffic from concurrent non-availability group activity. If throughput is suffering due to degraded bandwidth and concurrent traffic, you may consider isolating the availability group traffic to its own dedicated network adapter for each SQL Server instance hosting an availability replica. This post will describe this process and also briefly describe what you might expect to see in a degraded throughput scenario.

Joe Sack ◆ October 11, 2012

28

Checking if a non-LOB column needs to be updated

Occasionally I see people try to "optimize" their update statements to avoid writing the same value to a particular column. My understanding has always been that if you're going to update a row, assuming all of the values are in-row, the costs of locking the row are much higher than the incremental cost of updating one, two or all columns in that row.

Aaron Bertrand ◆ October 3, 2012

4