SQL Server MVP Deep Dives, Volume 2
Kalen Delaney, Louis Davidson, Greg Low, Brad McGehee, Paul Nielsen, Paul Randal, and Kimberly Tripp
  • October 2011
  • ISBN 9781617290473
  • 688 pages
  • printed in black & white

SQL Server MVP Deep Dives, Volume 2 lets you learn from the best in the business—64 SQL Server MVPs offer completely new content in this second volume on topics ranging from testing and policy management to integration services, reporting, and performance optimization techniques...and more.

Table of Contents show full



about Operation Smile

about this book

about the editors

about SQL Server MVPs

Part 1 Architecture

Edited by Louis Davidson

1. Where are my keys?

Ami Levin

Keys in the relational model

The debate

The arguments

Additional considerations


Simplicity and aesthetics


2. "Yes, we are all individuals" A look at uniqueness in the world of SQL

Rob Farley

Introducing uniqueness

Constrained to uniqueness

Unique constraint or unique index?

Uniqueness in results


3. Architectural growth pains

Chris Shaw

Manage your data types

Naming conventions

Inconsistent design


Primary keys and foreign keys



4. Characteristics of a great relational database

Louis Davidson



Fundamentally sound




Well performing


5. Storage design considerations

Denny Cherry

Selecting the correct RAID type

File placement

Disk alignment




6. Generalization: the key to a well-designed schema

Paul Nielsen

A place for normalization

Lessons from the UIX discipline

Generalization defined

Benefits of generalization


Part 2 Database administration

Edited by Paul Randal and Kimberly Tripp

7. Increasing availability through testing

Allan Hirt

Testing—it’s not just for application functionality

Knowledge is power

Test early, test often

Automated versus manual testing

What needs to be tested?

First things first

Remember the big picture, too


8. Page restores

Gail Shaw

Restore granularities

Requirements and limitations

Performing a page restore

What’s coming?


9. Capacity planning

Greg Larsen

What is capacity planning?

Gathering current database disk space usage

Performance metrics


10. Discovering your servers with PowerShell and SMO

Joe Webb

Using PowerShell and Excel

Using SMO with PowerShell

Collecting instance and machine information

Collecting SQL Agent job information

Collecting database information


11. Will the real Mr. Smith please stand up?

John Magnabosco

Personally identifiable data

Today’s superhero: the DBA


12. Build your own SQL Server 2008 performance dashboard

Pawel Potasinski

Using SQLCLR to get the performance counter values

Sample solution for performance monitoring

Use Reporting Services for performance monitoring

Some ideas to improve the solution


13. SQL Server cost recovery

Peter Ward

The context for SQL Server as a Service

What’s SQL Server as a Service?

An introduction to chargebacks

Implementing a chargeback model


14. Best practice compliance with Policy-Based Management

Rod Colledge

The context for contemporary database administration

The importance of best practice compliance

Central Management Servers

Policy-Based Management

Policy-Based Management with Central Management Servers


15. Using SQL Server Management Studio to the fullest

Rodney Landrum

Querying many servers at once

Creating and using a scripting solution with templates

Scripting multiple objects and now data, too


16. Multiserver management and Utility Explorer—best tools for the DBA

Satya Shyam K Jayanty

SQL Server 2008 R2 tools for the DBA

Tools of the trade

Managing multiple instances using Utility Control Point

Multiserver management and administration

Best practices


17. Top 10 SQL Server admin student misconceptions

Tibor Karaszi

Simple recovery model

Default collation

Table-level backups

Using replication for high availability

Timing query performance

Shrinking databases

Auditing login access

Tail-log backups

Database defaults



18. High availability of SQL Server in the context of Service Level Agreements

Tobiasz Janusz Koprowski

High availability—a definition

Types of unavailability

Unavailability indicators

High availability options in SQL Server

Service Level Agreement

Measurement indicators

The structure of a Service Level Agreement

Service Level Agreements: the context for high availability


Part 3 Database development

Edited by Paul Nielsen

19. T-SQL: bad habits to kick

Aaron Bertrand


Declaring VARCHAR without length

Not choosing the right data type

Mishandling date range queries

Making assumptions about ORDER BY


20. Death by UDF

Kevin Boles

Poor estimates

Row-by-row processing

What can you do about it?

What about code reuse?

One last example of how bad scalar UDFs can be


21. Using regular expressions in SSMS

