Monday, January 18, 2016

SQLSaturday Austin – Jan 30th

I will be speaking with other SQL Server experts for SQLSaturday #461 in Austin on Jan 30th, 2016. This is a FREE day of training. FREE!!!
There are also a couple of great Pre-Cons on Friday Jan 29th, 2016 that are very cheap compared to similar training at big conferences.
SQLSaturday #461 -- the precons will be held on Friday, January 29th, 2015 at the Microsoft office, 10900 Stonelake Blvd., Suite 225  Austin, TX  78759.  
Registration for SQLSaturday #447 Precon:
Creating a High Availability and Disaster Recovery Plan BY Ryan Adams --
Performance Tuning Like a Boss BY Robert Davis --

Here is a list of the sessions for Saturday (FREE!!!):

Session Time Session Title Track Speaker(s)
09:00 AM - 10:00 AM Branding Yourself for a Dream Job Professional Development Steve Jones
09:00 AM - 10:00 AM Designing Stored Procedure Solutions Application & Database Development Jennifer McCown
09:00 AM - 10:00 AM Enforce Best Practices with CMS and Policy Based Management Enterprise Database Administration & Deployment Ryan Adams
09:00 AM - 10:00 AM From Here to Azure Enterprise Database Administration & Deployment Thomas LaRock
09:00 AM - 10:00 AM Know what your machines are doing besides SQL Server - WMI, WQL, and PoSH Enterprise Database Administration & Deployment Michael Bourgon
09:00 AM - 10:00 AM Using Power BI when Implementing Data Analytics Management Practices BI Information Delivery Ginger Grant
10:10 AM - 11:10 AM Continuous Deployments using SSDT Application & Database Development Christopher Wolff
10:10 AM - 11:10 AM Fun Facts about Availability Groups Enterprise Database Administration & Deployment Joe Hellsten, Joe Hellsten
10:10 AM - 11:10 AM Introducing Azure Data Factory Enterprise Database Administration & Deployment Kevin Hazzard
10:10 AM - 11:10 AM Introduction to Time Series Forecasting Advanced Analysis Techniques Peter Myers
10:10 AM - 11:10 AM SQL Server Deprecated and Discontinued Features Enterprise Database Administration & Deployment Nancy Hidy Wilson
11:20 AM - 12:20 PM Database Virtualization (Monster VM) – Vsphere 6 Enterprise Database Administration & Deployment Michael Corey
11:20 AM - 12:20 PM Dimensional Modeling 101 BI Platform Architecture, Development & Administration Thomas LeBlanc
11:20 AM - 12:20 PM Making the Leap from Developer to DBA Professional Development Amy Herold
11:20 AM - 12:20 PM Monster Text Manipulation: Regular Expressions for the DBA Enterprise Database Administration & Deployment Sean McCown
11:20 AM - 12:20 PM Parameter Sniffing the Good, the Bad and the Ugly Application & Database Development Lance Tidwell
02:00 PM - 03:00 PM Azure Machine Learning: From Design to Integration Advanced Analysis Techniques Peter Myers
02:00 PM - 03:00 PM Backup and Restore Tips and Tricks Enterprise Database Administration & Deployment Ryan Adams
02:00 PM - 03:00 PM ColumnStore Index:
Microsoft SQL Server 2014 and Beyond
BI Platform Architecture, Development & Administration Jayaprakash Jothiraman
02:00 PM - 03:00 PM Database Design Throwdown Application & Database Development Karen Lopez, Thomas LaRock
02:00 PM - 03:00 PM SQL Server 2014/2016 In Memory Technologies - OLTP (aka Hekaton) & ColumnSt Application & Database Development Reinaldo Kibel
02:00 PM - 03:00 PM The Data Warehouse of the Future BI Platform Architecture, Development & Administration Stan Geiger
03:10 PM - 04:10 PM "Can I Get A Report of This?": Introduction to SSRS BI Platform Architecture, Development & Administration Jeremy Frye
03:10 PM - 04:10 PM Changing Your Habits to Improve the Performance of Your T-SQL Application & Database Development Mickey Stuewe
03:10 PM - 04:10 PM Configuring SQL Server for Mission Critical and Scalable Deployments Enterprise Database Administration & Deployment Suresh Kandoth
03:10 PM - 04:10 PM Make Your SQL Server Queries Go Faster Enterprise Database Administration & Deployment John Sterrett
03:10 PM - 04:10 PM Mission Possible: Interactive Performance Troubleshooting - Index Enterprise Database Administration & Deployment Jim Murphy
03:10 PM - 04:10 PM Overview of Security Investments in SQL Server 2016 and Azure SQL Database Enterprise Database Administration & Deployment Jamey Johnston
04:20 PM - 05:20 PM Analyze your query plan like a Microsoft Engineer! Advanced Analysis Techniques Daniel Janik
04:20 PM - 05:20 PM Introduction to Biml BI Platform Architecture, Development & Administration Tim Mitchell
04:20 PM - 05:20 PM Service Broker: Coach your processes asynchronously Application & Database Development William Wolf
04:20 PM - 05:20 PM SQL Server Database Encryption (TDE) Enterprise Database Administration & Deployment Ben Miller
04:20 PM - 05:20 PM SS2014 In-Memory Tables: Formula 1 Performance Unleashed! Application & Database Development Mike Byrd
04:20 PM - 05:20 PM Strategies for Working with Texas-sized Databases Enterprise Database Administration & Deployment Robert Davis

