SQL 2008 Data Collector - Custom Performance Counter Set

During my session "Server Monitoring Made Simple with SQL 2008" I covered the new Performance Studio, Management Data Warehouse, and Data Collectors in SQL Server 2008. Someone asked how one would create a custom Performance Counter Collection and I wasn't prepared to answer that particular question. However, I provided my blog link in my slide deck, and I said I would find the answer and post it, so hopefully that person actually reads my blog at some point, but if not, maybe someone else will benefit from this example.

If you were to do a search online, you will find a number of examples that don't work and generate a XML error when you try to run the code. The reason for this, is that almost every example I could find was written for CTP6 of SQL Server 2008. I was able to figure out the proper XML namespace reference that is needed in the RTM of SQL Server 2008 to create a custom collector.

Before you actually go out and create a completely custom data collector for performance counters, you should consider something. The System Activity Collection Set that is one of the System Collection Sets, already includes over 60 performance counters as a collection item. You can see what ones are already included by running the following query:

SELECT name, frequency, parameters
FROM syscollector_collection_items
WHERE name = 'Server Activity - Performance Counters'

Since the definition of the collection items is XML based, you can click the XML Document and it will open in a new window with friendly formatting. If you find that the default counters don't meet your specific needs, then you can use the following example to create a custom collection set:

use msdb;
--First create the collection set
declare @collection_set_id int
declare @collection_set_uid uniqueidentifier

exec [dbo].[sp_syscollector_create_collection_set]
   @name=N'Performance Counter Collection Set',
   @collection_mode=0, --Let's start in cached mode.
   @description=N'Collects Performance Counters from PerfMon',
   @target=N'', --Undocumented
   @logging_level=0, --0 through 2 are valid
   @days_until_expiration=5, --Let's just keep data 5 days. We will rollup for reporting.
   @proxy_name=N'', --Use if you want it to run under something other than the SQL Agent svc account.
   @schedule_name=N'CollectorSchedule_Every_5min', --Built in schedule
   @collection_set_id=@collection_set_id output,
   @collection_set_uid=@collection_set_uid output

-- Now create the collection item for the Performance Counters to be collected
declare @collector_type_uid uniqueidentifier
declare @collection_item_id int

select @collector_type_uid = collector_type_uid
from [dbo].[syscollector_collector_types]
where name = N'Performance Counters Collector Type';


exec [dbo].[sp_syscollector_create_collection_item]
   @name=N'Standard SQL Server Performance Counters',
   @parameters=N'
<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
 <PerformanceCounters Objects="Processor" Counters="% Processor time" Instances="_Total" />
 <PerformanceCounters Objects="Processor" Counters="% Privileged time" Instances="_Total" />

 <PerformanceCounters Objects="Memory" Counters="Available KBytes" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Pages/sec" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Committed Bytes" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Commit limit" Instances="*"  />

 <PerformanceCounters Objects="System" Counters="Processor Queue Length" Instances="*"  />
 <PerformanceCounters Objects="System" Counters="Context Switches/sec" Instances="*"  />

 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk Queue Length" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Read" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Write" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Reads/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Writes/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Read Bytes/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Write Bytes/sec" Instances="_Total" />

 <PerformanceCounters Objects="Process" Counters="% Processor time" Instances="sqlservr" />

 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Buffer Cache hit ratio" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Checkpoint pages/sec" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Page life expectancy" Instances="*" />

 <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Total Server Memory (KB)" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Target Server Memory(KB)" Instances="*" />
          
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="Batch requests/sec" Instances="*"  />
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Compilations/sec" Instances="*" />
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Re-Compilations/sec" Instances="*" />

 <PerformanceCounters Objects="SQLServer:Databases" Counters="Transactions/sec" Instances="_Total" />
 <PerformanceCounters Objects="SQLServer:Databases" Counters="Data File(s) Size (KB)" Instances="_Total" />

 <PerformanceCounters Objects="SQLServer:General Statistics" Counters="User Connections" Instances="*" />
</ns:PerformanceCountersCollector>',
   @collection_item_id=@collection_item_id output,
   @frequency=5,
   @collection_set_id=@collection_set_id, --output from sp_syscollector_create_collection_set
   @collector_type_uid=@collector_type_uid

--Start the collection
exec sp_syscollector_start_collection_set @collection_set_id = @collection_set_id --output from sp_syscollector_create_collection_set

