A Fine Slice Of SQL Server
Robert Cain doesn’t have time to wait:
What time is it? That’s what the KQL function
nowwill tell you. It will return the current date and time. It is mostly used in queries where you need data relative to the current date and time.
Read on to see how you can use it, including with offset intervals.
Comments closedTom Collins needs to encrypt some data:
Using a symetric key , how can I encrypt a column in a SQL Server database table? And what are some of the common pitfalls related to setting up column level encryption
Read on for the process. Even with other options available like Always Encrypted, I keep coming back to this because it’s simple, contained in-database, and works well.
Comments closedJared Poche looks at hash matching:
When I began working at Microsoft, I was very much a novice at performance troubleshooting. There was a lot to learn, and hash match joins were pointed out to me multiple times as the potential cause for a given issue. So, for a while I had it in my head, “hash match == bad”. But this really isn’t the case.
Hash matches aren’t inefficient; they are the best way to join large result sets together. The caveat is that you have a large result set, and that itself may not be optimal. Should it be returning this many rows? Have you included all the filters you can? Are you returning columns you don’t need?
I might throw in one caveat about hash match joins and being the best performers for two really large datasets joining together: merge join can be more efficient so long as both sets are guaranteed to be ordered in the same way without an explicit sort operator. That last clause is usually the kicker.
Comments closedSo essentially, every column has a whole bunch of indexes on it.
But there’s no order whatsoever as to which rows end up in which index.
This isn’t a problem for relatively small tables, but as you get to billion-row data warehouse fact tables where columnstore should really shine, performance gradually degrades. In data warehouses, fact tables often have a commonly filtered column, like SaleDate. However, until SQL Server 2022, even if you wanted a small SaleDate range, your query would likely check hundreds or thousands of row groups, each of which had a huge range of data.
But do read the whole thing, as it seems it’s not working correctly in CTP 2.0 of SQL Server 2022. It is quite useful in Azure Synapse Analytics dedicated SQL pools, at least—that I can confirm.
Comments closedJoey D’Antoni tightrope walks without a net for fun:
I was working with a client recently, were we had to reconfigure storage within a VM (which is always a messy proposition). In doing so, we were adding and removing disks from the VM. this all happened mostly during a downtime window, so it wasn’t a big deal to down a VM, which is how you can remove a disk from a VM via the portal. However, upon further research, I learned that through the portal you can remove a disk from a running VM.
Read on to see how. Though I’d generally still recommend shutting the VM off first just to be sure.
Comments closedErik Darling continues a series on Parameter Sensitive Plan optimization. First up is a post on indexing:
Anyway, let’s use the example that I had started with here, to illustrate that the PSP optimization does work with a computed column, but… like any other column, indexes make all the difference.
I’m using the same example query over and over again, because a lot of the other great examples of parameter sensitivity that I have demo queries written for don’t seem to trigger it.
And sometimes there’s just nothing to do:
After seeing places where the Parameter Sensitive Plan (PSP) optimization quite stubbornly refuses to kick in, it’s somewhat amusing to see it kick in where it can’t possibly have any positive impact.
Even though some parameters are responsible for filtering on columns with highly skewed data, certain other factors may be present that don’t allow for the type of plan quality issues you might run into under normal parameter sensitivity scenarios:
This continues to be a very interesting look into one of the most-anticipated features in SQL Server 2022, as well as a necessary wet blanket for the hype.
Comments closedStephanie Bruno fixes a credentials issue:
Have you ever had a Usage Metrics Report just stop refreshing? At the bottom you’ll see a little message that tells you to check the credentials. But you don’t have access to the Usage Metrics Report dataset in the list of datasets in the workspace because it’s hidden! So even if you wanted to delete it and nicely ask it to start again, you can’t do it from the workspace. What can you do? Well, you can delete the Usage Metrics Report dataset with the Power BI Rest API.
Read on to see how.
Comments closedTracy Boggiano continues a series on things to do at a new job as a DBA:
In my previous post, I expounded on my first 30 days I had at four jobs in the last four years. and how to setup your jobs box. I commented and got quoted on the fact that if it’s documented I don’t support it. So, these are methods of getting things documented, some including just having to have meetings, others running code.
One I believe in having a Central Management Server (CMS) where you can register your servers. Put them in as many groups as you desire but have core group such as Dev, Test, QA, UAT, Prod, Prod Sec, etc. The rest could be by application name if needed. I always have a set of names that are for the DBAs to use to do our work, other teams can have theirs for their work, i.e., deploying code.
If you aren’t using dbatools yet you should be. While not every shop can use to manage everything it is works every well for most tasks and that includes scanning the network for SQL Instances. Because unless you could into a well oiled machine there will be instances they don’t know about and one day someone will come knocking asking to fix it. Warn your security team before you run this.
Read on for examples of how you can find instances (assuming the security team is okay with it!), some of the information you’d want to document, and more. I would also recommend the most recent episode of the SQL Data Partners podcast, in which we talk to Jen and Sean McCown about documenting and managing your SQL Server inventory.
Comments closedGrant Fritchey points out another use for extended events:
Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here’s a little one that came up, auditing unique constraint violations.
It can also handle most other types of errors, making this a robust way of tracking issues. Back in the 2008 days, I built a little WPF program to watch for all of the errors on the couple of production SQL Server instances I managed. At one point, I saw one of the devs trying to write a query and getting an error. I IM’d the dev and said “You forgot the GROUP BY clause” (or whatever the problem was—it was something minor like that). He came over with a bit of panicked excitement, trying to figure out how exactly I was able to see what he wrote given that I was nowhere near his cube. Good times.
Comments closedTom Zika continues a series on why user-defined scalar functions are such a bad idea:
I’ll cover several test scenarios and analyze the performance using different monitoring tools.
The results will be for the second executions of the queries, so we have compiled and cached plans and all pages in the buffer pool.
Even if you already hate seeing scalar user-defined functions in code, the occasional reminder of how poorly they perform helps focus the mind.
Comments closed