Press "Enter" to skip to content

Day: February 4, 2021

Shuffling Excel Worksheets and Changing Tab Colors with Powershell

Published 2021-02-04 by Kevin Feasel

Mikey Bronowski continues a series on using Powershell to modify Excel files:

In this part, we will work on an existing workbook that already has worksheets. If you want to learn how to add new worksheets using the Add-Worksheet have a look at this blog post.

In case you haven’t noticed in the script above we used -MoveToStart switch, that means all the new worksheets were added at the beginning.

Read on for examples around moving sheets to the front or end, moving sheets before or after other sheets, and changing the colors of different tabs.

Comments closed

Indicators of Schema Issues

Published 2021-02-04 by Kevin Feasel

Erik Darling has a good list of schema-related issues:

Something is broken in the way that you store data.

You’re overloading things, and you’re going to hit big performance problems when your database grows past puberty.

Most of what he’s describing in this post is a failure of atomicity, which implies a failure to achieve first normal form. Mind you, all of these functions are perfectly reasonable as part of data loading, and many of them are perfectly reasonable in the SELECT clause of a query (though that’s still a sign of failure of atomicity), but once you start throwing them into the WHERE clause, we’ve got problems.

Comments closed

Memory Grants: Query Memory and Workspace Memory

Published 2021-02-04 by Kevin Feasel

Deepthi Goguri continues a series on memory grant internals:

The memory grant for the above query is 183496/8=22937 pages of memory grant. The total available grant for the queries is 92442. We can run the above query 4 times to hit the total memory grant (22937*4=91748). We can use the SQLQueryStress tool to run the query and see the query memory grants. Let us run the query with 100 number of iterations and 10 number of threads.

This has been an interesting series so far and I look forward to seeing the rest of it.

Comments closed

Random Number Generation in T-SQL

Published 2021-02-04 by Kevin Feasel

Chad Callihan generates random numbers:

The first way to generate a random number is by using the SQL Server RAND function. This will return a float value. 

Both of the techniques Chad shows are examples of generating uniform distributions—distributions in which any value is just as likely as any other. There are plenty of places in which a uniform is great: drawing by lot is one of them. But when you’re generating artificial data, the results tend to look unrealistic because not many natural phenomena follow uniform distributions.

If you’re interested in generating numbers which tend to look more realistic when generating artificial data, I have a post on generating random numbers built on a normal distribution.

Comments closed

Getting SQL Agent Jobs and Job Steps

Published 2021-02-04 by Kevin Feasel

Anthony Nocentino takes the dbatools approach to a problem:

Recently I needed to take a look at all of the SQL Server Agent Jobs and their Jobs Steps for a customer. Specifically, I needed to review all of the Jobs and Job Steps for Ola Hallengren’s Maintenance Solution and look at the Backup, Index Maintenance and Integrity Jobs to ensure they’re configured properly and also account for any customizations and one-offs in the Job definitions. This customer has dozens of SQL Server instances and well, I wasn’t about to click through everything in SSMS…and writing this in TSQL would have been a good candidate for a Ph.D. dissertation. So let’s check out how I solved this problem using dbatools.

Click through for the script.

Comments closed

SQL Server Compression Q&A

Published 2021-02-04 by Kevin Feasel

Bob Pusateri has some follow-up Q&A after a session:

I was extremely fortunate to be able to present about data compression at the EightKB SQL Server internals conference last week. If you missed my talk in person, you can now view it, as well as the entire day, on YouTube!

I was able to answer many questions during the session, but there were a few still left after time ran out. I wanted to address them all, so here they are!

Click through for the video as well as some Q&A.

Comments closed