SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

February 2008 - Posts

  • SQL 2008 CTP6

  • SQL Server LinkedIn group

    I have created a LinkedIn SQL Server group. Join up.

    SQL Server Professionals

  • Creating a custom data collection in SQL Server 2008

    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',



      <PerformanceCounters Objects="SYSTEM" Counters="Processor Queue Length" Instances="*" />     

      <PerformanceCounters Objects="Processor" Counters="% Processor Time" Instances="*" />


          @collection_item_id=@collection_item_id_8 OUTPUT,


          @collection_set_id=@collection_set_id_1, --Output from sp_syscollector_create_collection_set


    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

    EXEC [dbo].[sp_syscollector_create_collection_item]


          @name=N'CPU pressure check',




          <Value>select SUM(runnable_tasks_count) from sys.dm_os_schedulers where scheduler_id >2




    <Databases UseSystemDatabases="true" UseUserDatabases="true" />


          @collection_item_id=@collection_item_id_4 OUTPUT,


          @collection_set_id=@collection_set_id_1, --Output from sp_syscollector_create_collection_set


    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.

    Technorati Tags:
  • SQL Server 2008 performance data collection in 5 minutes

    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.

    • Disk usage - this one basically logs the info from a sp_spaceused for each db. They say that disk space related performance counters may end up in here as well.
    • Query Statistics - This is "notable" queries from the procedure cache.
    • Server Activity - This collects data from system and OS perform counters as well as the SQLOS DMV's.

    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.


    Technorati Tags:


    * Cross posted from *

  • Migrating your SQL Cluster to a new SAN

    There is a nice utility that does a lot of the heavy lifting for you when migrating your cluster to a new SAN. It is ClusterRecovery.exe. It is designed for replacing a failed disk but when you migrate to a new SAN, you have to go through the same steps. It basically moves all of the meta data like dependencies. The utility can be found the Win2k3 resource kit.

    The basic steps are:

    1. Present storage.

    2. Partition\format\letter. Pick any free letter. We will swap letters later. Let's choose h:\.

    3. Stop SQL and move data from old SAN to new SAN.

    4. Add the h:\ drive to  the SQL cluster group.

    5. Run the cluster recovery tool, connect to the cluster. Choose the old and new drive.


    6. Open disk management, change the original g:\ drive to a different letter.

    7. Change the new drive's letter to the letter of the original drive's letter. h:\ to g:\ in our example.

    8. Bring the drives and services online.

    9. Remove the old drive resource with the "(lost)" suffice.


    That is about it.

    * Cross posted from *

Powered by Community Server (Commercial Edition), by Telligent Systems