Making Presentations With My iPad

I recently gave a couple of talks at devLINK 2010 at the David Lipscomb University campus in Nashville.

One of the talks was technical in nature, the other was a professional development session. These are typical of the presentations I give for conferences, user groups, and clients.  devLINK 2010 made a perfect time to try out my iPad for presentations.

But first a bit of background.

You Need An Adaptor

The iPad is sleek, it’s thin, and it’s attractive. And it doesn’t have room for any external connectors like SVGA outputs or even USB devices. Everything must run through the sole proprietary connection port on the bottom. Fortunately, you can buy a dongle that allows you to connect the iPad to a standard SVGA device. Unfortunately, it’s not the same dongle that the MacBook Pro uses so it’s an extra “thing” to carry around. Every time I pack up my stuff for a presentation I can’t help but mentally singing “I’ve got dongles that dingle dangle dingle.” to the tune of “I’ve got spurs that jingle jangle jingle.” I guess that’s just me though.

Simply connecting the dongle to the iPad and a projector does not automatically send video to the projector. That surprised me. Instead it’s software driven, but not at the iPad O/S level. You won’t find a configuration switch in Settings to send all video output to the SVGA device. Instead it’s driven by the presentation software. That’s disappointing. That means you cannot show apps on the big screen that are not specifically designed to work with a projector.

The Presentation Software

To make a presentation, I bought Keynote. Keynote is software for the iPad that’s similar to PowerPoint. It’s surprisingly rich and powerful. And it’s completely amazing for  a $10 piece of software. It has cool animations, charts, tables, and all of the other things that you’ve come to expect from PowerPoint. You can create your own presentations in Keynote on the iPad or you can edit and show PowerPoint presentations that you’ve created on your laptop.

As you proof your presentation, you see the output on the iPad’s screen. A swipe of the finger from left to right will advance to the next slide. Reversing the direction of the swipe with go back on slide in the deck. One nice presentation feature is that when you put your finger on the iPad and leave it for a second, a red dot appears on the screen and you can use that instead of a laser pointer. Nice.

Unfortunately there are a couple areas where the Keynote development team seems to have been short sighted. First, when you go into presentation mode while an external projector is connected , the iPad screen goes black and only displays buttons that allows you to go forward or back. It doesn’t show the slide that’s being displayed on the projector. That can be bad since you, as the speaker, will have to turn your head from the audience to see what they are seeing on the screen. The iPad doesn’t act like a monitor for you.

Second, if any other application on the iPad takes focus during the presentation, the output to the project is cut off. This happened to me during devLINK. A calendar reminder popped up, killing the SVGA output. Fortunately as soon as I closed the reminder, the presentation was immediately displayed again. It would be nice to have a universal setting to suppress every thing else while in presentation mode.

Putting It Into Action

So, how did my experiment at devLINK 2010 go?

My Technical Presentation

When I give technical presentations, I try to make them engaging and interactive. And the best way that I’ve found to do that it is to incorporate live demonstrations in the presentations. I strive to make each presentation approximately 50% live demos. I bounce between the presentation slide deck and the SQL Server Management Studio.

That’s just not possible with iPad. Obviously SQL Server Management Studio won’t run on a device made by Apple. I thought about connecting to a remote server via an RDP client called WinAdmin that I have for the iPad and doing my demonstrations on that server. But WinAdmin doesn’t support the SVGA output so I couldn’t get the graphics to the projector.

Unless I revamp my presentations and make the demonstrations canned images embedded into the slide deck, using the iPad for technical demonstrations won’t work for me. For devLINK 2010 used my MacBook Pro.

My Non-Technical Presentation

My non-technical “Conducting Effective Meetings” session at devLINK was in the Alumni Auditorium with a seating capacity of a couple of thousand and an on-stage projection screen that was at least 20 feet tall. The session was recorded by devLINK, that’s why I was in the room for the big boys.

The A/V guy came to help me get set up and miked right before the session. I could tell he was a bit hesitant when he saw my iPad. He gave me the “You’re on your own with this one.” glance. Fortunately, I plugged it up and it worked without any problems. The graphics, even on the 20 foot tall screen were excellent. And apart from the small interruption due to the appointment reminder, it worked flawlessly.

In fact, it worked well enough that I’m planning to use it for the SQLSaturday #51 opening keynote address in Nashville on Saturday.

Related Posts

How I Use My iPad for Business

