Bert Wagner reviews the entries for T-SQL Tuesday 104:
This month’s T-SQL Tuesday topic asked “What code would you hate to live without?” Turns out you like using script and code to automate boring, repetitive, and error-prone tasks.
Thank you to everyone who participated; I was nervous that July holidays and summer vacations would stunt turnout, however we wound up with 42 posts!
Watch tsqltuesday.com for next month’s topic and consider signing up to host.
Read on for the 42 submissions.
Aaron Bertrand shows how he finds hidden features in new SQL Server builds:
One of the areas I like to focus on is new features in SQL Server. Under both MVP and Microsoft Partner programs, I get to see a lot of builds of SQL Server that don’t make it to the public, and documentation for these builds is typically sparse. In order to get a head start on testing things out, I often need to explore on my own. And so I wrote some scripts for that, which I’ve talked about in previous blog posts:
- How I spot not-yet-documented features in SQL Server CTPs
- More ways to discover changes in new versions of SQL Server
When I install a new version of SQL Server (be it a cumulative update, the final service pack for a major version, or the first CTP of vNext), there are two steps:
Create a linked server to the build that came before it
Create local synonyms referencing the important catalog views in the linked server
It’s a good way to get a glimpse at which features devs are currently working on but haven’t enabled yet.
Rob Farley demonstrates the downside of the READ UNCOMMITTED isolation level:
I’m going to create a table and insert exactly 1 million rows. This particular table will be a clustered index, and will contain 1 million GUIDs.
Next I prove that there a million rows.
Now without inserting or deleting any rows, I’m going to shuffle them.
And if while this is happening, I count the rows in a different session, I have to wait for that query to finish.
Read on to see what happens when someone gets the idea of running the select query with NOLOCK.
Louis Davidson has an example of a calendar table in SQL Server:
The solution is part of my calendar/date dimension code, and it is used to do relative positioning over date periods. For example, say you have the need to get data from the 10 days. You can definitely use a simple between to filter the rows, and a bunch of date functions to group by year, month, etc., generally all of the “normal” groupings. But using a calendar table allows you to prebuild a set of date calculations that make the standard values easier to get, and non-standard groupings possible. The technique I will cover makes moving around in the groupings more easily accessible. Like if you want data from the last 3 complete months. The query to do this isn’t rocket science, but it isn’t exactly straightforward either.
For the example, I will use the calendar table that I have on my website here: http://drsql.org/code in the download SimpleDateDimensionCreateAndLoad, and will load it with data up until 2020. Here is that structure:
Read on for examples of usage. This is an example where thinking relationally differs from thinking procedurally—imagining date ranges as pre-calculated sets isn’t intuitive to procedural developers, but it can give a big performance boost.
Andy Mallon looks back at a contribution by Junior DBA Andy, this one on checking file sizes:
This is every DBA’s favorite game. Figuring out what DMV contains the data you want. It turns out there are two places that database file size info is maintained. Each database has
sys.database_fileswhich has information for that database. The master database also hassys.master_files, which contains information for every database.Using
sys.master_filesseems like it would be the obvious choice: everything in one view in master is going to be easier to query than hitting a view in a bunch of different databases. Alas, there’s a minor snag. For tempdb,sys.master_fileshas the initial file size, but not the current file size. This is valuable information, but doesn’t answer the use cases we set out above. I want to know the current file sizes. Thankfully,sys.database_fileshas the correct current file sizes for tempdb, so we can use that.Using
sys.database_filesseems like it’s going to be the right move for us then. Alas, this isn’t quite perfect either. With Log Shipping, and Availability Group secondaries, if you’ve moved data files to a different location,sys.database_fileswill contain the location of the files on the primary database. Thankfully,sys.master_fileshas the correct local file locations for user databases, so we can use that.Ugh, so it looks like the answer is “both”… we’ll need to use
sys.database_filesfor tempdb, andsys.master_filesfor everything else.
Click through for the script, including Andy’s critical reflection on how Past Andy has failed him.
Jay Robinson shows off a few extension methods he creates to make dealing with C# easier:
Then I could use the extension like this:
if (mySeries.In(Enum.Series.ProMazda, Enum.Series.Usf2000)) myChassis = "Tatuus";As for the other two methods, well… When is a null not a null? When it’s a System.DBNull.Value, of course! SQL Server pros who have spent any time in the .NET Framework will recognize this awkwardness:
var p = new System.Data.SqlClient.SqlParameter("@myParam", System.Data.SqlDbType.Int);
p.Value = (object)myVar ?? System.DBNull.Value;With the extension, the second line becomes:
p.Value = mVar.ToDbNull();
I like it that Jay ended up going with a different language than T-SQL. It’s no F#, but it’ll do.
Kevin Hill has a couple scripts which help him find easy performance gains:
Recently, I’ve been getting a lot of performance tuning work, much of which is basically “things are slow…can you fix them?” type of requests. Most experienced DBAs know that there a few gazillion factors that can lead to this request, and I won’t re-hash them here.
Lets assume, that we’ve optimzed or eliminated the server, disks, network, etc. and were now looking at SQL code and everyone’s favorite – indexes.
I have two scripts I use that give me a quick overview of the type of work SQL Server is doing. These complement each other, and are used AS A STARTING POINT to locate the low-hanging fruit that can be causing excessive work for the disks and memory of the server.
Click through for those scripts.
Drew Furgiuele says “Game over, man, game over!” to indexes:
The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it:
- Creates a special schema to house a temporary object,
- Creates a special stored procedure to run the code,
- Calls said stored procedure,
- Generates a bunch of PRINT statements that serve as the output (along with new line support for readability),
- Cleans up the stored procedure it generated,
- And finally deletes the schema it created.
Nifty.
Click through for the script, as well as a bonus Powershell script. Because hey, it’s only six lines of code.
Chrissy LeMaire touts one of the best parts of dbatools:
dbatools is such a fun toolset to work on, but specifically, I can no longer live without Start-DbaMigration. Even in smaller shops, migrations are often required and they are always a lot of work.
At least they used to be, before I built the command that started it all: Start-DbaMigration. Start-DbaMigration is an instance to instance migration command that migrates just about everything. It’s really a wrapper that simplifies nearly 30 other copy commands, including Copy-DbaDatabase, Copy-DbaLogin, and Copy-DbaSqlServerAgent.
Also a bonus shout out to dbachecks.
Raul Gonzales has a script which provides useful information for tables and columns:
Useful information it provides at table level:
- tableType, to identify HEAP tables
- row_count, to identify tables with plenty of rows or now rows at all
- TotalSpaceMB, to identify big tables in size
- LastUserAccess, to identify tables that are not used
- TotalUserAccess, to identify tables that are heavily used
- TableTriggers, to identify tables that have triggers
Useful information it provides at column level:
DataType-Size, to identify supersized, incorrect or deprecated data types
Identity, to identify identity columns
Mandatory-DefaultValue, to identify NULL/NOT NULL columns or with default constraints
PrimaryKey, to identify primary key columns
Collation, to identify columns that might have different collation from the database
ForeignKey-ReferencedColumn, to identify foreign keys and the table.column they reference
Click through for the script.
Kevin Feasel
2018-07-19
T-SQL Tuesday
No Comment