VSphere 6.5 And VNUMA

David Klee notes that vSphere 6.5 might modify vNUMA settings on you:

I appreciate their attempt to improve performance, but this presents a challenge for performance-oriented DBAs in many ways. It now changes expected behavior from the basic configuration without prompting or notifying the administrators in any way that this is happening. It also means that if I have a host cluster with a mixed server CPU topology, I could now have NUMA misalignments if a VM vMotions to another physical server that contains a different CPU configuration, which is sure to cause a performance problem.

Worse yet is that if I were to restart this VM on this new host, the hypervisor could automatically change the vNUMA configuration at boot time based on the new host hardware.

I now have a change in vNUMA inside SQL Server. My MaxDOP settings could now be wrong. I now have a change in expected query behavior. 

Definitely read the whole thing if you’re using vmWare.

Running MapReduce Polybase Queries

I have a post which digs into what happens when a Polybase query invokes a MapReduce job:

Stream 2 sends along 27 MB worth of data.  It’s packaging everything Polybase needs to perform operations, including JAR files and any internal conversion work that the Polybase engine might need to translate Hadoop results into SQL Server results.  For example, the sqlsort at the bottom is a DLL that performs ordering using SQL Server-style collations, as per the Polybase academic paper.

Stream 2 is responsible for almost every packet from 43 through 19811; only 479 packets in that range belonged to some other stream (19811 – 43 – 18864 – 425 = 479).  We send all of this data to the data node via port 50010.

If you love looking at Wireshark streams, you’ll love this post.

Cardinality Estimator Regressions

SQL Scotsman has a great post on figuring out which of your queries have become worse as a result of the SQL Server cardinality estimator changes in 2014:

Instantly it is apparent that the most resource intensive query was the same query across both workload tests and note that the query hash is consistent too.  It is also apparent that this query performs worse under the new cardinality estimator model version 120.  To investigate and understand why this particular query behaves differently under the different cardinality estimators we’ll need to look at the actual query and the execution plans.

Looking at the information in #TempCEStats and the execution plans, the problematic query below belongs to the SLEV stored procedure.

There’s also a discussion of Query Store in there, but it’s important to understand how to figure this out even if you’re on 2014 and don’t have access to Query Store.

Power BI Synoptic Panel

Devin Knight’s series on Power BI visuals continues with the synoptic panel:

  • The Synoptic Panel allows you connect areas in an image with attributes in your data model.

  • Using the Synoptic Designer you can convert an image that you have to one that is compatible with plotting out data points on it.

  • You can fill each area with a color or a saturation of a color.

The Synoptic Panel is definitely a more advanced visual, but it’s very powerful.

Tuning Indexes For Stored Procedures

Kendra Little offers advice on index tuning within stored procedures:

The best way is to run the stored procedure yourself to generate and save an “actual” execution plan, which contains the estimates SQL Server used when it generated the plan as well as actual rowcounts, actual memory granted, etc. It will also contain a green tooltip with a “missing index request” if SQL Server thinks an index would help.

This is an introductory-level post which contains good advice.

Pushing An Image To Docker

Andrew Pruski walks us through pushing an image up to Docker so we can use it later:

And there it is, our image in our repository tagged as v1! The reason that I’ve tagged it as v1 is that if I make any changes to my image, I can push the updated image to my repository as v2, v3, v4 etc…

Still with me? Awesome. Final thing to do then is pull that image down from the repository on a different server. If you don’t have a different server don’t worry. What we’ll do is clean-up our existing server so it looks like a fresh install. If you do have a different server to use (lucky you) you don’t need to do this bit!

Read the whole thing.

New Version Of APS

James Serra notes that SQL Server Extremely Expensive Edition has a new version out:

This release is built on the latest SQL Server 2016 release, offers additional language surface coverage to aid in migrations from SQL Server and other platforms, adds PolyBase connectivity to the current versions of Hadoop from Hortonworks, additional PolyBase security with Kerberos support and credential support for Azure Storage Blobs, greater indexing and collation support and improvements to the setup and upgrade experience with FQDN support.

The majority of these capabilities have shipped in the monthly releases of Azure SQL Data Warehouse service and/or SQL Server 2016 following the cloud first principle of shipping, getting feedback, and improving rapidly across all of our products.

Click through for the list of enhancements.  There are quite a few of them.

Database Restoration In Linux Via SSMS

Andrew Peterson walks through the easy way of restoring a database backup to a Linux installation of SQL Server:

But my Backup file is still not visible in the wizard!

Permissions.  If you drill down into the folders in Linux, we found that the files already present in the /data/ folder are owned by the user mssql.  Our recently copied backup file is NOT owned by mssql, and it not accessible to other users. So, our wizard cannot see the file.

The whole process is pretty straightforward.

Cases For Using Azure Analysis Services

Melissa Coates enumerates several reasons why you might want to use Azure Analysis Services:

Varying Levels of Peak Workloads

Let’s say during month-end close the reporting activity spikes much higher than the rest of a typical month. In this situation, it’s a shame to provision hardware that is underutilized a large percentage of the rest of the month. This type of scenario makes a scalable PaaS service more attractive than dedicated hardware. Do note that currently Azure SSAS scales compute, known as the QPU or Query Processing Unit level, along with max data size (which is different than some other Azure services which decouple those two).

Read on for more use cases.


November 2016
« Oct Dec »