Shane O’Neill has a column difference showdown:
The original post for this topic garnered the attention of a commenter who pointed out that the same result could be gathered using a couple of
UNION ALLs and those lovely set-basedEXCEPTandINTERSECTkeywords.I personally think that both options work and whatever you feel comfortable with, use that.
It did play on my mind though of what the performance differences would be…what would the difference in
STATISTICS IO, TIMEbe? What would the difference in Execution Plans be? Would there even be any difference between the two or are they the same thing? How come it’s always the things I tell myself not to forget that I end up forgetting?
This may not be the most important thing to test, but it does show you a technique.
Denis Gobo links to five interesting GitHub repos:
dbatools
dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.
GitHub Repo is here: https://github.com/sqlcollaborative/dbatools
Also make sure to visit their website: https://dbatools.io/
Read on for the other four.
A friend had an interesting problem today. A really big table (multiple millions of rows) and no primary key. He then ran into an issue where he had to have one. The easiest thing is to create a new int column with an identity column right? Unfortunately in this case because of the size of the table, the log growth on adding an identity column was too much. So what to do?
Well, it would be nice if we could add an int column, populate it in chunks, then make it an identity column. Unfortunately, you can’t add identity to an existing column.
Read on for the answer.
William Wolf talks about Buffer Pool Extension:
With this feature, SQL Server will extend the Buffer Pool Cache to non-volatile(ssd) storage. This will alleviate the I/O contention of mechanical disks by augmenting memory. The BPE uses the SSD as memory extension rather than disk. This feature can be used with standard and enterprise, but would provide noticeable benefits for Standard Edition. According to books online, the BPE size can be up to 32 times(Enterprise) or 4 times(Standard Edition) the value of max_server_memory, but the recommended ratio is 1:16 or less.
By utilizing this option, we can alleviate some memory pressure. To demonstrate this for me was a litte difficult at first. My laptop, as most newer laptops, has a SSD. So I plugged in a SATA hard drive externally and moved my database there for testing. If the database files are already on SSD, adding BPE may not give much benefit as the memory from BPE would write to SSD as well.
Buffer Pool Extension did end up in the Hall of Shame, but scenarios like Wolf describes exist, and in those scenarios, BPE could be a viable third-best option.
Something isn’t right…as DBAs we think of things in rows and columns. So we’re going to count across the top and think the 7th column is going to yield the 7th column and it’s data for each row, right? Well, it will but data processed by awk is whitespace delimited by default and is processed row by row. So the 7th column in the second line isn’t the same as the output in the first line. This can be really frustrating if your row data has spaces in it…like you know…dates.So let’s fix that…the output from the DMVs via dbfs is tab delimited. We can define our delimiter for awk with -F which will allow for whitespaces in our data. Breaking the data only on the tabs. Let’s hope there isn’t any tabs in our data!
I’m a little surprised that these metrics don’t end up in /proc, but I imagine there’s a reason for that.
In many cases, you can easily provision resources in the web-based Azure portal. If you’re never going to repeat the deployment process, then by all means use the interface in the Azure portal. It doesn’t always make sense to invest the time in automated deployments. However, ARM templates are really helpful if you’re interested in achieving repeatability, improving accuracy, achieving consistency between environments, and reducing manual effort.
Use ARM templates if you intend to:
Include the configuration of Azure resources in source control (“Infrastructure as Code”), and/or
Repeat the deployment process numerous times, and/or
Automate deployments, and/or
Employ continuous integration techniques, and/or
Utilize DevOps principles and practices, and/or
Repeatedly utilize testing infrastructure then de-provision it when finished
Melissa walks through an example of deploying a website with backing database, along with various configuration changes.
Kenneth Fisher explains what the ANSI PADDING setting does:
ON is the default and is what you would expect. Trailing spaces are saved in VARCHAR and in CHAR additional spaces added to fill the entire space. When ANSI_PADDING is off then additional spaces are not saved .. unless the column is CHAR AND NOT NULL.
So there’s the first reason to not turn ANSI_PADDING off. Most people expect the ON results and the OFF results can be .. let’s just say confusing.
Click through for more details, including how painful it is to change the setting on a column after the fact.
Nate Johnson rails against bad defaults in SQL Server:
Your servers have many-core CPUs, right? And you want SQL to utilize those cores to the best of its ability, distributing the many users’ workloads fairly amongst them, yes? Damn right, you paid $3k or more per core in freaking licensing costs! “OK”, says SQL Server, “I’ll use all available CPUs for any query with a ‘cost’ over ‘5’“. (To give context here, in case you’re not aware, ‘5’ is a LOW number; most OLTP workload queries are in the double to triple digits). “But wait!”, you protest, “I have more than 1 user, obviously, and I don’t want their horrible queries bringing all CPUs to their knees and forcing the 50 other user queries to wait their turn!”
Nate has a few recommendations here, as well as a picture of kittens.
Jeff Mlakar has a set of tips and tricks around SQL Server performance:
Performance problems for a SQL Server based application are likely to be caused by environmental factors and not buggy code.
Whether it is a configuration you can change in SQL Server, Windows Server, VMware, or the network it is likely the first course of action is to perform a quick assessment of the environment. This is where understanding the various configurations and best practices are key. Knowing what to look for can save tons of time.
A mistake I often see is a performance issue is passed off to someone else (more senior) and that engineer assumes a lot of things without checking. People are going to relay the problem as they see it – not as it actually is. This leads to skipping over some elementary checks which can save time and frustration from tracking down imaginary bugs.
Start troubleshooting with a quick environmental check.
There are quite a few checks here.
I had a recent project to enable backup encryption on all our servers. Then question from the storage team came up will this required additional space. Well by then I had already enabled in all our test servers so I wrote a query that would compare the average size of backups before encryption to after encryption. Keep in mind we do keep only two weeks of history in our backup tables so this is a fair comparison. If you don’t have maintenance tasks to clean up your backup history then you should have backup_start_time to the where clauses to get more accurate numbers and setup a maintenance tasks to keep your msdb backup history in check.
Unfortunately, Tracy leaves us in suspense regarding whether they did increase.
Kevin Feasel
2017-06-23
Administration, Powershell
No Comment