“The iPad looks like an iPhone made by Playskool.” That’s how one comedian characterized the iPad when Steve Jobs first unveiled the revolutionary device. And his sentiment held a lot of merit. At first glance, the iPad does look lot like the iPhone OS on a giant device that cannot even make a telephone call. What’s so revolutionary about that?

However, over time I warmed to the idea and even convinced myself that this device could and would help my manage my business better. I needed to be more productive and the iPad was just the tool to help me do that. So I bought one about a month ago with very high expectations.

Why I Needed An iPad

For years, I carried a nice moleskine made out of leather. I carried it everywhere. In meetings, I’d make notes. While working, I’d record my hours and accomplishments. At other times, I’d jot down ideas for blog postings or record items that I need to do. In Getting Things Done terms, the moleskine was my ubiquitous collection device. I even had a legend for flagging to-do’s, waiting-for’s, etc.

However, the moleskine was only where I captured incoming information. My daily work was driven by my electronic system. I use Omnifocus to management my projects and to-do’s; I use my calendar to management my schedule; I use EverNote to keep reference materials that I may need later.

So there was an inherent duplication of effort in my system. I’d initially record things in my moleskine and then transfer the information to my electronic system. I’m busy and transferring the information was a chore. So, I’d postpone doing it, neglecting it for a week or two at a time. It wasn’t uncommon for a deadline that I’d captured in my moleskine to have expired before I ever got it into my electronic system where it could be integrated into my workflow.

In short, I was dropping balls occasionally and I didn’t like that.

My Electronic Moleskine

I need a way to easily capture information, obligations, to-do’s, etc, no matter where I am. I want the device to be lightweight, instant on, not very intrusive, and fully integrated into my other electronic systems. The iPad promised all of that plus more. So, I bought one and it’s worked out very well for the most part.

I carry it wherever I carried my moleskin. In meetings, I can easily capture meeting notes directly into EverNote, add to-do items directly into OmniFocus, and add appointments directly into my calendar. I could do all this with my laptop but I’ve found two distinct drawbacks to opening my laptop in a meeting. First, the temptation to mentally check out of the meeting when it drags on and on is too much. I’ve written about that already. Secondly the laptop screen places a subtle barrier between you and the person on the other side of the table. It’s intrusive at times.

I always have the iPad have with me so I can record my hours, jot notes, and calendar appointments as they come up.

The Apps I Use Most

Since my primary objective for getting an iPad was to eliminate redundancy, every piece of productivity/business software that I consider for it must allow me to conveniently sync among my various devices – my iPhone, iPad, and MacBook Pro. I’ve already mentioned several of the applications I use, but it may be worthwhile to put them all in one place.

  • Omnifocus. I use OmniFocus for my GTD implementation. There’s a client for my MacBook Pro, iPad, and iPhone and they all sync over the air using WebDAV. The only real drawback to OmniFocus is views each client as a separate stand-alone product. So buying all three can get expensive.
  • EverNote. I converted to EverNote a few months ago in preparation for buying the iPad, and I’m glad I did. EverNote can capture pretty much anything and index it to make it completely searchable for you later. I use EverNote as my repository for reference materials I may need later.
  • Mail. There’s a built-in mail client for the iPad. I have it configured for IMAP so messages that I read, delete, forward, etc, on my iPad appear that way in my other clients as well.
  • Web. Safari is the browser provided on the iPad. I don’t know if there are others available. The only issue I’ve discovered with Safari is that it doesn’t support Flash.
  • Calendar.  The iPad also comes with a Calendar application. I have mine automatically sync to my Google calendar over the air.
  • Contacts. I use Google to manage my contacts and the built-in Contacts application on the iPad syncs directly to it.
  • WinAdmin. At times I need to remote desktop into a client’s server to take care of an issue. I use WinAdmin for this. It uses the normal Remote Desktop Protocol and works well. There are other apps for this, too.
  • TwitBird Pro. The most popular Twitter application for the iPad appears to be TwitBird Pro. That’s what I use. It’s pretty good, I guess. It has FaceBook integration as well so your tweets can become status updates on FaceBook if you choose.
  • iBooks. I’ve been pleased with the iBooks app on the iPad. The graphics are nice and it’s easy to read.
  • Kindle Reader. I’ve also downloaded the Kindle Reader so I have access to a much larger variety of books from Amazon.
  • KeyNote. Presentations can be made from the iPad using KeyNote. It’s similar in many respects to PowerPoint. Actually I was quite surprised by the $10 app. It’s very, very, good. You can buy a dongle and drive a standard VGA device like a projector with your iPad.

