Code You Would Hate To Live Without #tsql2sday

T SQL Tuesday
T SQL Tuesday


I finally found a #tsql2sday topic that I thought I could contribute to and wanted to make a quick post. T-SQL Tuesday is a writing prompt shared with the SQL community at large to talk about something related to your work (usually with SQL) that occurs on a monthly basis and is now up to #104.

This month it is being hosted by Bert Wagner ( b | t ) and he has challenged us to share some code (preferably that we’ve written) that we honestly would probably be making currently if we didn’t have it existing already.

My bit of code that I’m throwing into the foray this month is my sp_FileSpace stored procedure. I’ve been given a large number of databases through the years that can be referred to as VLDBs (Very Large DataBases) or at least VLDB hopefuls. Since they are in the VLDB arena one of the best practices is to break out the database data into multiple files and then someone has to monitor those multiple files to ensure they are staying consistent.

When I want to get the state of files in a database often they are spanned across multiple drives and often the easiest way to understand what is going on with them is with a grid created in T-SQL. I’ve created this script to show me different things but all in all typically how big each file is, how big each filegroup is, and if they are consistent.

My parameters in this stored procedure all have default values because I like to typically see things the same way but anytime I look at something and I’d like to see it changed for one reason of another I’ve started to throw it into a parameter so it’ll be more useful in the long run.

As Bert says, my code is not perfect but that’s why it’s not a compiled stored procedure. When I find something that needs changing I change it.

Now I’ve made sure to publish this at midnight (my time) on Tuesday. I’m hoping that gives someone else plenty of time to put something together.

Without further ado I introduce you to sp_FileSpace:

USE [master]
IF OBJECT_ID('dbo.sp_FileSpace') IS NULL
ALTER PROCEDURE [dbo].[sp_FileSpace]
@Drive [CHAR](1) = NULL
, @ShowRunningTotals [BIT] = 1
, @Filegroups [VARCHAR](50) = NULL
, @ShowGB [BIT] = 1
, @ShowMB [BIT] = 1
, @ShowKB [BIT] = 0
, @ShowExtents [BIT] = 0
, @ShowPercents [BIT] = 1
, @ShowGrowth [BIT] = 1
, @DBName [VARCHAR](256) = NULL
PRINT 'Declaring Variables'
PRINT 'Check Compatibility Level of database to use SUM windowing functions'
SELECT @Compat = [compatibility_level] FROM [sys].[databases] db WHERE [db].[name]=DB_Name()
PRINT 'Building Select Statement'
SET @Select = '
[FileId] = [dbf].[file_id]
, [FileGroup] = dbf.data_space_id
, [FileGroupName] = COALESCE([fg].[name],''Log'')'
PRINT 'Deciding to put GB on values or not'
IF @ShowGB = 1
SET @Select = @Select + '
, [GBs] = [dbf].[size] / CAST(131072 AS FLOAT(2))
, [GBs Used] = FILEPROPERTY([dbf].[name],''SpaceUsed'') / CAST(131072 AS FLOAT(2))'
PRINT 'Deciding to put MB on values or not'
IF @ShowMB = 1
SET @Select = @Select + '
, [MBs] = [dbf].[size] / CAST(128 AS FLOAT(2))
, [MBs Used] = FILEPROPERTY([dbf].[name],''SpaceUsed'') / CAST(128 AS FLOAT(2))'
PRINT 'Deciding to put KB on values or not'
IF @ShowKB = 1
SET @Select = @Select + '
, [KBs] = CAST([dbf].[size] * CAST(8 AS FLOAT(2)) AS BIGINT)
, [KBs Used] = fileproperty([dbf].[name],''SpaceUsed'') * CAST(8 AS FLOAT(2))'
PRINT 'Deciding to put Extents on values or not'
IF @ShowExtents = 1
SET @Select = @Select + '
, [Total Extents] = [dbf].[size] / 8
, [Used Extents] = FILEPROPERTY([dbf].[name],''SpaceUsed'') / 8'
PRINT 'Deciding to put Percents on or not'
IF @ShowPercents = 1
SET @Select = @Select + '
, [Percent Used] = CAST(CAST((CAST(fileproperty([dbf].[name],''SpaceUsed'') AS NUMERIC(15,2)) / CAST([dbf].[size] AS NUMERIC(15,2))) * 100 AS NUMERIC(15,2)) AS VARCHAR(10)) + ''%'''
PRINT 'Adding file rows to select'
SET @Select = @Select + '
, [Logical File Name] = [dbf].[name]
, [Real File Name] = [dbf].[physical_name]'
PRINT 'Deciding to put Growth on or not'
IF @ShowGrowth = 1
SET @Select = @Select + '
, [Growth] = CASE [dbf].[is_percent_growth]
WHEN 0 THEN CAST([dbf].[growth] / 128 AS VARCHAR(8)) + '' MB''
WHEN 1 THEN CAST([dbf].[growth] AS VARCHAR(8)) + '' %''
ELSE ''New unknown growth pattern''
PRINT 'Deciding to put Running Sums on or not'
IF @Compat > 110 AND @ShowGrowth = 1
SET @Select = @Select + '
, [Running Total GB Used FileGroup] = CAST(SUM(fileproperty(,''SpaceUsed'') / CAST(131072 AS FLOAT(2))) OVER(PARTITION BY ORDER BY ( AS FLOAT(2))
, [Running Total GB FileGroup] = CAST(SUM(dbf.size / CAST(131072 AS FLOAT(2))) OVER(PARTITION BY ORDER BY ( AS FLOAT(2))'
PRINT @Select
PRINT 'Database we are looking at: ' + @DBName
SET @From = '
' + QUOTENAME(@DBName) + '.[sys].[database_files] [dbf]
LEFT JOIN ' + QUOTENAME(@DBName) + '.[sys].[filegroups] [fg]
ON [dbf].[data_space_id]=[fg].[data_space_id]'
SET @Where = '
IF @Filegroups IS NOT NULL
SET @Where = @Where + '
AND [dbf].[data_space_id] IN (' + @Filegroups + ')'
SET @Where = @Where + '
AND LEFT([dbf].[physical_name],1)='''+ @Drive + ''
SET @Order = '
SET @sql = @Select + ' ' + @From + ' ' + @Where + ' ' + @Order
PRINT @sql
EXECUTE sp_executesql @sql


