Horizontal Trees and Free Tools (2023-06-16)

SQLServerCentral Newsletter for June 16, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Horizontal Trees]( - [How to Visualize Timeseries Data with the Plotly Python Library]( - [Using Windows Security and Encryption with Flyway]( - [From the SQL Server Central Blogs - Masking Columns in Azure SQL and SQL Server: Safeguarding Sensitive Data]( - [From the SQL Server Central Blogs - Using Data Masker on Chinese Surnames]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Contained AG MSDB Naming]( The Voice of the DBA  Free Tools, Does Innovation Matter? The SQL Server community has been very lucky to have several free SQL Server tools to make work easier. Here are the offerings I see mentioned most often: • Maintenance Solution - Ola Hallengren • dbatools - Chrissy LeMaire and others • Diagnostic Information Queries (DIQ) - Glenn Berry • sp_WhoIsActive - Adam Machanic • First Responder Kit (FRK) - Brent Ozar I have a basic understanding of what each of these tools does, but I'm not an expert with any of them. In my opinion, these tools are designed for two different kinds of DBAs. I think Maintenance Solution and dbatools are primarily for operational DBAs, while sp_WhoIsActive and FRK are primarily for development DBAs. I think DIQ has pieces for both kinds of DBAs. There's an additional option for free SQL Server tools I have always been a development DBA, so I'm interested in tools for that role. In fact, I created a whole suite of tools primarily for that role. It's called [SQLFacts](. It has been a labor of love for me to build SQLFacts, but it has been very frustrating to share it. I wrote a series of [seven articles]( about SQLFacts for SQLServerCentral. The most [recent article]( included several bold claims for the toolkit. I stand by those claims, but I'm not sure they really matter. After spending nearly two years trying to introduce SQLFacts to the SQL Server community I'm disappointed in the reaction, actually a puzzling lack of reaction. It appears that entrenched interests, complacency, and the status quo leave no room for innovation with free SQL Server tools. My [seventh article]( got about 7000 reads in a month. I think that's a relatively high number for the website. The title is "Performance Tuning with SQLFacts Tools", so the focus on SQLFacts was clear up front. If people had no interest in SQLFacts they would not have clicked through to the content. I made some bold claims of innovation in the article. After more than 7500 reads nobody has refuted those claims. The SQLFacts toolkit has been downloaded many thousands of times. I'm sure some of those are repeat downloads for new versions, but still a lot of people must have a copy. There's no shortage of critics in the world, but nobody has even hinted that SQLFacts is unworthy of serious consideration. Unfortunately, nobody has publicly recommended it either. This combination of facts is baffling to say the least. How does SQLFacts compare to the list of tools above? SQLFacts does not include anything like Maintenance Solution or dbatools. Again, those tools are for operational DBAs and SQLFacts is for development DBAs. The sp_WhoIsActive tool is mostly for development DBAs. The SQLFacts toolkit provides very similar functionality with the Sessions tool. The FRK (sp_BlitzFirst, sp_BlitzIndex, sp_BlitzCache, sp_Blitz) is mostly for development DBAs. The SQLFacts toolkit provides very similar functionality with the Sessions, IndexHistory, QueryHistory, and MetricsNow tools. The SQLFacts toolkit leans toward providing all the relevant information, organized to make issues apparent. The FRK appears to lean toward deciding by itself what conditions represent issues and then listing those issues. The DIQ comparison is more complicated and I spent some time on it. I do not mean to denigrate DIQ, or any of the other tools, because they have helped many SQL Server users over the years. However, I will call things as I see them. The DIQ are basically a collection of blog post scripts. They are generally very short and very specific. About 40% of the DIQ are mostly for operational DBAs and SQLFacts does not include anything like them. About 60% of the DIQ are mostly for development DBAs. The SQLFacts toolkit provides "very similar" functionality with the SQLFacts, BACKUP, MetricsNow, SQLAgent, Bufferin, Databases, Sessions, Resources, AGLatency, Statistics, QueryHistory, IndexHistory, IndexNeeds, IndexNeedsPlus, and IndexActivity tools. I say it's "very similar" functionality, but SQLFacts goes much further. The SQLFacts toolkit neatly organizes all the functionality into tools for certain tasks. The tools provide all the necessary contextual information for those tasks. The functionality and the information are much more disjointed in the DIQ. SQLFacts compares very favorably to a combination of all the popular tools for development DBAs. What makes SQLFacts different from the other tools? The SQLFacts toolkit is unprecedented in breadth. There's nothing comparable in the sheer quantity of tools and the range of functionality. The tools vary in size, but each one is designed to serve a valuable purpose. The SQLFacts toolkit is much more than slapped together, piecemeal, blog post scripts from all over the WWW. The suite is a great resource for learning, but each tool also provides very beneficial details about a SQL Server environment. It's easier to learn from the tools, and use them in daily work, because there's consistency in T-SQL coding style and format. The greatest strength of SQLFacts may be the many features for performance tuning. SQLFacts goes far beyond other tools in analyzing and presenting opportunities for improvement. It connects information that other tools do not, which provides deeper insights into potential problems. Please refer to my [seventh article]( for a discussion of some of the innovative features. SQLFacts does a better job with common functionality (available in other tools), but it also provides a lot of unique functionality. Parts of the SQLFacts, Search, GenerateKeys, GenerateSQL, SQLAgent, Locksmith, QueryIndexes, IndexNeeds, and Indexer tools provide features I have not seen elsewhere. Most of the Trimmer, Planner, Threshold, QueryTracker, IndexNeedsPlus, and INCLUDE tools provide features I have not seen elsewhere. Why does SQLFacts not get a lot more attention? SQLFacts offers an extremely attractive alternative to the other tools. I think the word "alternative" is the reason it does not get more attention. There are many entrenched interests that may not welcome an alternative for various reasons. There's complacency among very experienced members of the community. They have been using the other tools for years and they may not believe that a better alternative could even exist. Further, the status quo is very hard to overcome. There may be many users of SQLFacts who are uncomfortable with speaking up because they feel like they must conform to what "experts" recommend. What if "experts" are biased? What if "experts" are wrong? Frankly, I think Microsoft should be a bit concerned that innovation is no longer encouraged, or even accepted, with free SQL Server tools. SQLFacts is intended to be a comprehensive toolkit for development DBAs. It's a very ambitious project, but it needs more advocates to achieve its potential. It would be very unfortunate for the community if there's no room for innovation with free SQL Server tools. Wingenious [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Horizontal Trees]( Glen Cooper from SQLServerCentral Introduction The sp_HorizontalTree procedure introduced here may be used to audit tables with dependent columns. A column is dependent on another if the meaning of the first column depends on the meaning of the second. For example, in a table containing accounting information, the meaning of a Fiscal Quarter column depends on the Fiscal Year to […] [External Article]( [How to Visualize Timeseries Data with the Plotly Python Library]( Additional Articles from MSSQLTips.com Learn how to use the Plotly library to visualize time series data in Python in this step-by-step article. [External Article]( [Using Windows Security and Encryption with Flyway]( Additional Articles from Redgate This article describes a simple technique that will allow you to use Flyway securely, even in cases where more than just the login credentials need to be protected. It uses a PowerShell technique that converts an encrypted Flyway configuration file into an array of parameters that Flyway can read just as if you were typing them in. [Blog Post]( From the SQL Server Central Blogs - [Masking Columns in Azure SQL and SQL Server: Safeguarding Sensitive Data]( hellosqlkitty from SQLKitty Information security and privacy are key in today’s data-driven world. Sensitive data needs to be protected from unauthorized access. With column masking in SQL Server and Azure SQL, you... [Blog Post]( From the SQL Server Central Blogs - [Using Data Masker on Chinese Surnames]( Steve Jones - SSC Editor from The Voice of the DBA A customer had a question recently on masking Chinese characters. I thought that was interesting, so decided to test this out. This is a short post on using SQL... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Contained AG MSDB Naming I have a SQL Server 2022 Contained Availability Group named "FinanceAG". What will be the name of the contained msdb that is included in this AG? This is the name that an admin will see in SSMS if not connected with a login used in the contained AG. Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Managed Instance Memory per vCore Limits There are three editions of Azure SQL Managed Instance, as of June 2023 (General Purpose, Business Critical, and Memory-Optimized). What are the amounts of RAM that I get per vCore for these editions? Answer: GP: 5.1GB/vCore, BC: 7GB/vCore, MO; 13.6GB/vCore Explanation: Each edition has it's own level of memory per vCore. The limits are: - General Purpose: 5.1GB/vCore - Business Critical: 7GB/vCore - Memory-Optimized; 13.6GB/vCore Ref: Overview of Azure SQL Managed Instance resource limits - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Availability Group database went into 'Restoring' state]( - Dear Friends, Please advise why would one of my Availability Group databases go into 'Restoring' state. Thanks in advance.  [SSIS DB]( - I was thinking to add SSIS db to Always on AG group. So during failover does anything need to be done differently in order to work packages? [Vulnerability Assessment removed?]( - In Brent Ozar's list of links today, I learned that Microsoft has removed the Vulnerability Assessment tool from SSMS as of v19.1. I recently started using it in my SQL Server environment and found it a great tool -- I was able to demonstrate to management why some of their security shortcuts were a bad […] SQL Server 2016 - Administration [Database copy issue through - COPY DB WIZARD]( - I have a production server with SQL server 2016 version (13.0.5026.0) and a database with 25 GB of Data and 525 tables in it. Now, I want to transfer this production DB to my development environment with SQL server 2016 version (13.0.5026.0). I am using DB copy wizard for DB copy to the new instance […] SQL Server 2016 - Development and T-SQL [Help with a SQL Unpivot]( - I am trying to use unpivot to extract some data, however, the results are getting messed up on at least one record. By this I mean it is out of order which it should be CLNUM: MODEL: CLSTATUS: CLSTATDATE: CLSTOP: Here is the query any help or advice would be appreciated. WITH ClientsWithOpenMattersCTE (ClientNo, HowMany) […] [SQL help with partition]( - With the following data, I need to create a column that will display a counter for each VID, Acc where PrgFld is not 'XXX' I am trying to do this with partition, but it's increasing the counter when PrgFld is XXX. I would prefer to not increase the counter or show 0 for XXX. Is […] [NOT LIKE Alternatives in WHERE clause]( - Hello, I need help to improve an existing (hence I cannot change table's structure nor the content's logic) WHERE clause, please. The table consists of returned products with their respective reason(s). In case there is more than one reason, they are concatenated. The request as shown in the mock-up below, has to retrieve a list […] SQL Server 2019 - Administration [Access db]( - Access db wanting to move  sql server, how much control does business have over table management? [Upgrading from 2016 to 2019 with Encryption]( - Hi, I have a few AWS EC2 instances currently running SQL Server 2016. I'd now like to upgrade these to SQL Server 2019 but the problem is that some of the data has been encrypted at the column level. I understand that, after SQL 2016, the encryption algorithm changed from SHA1 to SHA2. Would this […] SQL Server 2019 - Development [how to de escalte a isolation level, and are there any implications.]( - hi, I have one nested transaction and want to deescalate to outer transaction's isolation/ or lower isolation. is it the right way? SET TRANSACTION ISOLATION LEVEL read committed; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to do some job in read commited SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to […] [Advanced SQL query help]( - Hi, I am looking for some help with two queries using the following table/data. It would be great to see the most efficient way to achieve these. Queries 1: Show all homes with rent above the average rent value for their Company 2: Show the number of homes registered 0-3 months, 4-6 months, 6-12 months, […] Analysis Services [How can I show the last opening period for each project in my project dimension]( - I have two attributes hierarchies in my dimension [Dim Project]: Project Code Opening date Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03 Project B with 2 opening date : 2023-01-02 , 2023-01-04 MDX query : Select [Dim Project].[Project Code] .[Project Code].Allmembers * [Dim Project].[Opening date].[Opening date].Allmembers on 0 [Measures].[Measure1] on 1 […] Integration Services [You have an error in your SQL syntax; check the manual that corresponds]( - I am having great trouble creating a data pipeline using MariaDB connector 3 with SSIS/Mysql connector 8. I can view the tables, but it gives me this error: "TITLE: Microsoft Visual Studio ------------------------------ ERROR [42000] [ma-3.1.18][10.4.24-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the […] [Iterate Through Tables, Using a Control Table w/ a List of Tables to Process?]( - I'm trying to create an SSIS package that will query a master control table [TableList], which contains 2 columns (SourceTable and TargetTable), then load those tables from a source server to a target server. It "runs", but although the variables with the table names seem to update for each iteration, it looks like the table […] SQL Server 2022 - Development [SQL Query]( - Hi, i have a sample table: table ID  flag  name 1    0   test1 1    0   test2 1    1   test3 2    0   test4 2    0   test5 3    1   test6 4  […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -