New version of sp_WhoIsActive (v11.20) is available – Deployed on 123 instances in less than 1 minute

Last night, I received Adam Machanic’s (b | t) newsletter “Announcing sp_whoisactive v11.20: Live Query Plans”.

For those who don’t know about it, sp_WhoIsActive is a stored procedure that provides detailed information about the sessions running on your SQL Server instance.
It is a great tool when we need to troubleshoot some problems such as long-running queries or blocking. (just two examples)

This stored procedure works on any version/edition since SQL Server 2005 SP1. Although, you only will be able to see the new feature (live query plans) if you run it on SQL Server 2016 or 2017.

If you don’t receive the newsletter you can read this announcement here and subscriber to receive the next ones here.

You can read the release notes on the download page.

Thank you, Adam Machanic!

The show off part

Using the dbatools open source PowerShell module I can deploy the new latest version of the stored procedure.

By running the following two lines of code, I updated my sp_WhoIsActive to the latest version (we always download the newest one) on my 123 instances in less than one minute (to be precise, in 51,717 seconds).

$SQLServers = Invoke-DbaSqlcmd -ServerInstance "CentralServerName" -Query "SELECT InstanceConnection FROM CentralDB.dbo.Instances" | Select-Object -ExpandProperty InstanceConnection
Install-DbaWhoIsActive -SqlInstance $SQLServers -Database master

The first line will retrieve from my central database all my instances’ connection string.
The second one will download the latest version, and compile the stored procedure on the master database on each of the instances in that list (123 instances).

Thanks for reading

Using Common Table Expression (CTE) – Did you know…

Today I will write just a short blog post to do a quick reminder!

I still hear a lot of people suggesting CTEs because they think it works like a temporary table (you populate the table and then it can be/is reutilized).

It doesn’t!

From de documentation:

Specifies a temporary named result set, known as a common table expression (CTE).

Maybe they are focusing on the “temporary” word.

Using the CTE two times will perform two different executions! Don’t believe me? See the next example!
If we run the following code do you expect to get the same value for both queries? Note: we have a UNION ALL between them.

WITH cte AS
(
	SELECT NEWID() AS Col1
)
SELECT Col1
  FROM cte
UNION ALL
SELECT Col1
  FROM cte

Sorry to disappoint you but it will run the CTE’s code twice and return the value(s) from each execution.
As we are using the function NEWID(), two different values will be generated.

output.png

To complete the question: “Did you know that CTE’s code will be executed as many times as you use it?”

Thanks for reading!

TSQL Tuesday #96: Folks Who Have Made a Difference

tsql2sdayThis month’s T-SQL Tuesday is brought to us by Ewald Cress‏ (blog | twitter) and is all about “folks who have made a difference” in our careers.

Thank you, Ewald! This is a great topic!

Here is my short list:

Paulo Silva (in)

He was my first boss in the IT world! I was his apprentice when I started my internship. He was going to move to a manager position and I  had to continue his work. He was responsible for the beginning of my career with SQL Server 2000 and VB6.

He was one of the main culprits for my growth not only in IT but also as a person!

Etienne Lopes (t | b)

After 5 years working on IT I had the tremendous pleasure to meet Etienne. This guy is a professor! He has the gift of the word!

I have worked closely with him for about 2 years and were one of the best times of my career! I always consider myself as a sponge, and as long Etienne shared is knowledge I felt I was absorbing every single word!

Much of the bases I have with SQL Server I learned from him!

André Batista (t) / Niko Neugebauer (t | b)

These two guys are the responsible for my very first talk on a user group (SQLPort).

After that, I became more and more involved with the local community and today I speak for more user groups and I help with SQL Saturday / TugaIT events in Lisbon!

Rob Sewell (t | b)

The one and only DBAWithABeard! My recent experiments were from blog posts/presentations that I read/saw from Rob. PowerBI & Pester are just two of them. He is super accessible and always willing to help.

Chrissy LeMaire (t | b)

I met Chrissy less than 2 years ago at the TugaIT conference (May 2016) in Lisbon. At the time has passed like 1 month from the dbatools.io launch date and I had written a couple of PowerShell scripts that I thought would be nice to add to the initial tool.

We talked, exchange contacts and one month later, in June, I was submitting my first pull request to the dbatools GitHub repository.

From that time until now it has been a blast! I learned so much about PowerShell with her and she is also one of the responsible for my MVP not only because she nominated me for the very first time but also because all the visibility that the project brought to me.

She was also the first person delivering a presentation with me. 🙂

People I know from the magazines or internet

People that helped me to understand SQL Server much better and from whom I have read a lot of articles: Itzik Ben-Gan (I remember the times I read the SQL Magazine with great articles from him), Paul Randal, Kimberly L. Tripp, Adam MachanicPaul White and Kendra Little.

Wrap up

I could add more people to the list but, those are the ones that I want to highlight from different periods (the beginning, middle and nowadays) of my career.

Thank you all!

 

