Scripting Server Side Traces

August 31, 2009 3 Comments

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

Filed under Optimization, SQLServerPedia Syndication, T-SQL, Tools

Top 5 reasons to run for the PASS Board of Directors

August 27, 2009 3 Comments

There have been quite a few posts in the SQL Server community recently announcing the Call for Nominations for the upcoming PASS Board of Directors elections. Thomas LaRock, Andy Warren, and Kevin Kline, among others have chimed in on the subject.

Despite the obvious risk of having a “me, too” kind of post, I’d like to offer a slightly different perspective on the subject. This post, while hopefully entertaining to everyone, is primarily targeted to the fence sitters, those who have thought about running for the Board but just haven’t quite made up their minds. Yes, I’m talking to you.

In this post, I’d like share with you the Top 5 Reasons You Should Run For the Board. I almost called this the Top 5 Benefits of Being on the PASS Board, since the directors are not compensated for their time except for the free admission into the conference and a nifty PASS shirt.

5. The influence

PASS is a worldwide organization. It has local user groups in communities all around the globe and members on every continent. (Ok, not on Antarctica. There once was, but it was overun by penguins.)

Members of the PASS SQL Server community represent a wide range of organizations from small businesses to global companies, from government and non-profit organizations to the private enterprises, from independent consultants to CIOs. The sun never sets on the PASS SQL Server community.

With such a presence, PASS has a great relationship with Microsoft and can help to provide conduits for helping SQL Server professionals and Microsoft connect better through webcasts, seminars, speaking engagements, surveys, and the like. PASS helps to define and organize the SQL Server community.

4. The direction

Do you have a vision for where you’d like see to the SQL Server community in 3 years, in 5 years, in 10 years? Would you like help define the direction for the PASS SQL Server community? As a member of the Board of Directors, you can.

And on the flip side, if there’s something you don’t like about PASS, don’t sit on the sideline and complain. Step up, get in the game, and do something about it.

3. The experience

Actively participating on the PASS Board of Directors is a bit like working out when you’re not used to exercising. It flexes muscles that you may not get to use in your day job.

Being a good or even great technologist requires a certain set of skills. But being a visionary, a leader, a manager, requires a very different set of skills. As a member of the board, you’ll get to stretch yourself and grow in ways that will make you a more well rounded professional.

You’ll be responsible for a creating your goals and objectives for the year, deciding how you will accomplish them, and ensuring that you make the most of the budget you have.

You’ll regularly work with other highly professional and talented people and your game will be elevated as a result.

2. The people

Speaking of the other people. PASS is absolutely full of great people. Smart people. Talented people. Nice people. People that are fun to be around. As a board member you’ll get to meet and work with lots of great people from around the world. This is reason enough to run.

1. The fun

And the number one reason you should consider running for the PASS Board of Directors is because it’s fun. It’s challenging. It’s exhilerating. It’s working with good people, doing good things for a great community.

Still not convinced?

If you still sitting on the fence and unsure whether you want to throw your hat in the ring, feel free to contact me at joew@webbtechsolutions.com. I’ll be more than happy to talk with you and share my some of experiences while on the PASS Board. I served for six years and I don’t regret it a bit!

Filed under Community, Events, Professional Development, SQLServerPedia Syndication

Examing database shared locks using sys.dm_tran_locks

August 24, 2009 1 Comment

If you’ve worked with Microsoft SQL Server for any length of time, you’re probably already aware that SQL Server uses locks to control access to database objects such as rows, pages, partitions, and tables.

But did you know that SQL Server also creates a shared lock for every connection to the database? It does.

Viewing shared database locks

To see these, you can use the sys.dm_tran_locks dynamic management view as shown in the following query.

SELECT
     l.resource_type
    ,l.request_mode
    ,l.request_status
    ,l.request_session_id
FROM  
    sys.dm_tran_locks AS l
WHERE 
    resource_database_id = DB_ID();

When running this query against the AdventureWorks database, you can see the following results.

