A Fine Slice Of SQL Server
The more I work with Microsoft Fabric Data Factory, the more I like it.
In this post, I share one way to schedule a Microsoft Fabric Data Factory pipeline, a different way to test execute a pipeline, and then one way to monitor the execution.
This is all built right into the Microsoft Fabric UI, making it that much easier to use.
Comments closedIn this post I cover a script I’ve put together for measuring storage write speeds in SQL Server, namely against database data files.
This is meant to help get an idea of how the underlying storage performs when SQL Server is writing 1GB of data to a database.
At this point, you might be asking yourself: “Why not use CrystalDiskMark instead?”.
The answer is simple: you might not always be able to install/run additional software in an environment. Even more so if you work with external customers or you’re a consultant. It’s a lot simpler to ask a customer to run a script and send you the output, than it is to ask them to install and run some 3rd party software.
Click through for the script, what it does, and how to run it, as well as a note on limitations and example based on three drives.
Comments closedSteven Sanderson explains and does:
Before we dive into the how, let’s briefly discuss why you might want to set a column as the index in your data frame. By doing so, you essentially designate that column as the unique identifier for each row in your data. This can be particularly useful when dealing with time-series data, categorical variables, or any other column that serves as a natural identifier.
Setting a column as the index offers several advantages:
Read on to see those advantages.
Comments closedSandeep Pawar builds some really cool diagnostics:
Semantic Link v0.6 is out and it has many new exciting additions to its growing list of
list_*methods. Highlighted are some of the new methods. Install the latest version and check it out.Some of the existing methods such as list_columns() have an additional parameter
extendedwhich returns more column information such as column cardinality, size, encoding and many more column properties. This allows users to get detailed information about the dataset and the columns.
Click through to see how you can get this information not just for a single semantic model, but for all semantic models in a tenant.
Comments closedKendra Little goes on a journey:
It took me more than half hour to figure out how to start an XEvents trace on a read-scale out instance of Azure SQL Managed Instance. It’s hard to monitor read scale-out instances, so tracing is desirable! I started with a simple trace of sql_statement_completed. Hopefully this saves other folks some time.
Click through for that process. The process seems a bit painful, to put it kindly.
Comments closedErin Stellato shares an update:
SSMS 20 is the first major version of SSMS that supports Strict encryption and TLS 1.3, thanks to the migration to Microsoft.Data.SqlClient (MDS) 5.1.4. MDS is the data access library used by SSMS 19 and higher, as well as other SQL Server tools.
Read on for a quick primer on terminology, as well as what it means to force strict encryption. I’m not sure how quickly companies will jump on this, especially given the features that don’t support strict encryption yet, such as availability groups, replication, SQL Server Agent, database mail, linked servers, and PolyBase’s connector to SQL Server.
Comments closedJeff Hicks makes a connection:
I have a short tip today that you may find useful, especially if you write modules for your private use. I have a number of such modules that I have written to fill my needs. These are private modules that I don’t publish to the PowerShell Gallery. I develop and maintain these modules in
C:\Scripts. This means that when I need to import the module, I have to type the full path.
Read on to see how you can use symbolic links to make this a bit smoother.
Comments closedBen Johnston eats the elephant:
One of the first things you learn when working with SQL Server, and other transactional based SQL systems, is that set based operations perform best. If you are querying data, a cursor pulling individual rows doesn’t perform as well as a single query. It doesn’t matter if that cursor is on the client side or the server side. A set-based operation is more efficient, runs faster, locks less, and is generally better than submitting multiple queries.
This is also generally true with delete statements. This post covers the exceptions to that rule. Large delete statements impacting many rows and large amounts of data (millions of rows and many gigs of data) can actually have decreased performance. With transactional systems, such as SQL Server, each transaction follows the ACID standard. Part of that standard ensures that transactional statements either complete or roll back fully – partial transactions are not allowed. For a delete statement, that means that all of the rows specified by the delete are removed from the table, or none are removed and the data rolls back to the original state. The delete and rollback behavior must be predictable and consistent or the data could be left in a contaminated, unreliable state. Performing very large deletes can present some challenges and needs to be treated differently in production systems.
Read on for the reasoning behind this, as well as several techniques you can use and how they compare.
Comments closedGinger Grant gives us an overview of where we can use Apache Spark in Microsoft Fabric:
If you have used Spark in Azure Synapse, prepare to be pleasantly surprised with the compute experience in Microsoft Fabric as Spark compute starts a lot faster because the underlying technology has changed. The Data Engineering and Data Science Fabric experiences include a managed Spark compute, which like previous Spark compute charges you when it is in use. The difference is the nodes are reserved for you, rather than allocated when you start the compute which results in compute starting in 30 seconds or less versus the 4 minutes of waiting it takes for Azure Synapse compute to start. If you have different capacity needs that a default managed Spark compute will not provide, you can always create a custom pool. Custom pools are created in a specific workspace, so you will need Administrator permissions on the workspace to create them. You can choose to make the new pool your default pool as well, so it will be what starts in the workspace.
Read on for more of Ginger’s thoughts on the matter, including how you can use Copilot in Microsoft Fabric (if you pay for it) to help generate Spark code.
Comments closedChad Callihan restores an elephant one bite at a time…or something:
The larger a database grows, the more difficult it becomes to restore it in a timely manner. When a database is young, you might be able to manage full restores in seconds. But as it matures and backup sizes go from megabytes to gigabytes to terabytes, those restore times will expand as well.
If you plan ahead, it’s not always a requirement to restore the entire database if only part of the database is necessary. This is where the idea of piecemeal restores can save you time and wasted effort.
I’ve always found piecemeal database restoration more of an interesting idea than something quite practical. The problem is, if your data is so easily separable that you can restore one set and not need the other for some reasonable length of time, why are they in the same database? I understand that there are reasonable answers to this question, but I also rarely see those scenarios pop up.
Comments closed