Wednesday, December 2, 2015

Idera #SQLChat with John Morehouse

Please join Idera tomorrow for a #SQLChat on Twitter. The December #SQLChat will take place Tuesday, December 9 at 11 a.m. central to 12 noon with John Morehouse (@Sqlrus). We will be discussing how to survive the holidays with SQL Server.

Other Idera ACE’s will be helping with the chat and helping answer questions as well as follow up questions. Get started or improve your existing SQL Server systems to minimize the amount of time spent working during the holidays.

They are also giving away a Kangaroo Mobile Desktop Computer during the #SQLChat


Thursday, November 26, 2015

Covering Indexes

Microsoft continues to improve indexes and options for additional performance enhancements. One I see frequently is the need for a covering index. Before we look at those options, let’s talk about the need for a covering index.

The term covering index was created probably a decade ago. The idea is for the index to cover all columns need to improve the performance of a query. This includes the filters in the WHERE clause as well as the columns in the SELECT section of a query. Before Include Columns and Column Store indexes, this had to be accomplished by adding the columns to index tree structure.

Here is an example query that would benefit from a covering index using the Adventure Works database from CodePlex:

USE [AdventureWorks2014]
SELECT soh.[SalesOrderID], [RevisionNumber], [OrderDate], [DueDate]
    , p.Name, p.ListPrice, sod.OrderQty
  FROM [Sales].[SalesOrderHeader] soh
    INNER JOIN [Sales].[SalesOrderDetail] sod ON sod.SalesOrderID = soh.SalesOrderID
      INNER JOIN [Production].[Product] p ON p.ProductID = sod.ProductID
  WHERE OrderDate Between '20130101' AND '20130101'
   AND p.Color = 'Black'

In the case of the above query, the Product table has an Index Seek on the clustered index which implements a Seek Predicate on the ProductID, but the Color column has a separate Seek which is the Predicate part of the query plan below. So, work is done in the Data Pages of the Clustered Index to find the proper Color value to match the second part of the WHERE (AND …) clause.


We can create a new index to “cover” the ProductID and Color in order to have only a Seek Predicate in the execution plan.

CREATE NONCLUSTERED INDEX idxProduct_ProductIDColor ON [Production].[Product]
  (ProductID, Color)

Now, the problem is the execution plane shows a Key Lookup to get the columns Name and List Price.


