A Fine Slice Of SQL Server
Devin Knight continues his custom visuals series:
In this module you will learn how to use the Sankey Bar Chart Power BI Custom Visual. The Sankey Bar Chart is used to show a flow of data between different stages of a process.
It’s an interesting mix of sankey, bar chart, and funnel. In other words, you may only have one thing you can use it for, but it’ll be a really good use.
Comments closedErik Darling wants to create a DDL trigger in tempdb which survives after restart:
One workaround is to create it in model. Then it will be in tempdb on restart, but it will also be in all the databases you create.
That may or may not have the intended effect! If you don’t want people doing a certain thing in just tempdb, you have to add an additional check using the event data.
Click through for more details, including a couple of sample scripts.
Comments closedAndrew Pruski notes that you can delete active databases in SQL Server on Linux:
What we’re going to do is delete the database files whilst the instance is up and running. Something you can’t do to a database running in an instance of SQL on windows as the files are locked.
Click through for the results, which are counter-intuitive for Windows admins, as well as the reason for this behavior.
Comments closedReza Rad explains how to sort a column by another column’s value in Power BI visuals:
Problem happens when you want a Text field to be ordered based on something different than the value of the field. For example if you look at above chart you can see that months ordered from April to September. This is not order of months, this is alphabetical order. If you change the sorting of visual, it will only change it from A to Z, or Z to A. To make it in the order of month numbers you have to do it differently.
Read on for the solution.
Comments closedMonica Rathbun begins a new challenge by describing the importance of one tiny form control:
When I worked at the Port of Virginia, I was a little less experienced in SQL and didn’t notice this lovely little option. I of course failed to set it. Can anyone guess what happened? YEP, we got low on resources in the wee hours of the morning and SQL kicked off an Error 017-Insufficient Resources. Thousands of emails were generated and caused the Exchange server to go down as well as some other issues that arose because of this. The worst part is that all the emails had to finish processing before we could delete them from the system. I think when all was said and done there was well over 250k messages it created.
She also includes a list of important alerts; just make sure to set the delay.
Comments closedErin Stellato link to two feature requests for Query Store:
Two of these requests have Connect items, which I’m listing below. The first allows you to change the location of Query Store data to reside somewhere in the user database besides the PRIMARY filegroup:
Option to store query store data in a filegroup other than PRIMARY
The other request is related to exporting that data, which is technically possible now, but it’s not a supported method so it’s not something I really want to implement in a client environment. I’ve had many people describe their process for testing which includes restoring database nightly. If they’re using Query Store as part of testing, that data is lost every night by the restore.
Export Query Store tables separately from the database tables
Click through for more and vote up those items relevant to you.
Comments closedJens Vestergaard wants Intellisense for MDX:
The Connect Item I have chosen to write about is an old one and is about getting Intellisense for MDX in SQL Server Management Studio [SSMS]. Despite the fact that it was created back in 2009 by Jamie Thomson (b|l|t), it is still active and there has been a public acknowledgement back then, by the Analysis Service Team, that they will consider this request for an upcoming release. 2009, still active… True story.
Read on for more details and be sure to join Jens’s quixotic quest if you’d like to see MDX Intellisense.
Comments closedTom Martens explains a class of generic data processing problems:
Subset and Apply means that I have a dataset of some rows where due to some conditions all the rows have to be put into a bucket and then a function has to be applied to each bucket.
The simple problem can be solved by a GROUP BY using T-SQL, the not so simple problem requires that all columns and rows of the dataset have to be retained for further processing, even if these columns are not used to subset or bucket the rows in your dataset.
One quick example of this is running totals of orders for each customer, which Tom answers using T-SQL, R, and Power BI. Click through for those three solutions.
Comments closedDave Mason looks at two Connect items related to working with extended events:
One Event Behind
I another post, I wrote that the XEvents event_stream target is regularly “one event behind”. There is an existing Connect item seeking a fix to this problem: QueryableXEventData and “Watch Live Data” one event behind. If you use the “Watch Live Data” grid for XEvents in SSMS, this is an important issue and worthy of your upvote. It’s also important if you ever want to access XEvent data programmatically with C# or PowerShell because the QueryableXEventData class uses the event_stream target and is also subject to the issue.
Read on for more details.
Comments closedAnthony Nocentino discusses network latency and how that can affect Availability Groups:
Now let’s talk about Availability Group replication and network latency. Availability Groups replicate data over your network using Database Mirroring Endpoints which are TCP sockets used to move data between the primary and it’s replicas. When designing Availability Groups, we often think about things in terms of bandwidth…how much data do I need to move between my replicas. But there’s another design factor your need to consider, network latency. Why? Hint, it’s not going to have anything to do with synchronous availability mode and HADR_SYNC_COMMIT waits. Let’s talk about some fundamentals of TCP for a second.
Click through for some discussion of TCP fundamentals.
Comments closed