Quick Post–Troubleshooting SQL Server with PowerShell–Top 5 CPU Process by Server


Pessoal,

Estou fazendo minha apresentação pro webcast do Virtual Pass BR, provavelmente será dia 22 mas eu aviso antes, que será como fazer alguns troubleshootings do SQL Server usando PowerShell. Veremos MUITA COISA LEGAL..eu garanto. Essa é uma prévia do que está por vir. (começo..coisa simples..depois vou fritar muito neuronio)

I’m doing my presentation for the webcast of the Virtual Pass BR, probably  it  will be on 22 Now but I notice before, that is how some troubleshooting’s SQL Server using PowerShell. We will see some really cool stuff.. I guarantee it. This is a preview of what’s to come. (beginning ..simple stuff..the hard is coming Sorriso)

Uma das primeiras coisas que eu verifico quando não tenho um sistema de monitoramento em cima do processo do SQL Server e preciso fazer um troubleshooting de lentidão é dar uma olhada nos processos e verificar se realmente é o SQL Server que tá usando toda CPU. Independente de ser ele ou não se estiver trabalhando com cpu pressure teremos lentidão claro. Mas eu tiro a duvida primeiro, assim não gasto tempo a toa procurando chifre na cabeça de cavalo se o problema não é o SQL Server.

One of the first things I check when I  don’t  have a monitoring system on SQL Server process and a need to do a Slow Server  troubleshooting is  look at the processes and make sure that it is the SQL Server using All CPU. Regardless of whether or not it is working with CPU pressure will clear slowly your system . But I see at the first shot, so do not spend time aimlessly looking horn on a horse’s head if the problem is not SQL Server.

Isso é simples fazendo pelo task manager. Mas eu preciso rodar o MSTSC, entrar no Server e olhar o bendito do task manager. E se caso o problema estiver em mais de um server ou  se voce simplesmente quizer dar uma olhadinha nos top 5 que estão usando CPU nos seus servidores, a coisa começa a ficar complicada.

This is simply doing the task manager. But I need to run the MSTSC, enter the Server and look at the blessed of the task manager. And if in case the problem is in more than one server or if you just want to take a peek in the top 5 that are using CPU on its servers, it starts to get complicated.

Para isso temos PowerShell Sorriso

Se eu quiser ver os processos top 5 localmente :

For this we have PowerShell. If I want to see the top 5 processes locally:

   1: #5 Processos mais pesados

   2: Get-Process | Sort-Object cpu -Descending | select -First 5

1

Mas quando passo para acesso remoto, acontece um BUG . A coluna CPU não aparece :

But when I walk for remote access, a bug occurs. The CPU column does not appear:

   1: #5 Processos mais pesados

   2: Get-Process -computername Yoda | Sort-Object cpu -Descending | select -First 5

2

O Bug é relacionado a Credenciais e somente no cmdlet get-process. Podemos usar alguns workaround :

The bug is related to the Credentials and only get-process cmdlet. We can use some workaround:

WMI :

   1: #Bug na Coluna CPU quando Remoto - Usar WMI

   2: Get-WmiObject Win32_Process -computername yoda  `

   3:                             | select     Name, `

   4:                                         @{Name="CPU_Time"; Expression={$_.kernelmodetime + $_.usermodetime}} `

   5:                             | sort CPU_Time -Descending

3

Sinceramente ?  Apesar de tudo ser uma linha de comando somente, muito trabalho para formatar. Não gostei dela.

Honestly? Though everything is a command line only, a lot of work to format. I did not like it.

Invoke-Command

Como o problema é na credencial, primeiramente precisamos salvar as credenciais atuais que estou usando para passar ao Invoke-Command. Para isso usaremos as funções do Posh Jedi Hal Rottenberg (@halr9000), Export-PSCredentials e Import-PSCredentials. Voce pode fazer o download aqui . Eu fiz uma pequena alteração nas funções para guardar o XML encriptado no $PSHOME da maquina. Somente alterei as linhas para gravar no $PSHOME da maquina se não passarmos o path:

Since the problem is the credential, we first need to save your current credentials that I'm using to pass to Invoke-Command. For this we use the functions of the Jedi Posh Hal Rottenberg (@ halr9000), Export-PSCredentials and Import-PSCredentials. You can download it here. I made a small change in the functions to save the encrypted XML in $PSHome the machine. Only changed the lines to write to $PSHome the machine if you do not pass the path:

   1: function Export-PSCredential {

   2:     param ( $Credential = (Get-Credential), $Path = "$($pshome)\credentials.enc.xml" )

   3:     

   4:  

   5: function Import-PSCredential {

   6:     param ( $Path = "$($pshome)\credentials.enc.xml" )

Bom, ai primiera coisa a fazer é exportar uma vez somente sua credencial :

Well the first thing to do is to export only once your credentials:

   1: Export-PSCredential

Ira aparecer uma tela para voce passar suas credencias e sera salvo um XML encriptado no diretorio $PSHOME da sua maquina.

A popup will appear for you to pass your credentials and will be saved in an encrypted XML on directory $PSHome of your machine.

4

Apos salvar vamos ao codigo : Repare que nas credenciais do Invoke-Command eu uso o cmdlet Import-PSCredential

After we save the code: Note that the in the credentials of the Invoke-Command cmdlet I use the Import-PSCredential

Quero ver os top 5 processos com High CPU no computador yoda :

I want to see the top 5 processes with high CPU in the computer yoda:

   1:  

   2:  

   3: #Ou Usar Invoke-Command - Credenciais ja exportadas usando Export-PSCredential

   4: Invoke-Command   -ScriptBlock {get-process | Sort-Object cpu -Descending | select -First 5 }`

   5:                 -ComputerName Yoda -Credential (Import-PSCredential) 

5

Mas e se eu quiser passar para varios servidores ?

But what if I want to move to multiple servers?

Criei um Arquivo .txt no meu path c:\temp chamado Servers.txt com os nomes deles :

I created a .TXT file.  on my path c:\temp called Servers.txt with it names:

6

E então  :

So :

   1: #Varios Servidores

   2: Invoke-Command     -ComputerName (Get-Content c:\Temp\Servers.txt) `

   3:                 -ScriptBlock {get-process | Sort-Object cpu -Descending | select -First 5 } `

   4:                 -Credential (Import-PSCredential) 

7

 

Prontinho..Com uma linha de comando..

Yeahhh..A simple Command Line

Simples, Limpo e Rápido. PowerShell Clássico.

Simple , Fast and Clean. Classic PowerShell

Não percam meu webcast, vou fritar neurônios Sorriso

Do not miss my webcast, I promise to fry some neurons Sorriso

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

2 Responses to Quick Post–Troubleshooting SQL Server with PowerShell–Top 5 CPU Process by Server

  1. Just wish to say your article is as surprising. The clearness
    in your post is just great and that i can think you’re an expert on this subject. Well together with your permission allow me to grab your feed to stay up to date with coming near near post. Thank you one million and please keep up the enjoyable work.|

  2. Pingback: Displaying SQL Server Instance Name on Get-Process and Get-Counter | $hell Your Experience !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s