Some thanks must go out to Jason Massie whose original post on this topic was the original foundation for the modified script above. You can find his original post titled Creating a custom data collection in SQL Server 2008 on his blog http://www.statisticsio.com. It is one of the entries that was written for CTP6, but provided the foundation for what I have posted above, so I have to give credit where due.

Tool Tip for Presenters

I made a personal note during Kendal Van Dykes presentation on XML in SQL Server to ask him what tool he was using to zoom in on his text during his demos yesterday at SQL Saturday.  I expected to find out about a tool that I would have to shell out a few dollars for, but instead Kendal was actually using a free tool from Microsoft SysInternals called ZoomIt.

After downloading this tool tonight to play with it, I feel it is definitely worthy of a blog posting.  I know that a few people who read my blog also do presentations on SQL Server for their local user groups as well as for larger events, and this tool in my opinion is simply amazing.  A lot of times, I try to remember to set my font sizes in Management Studio to at least 14 points or larger so that the text is readable on the projected screen.  Saturday morning however, I missed setting the results text to a larger size and the resulting 8 point font was impossible to read even standing at the podium in front of the room.  To fix this would have meant closing down Management Studio and restarting it, which would have put even more dead space in my presentation than I already had due to a few demonstrations that would not run. 

If you do any kind of public presentations, I highly recommend downloading this tool for use.  Many thanks to Kendal for letting me know about it after his session.

SQL Saturday Debriefing

After getting up at 5am, meeting one of our Administrators at work who was interested in seeing the BI presentations at 6am, and driving 2 hrs to Orlando, all that I can say is it was definitely worthwhile.  Aside from the fact that I was presenting two sessions today at SQLSaturday, I loved attending the event itself, and getting to meet all the people that I either communicate with by email, or interact with online.  I also got to chat with presenters from previous SQL Saturdays as well.  Some people in attendance were:

• Joe Healy from Microsoft
• Steve Jones, who manages all content on SQLServerCentral.com
• Andy Warren who coordinates the SQL Saturday events
• Kendal Van Dyke who does an excellent presentation on XML in SQL Server
• Robert C Cain who does an excellent presentation on Full Text Search

There were many others as well.  Some key sessions were Steve Jones The Modern Resume - Building Your Brand, and Kendal's XML Features in SQL 2005 which both drew over 30 attendees.

Doing a Live Meeting Presentations - Lessons Learned

I did a Live Meeting Presentation for the PASS DBA SIG today on my lunch break, and what I had initially thought would be a easy online presentation rapidly fell apart.  Despite having practiced and tested the Live Meeting settings with the meeting coordinator, there were issues that prevented the attendees from joining onto the Live Meeting.  Had this been the only issue, the result would have simply been a minor delay as the meeting coordinator was able to login and get everyone connected to the meeting. 

However, despite testing and running through the presentation ahead of time, ensuring that my demonstrations worked and were ready, I had issues with SSMS consistently crashing, and none of my SQL Agent Task demonstrations working as expected.  The root cause which I have figured out two hours later was the installation of a new IPS agent on all of our machines at work, including laptops which get plugged into the corporate domain.  This was installed in the background while I was working this morning.  I guess that will teach me to plug into the network a bit more often with my laptop in the future.

Hopefully everyone who attended got something out of the meeting.  It didn't quite go as I had expected at all, but I was able to cover the content.  A copy of all the code and the database I use will be available on the PASS site as well as on the MSDN Code Project SQL Examples site that we use on the forums in the coming days.  I'll post links to both when they have been put up.

If you attended the event, and have any feedback, please feel free to contact me by email and let me know your thoughts.  I am always open to criticism, as it will only help me improve in the future.

SQL Server Health & History (SQLH2) Now Open Source

Last month I placed a feedback suggestion to Microsoft requesting the release of the SQLH2 source on codeplex for the community to maintain since Microsoft was no longer providing updates/changes to the project.  You can read the connect item here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=369941

I found by accident tonight that the project was in fact made available open source on codeplex this month:

http://www.codeplex.com/sqlh2

This is very cool, since the community can now drive ongoing changes and development of this awesome free tool.  I am not sure if my connect item had anything to do with this since it wasn't updated by Microsoft, but I did leave a comment on the connect letting them know it had been fulfilled and could be closed.  I plan to request to join the team that will be working on updates and changes to the app through codeplex soon.

Automating Common DBA Tasks

