How do you use the performance counter functionality?

Developer
Jan 13, 2011 at 7:50 PM
Edited Jan 14, 2011 at 1:18 AM

Maybe I am blind, deaf, or dumb, but I don't understand how the Get-Counter PowerShell command is that useful, and since SQLPSX seems to more or less just provide a wrapper around that, I am not sure the SQLPSX extension is that powerful (SQLPSX also has different names for things than standard PLA vocabulary, which is confusing).  It seems standard practice with PerfMon to define a Data Collector Set and run that against the target machine.  As far as I understand the API offered in SQLPSX, it just wraps the PowerShell built-in Get-Counter.  Shouldn't it be using PLA (the .NET Performance, Logs and Alerts) API?  Am I just being stupid and not using the SQLPSX extension correctly?

Edit: Another thing, Get-PerfCounterCategory does not use the standard PowerShell cmdlet extensions -Filter, -Exclude, and -Include.  It would be ideal to configure a DataCollectorSet using this.  I suppose there is room for two different PerfMon APIs in SQLPSX???

And Get-PerfCounterInstance can only be used as a pipeline operator as far as I can tell, limiting its usefulness.

I'm kind of dreaming big, pie in the sky, blue sky dreams tonight.  Don't mind me.  But feedback and thoughts appreciated.

 

Coordinator
Jan 17, 2011 at 5:02 PM

I would agree there are much better tools for collecting performance counter data.  In an Enterprise environment you'd typically use System Center or some other tool. As far as free performance collectors, PAL is much more feature rich tool and is also built using PowerShel. That said if you do want to use the PerfCounters module Laerte wrote an article for Simple-Talk which describes the setup in detail:

http://www.simple-talk.com/sql/database-administration/gathering-perfmon-data-with-powershell/

Developer
Jan 17, 2011 at 5:33 PM
Edited Jan 17, 2011 at 6:20 PM

Does System Center have a custom API, or is it just a wrapper around the PLA library for .NET?

As for PAL, as far as I am aware, it handles analyzing log files, but doesn't handle generating Data Collector Sets (DCSs)...

I've got 6 basic DCS's I've defined in a database... and I have corresponding Configuration tables that basically provide a configuration graph for generating PowerShell code to launch PLA stuff behind the scenes.  To give you an idea of where I am going with this, have a look at this blog article written by somebody else: http://www.jonathanmedd.net/2010/11/managing-perfmon-data-collector-sets-with-powershell.html

I have never used System Center before, and never even thought about it in the context of managing an Enterprise environment (but we're getting more complex here by the day, and I need to explore all options).

I saw Laerte's blog post, but I still found it confusing.  He presented things entirely in terms of functionality and not use cases.  I also am not a huge fan of piping in server names to Get-PerfCounterCategory.  Why not pass that in as a parameter and build the list using reflection if you have to... by hardwiring Get-PerfCounterCategory's input field to a server name, it becomes much less flexible.

In addition, why can't we (the free/libre open source software community) compete with System Center.  That's what the goal of this discussion is about.  I don't like the idea of having to learn some new piece of software (System Center).  I just went to their website and it is doesn't seem targeted to getting people up and running.  It seems like the website is designed for MSCE's who have warred through awful betas and now know all the gotchas in the product, and so the blog articles just "make sense" to the battle scarred veterans.

What sort of stuff would just be awesome to put into a great performance monitoring API for the community, by the community?  I think a way to contribute back PLA Configs would be great.  Right now there is very little sharing of the most important part of it... I bought tons of SQL books about performance and part of the reason for that was to build the best set of data collector sets...

 

Edit: By the way, hope I don't come across like I am slamming Laerte.  His blog is what (a) got me interested in SQLPSX (b) even made me think to use PowerShell for performance, logging and alerts.  I think there is a wheat germ of a great idea in his blogs.  I owe him pretty much everything I am discussing here.

Coordinator
Jan 17, 2011 at 6:51 PM

I think you're right about PAL, more of analyzer/reporter than collector.

I also agree that System Center very complex. Fortunately in my environment we have dedicated System Center administrators who know the product just as DBAs know SQL Server. For us we're simply users of System Center, but not the guru's that set it up and perform day-to-day administration. I see a lot overlap in MDW, PBM and UCP which makes me question their future. I think what's really needed for System Center is a good book/documentation geared towards the DBA user.

As far as the free stuff. I haven't messed with PLA, but it does look like a more detailed solution. If what you have already can be shared and you want to contribute a module to SQLPSX based on PLA, let me know and I'll add you as a developer.

Developer
Jan 17, 2011 at 7:15 PM
Edited Jan 17, 2011 at 7:17 PM

Sorry,

MDW = Management Data Warehouse?

PBM = Policy-Based Management?

UCP = Utility Control Point...

Well, I honestly only knew about 1 of those 3. :) MDW.

Awhile back SQL Server added support for querying the TSQL syntax of your code, but the way they did it in my humble opinion was lame.  I commented on The Data Dude's blog about this awhile back, too.  See: http://blogs.msdn.com/b/gertd/archive/2009/01/01/creating-t-sql-static-code-analysis-rules.aspx#9340536 for my comments on the TSQL Static Code Analysis API.  What sucks is that when I provide API feedback, it usually gets ignored to the point I don't get replies.

