Press "Enter" to skip to content

Day: June 1, 2021

NameNode and Secondary NameNode in Hadoop

Published 2021-06-01 by Kevin Feasel

The Hadoop in Real World team hit on a naming scheme that I think is bad:

NameNode is the heart of HDFS. NameNode maintains the metadata of HDFS – files, list of blocks, directories, permissions etc. The metadata is persisted on a file named FSIMAGE. During the start up of NameNode, the FSIMAGE file will be read and loaded into memory. 

Any ongoing changes to the files, directories in FSIMAGE will be written to memory and to a temporary log file. NameNode does not save the ongoing changes to FSIMAGE directly and this is because FSIMAGE file could be big for a big HDFS and updating a big file at runtime will be quite expensive and slow.

Read on to learn what the secondary NameNode does. As a hint, it’s not a secondary NameNode in the sense of high availability. If you’re a new Hadoop administrator, the name can be deceiving, letting you think you have high availability when you really don’t.

Comments closed

Dealing with Key Lookups

Published 2021-06-01 by Kevin Feasel

Jared Poche shows a good technique for removing key lookups:

A key lookup is an operation that occurs when a query has used a nonclustered index on a given table, but needs to access more columns to complete the query. It may need to check columns not in that index for additional filters, or it may just need to return that column as part of its result set.

In the simple query above, we’re retrieving 100 rows from the seek against a nonclustered index, then performing a key lookup against the clustered index. There is a nested loops operator between the two and understanding how that operates is important; for each row we receive from the first table, we perform the second operation once. So, in this query we are seeking 100 rows from the nonclustered index, then performing the key lookup 100 times. We go through the index once for each row we return, and you can see the cost of the key lookup operator is 99% of the query.

Read on for more information, including how to eliminate key lookups.

Comments closed

Whither Code Page 1

Published 2021-06-01 by Kevin Feasel

Solomon Rutzky unravels a mystery:

Collation names, at least in SQL Server, are structured in that they contain various pieces of information detailing some of the properties of the collation. The collation name of SQL_Latin1_General_CP1_CI_AS can be broken down as follows:

– SQL_ This indicates that the collation is a SQL Server collation, while names without this prefix indicate Windows collations

– Latin1_General This is the culture (a.k.a. locale) used for the sorting and comparison rules

– CP1 This is the 8-bit [C]ode [P]age

– CI This indicates [C]ase- [S]ensitive or [I]nsensitive

– AS This indicates [A]ccent- [S]ensitive or [I]nsensitive

Ok, but there’s no code page “1”. So, what’s going on there?

Read on to learn the answer behind this mystery.

Comments closed

Enumerating Breaking Changes to Power BI Reports

Published 2021-06-01 by Kevin Feasel

Brett Powell gives us a list of things which might cause breaking changes in Power BI reports:

A breaking change, which we can define as any change to a dataset which causes either reports to render errors or the dataset to fail to refresh, can severely impact business workflows and reflect poorly on those responsible for the solution. Given significant investments in other areas of the organization’s data estate such as Azure Synapse Analytics, a simple, easily avoidable oversight in a Power BI deployment may not be tolerated.

Read on for the list.

Comments closed

Speeding Up Azure Data Factory Pipelines

Published 2021-06-01 by Kevin Feasel

Hiram Fleitas doesn’t have all day to wait for that pipeline to finish:

His issue was pretty much as mentioned on the tile. Our bank’s Azure Data Factory pipeline is running slow moving data from on-prem, we’re copying all tables in a SQL Server database, files from ftp sites and network share drives to Azure SQL DB Managed Instance and to blob storage (our datalake) , do you have some recommendations how to make it go faster? Its around 300GBs and takes over 8 hrs.

So I replied with the following and figured to post it here as it may help others.

Hiram has a video, as well as specific advice to offer.

1 Comment

What to Do with Temp Tables in Stored Procedures

Published 2021-06-01 by Kevin Feasel

Chad Callihan invokes Betteridge’s Law of Headlines:

Generally speaking, it’s best to put things away that aren’t being used. Don’t keep indexes that aren’t getting utilized because they are taking up disk space and still have to be kept up to date with changes. A table is still loaded up with old data that’s not being used but needs kept? Maybe it’s time for options like an archive database or partitioning.

While it’s not on the same level of importance, one related argument I’ve seen and been in is how to handle temp tables in stored procedures. Do you drop them at the end of a stored procedure or do you leave them to be cleaned up by SQL Server? Is one way better for performance than the other? Let’s do some testing and see what we find out.

Read on for the answer.

Comments closed

Automate Availability Group Failover for SSISDB 2012 and 2014

Published 2021-06-01 by Kevin Feasel

Alex Stuart shows how to fail over SSISDB in SQL Server 2012 or 2014:

Hopefully not many people are still configuring SSIS instances on SQL 2012 or 2014 – especially HA instances – but if you are, this post is for you.

If you’re running SQL Server 2016 or above, having the SSIS catalog function correctly in an AG is supported by built-in functionality to manage the DMK (database master key). In 2012/2014 however there is no such support. Without intervention this makes SSISDB unable to be opened after a failover, because the DMK isn’t open – leading to errors such as “Please create a master key in the database or open the master key in the session before performing this operation.”

Read on to see how to resolve this error, and then how to do this automatically.

Comments closed