To cover the columns Name and ListPrice in the Product table, we need to add those columns to our index. Instead of adding to the end of the Column list like the following…


CREATE NONCLUSTERED INDEX idxProduct_ProductIDColor ON [Production].[Product]
  (ProductID, Color, Name, ListPrice)

…we can include it in the data page part of the index by using the INCLUDE part of the CREATE INDEX syntax.

CREATE NONCLUSTERED INDEX idxProduct_ProductIDColorIncludeListPrice
    ON [Production].[Product]
        (ProductID, Color)
    INCLUDE (Name, ListPrice)

Now, we have improved the performance of the query and limited the search part of the index structure to only the columns need for the Seek Predicate. In the image below, you can see in the output list the columns Name and ListPrice in addition to the Seek Predicates with extra Seek. The Object section of the display shows the index being used - idxProduct_ProductIDColorIncludeNameListPrice.


In conclusion, Microsoft has really helped us over the years with improvements to the Index creation. After we have created this new index, it is a good idea to start monitoring the indexes with DMVs/DMFs to see how often they are used (Reads and Writes), and if other indexes can be removed because they are not being utilized.

Thursday, November 5, 2015

Idera Ace

I started using a product called SQL Diagnostic Manager from Idera about 10 years ago at a Home Health company. It was very useful with monitoring a SQL Server instance and helped us estimate the future size of databases based on historical data. The numbers were very accurate for a 6 month and one year forecast.

I also discovered other tools for indexes and current state from the same company: Idera. Now, I have been selected as an Idera ACE for the Idera Community program to help speakers in the SQL Server Community.


They have given us the use of their peer network in order to blog about SQL Server and other Data topics. The best part is representing SQL Server and Idera at community events like SQLSaturday and PASS Summit. We will even be able to talk on webcasts with their support people.

Please visit their site and find out all about the wonderful tools for monitoring your systems. I got a great demo of BI Manager with capabilities to look at SSIS, SSAS and SSRS. The director of BI Manager development was a BI developer and he knows what needs to be captured through PerfMon(s) and DMV(s).

I am really looking forward to testing this monitoring tool during my upcoming sessions. Wish I had it before the Summit during my three hour tabular presentation.

The next 3 events I have scheduled are Live!360 (Orlando), SQLSaturday Austin and SQLSaturday Pensacola.

For Live! 360, here is a discount promo: Register for #Live360 Orlando with code LSPK49 and save $600!

Monday, October 19, 2015

PASS Summit 2015

Yes, it is that time of the year again. You know, when all the nut case and normal SQL Server experts collide in Seattle (or wherever the Summit is located) to experience the largest gathering of data experts on this planet. Well, I might be lying a little about the personalities of these people (including myself), but I am getting excited about next week.

PASS Summit 2015

I just tried to build my schedule of sessions to attend and had the same problem. 3 to 4 sessions that I want to see but only have time for one. Good thing these are recorded and I can watch after the event.


That is not the only problem. I have to find time to visit vendors, talk to attendees I know and have not meet yet and give a 3 hours session on Tabular plus the after hour events. This is an exhausting week, but has paid dividends 10 fold from attending 8 of the last 9 years.


If you have not attended this event and have the budget to do it, please do not pass up. You can read many blogs and articles about the benefits of membership to PASS plus sessions to learn about SQL Server and the Microsoft Data Platform roadmap.

Hope to see ya there and please come say hi to me!!!

Thursday, September 17, 2015

#ITDevCon15 Conner Cunningham Keynote

On Wednesday morning, Conner Cunningham from Microsoft was the keynote speaker for IT/DevConnections in Las Vegas. His talk focused on SQL Server 2016. To summarize the things he said that got my attention were a Live Query Plan, the Query Store to retrieve a previous query plan and Azure Dev First – then on-premise. Continue reading if you want more information.

The talk concentrated on SQL Server 2016. His favorite parts are making queries run faster because this is the area he helped in planning and development years ago. Now, he encourages the developers and architects to make queries run faster. DBAs love this stuff.

