11:49 AM PST
The room is filling up nicely! There’s a nice distribution of men and women in the room. This is great! I’m actually pleasantly surprised at the number of women at the Summit.  If it weren’t for this luncheon, I probably wouldn’t have even noticed just how many SQL ladies there actually are.

12:00 PM PST
The WIT luncheon is underway! We start off with watching an energetic video displaying various types of women and men who each state “I am a technical woman” or “I support technical women.”

Rushabh Mehta, Executive VP of Finance for PASS, opens the WIT luncheon with the statement “PASS Supports Technical Women.” He then hands the floor over to Wendy Pastrick (@wendy_dance) for moderation of today’s panel.

Today’s panel is:

• Kathi Kellenberger (@auntkathi)
• Jessica Moss (@jessicammoss)
• Cathi Rodgveller (@IgniteGirls)
• Lynn Langit (@llangit)

12:10 PM PST
Kathi starts off with a great message: “I want to encourage women to enter technology, but I want to encourage boys too.” She started off as a pharmacist because “I was probably born 5 years too early” but later switched to technology. She makes less money but enjoys it more. Her daughter had to learn HTML for school and actually helped her get her first work in IT by showing her how to program. “I want both boys and girls to have the opportunity to discover lots of different things and to find what they love. And hopefully that’ll be technology.”

12:17 PM PST
The floor is then handed over to Jessica Moss, BI guru extraordinaire. She gives a great example of how influential a father can be in a young girl’s life: her father was the one who got her interested in technology and who encouraged her career. She says she never felt like she could *not* be technical because she was raised to believe she could do anything. She ends with a challenge for everyone at the Summit: talk to just one young woman and encourage her interest in technology.

12:23 PM PST
Cathi Rodgveller shares her background in education and how she started IGNITE (Inspiring Girls Now In Technology Evolution). The goal of IGNITE is to excite young women, minority races, and low-income youth, about technology and about technical careers. Rodgveller gives us a powerful message: “You can have an impact in your community. One [positive technology] event can change a young girl’s life.”

12:28 PM PST
Last, but certainly not least, Lynn Langit takes the floor. She starts off with a challenge to all audience members: tweet or text one person to say “I’m a technical women” or “I support technical women.” The room gets active while people are busy typing or texting, and Twitter is abuzz with various tweets and retweets. Lynn then takes the floor back and talks about her background and about her charity work. She mentions that every time someone buys one of her books, a donation is made to the MONA foundation. Langit also shares some of her experiences as a technical women: “I’m a developer evangelist. I’m often the only woman in the room, and I’M the one giving the presentation.”

12:35 PM PST
It’s now time for Q&A with the audience. I’ve also invited members of the Twitter community to send in their questions or comments, and we’ll do our best to get them answered. Following is a brief summary of the questions and answers provided:

Q: First up is a father of 2 teenage girls. He wants to know why WIT programs have continued to fall since 1985.
A: WIT is a low priority for schools. Schools have so many other priorities, and not enough time or funding, to address everything they need to. We need intervention from outside sources to stimulate change and ensure it’s being addressed. Rodgveller is working with her state Senator to try to enact change on the national level.

Moss mentions that studies show the top 2 issues for WIT are recruitment and retention. She also points out that middle school years are very formative and important for young women to foster their interest in technology. Rodgveller interjects that even high school is not “too late” to inspire young women.

Q: Another father asks, how can he remove stereotypes for children?
A: Parents are the best resources, period. Parents need to support their children at home and to let them know that stereotypes are negative and not okay. This includes not just gender issues, but also issues of race, religion, sexual orientation, etc.

Q: Today’s youth are concerned about the technical job market in US because of the prevalence of off-shores outsourcing. Is it still a good idea for young people to join today’s technical workforce, and how can we encourage them?
A: There are still plenty of opportunities in IT. In fact, one of the hottest trends today in technology is BI. The best way to ensure that your job is not outsourced is to stay relevant and keep up with the newest technology; those are not the jobs that are outsourced overseas.

Q: In the South, there are still lots of stereotypes. For instance, women frequently are not hired by companies for technical positions. Comments like “We can’t hire a woman for that job because it’s too valuable; what if she gets pregnant?” are still made. What can be done about this?
A: One of the executives from CA, the sponsor of the WIT luncheon, takes the stage to answer. He says in no uncertain terms that, at his company, those individuals making the disparaging remarks would be terminated. He says that the type of attitude described has to come from the top down, and the company is limiting itself by not hiring women. He ends with a message for employees to not tolerate discrimination and to go to HR whenever they see it happening.

Q: What can parents do to help WIT?
A: Parents are the greatest resource kids have. Parents set the attitude for their kids; if your attitude is positive, it will encourage your daughter to try new things and will open her up to opportunity whenever it presents itself. Also, parents need to raise the issue with schools, i.e. through PTA meetings, to make them realize that it’s important to you and it’s important for your children.

