Here are the slides and scripts from my June presentation at the North Texas SQL Server Users Group. The presentation was on the Data Collector in SQL Server 2008 but it was short so I also touched on filtered indexes, query hashes and troubleshooting. Enjoy and let me know if you have any questions.
Also Peter Debetta is presenting TONIGHT at 6:30 on the following topics:
"We'll be discussing the various ways you can secure your SQL Server data via encryption, including the new Transparent Data Encryption feature of SQL Server 2008."
 Stop by.
Reminder: You can catch me at the North Texas SQL Server User's Group tomorrow night. The Data Collection presentation that I put together may be short so I have a few backup slides and demo's on filtered indexes, queries hashes and the 2 second SQL Server 2008 upgrade. Directions and details can be found at http://northtexas.sqlpass.org
Hope to see you there.
I am going to give a talk on the data collector in SQL Server 2008 at this month's meeting.
Topic:Data Collections in SQL Server 2008
Date:Thursday, June 19th, 2008
Overview:
Data collections are a new feature in SQL Server 2008. They allow you to collect perfmon, trace or query data to a central database. We will start off by setting up and configuring Data Collections. We will look at the default collections and historic reports in Management Studio. We will cover setting custom collections and providing a UI with SSRS.
Directions etc can be found here.
The big news of the week was the HH{H} launch. A lot of marketing fluff. Not just from MS but from vendors trying to ride the wave. That said, I think the rest of the year is going to be really exciting technology wise.
There are some good webcasts at the virtual event site through the slick but slightly buggy silver light interface. Check it out.
The MS SQL site got a facelift. I suspect it will replace the original site soon. Props to ThePremiers for breaking it.
Dan reports the return of the tsql debugger, object search and the new activity monitor. He demo'd these features and the other manageability features at the launch event.
SQL Server 2008 is setting records. Most impressively in loading 1TB+ in 30 minutes with SSIS. Read it on the SQLPerf site here and here, the SQLCAT site, Erin's blog, and Denis's blog.
Peter has the scoop about issues with SQL Server 2008 and leap year. Whoops :) Good thing they didn't launch today.
Linchi comments on the intel hexacore procs that should hit Q3-4.
Derek C drops knowledge on the SQL Server 2008 performance data collector.
This might be HAWTALTA to some but I consistently here people complaining about the speed of SQLWB. Jeff posts links to resources.
Bonnie posts the 2008 version of samples on codeplex. Direct dl here. Details here.
Check out this very detailed post on SNAC for the horse's mouth.
Andy has a no nonsense post on sparse columns.
I should have mentioned this last week. Simon did a great job in detailing full text in SQL Server 2008 in a series of posts. Start here.
Have a great weekend!
In the first post, we took a quick look at how data collection works. In this post, we will see how to create custom data collection.
To create a custom collection, you must use the stored procedures. In Bill Ramos's web cast, he hints it may stay like this i.e. NO GUI. I tend to doubt it because Microsoft built their empire making hard stuff easy.
In this example, we will look at collecting 3 key indicators of a CPU bottleneck. These items are actually included in the "server activity" system data collection. However, it cannot be modified and it collects everything under the kitchen sink. Multiply that times 20 or 100 servers and you are looking at some huge storage requirements. I think smaller data collections like this will be common.
Let's get to business...
use msdb;
--Let's create the collection set
Declare @collection_set_id_1 int
Declare @collection_set_uid_2 uniqueidentifier
EXEC [dbo].[sp_syscollector_create_collection_set]
@name=N'CPU indicators',
@collection_mode=0, --Let's start in cached mode.
@description=N'Collects CPU KPIs from perfmon and DMVs',
@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_1 OUTPUT,
@collection_set_uid=@collection_set_uid_2 OUTPUT
Select @collection_set_id_1, @collection_set_uid_2
--Let's get the needed perfmon counters
Declare @collector_type_uid_7 uniqueidentifier
Select @collector_type_uid_7 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';
Declare @collection_item_id_8 int
EXEC [dbo].[sp_syscollector_create_collection_item]
@name=N'Perfmon CPU counters',
@parameters=N'
<PerformanceCountersCollector>
<PerformanceCounters Objects="SYSTEM" Counters="Processor Queue Length" Instances="*" />
<PerformanceCounters Objects="Processor" Counters="% Processor Time" Instances="*" />
</PerformanceCountersCollector>',
@collection_item_id=@collection_item_id_8 OUTPUT,
@frequency=5,
@collection_set_id=@collection_set_id_1, --Output from sp_syscollector_create_collection_set
@collector_type_uid=@collector_type_uid_7
Select @collection_item_id_8
--Let's get the DMV data
Declare @collector_type_uid_3 uniqueidentifier
Select @collector_type_uid_3 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Generic T-SQL Query Collector Type';
Declare @collection_item_id_4 int
@name=N'CPU pressure check',
@parameters=
N'<TSQLQueryCollector>
<Query>
<Value>select SUM(runnable_tasks_count) from sys.dm_os_schedulers where scheduler_id >2
</Value>
<OutputTable>CPU_Pressure_OS_Schedulers</OutputTable>
</Query>
<Databases UseSystemDatabases="true" UseUserDatabases="true" />
</TSQLQueryCollector>',
@collection_item_id=@collection_item_id_4 OUTPUT,
@collector_type_uid=@collector_type_uid_3
Select @collection_item_id_4
--Let's start the collection
exec sp_syscollector_start_collection_set @collection_set_id = @collection_set_id_1 --Output from sp_syscollector_create_collection_set
We can now see that the collection has been created in SSMS. This is what it looks like:
That's it. In the next part, we will look at reporting off of this collection through tsql and SSRS.
This will be a quick and dirty post on data collection in SQL Server 2008. The next two posts will cover creating custom data collection sets and integrating reporting services. For a deeper understanding of data collections, check out Bill Ramos's web cast. He talks about it in this post.
Data collection is basically a separate application that collects data about your SQL Server 2008 server, stores in SQL Server and provides reporting. It is like some of those 3rd party applications by vendors like Quest and Idera. It is fairly simple in design. This will allow you to easily get up to speed and extend. That is where it may excel over the 3rd party products.
The moving parts include an executable called dcexe.exe, a warehouse database, SSIS, SQL Agent jobs and your data sources. The sources can be perfmon counters, DMV's,SQL traces and even application data. It was designed to minimize the stress on the source system but the warehouse database should reside on a different system.
This feature comes with 3(as of right now) system collections.
For a deeper discussion of this topic, please check out the web cast. The next post will demo setting up a custom collection. Part 3 will be on reporting off of the data collection.