Temporary Tables: Local Temporary Tables

Temporary Tables

I know. I know. The title looks weird. In our ongoing talk about temporary tables we have now talked about Common Table Expressions(CTEs). CTEs are the shortest lived temporary type tables (only kept around for the next statement after). Then we talked about table variables (table names that start with an @ sign). Those lasted so long as we were actively executing. Were now talking about the types of temporary tables that do not have another name associated with them and they are also the most common used. You can tell these tables because they always start with a # sign.

Temporary tables follow all rules that regular tables do with a few exceptions:
Regular tables can have names as long at 128 characters. Temporary tables can only be 116 characters. This is because SQL will place a unique value at the end. That unique value is 12 characters long. It is a hex value between 0 and E. This means that SQL server could have 184 quadrillion temp tables before reusing uniquifiers.

  • Temporary tables can only exist in the tempdb database. This is both good and bad. It means a shared workspace so your not mucking up your own database. It also means you can have contention from the other databases.
  • Temporary tables delete themselves when the connection breaks. This means if you are running a stored procedure that creates a temporary table the temporary table is gone at the end of the stored procedure. If you have a temporary table in a Management Studio session you can tell SQL to drop the table or close the connection.
  • When you have data that you only want to keep for this session the goto should be use of a temporary table. Temporary tables can have indexes added to them. They get statistics created for them so SQL gets a good idea of how much work it has to do.

Our test code this week is very close to last week:

CREATE TABLE #FirstUsers (Id int, DisplayName nvarchar(40))
SELECT Id, DisplayName
FROM dbo.Users
WHERE Id < 10
SELECT FU.Id, FU.DisplayName, COUNT(*)
FROM #FirstUsers FU
LEFT JOIN dbo.Votes V ON FU.ID = V.UserId
GROUP BY FU.Id, FU.DisplayName

