Home Archives Authors Categories Newsletters Latest Builds About GO RSS Feed

Year: 2012

Database smarter with deeper insights for Microsoft SQL
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!

  ◆  October 17, 2012
0
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.

  ◆  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.

  ◆  October 3, 2012
4
Hit-Highlighting in Full-Text Search

Hit-highlighting is a feature that many people wish SQL Server's Full-Text Search would support natively. This is where you can return the entire document (or an excerpt) and point out the words or phrases that helped match that document to the search. Doing so in an efficient and accurate manner is no easy task, as I found out first hand.

  ◆  September 24, 2012
4
How much impact can a data type choice have?

I've long been a proponent of choosing the correct data type. I've talked about some examples in a previous "Bad Habits" blog post, but this weekend at SQL Saturday #162 (Cambridge, UK), the topic of using DATETIME by default came up. In a conversation after my T-SQL : Bad Habits and Best Practices presentation, a user stated that they just use DATETIME even if they only need granularity to the minute or day, this way the date/time columns across their enterprise are always the same data type. I suggested that this might be wasteful, and that the consistency might not be worth it, but today I decided to set out to prove my theory.

  ◆  September 13, 2012
0
What is the most efficient way to trim time from datetime?

The most common need for stripping time from a datetime value is to get all the rows that represent orders (or visits, or accidents) that occurred on a given day. However, not all of the techniques that are used to do so are efficient or even safe. If you want a safe range query that performs well, use an open-ended range or, for single-day queries on SQL Server 2008 and above, use CONVERT(DATE). Read on to discover why…

  ◆  September 5, 2012
11
SentryOne Sponsor & Curator
SQLskills Contributor & Partner
Design and content © 2024 SQL Sentry, LLC.  All rights reserved.  User contributions are licensed under cc-sa with attribution required.