SQLServerCentral / Discuss Content Posted by Zach Mattson / Article Discussions / Article Discussions by Author / Paging Doctor Powershell / Latest Posts

RE: Paging Doctor Powershell

WI-DBA — Thu, 07 Apr 2011 07:17:58 GMT

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.

RE: Paging Doctor Powershell

alen teplitsky — Thu, 07 Apr 2011 07:08:35 GMT

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

RE: Paging Doctor Powershell

WI-DBA — Tue, 05 Apr 2011 12:22:32 GMT

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.

RE: Paging Doctor Powershell

dawaller — Tue, 05 Apr 2011 07:14:07 GMT

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

RE: Paging Doctor Powershell

WI-DBA — Tue, 05 Apr 2011 06:04:22 GMT

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

RE: Paging Doctor Powershell

SQLRNNR — Mon, 04 Apr 2011 22:19:43 GMT

Nice article and useful tool.Thanks

Paging Doctor Powershell

WI-DBA — Sun, 03 Apr 2011 23:18:03 GMT

Comments posted to this topic are about the item [B]Paging Doctor Powershell[/B]