The really nice part about these applications is that none of them require me to physically sync my iPad or iPhone to my MacBook Pro. All of it is done over the air. If I add a contact on the iPhone, it automatically shows up in the other two locations. If I delete an appointment on the iPad, it’s automatically removed from the iPhone and MacBook Pro calendars. All without syncing. That’s a big, big plus for me. I want all of my systems to be up to date all of the time.

No System Is Perfect

There are, of course some things I wish would change about iPad.

  • The Keyboard. The keyboard is, simply put, not a joy. When in landscape mode, it fits my hands well enough but there a reduced set of keys available. The biggest issue is the home key for my right pinky finger. On a traditional, full sized keyboard the home key is the semi-colon; on the iPad, it’s the return key. It doesn’t sound like much on an issue, but it happens more than you’d think. Additionally the reduced set of keys can be an annoyance when remoting into a client server using WinAdmin. I don’t have the control key available to me to use shortcuts.
  • Inconsistent Keyboards. One nice surprise for me was that iPhone apps work on the iPad. That’s really convenient at times. However the iPhone has a different keyboard layout than they iPad. They are both QWERTY, but the backspace key is in the lower right hand corner for the iPhone and in the upper right hand corner for the iPad. So the keyboard layout on my iPad changes based on whether the app is a native iPad app or one designed for the iPhone. That’s bad.
  • Single-tasking. I know studies show that individuals are less effect when they multi-task. They are even less effect when they try to multi-task on a system cannot multi-task. That’s the case with the iPad. It cannot multi-task. Supposedly there’s an update coming that will add that capability.

Parting Thoughts

Overall, the iPad has done exactly what I’d hoped it would do. It’s helped me to streamline the various inputs into my trusted GTD system. I can now enter information directly into the various applications and have it all available to me over the air to my other devices. That’s what I was hoping for.

It’s not perfect though. The keyboard leave something to be desired, however I found that with a little practice I can indeed keep up while taking notes. Also, since there is no multi-tasking, switching between applications (eg from EverNote to my calendar and back) does take longer than necessary. Hopefully that’ll be addressed in a future update of the iOS.

Question:

  • How do you use your iPad?
  • What applications have you found to be useful?

Indentifying Performance Bottlenecks presentation

heartmonitor-2009-11-13c

Thanks to everyone who attended the State of Tennessee’s .NET User Group meeting yesterday. I really enjoyed spending time with you and learning about how you use SQL Server and some of the challenges you face.

As promised, here is a copy of the presentation materials that I used during the session.

Speaking at the .NET User Group meeting

cogs2009-11-10When a SQL Server application is under performing, how can you isolate where the actual problem is? Would more memory help? What about adding additional processors? Or maybe some indexes are missing?

At the Tennessee State Employee’s .NET User Group meeting this Thursday (November 12th, 2009), I will demonstrate how SQL Profiler and Performance Monitor can be used to uncover your SQL Server’s bottleneck.

If you’d like more information about this meeting, just send me an email. I hope to see you there.

 

Running a Server Side Trace

This is part two of three part series on creating, running, and scheduling Server Side Traces in SQL Server. Part I, Scripting Server Side Traces, was posted on August 31, 2009. Part III will be posted soon.

SQL Server Profiler can bring your production server to a screeching halt

In a prior post entitled Scripting Server Side Traces I lauded the laurels of SQL Server Profiler as a wonderful tool to use in development and QA environments to really see what’s happening inside your SQL Server. It’s a great way to shine a spotlight into the nooks and crannies of your database server. But try to use it on a production server and you’re likely to notice some unpleasant performance degradations. My warning to you was to use with caution on production servers. It’s better to use Server Side Traces and I demonstrated how SQL Server Profiler can be used to script out the Server Side Traces for you. If you haven’t already read that blog post, it may be worth while to go back and read it before continuing on with this one.

In this, the second of a three part series on Server Side Traces, we’ll examine the trace script to see what it’s doing. Then I’ll show how you can start and stop Server Side Traces using T-SQL procedures.

Modifying the script

We’ll start with a relatively simple Server Side Trace script that was generated for us by SQL Server Profiler. The script can be seen below. It’s rather long, but we’ll break it down into just a few sections that can be easily digested.

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 08/27/2009  08:59:12 AM         */
/****************************************************/

–- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

