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.