A Fine Slice Of SQL Server
Jack Vamvas shows us how to query SQL Agent job steps for a string:
Question: I’d like to find all SQL Server Agent Jobs with a certain string within the Command section . There’s been a change in reference to a database table, therefore I need to review all SQL Server Agent Jobs and check they are not referencing this database table.
Read on for the answer.
Comments closedGrant Fritchey gives us another option for determining whether an index is in use:
One of the most frequent questions you’ll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead.
I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one.
What if we queried the information in Query Store?
Be sure to read Grant’s warning before jumping into this, but at least it gives us another option, as well as a better understanding of which queries are using particular indexes.
Comments closedDmitry Tolpeko gives us a scenario where YARN ignores vCore availability:
You can expect that the total number of vCores available to YARN limits the number of containers you can run concurrently, that’s not true in some cases.
Let’s consider one of them – Capacity Scheduler with DefaultResourceCalculator (Memory only).
The name “Memory only” does give away the game a bit.
Comments closedHadi Fadlallah walks us through the HDFS file source and destination components:
To test these components, we will create an SSIS package and add three connection managers:
1. Hadoop Connection Manager: to connect with the Hadoop cluster (check the previous article)
2. OLE DB Connection Manager: to connect to SQL Server instance where AdventureWorks2017 database is stored
3. Flat File Connection Manager: We will use it to export data from HDFS Source:
I wonder if they ever fixed the 4K screen resolution problem (kind of tells you how often I use SSIS anymore…). That was one of the things which made these components unusable for me on any modern screen.
Comments closedNiko Neugebauer walks us through improvements in certificate management with SQL Server 2019:
If you have ever used them for connection encryption (TLS 1.2), you might have had some battles with the certificates, having to go into the registry to edit the thumbprint and if you doing a Failover Cluster or Availability Group installation – you would have to enjoy this operation on the multiple nodes. Not-so-very-user-friendly to say at least!
So many times, it would scare-off a non Server/Database Administrator or a junior Server/Database Administrator from trying those features.
Niko also mentions something very interesting about SQL Server Configuration Manager compatibility at the end of the post.
Comments closedTaiob Ali walks us through the concept of memory grants:
DesiredMemory: Memory estimated to fit intermediate results in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialDesiredMemory.
(Amount needed to store all temporary rows in memory. This depends on the cardinality estimate, expected number rows and average size of row). This is called additional because a query can survive lack of such memory by storing part of temporary rows on hard disk. A query is not guaranteed to have the full amount if the total exceeds the preset limit.)
Read on for explanations of each of the elements in MemoryGrantInfo.
Because both the Actual Number of Rows and the Estimated Number of Rows are very valuable for many use cases, Microsoft has built their primary tool for viewing execution plans (SQL Server Management Studio) to highlight the value of at least one of these two properties, in a visual way, when you look at the graphical representation of an execution plan.
Look at the arrows that show which operator (is called by and) returns rows to which operator, and that therefore can be thought of as representing the data stream. In most execution plans, they are not all the same width. Management Studio uses the width of the arrow as a quick, simple visual representation of the value of Actual Number of Rows (in an execution plan plus run-time statistics, aka “execution plan plus” or “actual execution plan”), or of the value of the Estimated Number of Rows in an execution plan only (aka “estimated execution plan”).
This first post is the happy path post, where the information is correct and useful. Later posts promise to cover cases in which this does not hold.
Comments closedTeo Lachev hits on an annoyance I have with Power BI:
The Power BI image-rendering visualizations, such as Table or Card, expect image links to public servers hosting the images with anonymous permissions. This has obvious shortcomings. Can we load images from a database or Power BI data table? You bet, as Jason Thomas has demonstrated a long time ago. Here are the steps I followed to show the images from the Production.ProductPhoto table in the AdventureWorks2012 (or later) database. If you want to embed a few images into a Power BI data table (instead of an external database), you can convert them manually to Base64 using any of the online image converters, such as https://codebeautify.org/image-to-base64-converter and embed the resulting string into a Power BI data table (the Enter Data feature). Gerhard Brueckl takes this one step further by showing you how to automate the base64 conversion with many images.
The experience for displaying images isn’t the worst—I don’t mind needing base64 encoded strings for images—but there’s a strict limit on how large your images can be, and that size is puny. I have one dashboard where I show a CPU chart from Grafana and I have to leave it at a very small resolution or else I don’t get to see the whole chart—it cuts off in the middle.
Comments closedPrathy Kamasani embraces the power of “and”:
Power BI Bookmarks, the secret behind many sleek reports, It revolutionised the entire PBI Report design approach. Initially, Bookmarks were portrayed more as some saved views, which can be used for storytelling than for navigation. However, we certainly saw more report designers using Bookmarks for navigation than for storytelling. In March 2020, we have a new functionality called Page navigation, which brings us to the dilemma of which one to use and when?
Read on for the comparison.
Comments closedBrett Powell has a pair of Power BI templates for monitoring Azure Synapse Analytics:
Upon clicking ‘Load’ you’ll either need to provide your credentials for this source (if you don’t have this data source saved from previous use) or the queries will execute and the following report pages will be available:
– Executions
– Waits
– Sessions
– Waits Detail
– Execution Detail
– Memory
– ExecutionDrillThrough (hidden)
Click through to see what the templates look like and how to obtain them.
Comments closed