Paging Doctor Powershell

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Zach Mattson » Paging Doctor Powershell

Paging Doctor Powershell

Rate Topic

Display Mode

Topic Options

Author

Message

WI-DBA

Posted Sunday, April 03, 2011 11:18 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 93, Visits: 545

Comments posted to this topic are about the item Paging Doctor Powershell

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

Post #1087903

SQLRNNR

Posted Monday, April 04, 2011 10:19 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

Nice article and useful tool.

Thanks

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1088416

WI-DBA

Posted Tuesday, April 05, 2011 6:04 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 93, Visits: 545

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/

Post #1088535

dawaller

Posted Tuesday, April 05, 2011 7:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:14 AM
Points: 1, Visits: 127

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

Post #1088585

WI-DBA

Posted Tuesday, April 05, 2011 12:22 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 93, Visits: 545

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/

Post #1088830

alen teplitsky

Posted Thursday, April 07, 2011 7:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 1,408, Visits: 4,505

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

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #1089850

WI-DBA

Posted Thursday, April 07, 2011 7:17 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 93, Visits: 545

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/

Post #1089862

« Prev Topic | Next Topic »

Permissions