Moss: “But at the end of the day, it is the parent’s responsibility to expose your children to as much technology and as many experiences as possible.”

Kellenberger: “It is up to the parents to break stereotypes, for jobs, gender, race, etc. It’s the parent’s attitude that makes the difference.”

So that’s all I have for the Women In Technology luncheon. There was a lot of great content and some very positive messages from our panel. For more information on this topic, please check out the following resources:

• Steve Jone’s musings on the WIT luncheon
• What can men do? (to help WIT)
• IGNITE

8:36 AM PST
Keynote kicks off with Don’t Stop Believing by Journey. Awesome.

8:38 AM PST
PASS VP of Marketing Bill Graziano just takes the stage. He promises the shortest keynote of the conference, and appears to deliver on it. First up are Board announcements.

Outgoing Board Members are:

• Greg Low
• Pat Wright (@sqlAsylum)
• Kevin Kline (@kekline)

If you these folks at Summit, make sure to thank them for their hard work!

PASS President Wayne Snyder comes out to honor and thank Kevin Kline for his 10 YEARS of service.  Yes, that’s right, 10 YEARS. Wayne doesn’t get far into his speech before he gets choked up.  Great quote from Wayne:  “Kevin (@kekline) is a man of honor and integrity. He’s… well, he’s full of it.”  Kevin then gets a well-deserved standing ovation from the entire audience.

New Directors-at-Large are also announced:

• Brian Moran
• Jeremiah Peschka (@peschkaj)
• Tom LaRock (@SQLRockstar)

Next year’s PASS Summit is also announced.  It will be in Seattle from November 8th - 11th, 2010. The decision was made to have the conference in Seattle because it’s a launch year, so access to Microsoft employees will be invaluable.  The registration rate is $995 if you register soon. Details and registration can be found on the PASS site at www.sqlpass.org/summit/na2010.

8:52 AM PST
Dell keynote just started with Patrick Ortiz, Solution Architect with Dell’s Infrastructure Consulting Services for SQL Server & BI.  The keynote is pretty uneventful.

9:17 AM PST
Woot! Dr. David DeWitt, Technical Fellow, Data & Storage Platform Division at Microsoft, takes the stage. His presentation is entitled, “From 1 to 1000 MIPS.” He promises a very technical talk, against Microsoft Marketing’s wishes. He’s not going to be announcing any products, but instead plans to discuss the changes in database technology and what’s in store for us in the next 10 years.

Highlights (or at least, the ones that my simple mind was able to grasp):

• Basic RDMS design is essentially unchanged, but the hardware has changed dramatically.
• Interesting statistics in disk trends last 30 years: 10,000x capacity, 65x transfer rate, 10x avg seek time… not balanced at all
• “CPU’s and disks are totally out-of-whack in terms of performance.”
• The benefits of 1,000x improvement in CPU is almost negated by lack of improvement in disk
• Transfer bandwidth/byte trends: 1980 = 0.015, in 2009 = 0.0001… 150x slower today! “It’s like trying to provide drinking water for the town through a garden hose.”
• “Can incur up to one L2 data cache miss per row processed if row size is greater than size of cache line.”
• DBMS transfers the ENTIRE ROW from disk to memory even though the query required just 3 attributes.
• “Takeaway: DBMS must avoid doing random disk I/O as much as possible.”

In short, DeWitt shows us the power of indexing and vertical partitioning in very technical terms. He also gives us a taste for column-oriented design, which we’ll catch a glimpse of in SQL Server 2008 R2.  Awesomeness.

The keynote wraps up with a promise to include DeWitt’s presentation on the Summit DVD.  If you missed the conference, then trust me, DeWitt’s presentation is worth the cost alone; all of the sessions are just a nice bonus on top of that. 

8:00 AM PST
Michelle shows up extra early today, having learned her lesson from yesterday. Coffee in hand. Ah, much better than yesterday.

8:41 AM PST
Rushabh Mehta just took the stage to the tune of All Star.

8:46 AM PST
Interesting statistics:

• 15% reduction in revenue
• 40% growth in the SQL PASS community
• 67% reduction in IT expenses

Rushabh encourages support in the PASS community. He challenges everyone to volunteer at least an hour a month with your local user group. If you would like to become more involved in the local East Iowa SQL Server user group, please let me know!

8:51 AM PST
Wayne Snyder just took the stage to present the PASSion Awards. Folks recognized include:

• Tim Ford (@sqlagentman) - for his work on the Program Committee and Quiz Bowl. Tim was no where to be seen!  Probably eating left-over bagels.
• Grant Fritchey (@GFritchey) - for his contributions to getting SQL Server Standard relaunched. Grant also had the opportunity to show off his fabulous kilt!
• Jacob Sebastian (@jacobsebastian) - for his contributions to the Asia community. He has started 6 chapters in India. Wow!
• Amy Lewis - for her contributions to the BI Virtual Chapters and growing the sub-chapters.

