Tuesday, March 21, 2017

SSIS Articles on SQLShack

Please check out SQLShack.com for three articles on SSIS. I wrote them based on using the SSIS Catalog and Project Parameters created in SQL Server 2012 and improved on in the next 2 releases. Project parameters are a big deal in SSIS for changing values when executing a package or packages. The parameters are at the project level so you only have to change them once to effect multiple packages.

I use a simple example like changing the database name and server (instance) name for the Project level Connection Manage WHICH is another big change for SSIS. Starting in 2012, you can share connections among packages in a project. Enjoy!!!

Parametering Database Connection in SQL Server Integration Services

Deploying Packages to SQL Server Integration Services Catalog (SSISDB)

How to execute a Deployed Package from the SSIS Catalog with various options

Friday, March 17, 2017

PASS Excel BI VC: Improvements to Excel Pivot Table

Please join the PASS Excel Business Intelligence Virtual Group onn March 22nd 11AM Eastern for Microsoft’s Program manager Alexander Lahuerta. The presentation will be shorted than typical meetings, lasting 20-30 minutes.

But, the session will provide great insights into changes in Excel Pivot tables. You can register for the session at http://excelbivc.sqlpass.org or https://attendee.gotowebinar.com/register/3161827486429234947

Featured Presentation:  Improvements to Excel Pivot Tables
Alexander Lahuerta, Program Manager Microsoft

The Excel team is working on several interesting new features to Excel. Join us to take a look at some really interesting improvements to Excel that are soon to be released.

About Alexander:
I’m a Program Manager on the Excel Team at Microsoft. I’ve been here for three and a half years. For the past two years, I’ve been diving deep into Business Intelligence and making it easier for people to dig into their data. I want you to love PivotTables as much as I do.

Wed, Mar 22 2017 15:00 (UTC+00:00) Dublin, Edinburgh, Lisbon, London

Improvements to Excel Pivot Tables

RSVP: https://attendee.gotowebinar.com/register/3161827486429234947

Thursday, February 23, 2017

Upcoming PASS Events

There is a new website from PASS. Go to Pass.org instead of SQLPass.org. The change does not really concern me but marketing is something I am not into…yet!!!

The one thing I do like is the formatting when viewing on a mobile device. That is very nice. Here is what I found coming up in the PASS community.


BA Marathon – Business Analytics

There will be six back-to-back live webinars and the next BA Marathon will be spring 2017.

While you wait, why not access recordings from previous Marathons?


Here are some Virtual Chapter Recordings

SQL Server DBA Tips That Can Save Your A$$ -- Part 2 - Guy Glantser

Implementing New Security Features in SQL Server 2016, Part 2 – sponsored by Hewlett Packard Enterprise - Ronen Ariely

Why Your Data Type Choices Matter - Andy Yun

Implementing (and cracking) new Security Features in SQL Server 2016, Part 1 – sponsored by Hewlett Packard Enterprise - Ronen Ariely

Take Power BI Visualizations to the Next Level - Reza Rad

Azure SQL Database Elastic Pool – introduction and usage scenarios – sponsored by Hewlett Packard Enterprise - Kun Cheng

How SQL Server 2016 SP1 Changes the Game - Parikshit Savjani

Advanced Power BI: Solving the Hard Problems - Devin Knight

An (Advanced) Introduction to DAX - Eugene Meidinger


Here are some upcoming SQLSaturdays

25 FEBRUARY - SQLSaturday #585 - Boston 2017


25 FEBRUARY - SQLSaturday #589 - Pordenone 2017


25 FEBRUARY - SQLSaturday #596 - Denver - BI Edition 2017


04 MARCH - SQLSaturday #603 - Dallas - BI Edition 2017


11 MARCHSQLSaturday #583 - Lisbon 2017


11 MARCH - SQLSaturday #592 - Raleigh 2017


11 MARCH - SQLSaturday #600 - Chicago 2017


Friday, January 27, 2017

Upcoming PASS Free Training

Here is a list of the upcoming PASS Virtual Groups and SQLSautrday Events.

January, 2017


Introduction to Oracle SQL and PL SQL

More Details

February, 2017


Linux OS Fundamentals for the SQL Admin

More Details



More Details


Why Physical Database Design Matters By Kimberly L. Tripp

More Details


Partitioning Basics: Treating your tables like Lego

More Details


Detecting and Preparing for Corruption

More Details


Converting Unreliable Deployments Into Consistent Releases

More Details


Why Your Data Type Choices Matter

More Details


Feb 04, 2017    SQLSaturday #595 - Cleveland 2017

Feb 11, 2017    SQLSaturday #582 - Melbourne 2017

Feb 18, 2017    SQLSaturday #587 - Sydney 2017