John Paul Cook

Eliminating blank lines

Removing extra newline characters

Collapsing multiple lines into a single line

Using the beginning-of-line metacharacter

Using the end-of-line metacharacter


22. SQL Server Denali: what’s coming next in T-SQL

Sergio Govoni






23. Creating your own data type

Hugo Kornelis

Anatomy of a CLR user-defined type

Building the data type: the bare basics


24. Extracting data with regular expressions

Matija Lah

Understanding before coding

The solution



25. Relational division

Peter Larsson

Why use relational division?

Defining relational division


Sample data for two simple cases

Comparison charts

Let’s go on with the real stuff

Set-based solution to common relational division

Does one query exist for all types of relational division?


26. SQL FILESTREAM: to BLOB or not to BLOB

Ben Miller


Configuring FILESTREAM in SQL Server

Database configuration

Creating a table that uses FILESTREAM

Things to consider

How do I use FILESTREAM?


27. Writing unit tests for Transact-SQL

Luciano Moreira

Unit test basics

Automating unit test execution


28. Getting asynchronous with Service Broker

Mladen Prajdic

The Service Broker usage template

Creating Service Broker objects


29. Effective use of HierarchyId

Denis Reznik

Hierarchies in a database

Introduction to the HierarchyId data type

Effective indexing

More information about HierarchyId


30. Let Service Broker help you scale your application

Allen White

Scalable solutions

Service Broker objects

ETL trigger demonstration


Part 4 Performance tuning and optimization

Edited by Brad M. McGehee

31. Hardware 201: selecting and sizing database server hardware

Glenn Berry

Why database server hardware is important

Scaling up or scaling out

SQL Server and hardware selection

Database server-specific hardware factors

Intel vs. AMD processors

Memory recommendations

Traditional storage subsystems

New developments in storage subsystems

Benchmarking and sizing tools


32. Parameter sniffing: your best friend…​ except when it isn’t

Grant Fritchey

Understanding parameter sniffing

Parameter sniffing gone wrong

Dealing with bad parameter sniffing


33. Investigating the plan cache

Jason Strate

Plan cache dynamic management objects

Investigating missing indexes

Investigating index usage

Investigating operations

Investigating index scans

Investigating parameters

Plan cache considerations


34. What are you waiting for? An introduction to waits and queues

Robert Pearl

Introduction to total response time

What are wait stats?

The execution model

Viewing and reporting on wait statistics

Calculating wait time: signal waits vs. resource waits

Correlating performance data: putting it together


35. You see sets, and I see loops

Linchi Shea

What loops?

The loop perspective

Loops in a query execution plan

Loops in complex queries

User-defined scalar functions in implicit loops

Merging multiple loops into one

Parallelizing loops

Linked server calls in a loop

Squeezing the fat out of loops with a slim table


36. Performance-tuning the transaction log for OLTP workloads

Brad M. McGehee

How can the transaction log be a bottleneck?

Factors that contribute to transaction log bottlenecks

Determining whether the transaction log is a bottleneck

Strategies for dealing with transaction log I/O bottlenecks


37. Strategies for unraveling tangled code

Jennifer McCown

Organize: make it readable

Break down: what does it do?

Streamline: resolve obvious issues

Streamline: optimize joins

Streamline: similar subqueries and queries

Streamline: dynamic SQL


38. Using PAL to analyze SQL Server performance

Tim Chapman

Performance Analysis of Logs (PAL)

Using PAL with SQL Server

Capturing performance counter data

Performing PAL analysis

The PAL report


39. Tuning JDBC for SQL Server

Jungsun Kim

JDBC performance tuning can be effective

Recommendations for tuning jTDS configuration

Unicode character issues

API cursor issues

ResultSet Type issue

Prepared statement mechanism issue

Controlling global configuration


Part 5 Business intelligence

Edited by Greg Low

40. Creating a formal Reporting Services report part library

Jessica M. Moss

Report parts defined

Deciding to create a report part library

Selecting report parts

Using a report part library


41. Improving report layout and visualization

Greg Low

Target-based rendering

Control over pagination

Joining across datasets

Aggregates of aggregates

Writing mode

Domain scope




42. Developing sharable managed code expressions in SSRS

William (Bill) Vaughn

Coding report expressions

Creating sharable managed code expressions

Referencing DLLs from your report


43. Designing reports with custom MDX queries