–- Please replace the text InsertFileNameHere, with an appropriate
–- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
–- will be appended to the filename automatically. If you are writing from
–- remote server to local drive, please use UNC path and make sure server has
–- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL
if (@rc != 0) goto error

–- Client side File and Table cannot be scripted

–- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on
exec sp_trace_setevent @TraceID, 27, 8, @on
exec sp_trace_setevent @TraceID, 27, 32, @on
exec sp_trace_setevent @TraceID, 27, 56, @on
exec sp_trace_setevent @TraceID, 27, 64, @on
exec sp_trace_setevent @TraceID, 27, 1, @on
exec sp_trace_setevent @TraceID, 27, 9, @on
exec sp_trace_setevent @TraceID, 27, 41, @on
exec sp_trace_setevent @TraceID, 27, 49, @on
exec sp_trace_setevent @TraceID, 27, 57, @on
exec sp_trace_setevent @TraceID, 27, 2, @on
exec sp_trace_setevent @TraceID, 27, 10, @on
exec sp_trace_setevent @TraceID, 27, 26, @on
exec sp_trace_setevent @TraceID, 27, 58, @on
exec sp_trace_setevent @TraceID, 27, 66, @on
exec sp_trace_setevent @TraceID, 27, 3, @on
exec sp_trace_setevent @TraceID, 27, 11, @on
exec sp_trace_setevent @TraceID, 27, 35, @on
exec sp_trace_setevent @TraceID, 27, 51, @on
exec sp_trace_setevent @TraceID, 27, 4, @on
exec sp_trace_setevent @TraceID, 27, 12, @on
exec sp_trace_setevent @TraceID, 27, 52, @on
exec sp_trace_setevent @TraceID, 27, 60, @on
exec sp_trace_setevent @TraceID, 27, 13, @on
exec sp_trace_setevent @TraceID, 27, 6, @on
exec sp_trace_setevent @TraceID, 27, 14, @on
exec sp_trace_setevent @TraceID, 27, 22, @on
exec sp_trace_setevent @TraceID, 189, 7, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 55, @on
exec sp_trace_setevent @TraceID, 189, 8, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 56, @on
exec sp_trace_setevent @TraceID, 189, 64, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 41, @on
exec sp_trace_setevent @TraceID, 189, 49, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 26, @on
exec sp_trace_setevent @TraceID, 189, 58, @on
exec sp_trace_setevent @TraceID, 189, 66, @on
exec sp_trace_setevent @TraceID, 189, 3, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 51, @on
exec sp_trace_setevent @TraceID, 189, 4, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 52, @on
exec sp_trace_setevent @TraceID, 189, 60, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 122, 7, @on
exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 64, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 25, @on
exec sp_trace_setevent @TraceID, 122, 41, @on
exec sp_trace_setevent @TraceID, 122, 49, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 22, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 34, @on
exec sp_trace_setevent @TraceID, 122, 50, @on
exec sp_trace_setevent @TraceID, 122, 66, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 4, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 28, @on
exec sp_trace_setevent @TraceID, 122, 60, @on
exec sp_trace_setevent @TraceID, 122, 5, @on
exec sp_trace_setevent @TraceID, 122, 29, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

–- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

–- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

–- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Defining variables

The script starts off declaring some variables. You’ll want to make sure that the values you configured in SQL Server Profiler were exported correctly. In particular, make sure the @maxfilesize variable is set to the number of megabytes that you’d like for capture. In the example below, I’ve configured the trace to capture 500 MB of data.

– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

Creating the trace

The next part of the script creates the trace using the sp_trace_create stored procedure. Here, as the comments indicate, you’ll want to provide the fully qualified path for the output file that you’ll use to store the captured data. In this example, I’m collecting it in a E:\demo\demotrace2.trc. Notice that the trc extension is automatically added for us; you don’t need to include it. If you do, you’ll end up with a file named demotrace2.trc.trc. That’s redundantly redundant.

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL

The sp_trace_create procedure also accepts some other parameters that are not automatically produced when you export from SQL Server Profiler. For example, you can specify the TRACE_FILE_ROLLOVER option and the @filecount parameter to allow the trace information to span multiple operating system files. Modifying the preceding stored procedure call as shown below would allow the trace to rollover to subsequent files (the second parameter is 2), up to a maximum of five files (the last parameter is 5).

exec @rc = sp_trace_create @TraceID output, 2, N’E:\demo\demotrace2′, @maxfilesize, NULL, 5