Under the hood, SQL is doing very different things. When SQL creates this table it gives it a name that is somewhat meaningful under the covers. It is also creating statistics (at least when it needs to) which can be very helpful for getting our data as fast as possible.

These should be one of the first things that you learn how to make for data aggregation purposes as temporary tables are used often.

Temporary Tables: Table Variables

Last time we talked about Common Table Expressions. These temporary table types are good for the reasons we went over. Today we’ll talk about table variables. Table variables are the unloved middle child of SQL temporary table types. There are reasons behind this which we’ll go over. But, much like anything else, they have a time and place where they are the best solution.Table variables have a few drawbacks to them. The largest of these drawbacks is that statistics are not created for them. This means that SQL is never quite sure how much data it’s going to pull back and makes a default guess of none. For small subsets of data this isn’t an issue. For larger subsets this can be catastrophic. When defining a table variable every part of the structure has to exist when the table creation happens. No adding columns later or defining new indexes.

Now onto the good parts of table variables. Table variables are useful for small subsets of data. How is a small subset defined you ask? That’s up for debate but the easiest answer I can give without getting to far into the fray. A table variable should have less data than if SQL would decide to do a merge or hash join. If when you run your query using regular tables SQL decides to use a Nested (Loop) join then you’re starting off good. Table variables also have the same lifespan in code as a variable / parameter. Once a batch is complete in SQL it gets rid of the table variable and all the data that was in it. The other time when table variables are great is when you have to use them. This circumstance happens in SQL functions (scalar and table-valued). Again, with the way SQL treats these table variables are our only options.

Some code now to show how to create and use a table variable.
DECLARE @FirstUsers AS TABLE (Id int, DisplayName nvarchar(40))

SELECT Id, DisplayName
FROM dbo.Users
WHERE Id < 10

SELECT FU.Id, FU.DisplayName, COUNT(*)
FROM @FirstUsers FU
LEFT JOIN dbo.Votes V ON FU.ID = V.UserId
GROUP BY FU.Id, FU.DisplayName

This code will simply take the records from the User table that have an id value under 10 and put them into the table variable. Now that we have our subset of data we want to know how many Votes each of those users has done.

Table variable usage is anywhere within SQL but being the best idea is rare. Unless I am creating functions table variables I avoid table variables. They have more bad to them than good that comes from them. This of course is my opinion and I’d love to hear yours.

Temporary Tables: CTE

alberto-triano-169339-unsplashunsplash-logoAlberto Triano

Over the next few weeks, I will cover the basics of various temporary table types in SQL Server. From when to use them, and which type to use when.

Temporary tables in SQL are usually referring to only one kind of tables. I’ll refer to these right now as hashtag tables as the name of the table always starts with a #.

This is a very stunted view of temporary tables from a SQL perspective. There are four different table types that can are temporary in some aspect. I’m considering a table temporary if SQL destroys it without a user saying drop table.

The first table type I’d like to start to tell you about is the one that lasts the least amount of time. The “CTE” or Common Table Expression only lasts into the next statement. CTEs have many restrictions placed on them. If you can get around them in the right circumstances CTEs are great helpers.

One of the best reasons to use a CTE is that it can do something that nothing else in SQL can do. We can have recursive CTEs without a whole bunch of extra coding. Recursive CTEs can be difficult to navigate at first. When you get the hang of them they can be very powerful. This is especially true the more you deal with HR data or any hierarchical relationships.

For instance, lets say I want to find all the Posts in the StackOverflow database that have a postlink associated with them.
I also want to know below them unto the nth level. In this case we have a problem because there are some posts that refer to each other. This creates an endless recursion and when that happens SQL will give us an error telling us it will only go 100 levels of recursion deep. If your company has 100 levels of recursion from the top to the lowliest worker, you have too much middle management.

WITH RecursiveCTE AS
,RecursionLevel = 0
Posts P
ON P.Id = PL.PostId
,RecursionLevel = RC.RecursionLevel + 1
Posts P
ON P.Id = PL.PostId
ON P.Id = RC.RelatedPostId
SELECT TOP 100 * FROM RecursiveCTE
WHERE RecursionLevel >= 1