Feb 18, 2017    SQLSaturday #586 - Guatemala 2017

Feb 25, 2017    SQLSaturday #589 - Pordenone 2017

Feb 25, 2017    SQLSaturday #596 - Denver - BI Edition 2017

Feb 25, 2017    SQLSaturday #585 - Boston 2017

Mar 04, 2017    SQLSaturday #603 - Dallas - BI Edition 2017


Saturday, January 14, 2017

Upcoming SQL Server Events from PASS

There are many ways to reap benefits from being a member of the PASS Global Community. Today, as I write this blog, I glance over at twitter for hash tag #SQLSatNash and see all the speakers, volunteers and attendees express their love of this free event. You really never see “bad” tweets about one of these events.

image    image

SQLSaturday is just one of the many ways to earn about Microsoft Data Technologies. Click this link to see if an event is coming close to your town or city.

24 Hours of PASS happens a couple of times a year when there 24 hours of consecutive sessions. This is a Free event online.

Another way is to watch a virtual user group online for free. Click this link for a list of Virtual User Groups. Below is a list of upcoming events. This list can be viewed on the main page of SQLPass.org for your profile after you login.

Upcoming Events of Interest

There is a View All link to see a drill down of all the events.

Paid events include Pre-conference sessions at SQL Saturday, a week of sessions at the SQL PASS Summit and the Business Analytics area just had their first Day of Business Analytics in Chicago.

An individual can create many ways to take advantage of these resources to advance in his or her career. I have seen many people get involved with PASS and totally change the direction of there career. Some in consulting, some with a new company or some even get to work for some of the great software companies that are sponsors of PASS events. Start at SQLPass.org to find out what you can do to help in your current career or trying something new.

Tuesday, December 27, 2016

Query Data Store–SQL Server 2016

SQL Server’s new Query Data Store is a brilliant option for databases if you are a DBA or have to do performance tuning on T-SQL queries. This new feature of SQL Server 2016 is contained within a database with configuration options for storage of history and query plans. The option has many benefits you can take advantage of today, but also this is an option for future upgrades to new SQL Server versions because of the history contained in the database rather than the instance.



To enable the Query Data Store, you have to be db_owner of the database or SysAdmin to the instance. Once you right-click the database in SQL Server Management Studio (SSMS), go to the Query Store menu choice on the left and change Operation Mode (Requested) to Read Write. There are other options to handle the time retention of data as well as how often it is collected.



This will enable the database to start storing query execution plans as well as statistics like IO, Read/Write and execution times in the database itself. There are new Data Management Objects (DMVs/DMFs) to use to manual retrieve this data.



There are also reports available at the database level in SSMS to view information about query statistics. If you drill into the database in SSMS, you will see an option for Query Store.



Here, you will see the four default reports that come with this Option.

  1. Regressed Queries – shows query history and changes in statistics
  2. Overall Resource Consumption – history of resources used in the database
  3. Top resource Consuming Queries – Top x of queries using the most resources
  4. Tracked Queries – enables you to see multiple query plans for a T-SQL statement and compare the plans or force a plan

The above is just a summary of what you are able to do, but just start using this feature and you will be amazed at what it enables DBAs and others to drill into. This drilling is just the history of queries on one database rather than you having to sort through all plans for an instance.

Thursday, November 10, 2016

SQL Server 2016–Live Query Statistics

WOW!!! The next great update to Execution Plans is a way to watch the processing of rows through iterators while the query runs. Never saw that coming. And even a bonus, you can use SQL Server 2016 Management Studio (SSMS) to watch queries run on versions 2014, 2012 and 2008 R2.

SSMS 2016 can be installed by itself without the server installation. Go here for the installation. Once you install SSMS, you can start to view this addition in SSMS.like Figure 1.



Figure 1 – Live Query Statistics

After opening a New Query, go to the Query menu choice and select “Include Live Query Statistics.” You will need a long running query in order to see the processing happening at a slow pace.


Figure 2 – Include Live Query Statistics

Once you start a query, a new tab will show the Live Query Statistics like Figure 3. The dotted lne means the iterator has not finished or has not started.



Figure 3 – New Tab

The labeling of the iterators have the usually properties as an Execution Plan plus a running time clock of seconds that this iterator is using. In addition, you can see the “Actual Number of Row” of “Esitmated number of Rows” with a calculated Percent Done (Actual/Estimated). Figure 4 shows 144% for some iterators that have not even finished yet.



Figure 4 – Percent Greater Than 100

This is useful to see if estimate number of rows is the same or close to actual number of rows. The percentage will rise above 100 if there are more actual than estimated rows. The query in this case probably could use some performance tuning to get the estimate closer to the actual.