The other option in the Compute Size drop‐down is Custom, which enables the Compute Type and Core Count boxes, allowing you to select up to 256 (+ 256 driver cores) for either Basic or Standard type. In addition to allowing

Continue ReadingOptimize Pipelines for Analytical or Transactional Purposes – Troubleshoot Data Storage Processing-5

Both data flow performance and diagnostic metric sources just presented help you determine where to start with your optimization effort. From both a source and sink perspective, recall the configurations available through their numerous tabs, for example, Projection and Optimize,

Continue ReadingOptimize Pipelines for Analytical or Transactional Purposes – Troubleshoot Data Storage Processing-4

It is important to state that you should utilize all DMVs that are discussed in this chapter. This section is focusing specifically on the transaction DMVs, but all of them are useful in finding latency and implementing tuning techniques. This

Continue ReadingOptimize Pipelines for Analytical or Transactional Purposes – Troubleshoot Data Storage Processing-3

This code results in the creation of an updatable columnstore index on which OLAP operations can operate. Additionally, memory will be allocated to handle OLTP transactions that get regularly ingested into the columnstore index. This has effectively separated the OLTP

Continue ReadingOptimize Pipelines for Analytical or Transactional Purposes – Troubleshoot Data Storage Processing-2

Much of the effort required to optimize and tune a pipeline lies in the discovery phase. That means trying to find where the slowness is coming from. Once you locate the general area, you can further debug by drilling down

Continue ReadingOptimize Pipelines for Analytical or Transactional Purposes – Troubleshoot Data Storage Processing-1

The benefits of caching have been stated numerous times throughout this book and in this chapter. The fact is, retrieving data from memory is faster than retrieving it from disk, which is an I/O transaction. What has not been mentioned

Continue ReadingTune Queries by Using Cache – Troubleshoot Data Storage Processing

In this scenario it would make sense to create a clustered index that contains those two fields, something like the following. The indexes on the dedicated SQL table can be seen in SSMS, as illustrated in Figure 10.8. For more

Continue ReadingTune Queries by Using Indexers – Troubleshoot Data Storage Processing-3

A disadvantage of clustered indexes is that the only queries that benefit from them are those that filter the query in the way the index is created. This means that only queries that have those four IDs in theWHERE clause

Continue ReadingTune Queries by Using Indexers – Troubleshoot Data Storage Processing-2

Indexes reduce query times by creating pointers to the location where data is stored in the database. Figure 10.6 illustrates how an index on a column named EF_ID is referenced and used by the SQL query to retrieve all the

Continue ReadingTune Queries by Using Indexers – Troubleshoot Data Storage Processing-1

The result identifies that the partitions have been overprovisioned. It would be prudent to repartition from three to two so that your queries can benefit from the performance gained by the clustered columnstore and compressed segments. The equation should also

Continue ReadingTune Shuffle Partitions – Troubleshoot Data Storage Processing-4