9:00 AM PST
2009 International PASSion Award recipient is Charlie Hanania. Accomplishments include Swiss Chapter Leader and managing the entire 2009 PASS European Conference.

2009 North American PASSion Award recipient is Allen Kinsel (@sqlinsaneo)! Allen’s accomplishments include 5 years with PASS, and Program Manager for the 2009 Program Committee. He helped tremendously with Summit 2009, so if you see him around Summit, be sure to thank him!

Read more about the PASSion Award winners here.

9:07 AM PST
Tom Casey just took the stage. His first challenge is for all Summit attendees to act as agents of change in their respective organizations. Interesting statistic: only 20% of business users have the data they need to do their job effectively. Tom’s call to arms is to improve that statistic.

9:13 AM PST
Tom Casey: “PASS Summit is the place to be for B-I.” Statistics from Summit 2009: 2 dedicated BI tracks with 50+ BI-related sessions. Last year, 20% of attendees expressed interest in BI; this year, that number is 31%.

9:16 AM PST
Ron VanZanten, Directing Officer of Business Intelligence for Premier Bank Card, just joined Tom on stage. He works with over 25 TB of data and has used BI to leverage the data that they collect. His 3,200 employees use this information to perform their job. VanZanten wanted a flexible BI stack that would grow with the company. He’s successfully tested Madison to improve performance & scale out his environment. This is very encouraging news for any large data warehouse environment.

9:23 AM PST
Tom recognizes that data is available in a wide variety of mediums and that the numbers of sources will only continue to increase. He also introduces a new term: “spreadmart” - spreadsheets that are used as data marts.

9:30 AM PST
Amir Netz just takes the stage to give everyone a demo of the self-service BI offerings in SQL Server 2008 R2. Features include PowerPivot and SharePoint services. Amir shows us an Excel spreadsheet PowerPivot table with 100m rows. It’s amazingly fast. While I shudder to think of end users actually asking for 100m rows in Excel (and trust me, I’m sure it’ll happen), this is going to be a great breakthrough for our BI admins and our power IW users. It’s a time saver and gives greater visibility into data. While I realize the self-service BI offerings are still young, I’m excited for the future of BI. I can’t wait to implement what I’ve seen in our reporting environments.

11:55 AM PST
Tom Casey returns to center stage. Tom announces a contest to win an X-Box 360. To enter, follow @powerpivot & retweet: “Want to learn more, go to http://bit.ly/4n5vpd & sign up for the November CTP #powerpivot.” Tom concludes the keynote with his call to arms to increase the number of business users who have access to the BI data that they need to do their job effectively (currently 20%).

So there you have it, today’s keynote. Check back tomorrow for details on Thursday’s keynote; it looks to be the best yet.

8:00 AM PST
Michelle gets situated at the blog table, running late and doesn’t even have time for coffee. Ack!

8:05 AM PST
Computer finishes booting up, Twitter loaded. Interesting statistics: 31% of folks expressed interest in Business Intelligence, up significantly from previous years. More than 400 Microsoft product developers and managers, and 98 SQL Server MVP’s, are in attendance.

8:06 AM PST
Wayne Snyder talks about Virtual Chapters. Did you know there are 5 sub-chapters in the Business Intelligence virtual chapter?

8:07 AM PST
Wayne reviews 24HoursOfPASS. Interesting statistics: 50,123 registrations, and 3,524 folks in 70+ countries.

8:09 AM PST
Wayne announces the return of SQL Server Standard. The first article was posted this past week. Content is free but only available online. Seasoned SQL Server authors can earn $500 per article. Interested parties should contact Grant Fritchey (@GFritchey).

8:18 AM PST
Closing words by Wayne: “Remember, no one should be a stranger at Summit.” Make sure to say “hi” to at least one person you have never met before. The more people feel welcome, the more successful the event!

8:24 AM PST
I receive my first-ever press announcement and immediately start leaking sharing the news. First up: SQL Server 2008 R2 CTP scheduled for November release! Also, looks like Madison is being rebranded as “SQL Server 2008 R2 Parallel Data Warehouse.” I think I prefer the simplicity of “Madison.

8:26 AM PST
Microsoft announces a new TPC-E record of 2,012 tpsE. This is a platform-independent world record for *any* OLTP system. Oracle, eat your heart out.

8:30 AM PST
More details on the TPC-E benchmark world record: Unisys ES7000 model 7600R Enterprise Server on 96-core Xeon platform (first server with >64 cores). The same Unisys server also reached 102,778 QphH (queries per hour), a data warehousing performance improvement of 70%.

8:39 AM PST
Bob tells us that the IO strain on virtualized machines is there but negated by Hyper-V. We’re given a demo of live migration: it appears that migration is seamless and that transactions are unaffected by the process. Very cool.

8:42 AM PST
Bob talks about the future of SQL Server as more companies move toward the cloud.

8:50 AM PST
Bob concludes his speech by discussing the future of the data professional, and how the DBA role will not become obsolete but merely transformed.

