A Fine Slice Of SQL Server
Kendra Little walks through page-level details on a non-clustered index:
Just like in the root page and the intermediate pages, the FirstName and RowID columns are present.
Also in the leaf: CharCol, our included column appears! It was not in any of the other levels we inspected, because included columns only exist in the leaf of a nonclustered index.
Kendra does a great job of explaining the topic.
Comments closedPerry Skountrianos shows how to configure ASP.Net to use memory-optimized tables for session state:
ASP.NET session state enables you to store and retrieve values for a user as the user navigates the different ASP.NET pages that make up a Web application. Currently, ASP.NET ships with three session state providers that provide the interface between Microsoft ASP.NET’s session state module and session state data sources:
- InProcSessionStateStore, which stores session state in memory in the ASP.NET worker process
- OutOfProcSessionStateStore, which stores session state in memory in an external state server process
- SqlSessionStateStore, which stores session state in Microsoft SQL Server database
This blog post focuses on the SqlSessionStateStore provider and describes how you can configure it to use SQL Server In-Memory OLTP as the storage option for session data. You can either use the latest ASP.NET async version of the SQL Session State provider (which is the recommended approach), or configure an earlier version of the provider to work with In-Memory OLTP by downloading and running the In-Memory OLTP SQL scripts from our sql server samples github repo.
The me of seven years ago really needed this. But with the strong shift against session-based data collection and back to stateless or client-held state paradigms, I’m not sure how many people this helps.
Comments closedReza Rad announces a new feature of Power BI Helper:
I’m excited to share the news with you that we have added a new feature in Power BI Helper; Expression Tree. Expression Tree will expand the tree of expression for a Measure or calculated column, you can see what other measures are used to create this expression, and where other measures, calculated columns, or even normal columns are located (in which table). This feature is in addition to previous two features of this tool which were; Showing tables and fields used in visualization pages of a Power BI Report, and ability to search for a column or table that used in visualization pages of a report. In this post, I’ll explain how this new feature works.
Read on for the explanation. I can see this being quite useful.
Comments closedStuart Moore shows off a few examples of advanced Restore-DbaDatabase usage:
No matter how hard the dbatools; team tries, there’s always someone who wants to do things we’d never thought. This is one of the great things with getting feedback direct from a great community. Unfortunately a lot of these ideas are either too niche to implement, or would be a lot of complex code for a single use case.
As part of the
Restore-DbaDatabasestack rewrite, I wanted to do make things easier for users to be able to get their hands dirty within the Restore stack. Not necessarily needing to dive into the core code and the world of GitHub Pull Requests, but by manipulating the data flowing through the pipeline using standard PowerShell techniques, all the while being able to do the heavy lifting without code.
Click through for several examples.
Comments closedJulia 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 closed