DELETE data on SQL Server HEAP table – Did you know…

Before I complete my question let me provide context.

I’ve received an alert saying that a specific database could not allocate a new page (disk was full)

The message that you will see on the SQL Server Error log is:

Could not allocate a new page for database ” because of insufficient disk space in filegroup ”. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I didn’t know the database structure or what is saved there, so I picked up a script from my toolbelt that shreds all indexes from all table. Just some information like number of rows and space that it is occupying. I have sorted by occupying space in descending order, look what I found…

So…my script has a bug? 🙂 No, it hasn’t!

The joy of heaps

First, the definition:

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted. To guarantee the order of rows returned from a heap, you must use the ORDER BY clause. To specify the order for storage of the rows, create a clustered index on the table, so that the table is not a heap.

Source: MS Docs – Heaps (Tables without Clustered Indexes)

Until now, everything seems normal, it is just a table with unordered data.

Why am I talking about heaps?

Not because of table name (was created on propose for this demo), let me show to you the whole row of the script:

Do you have a clue? Yup, index_id = 0. That means that our table does not have a clustered index defined and therefore it is an HEAP.

Even so, how it is possible? 0 rows but occupying several MB…

The answer is…on the documentation 🙂

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

source: DELETE (Transact-SQL) – Locking behavior

That explains it!

So…what should I do in order to get my space back when deleting from a HEAP?

On the same documentation page we can read the following:

To delete rows in a heap and deallocate pages, use one of the following methods.

  • Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).
  • Use TRUNCATE TABLE if all rows are to be deleted from the table.
  • Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.

Following the documentation, it suggest we can to use the TABLOCK hint in order to release the empty pages when deleting the data.
Example:

DELETE 
  FROM dbo.Heap WITH (TABLOCK)

What if I didn’t that way or if anyone else run a DELETE without specify it?

You can rebuild your table using this syntax (since SQL Server 2008):

ALTER TABLE dbo.Heap REBUILD

This way, the table will release the empty pages and you will recovery the space to use on other objects in the database.

Wrap up

I hope that with this little post you understood how and why a HEAP can have few rows or even zero but still occupy lots of space. Also I have mentioned two ways to solve this problem.
Also, I have found databases with dozens of HEAPS almost empty or even empty that were occupying more than 50% of the total space allocated to the database. And guess what? People where complaining about space.

To finish, I need to complete the title, Did you know…you should use TABLOCK hint when deleting data from a HEAP?

Thanks for reading!

PowerShell Modules Central – Share with community – What PowerShell modules are you using?

Like the blog post title states this is all about sharing with others! My idea is to share with the community which PowerShell modules you are using.

Let me introduce to you the PowerShell Modules Central

PowerShellModulesCentral is a GitHub repository that was founded as a central hub to a list of PowerShell modules that people know/use. Each module has a file describing its name, basic information about the module, as well as one or more blog posts/videos from people that have written about or used them.

This way we can reduce friction when people are starting out or are trying to solve similar problems.

Why?

When a new module appears on the PowerShell scene it can be difficult to advertise and gain mindshare among developers/end users who could be interested in it. There are also times when difficulties arise in finding if a good tool exists or not, if its up to date, and how relevant it is in the community.

Why not just use the PS Gallery or script center?

This is, by no means, a replacement of those. Actually it is opposite, it is meant to be a community complement. Normally, when you need to do a task that you’ve never done before you like to have some jump start like blog posts or videos, and maybe you find the ones that are very close to your real scenario.
This repository enables not only people to write blog posts and share them with the community, but also the new guy (on PowerShell or just on a new task) that is searching for a specific tool to accomplish a task.
I can go to the PowerShell Gallery and see that the module I want to use has 1K downloads. That is really cool! It will give me confidence to use it. But, next, when you want to start working with it maybe you would like to see examples. The objective here is to have a quick look on some problems and tools used to solve them, as they can also be your problem.

Let me tell you a quick story

I went to google, found a PowerShell Gallery script, and after checking that the script didn’t work with some particulars I did a further research and found (google results – page 3 or 4 due ranks) a comment on a forum pointing to the GitHub repository. Guess what? The problems I was having were already addressed. 😉

Are you a module owner? Are you writing something new? Do you contribute to a module? Share it! The ones I know and use could be very different from the ones you know and use! Why not share?

How can this help me?

Are you trying to find a module to work on a specific task? Use the search on the top of the repository page and try to find what you need.

  • Working with Windows? Type “Windows”.
  • Working with SQL Server? Type “SQL Server”.
  • Do you know the author’s name? You can search that way too.
  • Have you read a blog post before and just remember one word or the blogger’s name? Type it and see what you find.

How to contribute?

Just fork the repository, add the information and send a pull request (PR). I will merge it once everything is OK.
For new modules please use the template available here. If you find that module already exists, you just need to add your URLs and any other information to be updated, tags that you think may be useful, add something to description, etc.

