East Iowa SQL User Group meeting

This may be one of the nuttiest things I’ve ever done, at least from a technical side.Yesterday I drove to Cedar Rapids to attend the monthly meeting of the I380 Corridor (East Iowa) SQL User Group. That’s a 9 hour round trip drive for a 2 hour meeting, not a very efficient use of my time. But I was glad I did.

The East Iowa group is headed by Michelle Ufford, writer of the SQL Fool blog and Ed Leighton-Dick. Last night’s meeting included a tip of the month by Michelle on using Adam Machanic’s latest version of his who is active script. This was followed by a Live Meeting with Jessica Moss who gave an introduction to data warehousing. That’s what I came for. Ed taped the meeting and hopefully it will come out clean enough so they can post it. I’d love to see it again.

Jessica discussed just what data warehousing is. She went into the design and life cycle of a dw, the definitions of fact and dimension tables, the types of dimensions, the differences between a star and snowflake schema, and how SSIS, SSAS, and SSRS fit into all this. She also discussed the different methodologies, Kimball and Inmon. Finally she gave a demo of a dw, from ETL to reporting, using the AdventureWorksDW2008 database. There was only one question, from Michelle, about using procedures to load data as opposed to a Slowly Changing control in SSIS. Jessica recommended using the SSIS approach.

Jessica’s presentation was exactly what I was looking for, a beginning point for how to build a data warehouse. There’s a lot of information and discussions out there but most of it assumes some knowledge already. And classes usually focus on the tools, not the methods. I need to learn both if I’m to create a good design.

Everyone at the user group was friendly. It was a small gathering, maybe 15 people total. The only difference between them and the Chicago group is they cut their pizza in wedges rather than squares.  

There was one piece of bad news, however. The Iowa group is sponsoring a SQL Saturday on October 17th in Iowa City. But they are far behind what they need as far as registered attendees. They need to hit a certain level for funding from their sponsors to kick in. I think they are running into an issue where there’s another SQL Saturday scheduled the same day in Orlando and others in October. That’s a shame, because I really want to go to this one. Jessica is supposed to come and expand on last night’s presentation. They’ll make the decision Friday if they need to cancel.    

Replaying Traces – What I’ve Learned

So I’ve been running these replays for a while now, with mixed results. There are a number of  lessons I’ve learned along the way.

• Check what’s running inside the workload you’re replaying. There may be something that will affect the replay. In my case there was a collection trace that was started by the third party software I use for monitoring the production servers for performance. Since this trace had no corresponding stop, it kept running after the rest of my workload was completed, making me think there was a different issue. The only thing I don’t understand is why it stopped on it’s own in half the replays.
• After you load the workload, you can apply a filter and reload the workload. This is how I eliminated any call for sp_trace_setstatus.
• Restore the master database to eliminate any replay errors due to mismatched database id’s. This comes directly from BOL. I was ignoring it because at first I wasn’t seeing any errors. But later, especially after I started on the virtual servers, I was seeing more and more errors where SQL wasn’t running against the right database. So when I started the last round of replays I restored the master db first.

I’m in the process of replaying all scenarios again so the data is consistent. So far I’ve completed the 4 physical tests. Coming next are the 3 32-bit virtual, 2 64-bit virtual, and 1 final with VMWare’s 8 CPU server.

24 Hours of PASS

If you haven't already heard, PASS is hosting 24 hours of PASS on September 2nd. These are a series of free, one hour long webcasts on a variety of SQL 2008 subjects. You can check out the schedule on the PASS website. The first webcast is actually at 7 PM on September 1st Central Time. It's not too late to register.

First look at SQL 2008 R2

I’ve started to install the CTP of SQL Server 2008 R2. The first change I ran across was this screen during installation. I don’t have a copy of SharePoint Fourteen(?) so I chose the more familiar SQL Server Feature Installation. I’m installing this on a virtual server so I can always change options later.

Replaying traces, Part 5

So I’m now testing my workload on a virtual 64 bit server, again hosted by VMWare, again with only 4 CPUs. And again I find more issues.

I ran this replay 3 times so far. All three times the replay appeared to have hung at the 99% mark, similar to the 8 CPU physical test. The only difference was that, for the final test, I didn’t save the results to disk. I did this because on the second test there appeared to be SQL activity that was suspended by the TRACEWRITE wait type. The drive it was writing to was the only virtual drive; all databases are on SAN storage.

While all three hung at the 99% mark after about 3 hours, each test had different results. The first test was showing transactions against the database throughout the trace (the black line. The blue line is % Processor Time, the yellow line is % Privilege Time, the red line is transactions/sec against tempdb). The Processor time showed activity from the Profiler after the 99% mark .
The second trace showed no transactions after the 99% mark. This is the one that showed the TRACEWRITE wait type. Notice that there are no transactions against the db after the 3 hour mark this time.  
Test 3 shows the same pattern as test 1. There were no wait times for tests 1 and 3.

There is one more issue with these three tests. After stopping the replays, I had to manually kill the Profiler process because profiler became unresponsive. The third test stayed alive and was stopping the process and I let it go. However the next morning I checked and it was still “stopping” after 13 hours. And the Profiler was still responding.

The more I run these replays the more confused I get. I’m going to try one more time. This run will be from a remote machine, I’m only going to capture the counters on the virtual server.

(Mostly) disappointing meeting

Last night I went to the monthly meeting of the Chicago SQL User group, held on the second Thursday of the month. You can check them out and register for meeting notifications here. This is a good resource for anyone wishing to attend. Anyway,there were two topics last night. The first was on SQL and Powershell, the second on clustering with SQL 2008 and Windows Server 2008.

I don’t have any experience with clustering since we don’t use it at work but it’s one of those topics I feel I should know more about. But last night I was really looking forward to the discussion on Powershell. I’m looking for reasons why I should use Powershell over other methods like SMO or WMI to monitor servers I’m responsible for. I wanted to see some amazing Powershell cmdlets in action.

Unfortunately the presenters of the Powershell segment were not prepared very well. It started with having problems connecting a laptop to the projector and went downhill from there. The demos were filled with errors; most flatly didn’t work. The first presenter dwelled too long on the basics of Powershell itself, the second barely did more than read from the slide deck. Their presentation ate into the second segment on clustering. I wonder if their book was written better.

The second presenter did a much better job. He knows his topic extremely well, being a Microsoft MVP on clustering, and you can tell he is passionate about the subject. And he’s an effective speaker. But since he didn’t have much time he had to cut his demos short.

Don’t get me wrong, I appreciate everyone who presents at these meetings. It’s not easy getting up in front of a room of 80 strangers and talking about a deep subject in a short period of time. I just wish they could have presented their material in a more effective manner.           

SQL Server 2008 R2 CTP is now available

Earlier this week Microsoft announced that the first CTP for SQL Server 2008 R2 is available for download. You can get a copy through TechNet or MSDN, or on the site page here.

According to Mary Jo Foley, R2 is tied tightly in with the upcoming Office 2010 release. Both can be expected in the first half of 2010, around May or June.Personally I’m hoping for April, before my TechNet subscription runs out.

Technorati Tags: SQL 2008 R2,CTP,SQL