There are two rows in the result set since I have two open connections to the database. If I close one of the connections or use another database, the result set will change as shown below.

Counting connections

So, why is this useful? Sometimes you’d like to know just how many connections are currently associated with a database, perhaps before you attempt to detach it. You can certainly use SQL Profiler to get this information, but running a quick query in Query Editor can be much quicker.

More information on the sys.dm_tran_locks dynamic management view can be found in Books Online.

Do you have other uses for this? Or do you have another way of getting the number of current connections to the database? If so, I’d love to hear about them.

Joe

Filed under SQLServerPedia Syndication, T-SQL

Ideas are a work of art

August 20, 2009 8 Comments

When you think about it, most of us in the IT field are paid to solve problems. Sure there are some routine tasks that we do that don’t really require us to think a lot. But for the most part, I’m convinced that we solve problems for a living.

Do you disagree? If you don’t believe me, think about how you spend your day.

  • “We need to roll out the latest service pack to all client desktops.” You figure out a way to make that happen.
  • “The network is slow!” You break out your network diagnostic tools, figure out why, and fix it.
  • “We need to represent and capture this business information in a database.” You interview the stakeholders and create the entity relationship diagrams.

See what I mean? We solve problems for a living. And that’s good. As long as there are users, there will be problems to solve.

Offering a solution

Often when we provide solutions to problems, we just do it. These are typically straightforward solutions that don’t require a lot of input from others. There is no approval process required. The elegance of the proposed solution is not reviewed, much less questioned. We are trusted to do our jobs and implement the solution. If we fall short, we’ll receive some feedback.

But not all solutions are like this. Many require us to put our thoughts together and creatively come up with a solution either as an individual or as part of a team and then present our solution to others.

Shotdown in flames

When you present your ideas to others for review or approval, there are really only three possible responses.

Yes! Yes!
Your proposal is met with unbridled enthusiasm. They really like it and are eager for you to proceed. You walk away from the meeting smiling. This point really doesn’t need much more discussion. All is good.

Uh, maybe.
Your proposal is met with what can only be described as a certain lukewarmness. The review committee or decision makers are somewhat skeptical but they give you tacit approval to proceed. And although they are less than convinced about its outcome, you can still knock their socks off by proving that it works by exceeding their expectations. It’s time to put your money where your mouth is and show results.

What were your thinking? Moron!
Sometimes, though, they actively, openly, and perhaps passionately dislike or even disdain your proposal. And they demonstrate little, if any, self-censorship in telling you why. Sometimes they even use colorful language to describe the ways they think the idea is bad. Your balloon is deflated. Your parade, rained out. Did you hear that clap of thunder off in the distance?

Paint a new picture

It is in those times that I like to think of my ideas as works of art.

Art can be a beautiful thing. But as they say, beauty is in the eye of the beholder. I may think that my proposal is wonderful. It’s great. It meets all their requirements and is all but guaranteed to have overwhelming success. It’s beautiful!

But they may see it another way. Their perception may be different. They may not like it at all. They may start shooting holes in it immediately. And this can hurt if my pride is wrapped up in the proposal, if my sense of self-worth is interwoven into the solution.

Think of it this way. If I’m holding this solution, this piece of art, right in front of me, close to my heart, I’m going to get hit when they start shooting holes in it. There’s no way around it. I’ll take the hits and it’s going to hurt.

But if I’ve decided to hold the piece of art off to one side when I’m presenting it, I’m safe. If they start shooting holes in it, I’m not going to get hit. It passes right through the art and continues on. I’m standing safely beside it.

That’s what we need to do with our ideas. Think of them as works of art. You may appreciate them, but your client or boss may not. No worries. Just set down the solution and create a new work of art based on the feedback you get.

Remember that you are not your ideas. You have self worth and intrinsic value regardless of what other people may think of your ideas or even of you. You cannot control what they think or how they act. But you can control how you respond to them.

