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

MORE DETAILS

25 FEBRUARY - SQLSaturday #589 - Pordenone 2017

MORE DETAILS

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

MORE DETAILS

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

MORE DETAILS

11 MARCHSQLSaturday #583 - Lisbon 2017

MORE DETAILS

11 MARCH - SQLSaturday #592 - Raleigh 2017

MORE DETAILS

11 MARCH - SQLSaturday #600 - Chicago 2017

MORE DETAILS

Friday, January 27, 2017

Upcoming PASS Free Training

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

January, 2017

Tuesday,January,31

Introduction to Oracle SQL and PL SQL

More Details

February, 2017

Wednesday,February,01

Linux OS Fundamentals for the SQL Admin

More Details

Tuesday,February,07

"ANSWERING THE QUESTION, “WHAT HAPPENED?” WITH QUERY STORE"

More Details

Thursday,February,09

Why Physical Database Design Matters By Kimberly L. Tripp

More Details

Tuesday,February,14

Partitioning Basics: Treating your tables like Lego

More Details

Tuesday,February,14

Detecting and Preparing for Corruption

More Details

Wednesday,February,15

Converting Unreliable Deployments Into Consistent Releases

More Details

Saturday,February,18

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.

image

 

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.

image

 

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.

image

 

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.

 

image

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.

 

image

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.

image

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.

 

image

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.

 

image

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.

WOW!!!

Friday, October 28, 2016

PASS Summit 2016 – Announcements

There have been different announcements for new and upcoming features in the Microsoft Data Technology ecosystem. The one today I am hearing about is Data Migration Assistant (DMA) tool v2.0 GA and Tech Preview of DEA – Data Experimentation Assistant. James Serra blogged about it here - http://www.jamesserra.com/archive/2016/10/pass-summit-announcements-dmadea/. The DEA helps you with understanding the performance improvements by upgrading.

image

The next one the Tech Preview of building PowerBI reports in SQL Service Reporting Services. For more info go to the PowerBI Community Blog - https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/10/28/create-power-bi-reports-in-the-sql-server-reporting-services-technical-preview/

The most exciting one for me is the Azure Analysis Services. It is only Tabular Models, right now, but OLAP is said to be in the works. This is interesting because it once was in the Cloud and then that cloud disappeared. It will be nice to see creating a DW in the Azure DWU, then connect to that data in Analysis Services and reporting in PowerBI, all in Azure. Never though I would say that Smile

 

image

 

Go read about Azure SSAS here - http://sqlmag.com/sql-server/microsoft-announces-azure-analysis-services-preview-pass-summit-2016 and https://azure.microsoft.com/en-us/blog/introducing-azure-analysis-services-preview/

Chris Webb has already wrote his first thoughts about Azure Analysis Services - https://blog.crossjoin.co.uk/2016/10/25/first-thoughts-on-azure-analysis-services/

PASS New Logo

image

Devin Knight presenting Advanced PowerBI

Media preview

Community Zone

image

PASS Summit 2016-Thursday Part II

The second half of the day was spent talking with various speakers and attendees. I got into a great conversation about trying to mentor developers at work places about how to write good T-SQL. It was interesting to here how some developers (or DBAs) are not open to new ways to write T-SQL like using CTEs or proper join syntax.

image

I meet Lance from the program committee and discussed the difficultly of having 800+ submissions and only about 140 slots for those submissions. To make sure you get a wide range of topics and not covering too little or too many of the same topic is not always easy. They do a great job with the volunteers to make sure Summit is a great learning opportunity for all.

I also saw a new posting on SQLServerCentral.Com about learning SQL Server 2016. Try this Link - http://www.sqlservercentral.com/articles/SQL+Server+2016/127518/.

image

The only session I attended was on learning how to price and scale an Azure SQL Server Data Warehouse. So, now I understand DWUs and pricing is 3 fold: DWUs, Storage and GEO DR. It can be expensive, but you get Massive Parallel Processing in the cloud and easier scaling up or down the usage.image

The last 2 session time slots was spent co-presenting with Bill Anton on Performance Tuning Analysis Services. It was well attended but in a large room. There was about 2/3 of the people returning after the break for the 3 hour session. Bill did a great job getting people started with Profiler, PerfMon and some free tools and scripts. Lots of questions and even a Microsoft developer helping us answer questions. Thanks Microsoft.

Last stop of the day was dinner with some great guys from Atlanta at Rock Bottom. They went to a BIML party, I went to bed. Smile