You can also specify a time for the trace to end. This is the second to last parameter, which is NULL in the example above.

Notice that the @TraceId parameter is defined as an output parameter. When the sp_trace_create procedure is called, it creates the new trace and returns to us a unique number that we can use to refer to the new trace. We’ll use the @TraceId variable throughout the rest of the script to update the trace properties.

For more information, visit Books Online sp_trace_create.

Specifying the events

The next section of the script defines the events that the trace will capture. The sp_trace_setevent stored procedure is executed repeatedly to define each and every event we’ve asked to see. If you’re monitoring a lot of events, this section is rather tedious to look at. For more information, see sp_trace_setevent in Books Online.

–- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on

Filtering the events

Filters are set using the sp_trace_setfilter stored procedure in the next section. This is where we can narrow down our trace to just one database or to queries that take longer than some threshold we’ve set. On a SQL Server instance with lots of databases, you’ll definitely want to set some filters. For more information on filtering traces, visit sp_trace_setfilter in Books Online.

– Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

Starting and stopping the trace

Once the trace has been fully defined within SQL Server, all we have left to do is to start the trace. We do that by setting its status using the sp_trace_setstatus stored procedure.

– Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

There are two parameters that we must supply to the sp_trace_setstatus stored procedure. The first is the trace that we wish to modify; this was the output of the sp_trace_create procedure at the beginning of the script. The second is that status of the trace.

A value of 0 for the status stops the trace. 1 starts the trace. 2 gets rid of the trace from the server. See Books Online for information about the sp_trace_setstatus stored procedure.

Next up: scheduling Server Side Traces

trainschedule_2009-09-14Alright! Now that we have a pretty good understanding of what this script does and how to manually start and stop Server Side Traces, next time we’ll look at ways to automate the trace.  Scheduling the trace will make it easy for us automatically collect data as  at various points throughout the business day and week.  This information will prove invaluable in helping us to benchmark our systems.

Until next time,

Joe

Scripting Server Side Traces

If you’ve never used SQL Server Profiler, you truly don’t know what your missing. And that statement has an intentional double meaning. You don’t know what a powerfully informative tool you’re missing, and you really don’t know what’s going on inside your SQL Server box either. Once you try it and figure it out, it’ll be like getting a new birthday present. Or finding $20 in your pants pocket when you wash your jeans.

Using SQL Profiler

There are plenty of tutorials online to help get you started using SQL Profiler. Books Online is a great place to start. It provides a good introduction as well as showing you the permissions required to run SQL Server Profiler. Brad McGehee, who probably knows as much about it as anyone, has written a book dedicated to the subject; it’s available as a free pdf download. And SQLServerPedia and JumpStartTv both have video tutorials so you can watch someone else show you how it’s done.

But Performance was bad before…

SQL Server Profiler can be great on development and test systems, but it only takes a time or two of running SQL Server Profiler on a production server before you learn the hard way that the SQL Server Profiler can have a significantly negative impact on performance. When you fire up the trusty tool, it doesn’t take long before your phone starts ringing. If you thought performance was an issue before, you ain’t seen nothin’ yet.

A disgruntled user calling

In speaking engagements on identifying performance bottlenecks, I often refer to Linchi Shea’s eye-opening work on quantifying the performance impacts fo SQL Server Profiler. His blog post on the subject is clear and convincing. It puts numbers on something that we knew anecdotally.

But what is really noteworthy in the blog is the impact, or really the lack of impact, that Server Side Traces have on performance. I won’t repeat his proof or findings here; go read it for yourself. But suffice it to say, you should use Server Side Traces and not SQL Server Profiler on production systems. Server Side Traces are very similar to SQL Server Profiler traces only without the overhead.

Configuring Server Side Traces

Okay, okay, Server Side Traces are the way to go. But have you seen the scripts required to set one of those up? They are ugly! Just look at the Books Online example!

Here’s another example. The script below creates a really simple Server Side Trace. It only captures a few events (Stored Procedures – RPC:Completed, SP:StmtCompleted and TSQL – SQL:BatchCompleted) for the AdventureWorks and AdventureWorksDW databases, saving the output to a local drive. It’s not quite Assembler, but it’s not that fun to write either.

– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100

– Create the trace
exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace’, @maxfilesize, NULL
if (@rc != 0) goto error

– Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

– Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

– display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Here is a list event ids and filter options for Server Side Traces. Fortunately, we don’t have to create all this by hand. We can return to our beloved SQL Server Profiler and have it do the heavy lifting for us.