Separate yourself from your ideas. And most importantly separate yourself from what others think.

So what do you think? How do you cope with negative feedback to your proposed solutions?

Joe

Filed under Consulting, Professional Development, SQLServerPedia Syndication

TechNet webcast on “Getting Started in IT Consulting”

August 19, 2009 8 Comments

Later today, I’m presenting a free Microsoft Thrive! TechNet webcast on how to get started in Information Technology consulting. If you have the expressed goal or the suppressed desire to become an independent consultant, this webcast is designed to help your create a clear transistion strategy from full-time employee to full-time IT consultant.

I’ll discuss:

  • The many hats of a consultant
  • Strategies for minimizing risk
  • Setting up your business
  • How to handle sales
  • Low cost promotions
  • Some best practices

Much of the information comes from my “The Rational Guide to IT Consulting” book published by Rational Press.

I hope you’ll join me for this event. To register, visit the TechNet Event Registration page.

Joe

Filed under Consulting, Events, Professional Development

The theft of ideas and content

August 16, 2009 10 Comments

If you regularly read SQL Server or other technology blogs, you know that the blogs can be a wonderful way to stay connected. You can learn about the latest innovations in the technology, you can keep abreast of the recent services packs and vulnerabilities, you can even learn of techniques to improve performance. The benefits of reading blogs goes on and on. I’ve got a long list of SQL Server related blogs that I read almost daily.

Additionally blogs are a good way to keep up with others on more personal level. When you read somone’s blog, you get a sense of who they are and what they are like. You almost feel like you know them. And that can be a good thing.

Stealing another’s work

But there’s been a disappointing yet growing trend in SQL Server content on the web over the past few years – the outright theft of blog content.

Initially, the plagiarism was of a more traditional style. It was typically committed by people who wanted the accolades associated with writing really good content without the effort of actually having to write it. Maybe they didn’t have the knowledge or skills to write? Maybe they did have the knowledge and skills but not the time? Who knows? But for whatever the reason, they intentionally decided to steal someone else’s work and place their name on it.

Plagiarized content of this nature would usually show up on personal blog sites. Some of the more cavalier plagiaizers would actually submit the stolen content to sites like SQLServerCentral.com and receive payment for it, hoping that their ill-conceived acts would go unnoticed. I know Steve Jones regularly has to deal with this situation.

A more subtle theft

Recently, it seems that there another breed of plagiarism that has popped up on the Internet. This form of theft is much less obvious, much less overt.

It seems more and more sites are attempting to drive traffic to their own sites with content that is not their own. They do this under they guise of being content aggregators. They may even believe that they are offering the community a service by collecting the a bunch of disparate sources of information and presenting them in one unified place.

However, they are deceiving themselves and their readers. They are, in my humble opinion, stealing content for the explicit purpose of driving traffic to their site. Perhaps they do this for ad revenue? Perhaps for the sense of accomplishment in creating a site that has millions of hits? Whatever the reason, the ends do not justify the means.

A rose by any other name is still a rose

Many, if not most, of these so-called aggregators, do not give credit to the author, they do not link back to the original source of the information, and they are not up front about their techniques for acquiring the content.

When you take the work of another and use it without the authors consent, you are plagiarizing. It’s that’s simple.

The right way

There are, of course, many aggregators that syndicate content legally with the author’s permission and consent. SQLServerPedia.com is a great example of this. They ask authors to select and submit their works. They link back to the original works. And they give credit to the author. This is the right way of doing things.

So how does this affect me?

If you enjoy reading content that others have created and want to see that content continue to flow in the most convenient way possible, you can help. Notify authors with their content has been plagiarized. Even if you are unsure whether it was used without permission or not, let the author know. They will appreciate hearing from you.

If you are an author and you’ve noticed that your content is being used without your permission, you have rights. Brent Ozar (@BrentO on twitter) has some good information on his site about plagiarism and what to do about it.

