Paging Doctor Powershell

Question

Post reply

Paging Doctor Powershell

WI-DBA

SSCrazy

Points: 2110
More actions
April 3, 2011 at 11:18 pm

#235065

Comments posted to this topic are about the item Paging Doctor Powershell
Cheers
http://twitter.com/widba
http://widba.blogspot.com/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nice article and useful tool.

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WI-DBA SSCrazy Points: 2110 More actions · Answer 2

Thanks. If folks have ideas on things to add to it, let me know and I can work them in and share the script.

Cheers
http://twitter.com/widba
http://widba.blogspot.com/

dawaller SSC Veteran Points: 213 More actions · Answer 3

This looks like some great stuff, however I am new to powershell. Do you have any step by step instructions for running the scripts? I would like to try it out in my test servers.

Thanks,

David

WI-DBA SSCrazy Points: 2110 More actions · Answer 4

I don't have anything step by step - but I could easily create a step by step article or a set of blog posts that cover it. Is that something people would like to see? It could be articles or I could just blog it in small bites.

The script I uploaded should be able to be run as long as you have Powershell 2 installed without any issues. (you shouldn't need any experience) Just give it a server name and a few hours and let it run.

Cheers
http://twitter.com/widba
http://widba.blogspot.com/

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

nice article, going to have to look at it. right now i have perfmon running on a server dumping data to a central database where i run reports from. this is a lot lighter, but i was going to use the perfmon data for historical baselines as well. one thing i don't like about powershell is that you can't seem to dump the data into SQL

WI-DBA SSCrazy Points: 2110 More actions · Answer 6

You can dump directly to SQL, I do that for gathering stats for our VM admins. I don't like looking at csvs or text files anymore than the next DBA.

Once you have a list of counters (note I have some extra properties in my object collection) - you could simply call a proc (or just directly insert them)

$CounterCollection | Where-Object{$_.Path -ne $null} | ForEach-Object{

$qry = "EXEC .dbo.pr_Add_Perf_Collection "

$qry = $qry + " @server='" + [string]$_.Server + "'"

$qry = $qry + ", @performance_metric='" + [string]$_.Path + "'"

$qry = $qry + ", @performance_value=" + [decimal]$_.CookedValue

$qry = $qry + ", @date_sampled='"+ $_.Timestamp + "'"

$qry = $qry + ", @samples_taken=" + $_.Samples

$qry = $qry + ", @sample_interval=" + [int]$_.SampleInterval

Invoke-SqlNonQuery -ServerInstance $SQLServerToStore -Query $qry

}

function Invoke-SqlNonQuery{

param(

[string]$ServerInstance,

[string]$Query

)

$QueryTimeout=30

$conn=new-object System.Data.SqlClient.SQLConnection

$constring = "Server=" + $ServerInstance + ";Integrated Security=True"

$conn.ConnectionString=$constring

$conn.Open()

if($conn){

$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)

$cmd.CommandTimeout=$QueryTimeout

$cmd.ExecuteNonQuery() | out-null

$conn.Close()

}

Hope this helps. I will see about writing a perfmon metric gather/storage article if SSC wants to publish it.

Cheers
http://twitter.com/widba
http://widba.blogspot.com/

Aaron Street SSC Veteran Points: 230 More actions · Answer 7

Examples would be helpful how to pipe data directly to sql server database.

WI-DBA SSCrazy Points: 2110 More actions · Answer 8

This is probably what you are looking for http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

Note it uses bulkcopy and there are some restrictions with that.

Cheers
http://twitter.com/widba
http://widba.blogspot.com/

Aaron Street SSC Veteran Points: 230 More actions · Answer 9

How could you exclude certain error codes in array string?

The below example does not seem to filter errors from my array.

Ex.

$IgnoreErrorCode=@(

'1608',

'17832',

'17824',

'18456';

)

# Write-Host "Reading SQL Log for $sqlServerName"

try{

$sqlServer.ReadErrorLog() | Where{$_.LogDate -is [datetime] } |

Where-Object{$_.LogDate -gt $DatetoCheck } |

Where-Object{$_.Text -like "*Error*" -or $_.Text -like "*Fail*"} -and ($_.Text -notcontains $IgnoreErrorCode) |

Select-Object LogDate,Text |

Out-GridView -Title "$sqlServerName Log Errors"

} catch {

Write-Host "Error Reading $sqlServer.Name"

}

paulo.morgado SSC Enthusiast Points: 107 More actions · Answer 10

As a norm, I only use double quotes when I intend the string to be interpolated. When I want to use the string as it is, I use single quotes. It's not much, but why waste CPU and time for nothing?

You can filter the WMI instances in the query:

Get-WmiObject -Class Win32_Service -Filter 'Name like "%SQL%"'