8:52 AM PST
Ted Kummert, Senior VP of SQL Server, takes the stage.

8:56 AM PST
Ted’s Top 5 Reasons to be at PASS Summit:

#1 You are part of the world’s largest gathering of SQL Server professionals
#2 You can take your questions directly to the “source”
#3 We’ve got Wayne and Rushabh
#4 You can work hard and PLAY hard
#5 You will build skills & knowledge on the #1 database in the world

9:16 AM PST
Dan Jones, Principle Group Program Manager for SQL Server Manageability, takes the stage.

9:20 AM PST
Dan gives us a demo of SQL Server 2008 R2, including Utility Control Points.

9:30 AM PST
I need to leave to check in for my 10:30 am presentation. Bummed I’m going to miss the last of the keynote.

First, and long overdue, I want to give a brief recap of the East Iowa SQL Saturday. On October 17th, our local PASS chapter, 380PASS, sponsored our first ever SQL Saturday at the University of Iowa in Iowa City. By all accounts, the event was a great success! We had 90 attendees, 11 speakers, and 21 sessions. We received numerous compliments on the quality of the speakers, the niceness of the facilities, and the abundance of food. Not too shabby for our first time hosting the event, if I do say so myself.

I’d like to thank all of our wonderful speakers, especially those who traveled from out of town and out of state, for making this event such a success. I’d also like to thank our amazing volunteers for helping put this all together. Lastly, but certainly not least, I’d like to thank our generous sponsors, without whom this event would not be possible. Because this event went so smoothly and was so well received in the community, we’ve already started planning our next big SQL event! In the meantime, don’t forget to check out our monthly 380PASS meetings to tide you over.

I’d also like to take a moment to discuss the PASS Summit. Unless you’re a DBA who’s been living under a rock, you’ve probably heard of the PASS Summit. If you *have* been living under a rock — and hey, I’m not poking fun, I used to live under a rock, too! — then what you need to know is that the Summit is the largest SQL Server conference in the world. It’s a gathering of Microsoft developers and SQL Server gurus; the rest of us show up to try to absorb as much from them as possible. Since I’ve recently moved to the Business Intelligence team, I’m extremely excited to delve into the amazing amount of BI content offered.

I’m also deeply honored to be presenting at the Summit this year on some of the performance tuning techniques I’ve used with great success in my production environments. The session is titled, Super Bowl, Super Load - A Look At Performance Tuning for VLDB’s. If you’re interested in performance tuning or VLDB (very large database) topics, consider stopping by to catch my session. From what I can tell, I’ll be presenting on Tuesday from 10:15am - 11:30am in room(s?) 602-604.

If you read my blog, or if we’ve ever interacted in any way on the internet — Twitter, LinkedIn, e-mails, blog comments, etc. — please stop by and say “hi”! Aside from all of the awesome SQL Server content, I’m really looking forward to meeting as many new folks as possible.

And on that note…

Getting to meet all of the amazing SQL Server professionals out there who have inspired and encouraged me in so many ways #PASSAwesomeness

Easy Partition Staging Tables

Switching partitions (or more specifically, hobts) in and out of a partitioned table requires the use of a staging table. The staging table has very specific requirements: it must be completely identical to the partitioned table, including indexing structures, and it must have a check constraint that limits data to the partitioning range. Thanks to my co-worker Jeff, I’ve recently started using the SQL Server Partition Management tool on CodePlex. I haven’t used the automatic partition switching feature — frankly, using any sort of data modification tool in a production environment makes me nervous — but I’ve been using the scripting option to create staging tables in my development environment, which I then copy to production for use. It’s nothing you can’t do yourself, but it does make the whole process easy and painless, plus it saves you from annoying typos. But be careful when using this tool to just create the table and check constraints automatically, because you may need to…

Add Check Constraints After Loading Data

Most of the time, I add the check constraint when I create the staging table, then I load data and perform the partition switch. However, for some reason, I was receiving the following error:

.Net SqlClient Data Provider: Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘myStagingTable’ allows values that are not allowed by check constraints or partition function on target table ‘myDestinationTable’.

This drove me crazy. I confirmed my check constraints were correct, that I had the correct partition number, and that all schema and indexes matched identically. After about 30 minutes of this, I decided to drop and recreate the constraint. For some reason, it fixed the issue. Repeat tests produced the same results: the check constraint needed to be added *after* data was loaded. This error is occurring on a SQL Server 2008 SP1 box; to be honest, I’m not sure what’s causing the error, so if you know, please leave me a comment. But I figured I’d share so that anyone else running into this issue can hopefully save some time and headache.

Replicating Into Partitioned and Non-Partitioned Tables

Recently, we needed to replicate a non-partitioned table to two different destinations. We wanted to use partitioning for Server A, which has 2008 Enterprise; Server B, which is on 2005 Standard, could not take advantage of partitioning. The solution was really easy: create a pre-snapshot and post-snapshot script for the publication, then modify to handle each server group differently. Using pseudo-code, it looked something like this:

