Tara Kizer Blog

Tara Kizer

PASS 2005: Session notes/comments Part 1

by Tara Kizer in  

On Tuesday, I attended Kimberly Tripp's "The Next Generation DBA: Critical New SQL Server Features NEED to Learn!" pre-conference seminar.  It was awesome!  She is a fantastic speaker.  She covered database snapshots and database mirroring amongst many other DBA topics.  I see my company using database snapshots so that developers can quickly query production data without querying the production database and also without having to copy the database to a development server.  Even though log shipping still exists in SQL Server 2005, I think we'll be switching over to database mirroring to sync our disaster recovery site.  Database mirroring won't be supported at RTM though.

On Wednesday, I attended three sessions: Kimberly Tripp's "Indexing Best Practices in SQL Server - Creation, Performance, Maintenance", Charles Felber's "Performance Trend Analysis Using SQL Trace", and Keith Elmore's "Fine Tuning Performance with Plan Guides".

The only negative to Kimberly's session was that it was only 75 minutes long.  She said it normally takes two days to cover this material.  She showed us how you can compare a Performance Monitor counter log with a SQL Profiler trace file on one screen in SQL Profiler.  The counter log doesn't even have to be from the same server as the trace file; you just need to make sure that the times are synced on both servers.  You can now see the execution plan graphically in SQL Profiler if you select the show plan in XML event.  One thing that I found interesting was her tip to use UNION instead of OR in SQL Server 2000.  In 2005, you won't see the performance improvement, but in 2000, you could see a drastic improvement.  I'll have to try this one out when I get back home.

Charles' session showed us how to use SQL Trace, which is the alternative to SQL Profiler.  SQL Trace is run via T-SQL commands, whereas SQL Profiler is a GUI tool.  He demonstrated SQL Trace via his custom scripts.  I'll be downloading them as soon as I get back.  They are very well written.  They are even formatted the way that I write T-SQL code. 

Keith's session showed us the new plan guides feature.  This is intended for scenarios where you have no control over the input SQL, such as third party applications.  It is available in both Standard and Enterprise Editions.  I don't currently support any third party applications, so I won't be able to use this feature, but it sounded useful.

Legacy Comments
Adam Machanic
2005-09-30		 re: PASS 2005: Session notes/comments Part 1
Do you mean UNION ALL instead of OR?

SELECT *
FROM Tbl
WHERE Tbl.Col = 1
OR EXISTS
(SELECT *
FROM Tbl2
WHERE Tb1.x = Tbl2.x)

... is sometimes more efficiently re-written as:

SELECT *
FROM Tbl
WHERE 1
EXISTS
(
SELECT 1
WHERE Tbl.Col = 1
UNION ALL
SELECT 1
FROM Tbl2
WHERE Tb1.x = Tbl2.x
)

In this case, the EXISTS will be satisfied if Tbl.Col = 1... No need to hit the second query.

By the way, how about a meet-up for SQL Team people tomorrow? Lunch? Maybe I missed one already though -- I haven't had a chance to check the forums recently... Either way, let me know.

amachanic [at] gmail [dot] com
Tara
2005-09-30		 re: PASS 2005: Session notes/comments Part 1
UNION ALL was also mentioned, but the suggestion was to try out UNION instead of OR. I haven't tried it yet though.

I'm wearing light blue jeans and a black button down shirt. I've got brown hair past my shoulders.

I saw you on Tuesday, but I wasn't sure if you'd know who I was. I'll be sure to introduce myself if I see you today.
Adam Machanic
2005-09-30		 re: PASS 2005: Session notes/comments Part 1
OK, are you talking about:


SELECT *
FROM tbl
WHERE x = 1
OR x = 2

==

SELECT *
FROM tbl
WHERE x = 1
UNION
SELECT *
FROM tbl
WHERE x = 2

?
Tara
2005-09-30		 re: PASS 2005: Session notes/comments Part 1
She didn't provide an example in her demonstration, but I assumed that yes that's what she was referring to. She commented that she once was able to switch to a UNION from an OR and the query went from minutes to under 10 seconds.
sql
2005-11-18		 re: PASS 2005: Session notes/comments Part 1
From personal experience, the exact opposite was true. Switching from using the "union" to using "or" DECREASED the execution time by an order of magnitude. Undoubtedly, whoever originally wrote the query must have also heard this "tip" to use union.