I’m actually taking his advice now. Last week I discovered that several of my blog posts are being used without my consent. Take it from me, this is not a pat-on-the-back. It’s not a I-should-feel-flattered-that-someone-liked-my-stuff-enough-to-rip-it-off. No, it’s an annoyance.

What do you think? Are aggregators plagiarizing? Have you had issues with plagiarism? I’d like to hear your thoughts and experiences.

Joe

Filed under Blogging, Community, SQLServerPedia Syndication

The effects UNION in a SQL query

August 6, 2009 9 Comments

In a recent blog, I shared some of the natural but sometimes unanticipated consequences of adding DISTINCT to a SELECT statement. Adding DISTINCT ensures that you don’t have duplicated rows in your result set, but that assurance comes at a price. Performance is typically impacted negatively when you add DISTINCT to a query. You can read the blog and see the proof here.

But there is another operator that can have some not-so-obvious side affects. Let’s a have look how UNION can affect a query’s performance.

A brief review

The UNION operator combines the results from two completely separate queries into a single result set. For example, let’s say you want to retrieve all customers and all employees in a single list. You could individually issue two separate queries and then combine the results in the presentation layer or in Microsoft Excel to produce your list.

But you can also let SQL Server do the work for you. For example:

SELECT FirstName, LastName
FROM DimCustomer
UNION
SELECT FirstName, LastName
FROM DimEmployee

There are a couple of requirements for using the UNION operator. Each of the two queries that are combined using the UNION operator must have the same number of columns. Furthermore, the columns must be of compatible datatypes. You can read more about the UNION operator on MSDN.

Running with UNION

So, how can this be bad? It’s just running two queries and concatenating the results, right? Let’s have a look at the query execution plan to see what’s going on behind the scenes.

As you can see from the query and execution plan in the graphic above, each of the two queries are executed separately and then concatenated together into one result set. But notice that there’s another operation before results are returned; there’s a Distinct Sort operation which accounts for 61% of the overall cost of the query. What’s that doing in there?

When you use UNION to combine the results from two queries, SQL Server ensures that the combined result set does not contain any duplicated rows. Or put another way, if one of the rows in the first query is identical to a row produced in the second query, only one of the two rows will be returned in the results.

This sounds a bit confusing or at least wordy. So let’s consider our example again. If an employee had purchased something from our company, that person would be an employee and a customer. So each of the two queries in our example would return a row for that person. Using UNION, however, that person would only be listed once in the combined result set. UNION returns distinct rows.

Does this sound familiar? It should; this is essentially the same thing that the DISTINCT keyword does in a SELECT statement.

Before moving on, let’s look at the subtree cost for the entire query. The following graphic shows a subtree cost of 2.01387.

Running with UNION ALL

What if we don’t care about duplicates in our result set? What if we know that none of our employees would ever buy one of our products? Or, what if we are going to handle the duplicates in the presentation layer? Can we optimize the query so that the Distinct Sort doesn’t occur?

Sure. Using UNION ALL rather than UNION tells SQL Server to skip the Distinct Sort step.

By changing the query ever so slightly, using UNION ALL rather than UNION, we tell SQL Server that we don’t mind if there are duplicates in the result set and that it’s ok to skip the Distinct Sort.

What does this do to the over all cost of the query? Let’s look at the new subtree cost for the SELECT statement.

The overall cost decreased to 0.77873 from 2.01387; that’s an appreciable amount, particularly if this query is frequently run throughout the day.

Do you regularly use UNION in your queries? If so, I’d love to hear your experiences with UNION vs UNION ALL and how it’s affect performance.

Joe

Filed under Optimization, SQLServerPedia Syndication, T-SQL

Revive your User Group, part IV

August 1, 2009 3 Comments

Over the past three posts in this series entitled “Revive your User Group”, I’ve shared some of best practices that I’ve learned over the years for keeping a technical user group going. If you haven’t read those posts already, I’d encourage you to go back and read those before continuing on with this post. Or at least go back and read them after you’ve completed this one so you’ll have the complete picture of the recommendations.