/* Identify which servers get the partitioned version */
IF @@SERVERNAME In ('yourServerNameList') 
BEGIN
 
    /* Create your partitioning scheme if necessary */
    IF Not Exists(SELECT * FROM sys.partition_schemes WHERE name = 'InsertPartitionScheme')
        CREATE PARTITION SCHEME InsertPartitionScheme 
            AS PARTITION InsertPartitionFunction ALL TO ([PRIMARY]);    
 
    /* Create your partitioning function if necessary */
    IF Not Exists(SELECT * FROM sys.partition_functions WHERE name = 'InsertPartitionFunction')
        CREATE PARTITION FUNCTION InsertPartitionFunction (SMALLDATETIME) 
            AS RANGE RIGHT FOR VALUES ('insertValues');    
 
    /* Create a partitioned version of your table */
    CREATE TABLE [dbo].[yourTableName] (
	    [yourTableSchema]
    ) ON InsertPartitionScheme([partitioningKey]);
 
END
ELSE
BEGIN
 
    /* Create a non-partitioned version of your table */
    CREATE TABLE [dbo].[yourTableName] (
	    [yourTableSchema]
    ) ON [PRIMARY];
 
END

You could also use an edition check instead of a server name check, if you prefer. The post-snapshot script basically looked the same, except you create partitioned indexes instead.

Compress Old Partitions

Did you know you can set different compression levels for individual partitions? It’s true! I’ve just completed doing this on our largest partitioned table. Here’s how:

/* Apply compression to your partitioned table */
ALTER TABLE dbo.yourTableName
Rebuild Partition = All
WITH 
(
      Data_Compression = Page ON Partitions(1 TO 9)
    , Data_Compression = ROW  ON Partitions(10 TO 11) 
    , Data_Compression = NONE ON Partitions(12)
);
 
/* Apply compression to your partitioned index */
ALTER INDEX YourPartitionedIndex
    ON dbo.yourTableName
    Rebuild Partition = All
    WITH 
    (
      Data_Compression = Page ON Partitions(1 TO 9)
    , Data_Compression = ROW  ON Partitions(10 TO 11) 
    , Data_Compression = NONE ON Partitions(12)
    );
 
/* Apply compression to your unpartitioned index */
ALTER INDEX YourUnpartitionedIndex
    ON dbo.yourTableName
    Rebuild WITH (Data_Compression = ROW);

A couple of things to note. In all of our proof-of-concept testing, we found that compression significantly reduced query execution time, reads (IO), and storage. However, CPU was also increased significantly. The results were more dramatic, both good and bad, with page compression versus row compression. Still, for our older partitions, which aren’t queried regularly, it made sense to turn on page compression. The newer partitions receive row compression, and the newest partitions, which are still queried very regularly by routine processes, were left completely uncompressed. This seems to strike a nice balance in our environment, but of course, results will vary depending on how you use your data.

Something to be aware of is that compressing your clustered index does *not* compress your non-clustered indexes; those are separate operations. Lastly, for those who are curious, it took us about 1 minute to apply row compression and about 7 minutes to apply page compression to partitions averaging 30 million rows.

Looking for more information on table partitioning? Check out my overview of partitioning, my example code, and my article on indexing on partitioned tables.

East Iowa SQL Saturday:

I’m the event organizer for East Iowa SQL Saturday, which is eating up a lot of my free time. If you haven’t yet heard about our SQL Saturday event, let me give you a brief overview. It’s a FREE, one-day training event geared toward SQL Server professionals and anyone who wants to learn more about SQL Server. We have 22 sessions planned covering a variety of topics, from Business Intelligence to Disaster Recovery to SQL Server 2008 topics. And if you’re a .NET developer, we also have some .NET-related presentations, including PowerShell and MVC.

We’re very fortunate to have snagged an excellent set of speakers. Jessica Moss, Louis Davidson, Timothy Ford, Jason Strate, and Alex Kuznetsov are just a few of the great speakers we have lined up.

There’s only a handful of spots left, so if you’re interested in attending, you should register soon. To find out more details about the speakers and sessions, or to register, be sure to check out our website at http://sqlsaturday.380pass.org.

The Other Reason:

Yes, that’s right, I’m with child. Expecting. Eating for two. Bun in the oven. In the family way. You get the idea.

So when I’m not at work, planning SQL Saturday, or playing Civilization Revolution, I’m sleeping. For those who remotely care, I’m due around Super Bowl time in February 2010.

2010: The Year I Make Contact

Rest assured, this blog isn’t going away. And hopefully once I get through SQL Saturday and then PASS Summit, I’ll have more free time again.

To do this, I first created a couple of tables. One table is used to actually store the monitored values. The second table is used for configuration; you insert only the counters you want to monitor.

