The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

It's always bothered me slightly that the main rationale behind the design of clustered indexes in SQL Server seems to be to proactively prevent…fragmentation, rather than organize the data based on the needs of the most critical queries, by storing together those values that we are likely to query together. It was refreshing to see Brent Ozar's recent take on the index fragmentation issue, and his advice to "stop worrying" so much about it, and instead try to tackle the real underlying problems.

As Brent points out, index defragmentation is easy to do. We can configure SQL Server Maintenance Plans to rebuild all our indexes, on a schedule, regardless of whether they need it or not. Unfortunately, most of the rebuilds will be a waste of time, if not actively harmful. Ola Hallengren's scripts, or any script that interrogates the DMVs to check the fragmentation level, before rebuilding, offers a much better option. However, if the rebuild threshold is some arbitrary value across all indexes, then you'll still do a lot of needless rebuilding.

Index fragmentation can, of course, cause performance problems, but is actually not a huge issue for many indexes. Logical fragmentation (index pages in the wrong order) thwarts SQL Server's read-ahead mechanism and makes it less IO-efficient at reading contiguous pages on disk. However, this only really affects large range scans from disk, with the emphasis on large and disk. Reduced page density (many gaps causes by page splits and deletes) is a knottier form of fragmentation. If pages are "half full", they will take up twice the space on disk, and in memory, and twice the IO bandwidth to transfer the data. Again, though, this won't affect infrequently modified indexes.

If our data is in memory anyway, as Brent suggests, or we're able to take hard look at our queries and reduce the number of large range scans, then we can stop worrying so much about fragmentation for those indexes. Once we do, maybe we can return to considering index design primarily in terms of establishing the "natural order" of the data, rather than fragmentation fear.