In this, the final post in the series, I’d like to share with you some ways to expand the boundaries of the user group.

Most groups think of their group as one meeting per month where someone else is willing to share their knowledge with the group. But a technical group can be much more than that. You can, with a little effort and setup, transform your group from a once-a-month meeting to a continuous interaction among the membership.

Establish a web presence

This one seems like a no-brainer, particularly in today’s Internet ladened world. Unfortunately, however, this is where many groups stop. They create a static web site, publish it to their hosting provider, and “forget about it”. Sure, the site may contain there meeting frequency and location, but little else.

The problem with static, or stagnant, web pages are this: If someone visits the web site and it hasn’t been updated in months, they’re likely to think the group has just faded away.

Your group’s web site is one of the primary ways you have to communicate with the existing and potential members of the group. Make sure the web site is up to date with meeting times, locations, and even recent meeting information. As suggested in part three of this series, appoint one person to update the web site regularly.

But don’t stop there. Make the site dynamic and give members a reason to visit it. Post speaker’s slide decks, aggregate RSS feeds and blogs on the site, write summaries of recent meetings, and post pictures from the meetings.

The Microsoft web site has RSS feeds for many of their technologies. Find one and display the feed on your groups web site. It’ll give it fresh information without you having to do anything. You can even subscribe to custom feeds for your location. Visit the TechNet Events Custom RSS Feeds page for more information.

If you are a SQL Server User Group and you don’t have a web site, PASS can help by giving you hosting space and Dot Net Nuke (DNN) content management system framework. Visit the PASS Chapters page for more information. Similarly free hosting probably exists for .NET user groups as well as for other technologies. If nothing else, turn to a blogging site such as WordPress or Blogger.

Stay in touch with three emails per month

To really keep a group going, meeting just once per month is not enough. If someone misses a couple of meetings in a row due to scheduling conflicts, he may not return since he’s gotten out of the habit or it’s fallen off his calendar.

To really build a community, you’ll want to make sure you regularly stay in touch with members. Email is a great medium for that. Send an email one week before each meeting. The body of the email should include the meeting time and location, the speaker’s name, topic, and bio, and any free give-aways that you’ll have. Also be sure to thank your sponsors.

A second email the day before (or the day of) the meeting is also a good idea to give people a last minute reminder.

And finally one week after the meeting, consider sending a wrap-up or summary email to the membership. This email should contain four or five tips that the speaker shared with the group, a link to his PowerPoint slide deck on your web site, a thanks to the sponsors, and if possible some information about the next meeting. The follow up email is designed to let those who didn’t come know that they missed a good and valuable meeting and that they should really come to next month’s meeting.

As a bonus, each new subscriber to your group should receive a “Welcome, we’re glad you’re here” email.

Social Networking

Many successful groups go beyond the traditional web site and email approaches. They also use online social networking platforms to encourage members to reach out to each other for technical issues, for job searches, and for purely social exchanges.

For example, Twitter is a great tool to share information with your membership. Have someone tweet messages about local events or Internet resources of interest. Use hash tags, like #nashsql, to identify tweets specific to your group. LinkedIn is another great option for creating an online social presence for your group. There are others, too, just do a little research and ask your membership what platforms they regularly use.

The goal is to give people in your local technical community a way to keep in touch. Some may not use one particular platform, others may. That’s up to them. What you want to do is to provide ways that they can connect to others if they choose.

And in conclusion

Over the past four posts in this series I’ve outlined some best practices that I’ve found over during my years of leading both local and international user groups. But this list is not be comprehensive. There may be ideas and techniques out there that work for your group that haven’t been covered in this series. If that’s the case, I’d love to hear about the them.

Please post how your user group stays vibrant and active in the comments section of this post. After all, that’s what we’re all trying to do, keep a good technical community going so we can all benefit from it.

Filed under Community, SQLServerPedia Syndication

Follow

Get every new post delivered to your Inbox.