The talked started with an overview of the new features in releases 7.0 to 2014. It was good to see the progress in SQL Server from the early days to today.

Conner said SQL Server 2016 is a very, very big release – more announcements will come at PASS Summit 2015 at the end of October in Seattle.

The data engine is now 3 to 4 different actual engines in different development streams but using the same skill set for the DBA. The rise of cloud computing has shifted to development in Azure first, then using the same code base to release on-premise.

The development can be done faster now due to changes in the process at Microsoft and release and testing is easier because of the cloud. This gets feedback to the development team faster and updates to problems released sooner. What used to take 3-5 years, now is done in months.

Microsoft only vendor where on premise and cloud are fully supported on same code base!!!

Here is a list of new 2016 features


  1. Row level permissions to limit rows returned (select statement returns only the rows the user has permission to see.)
  2. TQE – Transparent Queryable Encrypt (better protection)
  3. Data Mask columns (x’s in a SSN)

AlwaysOn Improvements

  1. Log transport improvements
  2. DB level failover rather than instance
  3. Load balance readable secondary
  4. ActiveDirectory integration
  5. DTC transaction support  (this was a big one)
  6. 2 failover targets

Language enhancements

  1. JSON support 
  2. Temporal table support – Historical tracking of changes automatically, Think Audit Tables

In-Memory Engine – OLTP (2014)

  1. 5-20 times faster
  2. Collation support, JOINs, Large DBs, MARS support, Initial UDF/TDF support and row level security

Column store enhancements

  1. Updateable non-cluster column store indexes
  2. Non-clustered B-tree indexes
  3. Always On support – readable secondary support


  1. Query relational and non-relational data with T-SQL
  2. Hadoop support

Column store scales better with Degrees of Parallelism

  1. Batch mode scales far better

Stretch SQL Server in Azure

  1. For data you do not want to delete but need at some point
  2. Increases backup time where the warm data is backed up and cold data is in cloud (which has HA and backups/restores)
  3. For historical data


  1. Rewriting upgrade guide (was 429 pages)

Improved Upgrade advisor

  1. Try the tool

Eliminate Trace flags for High-End Scaling

  1. Most have been integrated in 2016

Optimizer Changes Now Tied to DB Compatibility Level

  1. Less risk for upgrades
  2. Trace Flag 4199 folded in new DB compatibility Level

Query Store

  1. Force prior plan
  2. Deep insight into workload performance
  3. Simplifies upgrades by reducing change risk

Conner says he has been working on Query Store for a long time and is extremely happy about this has finally been done

Monday, September 7, 2015

Dustin Ryan: Power Pivot 101: An Introduction

The PASS Excel Business Intelligence virtual chapter presents Dustin Ryan introducing everybody to Power Pivot in Excel. This chapter has gotten a lot of questions during some of the last couple of sessions that can be directly done in Power Pivot through Excel.

Please join us this Thursday September 10th at Noon Central time for our monthly Excel BI VC meeting. Below is more information:

Thu, Sep 10 2015 12:00 Central Daylight Time

Excel BI VC presents Dustin Ryan - Power Pivot 101: An Introduction



Power Pivot is a powerful yet flexible analytics tool built into a familiar environment yet many users remain unsure of how to take advantage of this dynamic tool. In this session, learn the purpose of Power Pivot, where Power Pivot fits within your organization and the basics of designing a Power Pivot model that integrates disparate data sources with the goal of gaining previously unrecognized insight into key business metrics.

Dustin Ryan is a senior BI consultant and trainer with Pragmatic Works in Jacksonville, FL. Dustin specializes in delivering quality enterprise-level business intelligence solutions to clients using SSRS, SSIS, SSAS, SharePoint, and Power BI. Dustin has authored and contributed to SQL Server books. You can find Dustin speaking at events such as SQLSaturday, Code Camp, and online webinars.