Turning SQL Server Profiler traces into Server Side Traces

Once you’ve configure a trace in SQL Server Profiler, you can export the trace definition. Click on File | Export | Script Trace Definition and select the version of SQL Server you have. I’m using the version of SQL Server Profiler that came with SQL Server 2008 and I have the option to script the trace definition for SQL Server 2005-2008. If you’re using SQL Server 2000, you have your own special option for their trace definition.

To see the fruits of our efforts, let’s look at the script file to see the trace definition in T-SQL. As you can see, it’s all there.

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 08/27/2009  08:59:12 AM         */
/****************************************************/

– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

– Please replace the text InsertFileNameHere, with an appropriate
– filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
– will be appended to the filename automatically. If you are writing from
– remote server to local drive, please use UNC path and make sure server has
– write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL
if (@rc != 0) goto error

– Client side File and Table cannot be scripted

– Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on
exec sp_trace_setevent @TraceID, 27, 8, @on
exec sp_trace_setevent @TraceID, 27, 32, @on
exec sp_trace_setevent @TraceID, 27, 56, @on
exec sp_trace_setevent @TraceID, 27, 64, @on
exec sp_trace_setevent @TraceID, 27, 1, @on
exec sp_trace_setevent @TraceID, 27, 9, @on
exec sp_trace_setevent @TraceID, 27, 41, @on
exec sp_trace_setevent @TraceID, 27, 49, @on
exec sp_trace_setevent @TraceID, 27, 57, @on
exec sp_trace_setevent @TraceID, 27, 2, @on
exec sp_trace_setevent @TraceID, 27, 10, @on
exec sp_trace_setevent @TraceID, 27, 26, @on
exec sp_trace_setevent @TraceID, 27, 58, @on
exec sp_trace_setevent @TraceID, 27, 66, @on
exec sp_trace_setevent @TraceID, 27, 3, @on
exec sp_trace_setevent @TraceID, 27, 11, @on
exec sp_trace_setevent @TraceID, 27, 35, @on
exec sp_trace_setevent @TraceID, 27, 51, @on
exec sp_trace_setevent @TraceID, 27, 4, @on
exec sp_trace_setevent @TraceID, 27, 12, @on
exec sp_trace_setevent @TraceID, 27, 52, @on
exec sp_trace_setevent @TraceID, 27, 60, @on
exec sp_trace_setevent @TraceID, 27, 13, @on
exec sp_trace_setevent @TraceID, 27, 6, @on
exec sp_trace_setevent @TraceID, 27, 14, @on
exec sp_trace_setevent @TraceID, 27, 22, @on
exec sp_trace_setevent @TraceID, 189, 7, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 55, @on
exec sp_trace_setevent @TraceID, 189, 8, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 56, @on
exec sp_trace_setevent @TraceID, 189, 64, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 41, @on
exec sp_trace_setevent @TraceID, 189, 49, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 26, @on
exec sp_trace_setevent @TraceID, 189, 58, @on
exec sp_trace_setevent @TraceID, 189, 66, @on
exec sp_trace_setevent @TraceID, 189, 3, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 51, @on
exec sp_trace_setevent @TraceID, 189, 4, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 52, @on
exec sp_trace_setevent @TraceID, 189, 60, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 122, 7, @on
exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 64, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 25, @on
exec sp_trace_setevent @TraceID, 122, 41, @on
exec sp_trace_setevent @TraceID, 122, 49, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 22, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 34, @on
exec sp_trace_setevent @TraceID, 122, 50, @on
exec sp_trace_setevent @TraceID, 122, 66, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 4, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 28, @on
exec sp_trace_setevent @TraceID, 122, 60, @on
exec sp_trace_setevent @TraceID, 122, 5, @on
exec sp_trace_setevent @TraceID, 122, 29, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

– Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

– Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

– display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

This looks remarkably similiar to the one I showed earlier in the post. I used SQL Server Profiler to create that one for me, too.

Next Up: running and scheduling Server Side Traces

Whew! This blog has almost exceeded our collective attention spans, so we’ll stop here for now. But in the next post, I’ll show you how to run Server Side Traces and even schedule them to run automatically so you can capture benchmarking data.

Until then, I’d love to hear your experiences with SQL Server Profiler and Server Side Traces. Got some good scripts that you don’t mind sharing? Post or link to them in the comments section below.

Joe

Follow

Get every new post delivered to your Inbox.

Join 36 other followers