/* Create the table to store our logged perfmon counters */
CREATE TABLE dbo.dba_perfCounterMonitor
(
      capture_id    INT IDENTITY(1,1)   Not Null
    , captureDate   SMALLDATETIME       Not Null
    , objectName    NVARCHAR(128)       Not Null
    , counterName   NVARCHAR(128)       Not Null
    , instanceName  NVARCHAR(128)       Not Null
    , VALUE         FLOAT(6)            Not Null
    , valueType     NVARCHAR(10)        Not Null
 
    CONSTRAINT PK_dba_perfCounterMonitor
        PRIMARY KEY CLUSTERED(capture_id)
);
 
/* Create the table that controls which counters we're going to monitor */
CREATE TABLE dbo.dba_perfCounterMonitorConfig
(
      objectName    NVARCHAR(128)   Not Null
    , counterName   NVARCHAR(128)   Not Null
    , instanceName  NVARCHAR(128)   Null
);

If you leave the instanceName NULL in the config table, it’ll monitor all instances. Now we’re going to insert some sample performance counters into the config table. The counters you’re interested in can, and likely will, vary.

/* Insert some perfmon counters to be monitored */
INSERT INTO dbo.dba_perfCounterMonitorConfig
SELECT 'SQLServer:Buffer Manager', 'Page Life Expectancy', Null UNION All
SELECT 'SQLServer:Locks', 'Lock Requests/sec', Null UNION All
SELECT 'SQLServer:Locks', 'Lock Waits/sec', Null UNION All
SELECT 'SQLServer:Locks', 'Lock Wait Time (ms)', Null UNION All
SELECT 'SQLServer:Buffer Manager', 'Page reads/sec', Null UNION All
SELECT 'SQLServer:Buffer Manager', 'Page writes/sec', Null UNION All
SELECT 'SQLServer:Buffer Manager', 'Buffer cache hit ratio', Null UNION All
SELECT 'SQLServer:Databases', 'Transactions/sec', 'AdventureWorks' UNION All
SELECT 'SQLServer:General Statistics', 'Processes blocked', Null;

Now let’s create our proc. This proc will run for a specified time period and will *average* the counters over that time. I personally take snapshots every 15 seconds for 4 minutes; I have a scheduled task that runs this every 5 minutes. It’s not perfect, but it gives me a good idea of what’s happening on the server.

CREATE PROCEDURE dbo.dba_perfCounterMonitor_sp
 
        /* Declare Parameters */
          @samplePeriod    INT      =  240  /* how long to sample, in seconds */
        , @sampleRate      CHAR(8)  =  '00:00:15'  /* how frequently to sample, in seconds */
        , @displayResults  BIT      =  0  /* display the results when done */
AS
/*********************************************************************************
    Name:       dba_perfCounterMonitor_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Monitors performance counters.  Uses the dba_perfCounterMonitorConfig
                table to manage which perf counters to monitor.  
 
                @samplePeriod - specifies how long the process will try to monitor
                                performance counters; in seconds.
 
                @sampleRate - how long inbetween samples; in seconds.
 
                The average values over sample period is then logged to the
                dba_perfCounterMonitor table.
 
    Notes:      There are 3 basic types of performance counter calculations:
 
                Value/Base: these calculations require 2 counters. The value 
                            counter (cntr_type = 537003264) has to be divided 
                            by the base counter (cntr_type = 1073939712).
 
                Per Second: these counters are store cumulative values; the
                            value must be compared at 2 different times to
                            calculate the difference (cntr_type = 537003264).
 
                Point In Time:  these counters show what the value of the
                                counter is at the current point-in-time 
                                (cntr_type = 65792).  No calculation is 
                                necessary to derive the value.
 
    Called by:  DBA
 
    Date        User    Description
    ----------------------------------------------------------------------------
    2009-09-04  MFU     Initial Release
*********************************************************************************
    Exec dbo.dba_perfCounterMonitor_sp
          @samplePeriod     = 60
        , @sampleRate       = '00:00:01'
        , @displayResults   = 1;
*********************************************************************************/
 
SET NOCOUNT ON;
SET XACT_Abort ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET Numeric_RoundAbort OFF;
 