What do you think? What might make it easier to get over fragmentation fear? Would more/better indexing options help (I noticed Oracle has at least four index clustering structures to SQL Server's one)?

Cheers,
Tony.

Tony Davis from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Tech News : General Interest

Wickedpedia: The dark side of Wikipedia - "Behind the Web site, Wikipedia stories are being manipulated for cash and written and edited by insiders without a clue while outside experts are ignored. "...(more)

Tech News : Security

Trade group exposes 100,000 passwords for Google, Apple engineers - A breakdown of the 18 most common passwords exposed by IEEE suggest that engineers aren't much better than lay people at choosing secure passcodes....(more)

Microsoft Windows Update Hoax Steals Your Passwords - Beware: The next time you get an email from privacy@microsoft.com in your inbox, click delete....(more)

Rent-to-own PCs surreptitiously captured users' most intimate moments - Seven rent-to-own companies and a software developer have settled federal charges that they used spyware to monitor the locations, passwords, and other intimate details of more than 420,000 customers who leased computers....(more)

Data breach at IEEE.org: 100k plaintext passwords. - Due to several undoubtedly grave mistakes, the ieee.org account username and plaintext password of around 100,000 IEEE members were publicly available on the IEEE FTP server for at least one month. Furthermore, all the actions these users performed on the ieee.org website were also available. ...(more)

Privacy Tip: Assume Everything Online Could Be Public - "The Internet collectively freaked out yesterday after rumors swirled that Facebook accidently began posting private messages to users’ public timelines, exposing all the nasty, salacious, and incriminating messages that were thought to be securely hidden from view."...(more)

SQL Server News

Announcing Microsoft SQL Server 2012 Service Pack 1 (SP1) Community Technology Preview 4 (CTP4) - While CTP3 was primarily targeted at introducing the BI functionality for Office 2013 Preview and SharePoint 2013 Preview, CTP4 covers SQL Server functionality across the board....(more)

Microsoft News : General Interest

Did Microsoft pull the trigger on Windows 8 too early? Weigh in - There's a September 25 Bloomberg report out of Taiwan claiming that Intel CEO Paul Otellini privately told Intel staff that Windows 8 is being released before it's fully ready....(more)

Microsoft invests in Klout; integrates data into Bing - On Bing, Microsoft is going to display Klout data -- including a person's Klout score and topics they are "influential" about -- on the new Bing Sidebar pane for those users who can and want to see this information. ...(more)

Hardware News

A fast NAS with class: 5 months with the Synology DS-412+ - Pricey but quick network storage has tons of features—and gigabit Ethernet speed....(more)

Op-ed: AMD may be facing irrelevance - The chips aren't bad—the integrated graphics processor on some of the chips are capable of beating Intel's HD 4000 GPU in gaming performance, though the CPUs aren't quite capable of beating last year's Sandy Bridge processors at similar clock speeds. For AMD, the issue is that they're still talking mostly about desktop and laptop chips....(more)

Blogs : Administration

The SQLBeat Podcast - Armed with new digital recorder and a case that makes it look like a large caliber pistol, Rodney Landrum, AKA SQL Beat, set about convincing SQL experts all over the world to do podcasts. By turns irreverent, uncanny, and deeply technical… First up are Buck Woody, Mark Rasmussen, Tom LaRock and Max Trinidad…...(more)

Provisioning a New SQL Server Instance – Part Three - Once you've installed and configured SQL Server 2012, there are some tasks that should be done to ensure that maintenance, monitoring and alerting systems are in place to keep the instance running smoothly. Glenn Berry explains how...(more)

Blogs : Analysis Services / BI

Row Level Security - Demystified - Nishant Thacker on designing row level security for an AS model, in Tabular mode, for a survey application where managers are only allowed to see feedback if at least four employees reporting up to them have responded....(more)

The State of Self-Service Reporting - What tools should you be using for ad hoc reporting and exactly what criteria to choose among them? There’s a lot of “it depends” to that kind of question but Paul Turley offers some guidelines and direction....(more)

Installing SQL Server 2012 SP1 CTP4 Experience - Dan English reports on his install of SQL Server 2012 SP1 CTP4, and jumps straight into the new PowerView features....(more)

Blogs : Backup and Recovery

Multiple log files and why they're bad - Paul Randal on the results from a survey to discover how many log files your databases have....(more)

Blogs : Computing in the Cloud

Keeping your options open in a cloud solution - In on-premises solutions we have the full range of options open for a given computing solution – but we don’t always take advantage of them, for multiple reasons. Data goes in a Relational Database Management System, files go on a share, and e-mail goes to the Exchange server....(more)

Blogs : Database Design, Theory and Development

The Trouble with Keys - "One of the more troublesome issues architects face when scaling out is the issue of distributing identity. It’s often advantageous for object identity to be unique and portable across all database instances in an application – we may need to move a user from server A to server B. Once multiple database servers are involved, a centralized sequence generator can be come a single bottleneck or may even run out of values under heavy load."...(more)

10 Ways to Avoid Datatype Mismatches - Tom LaRock is willing to wager that you have systems in your care, right now, a less than optimal selection of datatypes for the actual data being stored. As such, here are his top ten ways to right-size your datatypes and your data....(more)

Book Review: Pro SQL Server 2008 Relational Database Design and Implementation - Investing in proper database design is a very efficient way to cut maintenance costs. If we expect a system to last, we need to make sure it has a good solid foundation - high quality database design...(more)

Blogs : DMO/SMO/Powershell

My Short Struggle With SQL Agent - Michael J Swart recently got into trouble when I created a SQL Agent Job step. The only thing this step needed to do was to move some text files from one network drive to another. Long story short… he went with a powershell script....(more)

Get SQL Server Service Accounts with WMI - DBADuck needed a simple way to retrieve the SQL Server Service accounts to validate them in Active Directory, and turned to WMI under SMO with PowerShell....(more)

Use PowerShell to Download Web Page Links from a Blog - By using the Invoke-WebRequest cmdlet in Windows PowerShell 3.0, downloading page links from a website is trivial. The Scripting Guy explains how to do it....(more)

Scanning the Error Log with PowerShell - Allen White on how to use Select-String, a new PowerShell 2.0 cmdlet, to search the error log and return the lines matching the target string....(more)

Blogs : Events

SQL In The City: Austin - So, No SQL Saturday in Austin This Year....SQL In The City To The Rescue!...(more)

Blogs : Hardware

Book Review: SQL Server Hardware by Glenn Berry - Brad McGehee's thoughts on Glenn Berry's latest book: "One option to get DBAs up to speed quickly on hardware and OS selection and configuration is for them to read Glenn Berry's book SQL Server Hardware....it also includes information on selecting and configuring the OS, selecting the right SQL Server version and edition, and on how to install and optimally configure a SQL Server instance before using it for the first time. ...(more)

Blogs : High Availability/Disaster Recovery

AlwaysOn Availability Group Forced Failover: Under the Hood - A walkthrough to take you through one aspect of FCI + AG configuration: forced failover. We will simulate failure and force failover. And in the process we will have some … Data Loss! The true intent of this post is to show you how the Availability Group architecture deals with ‘multiple master’ scenario and how it avoids the ‘split-brain’ condition....(more)

Blogs : Performance and Tuning

T-SQL Update Takes Much Longer Than The Matching Select Statement - A case where an update takes significantly longer than a select and it seems excessive when you start looking at the actual number of I/O operations and such that are taking place....(more)

Most common latch classes and what they mean - Paul Randal presents some interesting survey results, regarding prevalent latches on SQL Server instances across the world....(more)

Blogs : Professional Development

Interviewing a DBA - You get a call from one of the business people. They tell you that the database is running slow. What do you do? Grant Fritchey explains why this is one of his favorite interview questions....(more)

Blogs : Security and Auditing

Identify blank and weak passwords for SQL Server - Manvendra Singh on how to track all SQL Server logins which have either the same password or a blank password, a precautionary measure to avoid any hacking as well as ensuring a SQL Server environment is secure....(more)

Blogs : Spatial Data

Bing Maps AJAX Control 7.0 with SQL Server (and a touch of ASP.NET) - The Bing Maps AJAX Control 7.0 is essentially a JavaScript "control" that contains everything you need to display your maps with Bing and a browser. Stefan Zvonar shows how to create a simple map search with clickable results menu items, using the Bing Maps control and SQL Server 2008+....(more)

Blogs : T-SQL

Transferring Table Types - "Can you move a table type between schemas?" You can, and Steve Jones shows how....(more)

How BETWEEN is inclusive - An old and simple one, but an important subtlety that TSQL developers must be aware of. ...(more)

Nested Partitioned Views - Klaus Aschenbrenner explains a possible requirement for Nested Partitioned Views and how to implement them in SQL Server....(more)

T-SQL Window Functions - Part 4: Analytic Functions - Steve Hughes concludes his series on SQL window functions with analytic functions, introduced in SQL Server 2012 with the expansion of the OVER clause capabilities....(more)

So you want a rollback script? - So you’ve got a new release coming up, including upgrading your production SQL database. You’re writing great upgrade scripts, but now your DBAs are demanding rollback scripts as well. How do you approach this?...(more)

DBCC CHECKINDENT can be used to reset a tables identity value. - TRUNCATE TABLE is a command which will enable you to delete all rows from a table and at the same time, reset the identity value. However you need permission to be able to do this and it will only work if the table is not referenced by a foreign key constraint. This is when DBCC CHECKIDENT comes in handy....(more)

TSQL - Solve it YOUR Way - Finding the percentage of NULL values for each column in a table - Samuel Lester continues his "TSQL YOUR Way" series, challenging Jens Suessmeyer, Naomi Nosonovsky, and Jingyang Li to provide solutions find the percentage of NULL values for each column in a table....(more)

Blogs : Virtualization

Why Your SQL Server Cluster Shouldn’t Be Virtualized - “Can I build SQL Server clusters in VMware and Hyper-V?” Micrsoft supports it, but for Brent Ozar, the real question is about whether you can support it. Adding virtualization (which also means shared storage) makes things much tougher to troubleshoot....(more)

SQL Consolidation Planning and recommended practices - SQL server consolidation is simply the task of reducing the number of physical SQL servers, by migrating/moving different SQL databases running on different servers into one high performance server machine. Issa Ayyash provides some pointers....(more)

Blogs : XML, XPATH and XQUERY

Getting Started With XML Indexes - XML Indexes make a huge difference to the speed of XML queries, as Seth Delconte explains; and demonstrates by running queries against half a million XML employee records. The execution time of a query is reduced from two seconds to being too quick to measure, purely by creating the right type of secondary index for the query....(more)

Getting started with Selective XML Indexes in SQL Server - New in CTP4...Selective XML Indexes. According to Bob Beauchemin, "an interesting concept, especially because dragging around a primary XML index that's 3-6 bigger than the data itself is a burden. But, without it, querying on any non-trivial XML is quite slow. To put it nicely... "...(more)