There is also tons of overlap in the tools that the Microsoft support center team uses for SQL Server incident responses... I honestly can't even remember off the top of my head all the tools they've developed since SQL Server 2000 or built and used by community... a lot of it is growing long in the tooth if you ask me (SQLIOSim and SQLStress, RML(Read80Trace, OSTRESS), etc.).

Developer
Jan 17, 2011 at 7:49 PM
Edited Jan 17, 2011 at 8:25 PM

As for contributing stuff to SQLPSX, that would be cool but I have some polishing off to do.  I don't think people should be required to do things my way (e.g., store configuration in SQL Server) and so I want a parallel method for letting people maintain perf configs in PowerShell scripts, and so you would only have to do something like:

./MonitoringDaemon -GracefulRestart and it would read a config file "MonitoringDaemon.config.ps1" before the heart of the script starts.  the config file would just contain class construction code, so it would assemble objects and MonitoringDaemon would then retrieve them.

so, for example, something like

 

$objectGraph = @{Servers=@("Hi","Hi2")};

 

would be a snippet for defining a data collector set.  Then you could kind of compose together your definitions for data collector sets.

The way I do this in a database is I do (for server-side traces with SQL Profiler):

select *
from Trace_Configuration tc
	left join Trace_Configuration_Column tcc
		on tc.trace_configuration_pk = tcc.trace_configuration_fk
	left join Trace_Configuration_Event tce
		on tc.trace_configuration_pk = tce.trace_configuration_fk
	left join Trace_Configuration_Filter tcf
		on tc.trace_configuration_pk = tcf.trace_configuration_fk
	left join Event e
		on tce.event_id = e.id
	left join Column c
		on tcc.column_id = c.id
	left join Logical_Operator lo
		on lo.id = tcf.logical_operator_id
	left join Comparison_Operator co
		on co.id = tcf.comparison_operator_id

[Note: Above, what I basically did was insert all the values from SQL Server Books Online into "Look Up Tables" (tables with PK named id).  This took some time, but it now makes dynamically generating user interfaces for this stuff a breeze.]

And here are the first 6 reports for server-side traces that I added to the system, based on somebody's old scripts for server-side profiling I found online somewhere:

NAME
DESCRIPTION
Slow stored procedures and queries
Identify long-running stored procedures and queries.
List all active stored procedures
Get a list of all the stored procedures called within a specific database
Find a specific TSQL error/exception
Generally useful, some examples:\n(1) finding application's that are using objects that have been dropped;\n(2)finding databases that have been set offline.
Troubleshooting deadlocks
Identify the connections (SPIDs) involved in a deadlock, using the Deadlock and Deadlock Chain events.
Identifying stored procedure recompilations
Stored procedure recompiles have a potential to hinder the performance of your application. So it is important to identify those procedures that are recompiling repeatedly, and fix them, if the recompilation is not beneficial. The following template creates a trace that logs the stored procedures that are recompiling along with the database ID in which they are running. It also captures EventSubClass. From SQL Server 2000 SP3 and above, EventSubClass tells you the exact reason for the stored procedure recompilation. For more information search Microsoft Knowledge Base (KB) for article 308737.
Blackbox trace
A black box trace stores a record of the last 5 MB of trace information produced by the server. This is very useful for troubleshooting nasty problems, bugs and access violation errors, that cause the SQL Server to shutdown. Consult SQL Server 2000 Books Online and Microsoft Knowledge Base for more information on Blackbox traces.

and for PerfMon, the skeleton so far is:

select *
from
	Counter_Set_Type cst
	inner join Counter_Set cs
		on cst.id = cs.counter_set_type_id
	inner join Counter c
		on cs.id = c.counter_set_id

Obviously I still need to add a permanent Data_Collector_Set schema or something similarly named, but I haven't designed that portion of the schema yet.

But the idea would be that you should be able to "map" (in the object-relational sense) your database objects to PowerShell objects, and if you don't want dependency on SQL Server, then don't install SQL Server on the machine you want to run the scripts from...

Coordinator
Jan 17, 2011 at 9:08 PM

Looks interesting. I've added you as developer to the SQLPSX project. We're using SVN specially TortoiseSVN as the source code tool for now, but plan to switch to Mecruial in the future. On the main CodePlex site there's an FAQ on how to hook up SVN with CodePlex.

For source control structure we're using the classic branch/tab/trunk structure. Each developer creates a folder with their name under branch. For the most part a developer will own a module or group of modules. So think about what new modules you want to contribute. Obviously your monitoring solution would be one. There's a private discussion thread in the same discussion area where you can post messages for the team. We use the private discussions to send updates to everyone on what we're working on and planning for next release. Feel free to comment on this thread:

http://sqlpsx.codeplex.com/Thread/View.aspx?ThreadId=235858

Releases are every 30 to 90 days, so just let me know something is ready and include it in the next release.