A Fine Slice Of SQL Server
Greg Dodd follows Betteridge’s Law of Headlines:
When SQL Server caches your plan, it caches it with the parameter values that you pass through the first time, and it assumes that the same query plan will be the best one for any parameter you pass in next time.
But does SQL Server always cache your parameters? Does it always keep track of what you pass in?
Click through for a demonstration good enough to give you a conclusive answer.
Comments closedAndy Mallon explains why you should never shrink tempdb data files:
I recently wrote about growing, shrinking, and removing tempdb files. In that article I explained that SQL Server won’t move a page that contains an internal worktable object, and thus trying to shrink tempdb files can be futile. Today, I’m going to explain how attempting to shrink tempdb files can actually be harmful.
Andy has good advice for tempdb here. Shrinking other database is generally bad but sometimes necessary; shrinking tempdb can lead to all kinds of problems.
Comments closedAndy Levy has an admission to make:
It turns out I was doing this all wrong for months.
We know, but we thought it polite not to say anything about it.
Read on to see how Andy uses Test-DbaBuild to look for new CUs of SQL Server.
It used to be that the only way to use SQL Server Management Studio (SSMS) against Azure SQL Database Managed Instance (SQLMI) was to create a VM on the same VNET as SQLMI and use SSMS on that VM. That VM was usually called a jumpbox (see instructions here).
But about a year ago Microsoft added a way to use SSMS without using a VNET (announcement) by allowing you to enable a public endpoint for your SQLMI. This made it easy for me to access a SQLMI database on my laptop.
That change enables what James shows us.
Comments closedTaryn Pratt shares an issue with columnstore cleanup in SQL Server 2019:
The output of
sp_who2was repeatedly showingGHOST CLEANUPandCREATE INDEX. Over and over and over again. To be clear, I’m not a clustered columnstore expert, I know enough to be able to maintain them as needed. I went to Twitter and mentioned what I was seeing. I was advised by @sqL_handLe to try trace flag 661 which disables the ghost record removal process, and by Joe Obbish via Erik Darling to enable trace flag 634 to disable the tuple mover background task.Initially, we enabled trace flag 634, but the logs continued to grow. We disabled trace flag 634. Then we enabled trace flag 661, and the logs continued to grow, so we disabled it. Finally, we tried enabling both of the trace flags. The big jumps stopped, but we now had about 400GB of logs that needed to be written to the reporting cluster before we could perform the failover.
While the logs were exploding we wondered if whatever was happening might have been caused by the deletions we did in early February. But why would they be triggered by the upgrade to SQL Server 2019?
Read the whole thing if you’re looking at a migration to 2019.
Comments closedKenneth Fisher needs to know a server’s current pricing tier:
I ran into an interesting problem today. I needed to find out the pricing tier of an Azure SQL DB but while I have access to to the DB via SSMS I don’t have access via the Portal. So I needed something I could use via T-SQL. I did some research, and found something that said it worked but didn’t look right to me. So I asked on twitter.
Click through to see the answer.
Comments closedStuart Moore has a public service announcement for us:
From looking around at some of the Issues that get logged for dbatools, it looks like people never clear out their SQL backup history from msdb. This causes slow queries and potentially misleading backup trees to be returned.
In my (not so humble) opinion you really shouldn’t let your backup history fill up with, let’s be honest, useless junk. In this post I’ll go through why I think you should be keeping way less in there than people think you should
Read on for Stuart’s thoughts on the topic.
Comments closedKevin Hill takes us through a nice use of dbatools:
My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule, but not for certain databases that are close to or over 1TB in size.
Read on to see how Kevin pulled it off, all in under 4 hours.
Comments closedAndy Mallon walks us through setting up tempdb:
There are three problems I’ve got to fix. I need to (1) remove those two extra files, (2) grow the
tempdblog file, and (3) even out the size of the data files (and shrink them a little to make room for the larger log file. We’re going to tackle these in the reverse order than I listed them–partially out of necessity, and partially because it’s going to be easier.
Click through to see how Andy sets up tempdb. This is a good way to set up tempdb.
Comments closedSheldon Hull takes us through the Windows Terminal experience:
I’ve long been a Cmder/ConEmu user for Windows as it’s provided a much-needed improvement of the standard Windows terminal.
I’ve started trying to use the newer Windows Terminal project to benefit from the improved performance and support, and found getting it up and running with any customizations was a little time consuming and confusing. This wasn’t something I’d hand off to someone who wasn’t looking for experimentation.
Click through for a few scripts and screenshots.
Comments closed