A Fine Slice Of SQL Server
Julia Silge revisits her Hacker News word vectorization problem:
So hooray! We have found word vectors again, a bit faster, with clearer and easier-to-understand code. I do argue that this is a real benefit of this approach; it’s based on counting, dividing, and matrix decomposition and is thus much easier to understand and implement than anything with a neural network. And the results?
Click through to see the new method, as well as some basic analogy testing.
Comments closedJen Underwood has some good tips when preparing data for a machine learning operation:
Data preparation for machine learning requires business domain expertise, bias awareness and an experimental thought process. Before preparing your data, you’ll first define a business problem solve. During that exercise, you’ll select an outcome metric and brainstorm potential input variables that influence it from many varied perspectives. From there you will begin identifying, collecting, cleaning, shaping and sampling data to run through automated machine learning model processes.
Note that it is also not unusual for relevant machine learning input data to occur outside of existing transactional processes. If that is the case, you can still start creating a first-generation machine learning model with existing data and continue to build new model versions over time as supplementary data is acquired.
Click through for the ten tips.
Comments closedTracy Boggiano has some advice on configuring and monitoring distributed Availability Groups:
Monitoring can be tricky with distributed AGs because of the way it shows up in SSMS. The distributed AG created on the primary AG does not have the ability to show you a dashboard to monitor traffic like the typical AG and the secondary replicas do not even show the distributed AG. So, to monitor the distributed AG you are best to head to the DMVs. DMV sys.availability_groups has the is_distributed column that will allow you to only see the distributed AGs. Below is a query adapted from the Microsoft Docs query to add additional information to check on the status of the distributed AGs which you must run on the primary AG.
Click through for more details.
Comments closedKellyn Pot’vin-Gorman explains what the Legacy Cardinality Estimation setting does in SQL Server:
Oracle DBAs have used the CARDINALITY hint for some time and it should be understood that this may appear to be similar, but is actually quite different. As hinting in TSQL is a bit different than PL/SQL, we can compare similar queries to assist:
TSQL
SELECT CustomerId, OrderAddedDate FROM OrderTable WHERE OrderAddedDate >= '2016-05-01'; OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); goPL/SQL
Where you might first mistake the CE hint for the following CARDINALITY hint in Oracle:
SELECT /*+ CARDINALITY(ORD,15000) */ ORD.CUSTOMER_ID, ORD.ORDER_DATE FROM ORDERS ORD WHERE ORD.ORDER_DATE >= '2016-05-01';This would be incorrect and the closest hint in Oracle to SQL Server’s legacy CE hint would be the optimizer feature hint:
SELECT /*+ optimizer_features_enable('9.2.0.7') */ ORD.CUSTOMER_ID, ORD.ORDER_DATE FROM ORDERS ORD WHERE ORD.ORDER_DATE >= '2016-05-01';If you’re wondering why I chose a 9i version to force the optimizer to, keep reading and you’ll come to understand.
Read on for the comparative explanation as well as more details on SQL Server’s legacy cardinality estimator hint and database-scoped configuration setting.
Comments closedDmitri Korotkevitch shows a way of dealing with non-empty partitions on columnstore indexes:
The common, by the book approach recommends dropping columnstore index, splitting or merging partitions and recreating the index afterwards. As you can imagine, it would lead to extremely inefficient process with huge amount of unnecessary overhead on large tables. After all, you have to drop and recreate columnstore index, converting table to Heap, while just subset of the partitions needs to be rebuilt. Fortunately, you can minimize the overhead with simple workaround:
Switch partition(s) to split or merge to the separate staging table
Split or merge partition(s) in the main table. You can do that because partitions will be empty after the previous step
Drop columnstore index in the staging table, split/merge partition(s) there and recreate the index afterwards
Switch partition(s) back from staging to the main table.
Read on for a detailed walkthrough of these steps.
Comments closedErin Stellato gives us a starting point for good values for Query Store settings:
QUERY_CAPTURE_MODE
The default value for SQL Server 2016 and SQL Server 2017 is ALL. For Azure SQL Database, the default value is AUTO.
With AUTO, queries that are insignificant from a resource utilization perspective, or executed infrequently, are not captured. If you need to capture queries that may only execute a few times, or those that use very few resources, then use ALL. Otherwise, use AUTO, as this will capture the relevant majority of your workload.
There is a third option, NONE, where no new queries are captured. Runtime and wait statistics will continue to be captured for queries that are already in Query Store.
I recommend setting this option to AUTO, as the number of queries in your environment that need tuning/your attention is a small percentage of the total number of queries that execute. You won’t miss out on important data if you exclude queries that don’t use a lot of resources or don’t execute very often.
Click through for good defaults for all nine settings.
Comments closedGinger Grant shows how to install SQL Server 2017 Machine Learning Services:
There are two installation options: In-Database or Standalone. If you are evaluating Machine Learning Services and you have no knowledge of what the load may be, start by selecting the Machine Learning Service In-Database. There are several reasons why by default you want to select the In-Database option. One of the problems that Microsoft was looking to solve by incorporating advanced data analytics was to improve performance of the native code by greatly reducing data latency. If you are analyzing a lot of data which is stored within SQL Server, the performance will be improved if the data does not need to be moved around on a network. Also, the licensing costs of installing R Server standalone also need to be evaluated with a Microsoft representative as well. An evaluation of the resource load on the network, as well as analysis of the code running on SQL Server should be performed prior to the decision to install the Machine Learning Server Standalone.
Read the whole thing.
Comments closedRob Sewell shows how to iterate through a collection of Pester tests:
The problem with Test Cases is that we can only easily loop through one collection, but as Pester is just PowerShell we can simply use ForEach if we wanted to loop through multiple ones, like instances and then databases.
I like to use the ForEach method as it is slightly quicker than other methods. It will only work with PowerShell version 4 and above. Below that version you need to pipe the collection to For-EachObject.
Check it out for a good explanation of running groups of Powershell tests.
Comments closed