If you use a module that doesn’t have a blog post and/or videos yet, you can submit a PR anyway so all of the community can know that it exists and maybe someone will write about it!

Follow up

Follow the repository news by clicking on “Watch” button and/or follow @psmcentral Twitter account.

Feel free to share this blog post! The more people we reach, the better!

Thanks for reading.

Generate Markdown Table of Contents based on files within a folder with PowerShell

Last week I was talking with Constantine Kokkinos (b | t) about generating a Table Of Contents (TOC) for a GitHub repository.

He wrote a cool blog post – Generating Tables of Contents for Github Projects with PowerShell – about it and I will write this one with a different problem/solution.

Context

I’m working on a new project (news coming soon) that uses a GitHub repository and I expect to have a big number of files within a specific folder.

Requirement

After some pull requests and merges, I want to update my readme.md file and update the INDEX with this TOC.
For this:

  • I want to be able to generate a TOC based on the existing content of a specific folder.
  • Each TOC entry must be composed by a name and a link to the .md online file.
  • This list must be ordered alphabetically.

Then, I can copy & paste and update the readme.me .
NOTE: For now, I just want a semi-automatic way to do it. Maybe later I will setup Appveyor and make this fully automated 🙂 ).

Approach

Get all files with .md extension, order by name and, for each one, generate a line with a link to the GitHub repository .md file.

To do the list I will use the “*” (asterisk) character after a “TAB” to generate a sub list. (This is Markdown’s syntax)

The code

I have three parameters:

  • the $BaseFolder – It’s the folder’s location on the disk
  • $BaseURL – to build the URL for each file. This will be added as a link
  • $FiletypeFilter – to filter the files on the folder. In my case I will use “*.md” because I only want markdown files.

The code is:
UPDATE: Thanks to Jaap Brasser (b | t) who has contributed to the this code by adding the help and some improvements like dealing with special characters on the URL (spaces). You can find the most recent version of this Convert-FolderContentToMarkdownTableOfContents.ps1 function here on my GitHub

Running this code pointing to my “NewProject” folder

I will get this output (This have fake links but just to show the output format)


Index


Nice! This has the following code behind:

## Index
* Modules
  * [File1](https://github.com/user/repository/tree/master/Modules/File1.md)
  * [OneNewFile](https://github.com/user/repository/tree/master/Modules/OneNewFile.md)
  * [OtherFile](https://github.com/user/repository/tree/master/Modules/OtherFile.md)

Now, I can copy this markdown code and update my readme.md file.

Final thoughts

This isn’t rocket science 🙂 but it is an idea and a piece of code that will help me and maybe can help you too 🙂

Read Constantine’s blog post (Generating Tables of Contents for Github Projects with PowerShell) to get different ideas.

Thanks for reading

[PowerShell] From 20 minutes to 20 seconds – Change domain user password on multiple domains without login into each one

I’m working on a environment where I have to deal with multiple domains.
The user’s password needs to be updated each 40/45 days (it depends on the domain).

Can you see the pain?

This means that every month and half I have to dedicate like 20 minutes to change my password on 10 different domains by logging in to a host that belongs to that domain to be able to change it.

Because I would like a faster way to do this and I’m not proficient with AD, I asked help to Jaap Brasser (b | t). He pointed me to a blog post he has written sometime ago called Active Directory Friday: Change a user’s password.

This code resets the password and not to change/update it. Because I don’t have permission to do it, this method won’t work for me. Also, I don’t have the AD module with all *-ad* cmdlets installed on my machine.

Despite this, his code gave me some clues about the way it could work. I did some more research and finally I found a way to do it from a single machine.

Disclaimer: For this script work, you need to be able to query the LDAP on the different domains. Some things may differ between my environment configuration and yours. Example: In my environment I had to use the IP of the DC instead of name.

When you run the script you will be prompted for your current/old credentials and the new password.

$oldCredential = Get-Credential -Message "Enter domain, user name and old password"
$NewPassword = Read-Host -AsSecureString -Prompt 'Enter new password'

#Here, we get the domain part from credential - in my case I had to use the IP
$DC = $($oldCredential.UserName.split('\')[0]) 
$userName = $($oldCredential.UserName.split('\')[1])
 
$DomainEntry = New-Object -TypeName System.DirectoryServices.DirectoryEntry "LDAP://$DC" ,$oldCredential.UserName,$($oldCredential.GetNetworkCredential().password)
$DomainName = $DomainEntry.name

# Example search against remote domain
$Searcher = New-Object -TypeName System.DirectoryServices.DirectorySearcher
$Searcher.Filter = "(samaccountname=$userName)"
$Searcher.SearchRoot = $DomainEntry
 
$user = [adsi]$Searcher.FindOne().Path
$user.ChangePassword($oldCredential.GetNetworkCredential().password, [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($NewPassword)))

With this code you should be able to change your password on a different domain from just one location.

I have updated my password on all domains in less than 20 seconds.

Thanks for reading