BEGIN
 
    /* Declare Variables */
    DECLARE @startTime DATETIME
        , @endTime DATETIME
        , @iteration INT;
 
    SELECT @startTime = GETDATE()
        , @iteration = 1;
 
    DECLARE @samples TABLE
    (
          iteration     INT             Not Null
        , objectName    NVARCHAR(128)   Not Null
        , counterName   NVARCHAR(128)   Not Null
        , instanceName  NVARCHAR(128)   Not Null
        , cntr_value    FLOAT           Not Null
        , base_value    FLOAT           Null
        , cntr_type     BIGINT          Not Null
    );
 
    BEGIN Try
 
        /* Start a new transaction */
        BEGIN TRANSACTION;
 
        /* Grab all of our counters */
        INSERT INTO @samples
        SELECT @iteration
            , RTRIM(dopc.OBJECT_NAME)
            , RTRIM(dopc.counter_name)
            , RTRIM(dopc.instance_name)
            , RTRIM(dopc.cntr_value)
            , (SELECT cntr_value FROM sys.dm_os_performance_counters AS dopc1
                WHERE dopc1.OBJECT_NAME = pcml.objectName
                And dopc1.counter_name = pcml.counterName + ' base'
                And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name))
            , dopc.cntr_type
        FROM sys.dm_os_performance_counters AS dopc
        Join dbo.dba_perfCounterMonitorConfig AS pcml
            ON dopc.OBJECT_NAME = pcml.objectName
                And dopc.counter_name = pcml.counterName
                And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name);
 
        /* During our sample period, grab our counter values and store the results */
        WHILE GETDATE() < DATEADD(SECOND, @samplePeriod, @startTime)
        BEGIN
 
            SET @iteration = @iteration + 1;
 
            INSERT INTO @samples
            SELECT @iteration
                , RTRIM(dopc.OBJECT_NAME)
                , RTRIM(dopc.counter_name)
                , RTRIM(dopc.instance_name)
                , dopc.cntr_value
                , (SELECT cntr_value FROM sys.dm_os_performance_counters AS dopc1
                    WHERE dopc1.OBJECT_NAME = pcml.objectName
                    And dopc1.counter_name = pcml.counterName + ' base'
                    And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name))
                , dopc.cntr_type
            FROM sys.dm_os_performance_counters AS dopc
            Join dbo.dba_perfCounterMonitorConfig AS pcml
                ON dopc.OBJECT_NAME = pcml.objectName
                    And dopc.counter_name = pcml.counterName
                    And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name);
 
            /* Wait for a small delay */
            WAITFOR Delay @sampleRate;
 
        END;
 
        /* Grab our end time for calculations */
        SET @endTime = GETDATE();
 
        /* Store the average of our point-in-time counters */
        INSERT INTO dbo.dba_perfCounterMonitor 
        (
			  captureDate
			, objectName
			, counterName
			, instanceName
			, VALUE
			, valueType
		) 
		SELECT @startTime
		    , objectName
		    , counterName
		    , instanceName
		    , AVG(cntr_value)
		    , 'value'
		FROM @samples
		WHERE cntr_type = 65792
		GROUP BY objectName
		    , counterName
		    , instanceName;
 
        /* Store the average of the value vs the base for cntr_type = 537003264 */
        INSERT INTO dbo.dba_perfCounterMonitor 
        (
			  captureDate
			, objectName
			, counterName
			, instanceName
			, VALUE
			, valueType
		) 
		SELECT @startTime
		    , objectName
		    , counterName
		    , instanceName
		    , AVG(cntr_value)/AVG(IsNull(base_value, 1))
		    , 'percent'
		FROM @samples
		WHERE cntr_type = 537003264
		GROUP BY objectName
		    , counterName
		    , instanceName;
 
        /* Compare the first and last values for our cumulative, per-second counters */
        INSERT INTO dbo.dba_perfCounterMonitor 
        (
			  captureDate
			, objectName
			, counterName
			, instanceName
			, VALUE
			, valueType
		) 
		SELECT @startTime
		    , objectName
		    , counterName
		    , instanceName
		    , (MAX(cntr_value) - MIN(cntr_value)) / DATEDIFF(SECOND, @startTime, @endTime)
		    , 'value'
		FROM @samples
		WHERE cntr_type = 272696576
        GROUP BY objectName
		    , counterName
		    , instanceName;
 
        /* Should we display the results of our most recent execution?  */
        IF @displayResults = 1
            SELECT captureDate
                , objectName
                , counterName
                , instanceName
                , VALUE
                , valueType
            FROM dbo.dba_perfCounterMonitor WITH (NoLock)
            WHERE captureDate = CAST(@startTime AS SMALLDATETIME)
            ORDER BY objectName
                , counterName
                , instanceName;
 
        /* If you have an open transaction, commit it */
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;
 
    END Try
    BEGIN Catch
 
        /* Whoops, there was an error... rollback! */
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
 
        /* Return an error message and log it */
        EXECUTE dbo.dba_logError_sp;
 
    END Catch;
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go

Like I said, it’s not perfect, but it gets the job done.

Getting an error about dba_logError_sp? Take a look at my error handling proc.

The fn_physLocCracker function can be called in the following way:

SELECT TOP 100 plc.*, soh.SalesOrderID
FROM Sales.SalesOrderHeader AS soh
Cross Apply sys.fn_physLocCracker (%%physloc%%) AS plc;

Results (just a sample):

file_id     page_id     slot_id     SalesOrderID
----------- ----------- ----------- ------------
1           14032       0           43659
1           14032       1           43660
1           14032       2           43661
1           14032       3           43662
1           14032       4           43663

If you look at the sp_helptext for sys.fn_physLocCracker, %%physloc%% is apparently a virtual column that contains information on where the record is stored. In fact, you can even append %%physloc%% to your column list if you want to see how the information is stored. But for our purposes, we now have a file number, page number, and slot number. What do we do with it?