Wednesday, October 22, 2008, I will be presenting a Live Meeting session for the Database Administration Special Interest Group of PASS at 12pm EST (Noon).  Information on the Live Meeting can be found on the link above, but I will post it here as well:

Meeting Abstract
Database Administrators often spend hours each day doing the same repetitive tasks; checking Error Logs, Backups, Drive Space, Agent History, Job Logs, and other common “checklist” items.  Learn how to easily automate these common tasks in SQL Server 2000, 2005 and 2008 using the tools that are already available in the Operating System and SQL Server.

Meeting Details
Meeting URL: www.livemeeting.com/rm/microsoft/join
Meeting ID: SIGS
Meeting Key: SGISSIGS
Audio Conferencing (Toll-free): 1 (866) 231-6479
Participant Code: 2775795
Please mute your line once you dial-in to the audio portion of the meeting.

FIRST TIME USERS: To save time before the meeting, check your system to make sure it is compatible with Microsoft Office Live Meeting at www112.livemeeting.com/cc/microsoft

 

Hope to see you there.

Attending PASS Summit

I have been back and forth about attending PASS Summit this year.  I really wanted to attend for a number of reasons, not the least of which being the announcement of the winners of the SQL Heroes Contest. (You did vote in the SQL Heroes 2008 Community Finalists Survey didn't you?)  The deciding factor came the other day when I got the opportunity to attend PASS and report on the sessions I attend for Universal Thread Magazine.  You can read about previous Summits on the following links:

http://www.utcoverage.com/PASS/2007/

http://www.utcoverage.com/PASS/2006/

http://www.utcoverage.com/PASS/2005/

I'll also update my blog with posts as I can from there as well.  If you are attending let me know with a comment or an email through my profile here.

 

Using SQL Client Configuration Alias to Create Linked Server

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/10/14/using-sql-client-configuration-alias-to-create-linked-server.aspx

SQL Hero's Contest Survey - Vote for your favorite project

At the beginning of the year, Microsoft started a SQL Server 2008 Hero's contest, which was an open source contest to build a application leveraging SQL Server 2008 and one of the other newer technologies.  The official rules were:

http://blogs.msdn.com/sqlheroes/pages/sql-heroes-2008-official-contest-rules.aspx

Ten finalists have been picked, and you can vote for the top 5 on the connect site.

SQL Heroes 2008 Community Finalists Survey

I encourage people to check out all the projects and give them a whirl.  There are a couple of really neat ones in there, and not just my own, I am having fun playing with a few of the others that were submitted.

Microsoft Announces "Kilamanjaro" as code name for SQL 2010

http://www.microsoft.com/presspass/press/2008/oct08/10-06BI08PR.mspx

This appears to be more of a overhaul addin to 2008 for BI based on the information, but it will be fun to be playing with CTP's again next year. With the changing pace of technology today, database administrators and developers will have their work cut out for them if they plan to stay on top of the current releases and technology.

I also imagine that the turnover on book publishing is going to be significant for authors here, as the pace quickens.

So you think MS-DOS is dead?

A friend of mine posed a problem to me recently that I was able to solve using old MS-DOS command line tricks.  Recently I have been doing a good bit of work in HP-UX with Oracle since I am the backup for our Oracle DBA.  One thing that has done is to make me revisit the command line, something that I haven't had to deal with very much in recent years.  So I also started playing around with some stuff in the Windows Command line as well for how to automate certain tasks.  Take for instance, to output the results or text of executing a process at the command line, you can pump it to a file:

process >> process_log.txt

One of the hardest things I had to remember was how to provide responses to prompts.  Take for instance the rskeymgmt utility that is used to extract the encryption key from Reporting Services for safe keeping.  No matter what switches you provide to the commandline call, it still always asks "Are you sure you want to extract the key from the Report Server? Yes(y)/No(n)".  To get around this, you can create a text file called prompts.txt with a single like that has a letter Y .  Then you provide this file as the input for prompts:

rskeymgmt < prompts.txt

What is crazy is that when I was younger, basically a script kiddy, this kind of stuff was very common place in the windows world, because the GUI wasn't always there.

Congratulations 2009 Microsoft MVP

I received the following email this morning:

Dear Jonathan Kehayias,
Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others.

It is a true honor to be included in this group with people like Kalen Delaney, Paul Randall, Kimberly Tripp, Kevin Kline, Jacob Sebastian, Deepak Rangarajan and all of the other amazing MVP's for SQL Server.  Many thanks to the MVP's on the SQL Forums who have helped me along the way.