CTEs are also great for readability sake. Let’s say we have a statement like this which gives us the badge count of all users:

Users U
,BadgeCount = COUNT(*)
FROM Badges B
) AS BadgeCount
ON U.Id = BadgeCount.UserId

But we want to make it simpler to read by moving the materialized view creation out of the FROM clause. This is where CTEs come in I define the CTE first, then use it in my next select statement like so:

WITH BadgeCount AS (
,BadgeCount = COUNT(*)
FROM Badges B

Users U
LEFT JOIN BadgeCount
ON U.Id = BadgeCount.UserId

Let me know what other uses you’ve seen for CTEs. Thanks all.

Install Woes with PowerShell (Part 3)

In part 1 of this post I explained how to install an MSI by telling it all your options upfront so it can do everything needed and not be attended. In part 2 I then went on to talk about getting PowerShell to do this remotely. Then I came across an issue that really had me stumped.

I worked recently to script out the changes being done in a major upgrade (that I could at least). This reduced our install time on many steps but one of the steps it actually increased our time on was the re-install of their MSI. Before I had it scripted we had multiple people handling multiple servers at the same time so this step didn’t take to long and with my first attempt as automating this I had it single-threaded so it bought us better knowledge that everything was done and done correctly but it made it interminably slow.

I was working to make these all functions in a module at the same time so I was having to think in multiple different what-ifs and could not figure out how to multi-thread with my code effectively. There is code out there to multithread PowerShell but it was quite frankly beyond my comprehension at this point and I try not to use anything in Production (that I’m introducing at least) that I don’t understand.

In order to install this MSI correctly the path I eventually headed down was 3 parts.

My solution to this while still keeping it in a function but not making it wait was to not make the function actually install but instead make the function create a scheduled task on each server that I wanted to install the application on. In order to do this we have to give the scheduled task 4 things.

The name of the task.
The execute action (this is the exe were calling).
The execute Argument (this is the MSI were calling along with all it’s arguments).
The time that this job should execute.
In my case I knew the EXE was msiexec.exe so I put that in a parameter. Then I put the very long string of what I wanted it to work with in the next parameter and in the third parameter I put in a start time of 5 seconds from when the script was run.

This is eventually the script I came up with and now I just beam it down to any of my other servers and have them run it.

$STActionExecute = "msiexec.exe"
$STAction = New-ScheduledTaskAction -Execute $STActionExecute -Argument $STActionArgument

$TimeToStart = (Get-Date).AddSeconds(5)
$STTrigger = $trigger = New-ScheduledTaskTrigger -Once -At $TimeToStart

$TaskName = "Install MyApp"
Register-ScheduledTask -Action $STAction -Trigger $STTrigger -TaskName $TaskName -Description $TaskName -User $TaskUser -RunLevel Highest -Force

Now I can safely have all of my servers install the MSI package I need without having to do it one by one. The way I beam this script to another server to execute is by running this command after naming the above Install-CRMPerServer.ps1 and always making sure it’s in the same folder as this command.

$filepath = "$PSScriptRoot\Install-CRMPerServer.ps1"
Invoke-Command -ComputerName $computer -FilePath $filepath

Install Woes with PowerShell (Part 2)

Now that I know my string to install my application (see part 1), I can now start to install remotely using PowerShell.

Scripting languages are awesome. Some more so than others. Microsoft has had its share of scripting languages in the past from DOS batch to VBScript to now PowerShell. I’ve used all of these with varying success based on what I wanted to accomplish and to be honest still mix and match what I’m using at any particular time.

The way I eventually did this install was within a function inside PowerShell with some parameters that I could set-up during runtime. This is a fairly easy process really with only 2 bits of information needed. The first bit we already have and that is, what are we executing? The second bit is, where are we executing it? In this case I was executing this on about 40 servers so a foreach loop was my best solution.

The very basics of the script are below and we’ll work to unpack them 1 by 1.

function Install-MSI {
param (
[string[]]$ComputerName = $env:COMPUTERNAME)
$computer = $_
$destinationFolder = "\\$computer\C$\Temp\MSIInstall"
$DestinationFile = "$DestinationFolder\MSIInstaller.msi"
New-Item $destinationFolder -ItemType Directory |out-null
Write-Verbose -Message "Copying Install Package to $computer"
Copy-Item -Path $sourcefile -Destination "$DestinationFile"
Invoke-Command -ComputerName $computer -ScriptBlock { cmd /c 'msiexec.exe /qn /i "C:\Temp\MSIInstall\MSIInstaller.msi" INSTALL_SERVER=1 INSTALL_CLIENT=1 INSTALL_OTHER1=1 INSTALL_OTHER2=1 INSTALL_OTHER3=1 INSTALL_OTHER4=1 INSTALL_OTHER5=1 INSTALL_OTHER6=1 INSTALL_OTHER7=1 INSTALL_OTHER8=1 INSTALL_OTHER9=1 /l*vx C:\Temp\MSIInstallerInstallLog.txt' }

So in the first 4 lines of the script we are naming the function and setting up our parameters, In this case just the one of the computername which can have multiple values and by default is set-up as just the computer we are currently running the script from.

The next lines are the “process”, it’s important to note that the way I setup the $ComputerName parameter it is now causing that to automatically be our foreach loop. So for every computer it sets up the folder where we’re going to copy the MSI to and the name of the MSI file once copied. The script then creates the folder and copies the file to the folder. We then use the invoke-command built-in function to create a session on that remote computer and send it the command to install.

In process now that this command has been loaded we would now call it with something like this.

$ComputersForInstall = @("Server1","Server2","Server3")
$ComputersForInstall | Install-MSI

This will then install using the command to all 3 computers listed. The biggest problem I had with this approach was that this is an iterative command meaning it would wait for the first server to have the MSI installed before going to the next one. This may not be a problem with your environment but with this install it was for me as this is one step in many where we have a production system down so I needed to find a way to make this faster. We’ll get into that in the next post.

Install Woes with PowerShell

rat-pets-eat-51340.jpegDisclaimer: This will be a multi-part blog post about how I finally got an install to work on many computers using PowerShell but I will not be going into any of the PowerShell in this post as it’s the precursor of all the things I had to figure out before-hand.

Ever had to install an application? Ever had to install it so many times that you could do it even with all the extra options you have to click with your eyes closed? Ever feel like a rat stuck in a wheel doing the same thing over and over again? That’s where I’d gotten to with one application I deal with and this is how I got it to go faster and with less interaction from your’s truly.

First, the background. I work as my company’s tech liaison for one of our main ISV applications. This application has large updates on a consistent basis. About 3 times a year and taking about 5-6 hours each time for an upgrade with the fact it touches around 50 servers.

First we’ll go over how to install an MSI without interaction. This MSI install has options that must change from default when we do our install. To script these out I turned to a free application from Microsoft which I’ve used in the past called Orca ( ). There are a whole lot of options in Orca. The only section I had interest in at this point was Condition. Here is what shows up for me under Condition with my installer.

Orca Scrrenshot

This is the page that shows up something like this in an installer:

Installer Screenshot

Once I knew this I knew how to install without any dialog box and with all the correct options. My string looked a little like this my the end.¬†INSTALL_SERVER=1 INSTALL_CLIENT=1 INSTALL_OTHER1=1 INSTALL_OTHER2=1 INSTALL_OTHER3=1 INSTALL_OTHER4=1 INSTALL_OTHER5=1 INSTALL_OTHER6=1 INSTALL_OTHER7=1 INSTALL_OTHER8=1 INSTALL_OTHER9=1. To test this the best way I know is to open up a DOS Prompt on a computer that doesn’t have the application installed. Have it now run a command like this:

msiexec.exe /qn /i *MSIFileName* *MSI Options you are going to use* /l*vx

MsiExec is the actual application that Microsoft uses to install your MSI files and the qn switch makes it quiet with no prompts. Then the i switch points it to the installer. After that the l*vx is the most verbose logging you can get from an MSI install so if something does go wrong someone can research that to find out where. More information on all these can is at Microsoft’s website ( ). So now we know how to install the file.

Next time we’ll explore how to install it to multiple computers as the same time with PowerShell.