Well, you can use the investigation proc I wrote to retrieve the actual data page:

EXECUTE dba_viewPageData_sp 
      @databaseName = 'AdventureWorks'
    , @fileNumber = 1
    , @pageNumber = 14032;

Results (just a sample):

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
 
SalesOrderID = 43659                 
 
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
 
RevisionNumber = 1                   
 
Slot 0 Column 3 Offset 0x9 Length 8 Length (physical) 8
 
OrderDate = 2001-07-01 00:00:00.000  
 
Slot 0 Column 4 Offset 0x11 Length 8 Length (physical) 8
 
DueDate = 2001-07-13 00:00:00.000

Neat, huh? So why would you use it to look up the data page and file number when you can just pass the table name and index name to my proc and retrieve data pages? Well, my investigation proc will retrieve data pages for any index type — the fn_physLocCracker function will only retrieve data for the clustered index — but it will not retrieve the data page for a specific record. So just something to be aware of.

That’s all for now. Back to the #24HoursOfPASS!

USE AdventureWorks;
Go
 
CREATE PROCEDURE dbo.LastOrderGet_sp
AS
SET NOCOUNT ON;
BEGIN
 
    SELECT MAX(SalesOrderID) AS 'LastSalesOrderID'
    FROM AdventureWorks.Sales.SalesOrderHeader WITH (NoLock)
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go
 
CREATE PROCEDURE dbo.ProcessLastOrder_sp
    @LastOrderID INT
AS
SET NOCOUNT ON;
BEGIN
 
    SELECT SalesOrderDetailID
        , ProductID
        , OrderQty
        , LineTotal
    FROM AdventureWorks.Sales.SalesOrderDetail WITH (NoLock)
    WHERE SalesOrderID = @LastOrderID;
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go
 
CREATE TABLE dbo.testStage
(
      SalesOrderDetailID    INT
    , ProductId             INT
    , OrderQty              SMALLINT
    , LineTotal             NUMERIC
);
Go

Now for the fun stuff!

(Please note, I’m assuming some basic understanding of SSIS, so I’m skipping the “how to create a project”, etc. stuff and just going to the pertinent parts).

Inside BIDS (Business Intelligence Development Studio), create a new SSIS project and call it what you will. If your Variable window is not already open, open it now by going to View –> Other Windows –> Variables.

Open Variables Window

Now let’s create a variable. To do this, click on the little icon in the upper left-hand corner of the Variables window. Name the variable LastSalesOrderID.

Create a variable

After you create the variable, you should now see it in the Variables window. Make sure the scope of the variable is the name of your project, which is “Blog” in my case (for obvious reasons); this means the variable is defined at the package scope. Once you’ve confirmed that the variable exists, create an Execute SQL task.

(Variables in SSIS, like in other programming languages, can have different scopes. For instance, a package scope means the variable can be accessed anywhere within the package, but a variable with a Data Flow scope can only be accessed within the specified Data Flow task.)

Create Execute SQL Task

Double-click on your Execute SQL Task and configure with the following values:

• Set “Result Set” to Single Row.
• Set your Connection to your appropriate data source.
• Set your SQL Statement to: Execute AdventureWorks.dbo.LastOrderGet_sp;

Set up your Execute SQL Task

Now click on “Result Set” and click on “Add.” You’ll want to put the name of the column that’s returned by the proc in the “Result Name” column; in our case, that’ll be LastSalesOrderID. Click on the Variable Name column and scroll down until you find the appropriate one (User::LastSalesOrderID).

Mapping the results to a variable

Go ahead and add a Data Flow task to the designer surface. We don’t need to use a Data Flow task here — for example, we could use another Execute SQL task instead — but this will help demonstrate one way to use variables.

Add Data Flow Task

Double-click on the Data Flow task and add an OLE DB Source, then double-click on it to open up the properties. Enter the following text in the “SQL Command text” window:
Execute AdventureWorks.dbo.ProcessLastOrder_sp ?
The question mark (?) tells SSIS to expect a parameter.

Edit OLE DB Source

Now click on the Parameters button on the left. This is where we map our variable to our parameter. For the “Parameters” value, enter @LastOrderID (the parameter the stored procedure is expecting). In the “Variables” column, click on the drop-down and navigate to the User::LastSalesOrderID variable.

Map Variables to Parameters

Finally, set up an OLE DB Destination, and configure the OLE DB Source to load into the testStage table.

Configure OLE DB Destination

At this point, you should be able to successfully execute your package. Upon successful execution, the testStage table will return the following results:

SELECT * FROM testStage;
 
SalesOrderDetailID ProductId   OrderQty LineTotal
------------------ ----------- -------- ------------------
121315             878         1        21
121316             879         1        159
121317             712         1        8

That’s all for now. Hopefully this gives you an idea of how easy and useful it is to work with variables in SSIS.