Paul Turley

MDX query builder

Building a report

Handling parameters


44. Building a scale-out Reporting Services farm

Edwin Sarmiento

What is network load balancing?

Preparing your network

Adding the network load balancing feature

Creating the NLB cluster

Adding hosts to the NLB cluster

Installing Reporting Services on the NLB cluster

Configuring the first Reporting Services instance

Configuring the second Reporting Services instance

Joining the second Reporting Services instance

Configuring view state validation

Configuring the hostname and UrlRoot

Workarounds for the HTTP 401 error message


45. Creating SSRS reports from SSAS

Robert Cain

Creating the report project

Adding a shared data source

Creating shared datasets

The report


46. Optimizing SSIS for dimensional data loads

Michael Coles

Optimization quick wins

Type 0 dimensions

Type 1 SCDs

Type 2 SCDs


47. SSIS configurations management

Andy Leonard

Building the demo database

Starting in the middle

Changing the connection

Externalizing the connection

Taking a step back

Abstracting a bit


48. Exploring different types of enumerators in the SSIS Foreach Loop container

Abolfazl Radgoudarzi and Shahriar Nikkhah

Make it dynamic


49. Late-arriving dimensions in SSIS

John Welch

A late-arriving dimension scenario

Natural keys and surrogate keys

The example data structure

Working around late-arriving dimensions

Handling late-arriving dimension members in fact processing

Processing the dimension update


50. Why automate tasks with SSIS?

Ted Krueger

Automation by example

SSIS key tasks and components

Creating reusable and mobile SSIS packages

Precedence and manipulating control flow

Monitoring the results


51. Extending SSIS using the Script component

Tim Mitchell

The Swiss Army knife of SSIS


52. ETL design checklist

Rafael Salas

Discovering data realities

Extract phase

Transform phase

Load phase

ETL system instrumentation and management


53. Autogenerating SSAS cubes

Johan Åhlén


Developing InstantCube, a simple cube generator



54. Scripting SSAS databases — AMO and PowerShell, Better Together

Darren Gosbell

Advantages of PowerShell

Advantages of compiled code

Automating processing

Repetitive design changes


55. Managing context in MDX

Boyan Penev

Named sets

Calculated members

Scope assignments


56. Using time intelligence functions in PowerPivot

Thiago Zavaschi

Introducing Data Analysis Expressions

Time intelligence functions



57. Easy BI with Silverlight PivotViewer

Gogula Aryalingam

Presenting Silverlight PivotViewer

What makes up your pivot

The way to go


58. Excel as a BI frontend tool

Pedro Perfeito

Key points to consider when choosing a BI frontend tool

Why Excel?

Why use OLAP as a source?

Dashboard implementation


59. Real-time BI with StreamInsight

Allan Mitchell

What is StreamInsight?

What are events and event streams?



How does querying streams differ from querying an RDBMS?

Where is StreamInsight useful?

Querying the streams


60. BI solution development design considerations

Siddharth Mehta

Architecture design

Other aspects influencing architecture design

Solution development



About the book

To become an MVP requires deep knowledge and impressive skill. Together, the 64 MVPs who wrote this book bring about 1,000 years of experience in SQL Server administration, development, training, and design. This incredible book captures their expertise and passion in 60 concise, hand-picked chapters.

SQL Server MVP Deep Dives, Volume 2 picks up where the first volume leaves off, with completely new content on topics ranging from testing and policy management to integration services, reporting, and performance optimization. The chapters fall into five parts: Architecture and Design, Database Administration, Database Development, Performance Tuning and Optimization, and Business Intelligence.

What's inside

  • Discovering servers with PowerShell
  • Using regular expressions in SSMS
  • Tuning the Transaction Log for OLTP
  • Optimizing SSIS for dimensional data
  • Real-time BI
  • Much more

About the reader

This unique book is your chance to learn from the best in the business. It offers valuable insights for readers of all levels.

About the authors

Written by 64 SQL Server MVPs, the chapters were selected and edited by Kalen Delaney and Section Editors Louis Davidson (Architecture and Design), Paul Randal and Kimberly Tripp (Database Administration), Paul Nielsen (Database Development), Brad McGehee (Performance Tuning and Optimization), and Greg Low (Business Intelligence).

combo $59.99 pBook + eBook
eBook $47.99 pdf + ePub + kindle

FREE domestic shipping on three or more pBooks