Monthly Archives: March 2011

SQL Saturday #67: There and Back Again

SQL Saturday #67

This SQL Saturday was a star-studded cast of speakers and a host of enthusiastic attendees. This was by far one of the biggest SQL Saturdays I’ve been to with over 300 people in attendance.

For me things started off right in Tom LaRock’s (blog | twitter) “Performance Made Easy” talk.

Between trading verbal jabs with Brent Ozar (blog | twitter) Tom broke down the DMAIC (Define, Measure, Analyze, Improve, and Control) method of performance tuning.

He also outlined his “twelve steps” (no, not *those* twelve steps. Twelve steps of tuning).

1. List all tables in query
2. Gather row counts
3. Find all filters (where/join)
4. Calculate selectivity – Don’t use logic yet. Rows/Total Rows per table/join
5. Gather info on additional columns used
6. Gather info on existing keys and indexes
7. Examine the execution plan (set stats IO on, set stats time on)
8. Record results
9. Adjust indexes for table with lowest selectivity. (the one closer to zero)
10. Re-run query and examine results
11. Repeat on next lowest selectivity
12. Continue on, reducing your logical and physical reads.

The next sit-in was with Aaron Betrand (blog | twitter) and “What’s New in Denali?”. Aaron went through some amazing new features including:

SEQUENCE – Replacing IDENTITY. This will work in-memory versus using the database to generate the next in sequence.
SSMS to become a Visual Studio “shell” and bring snippets, surround-with, and clipboard cycling.
Startup options easier to configure.
….and on and on.

Aaron said this is (in his opinion) the next logical update if you’re still on SQL 2005. “It’s worth the update”. I won’t go into much more detail as he has already posted his slides from this talk on his blog here.

There is a disturbance in the Twittersphere.

After lunch I took in Erin Stellato’s (blog | twitter) talk on “Baselines First, Troubleshooting Second“.

Takeaways from this talk were:

Determine what’s most important to baseline and capture
Start simple, work up
Be consistent

Next up was Karen Lopez (blog | twitter) talking about “Database Design Contentious Issues”
This was interactive debate on best practices on Database Design.

Each person was given Post-Its used to “vote” their opinions. It turned into a spirited debate at points but my key takeaway was the phrase “Always never works in system design”.

The other takeaway was that they make Ketchup Pringles in Canada. Really? Ketchup Pringles?

Ketchup Pringles. Only in Canada

Last, but most definitely not least was Michelle Ufford (blog | twitter) talking about “Indexing For Mere Mortals”.

This was the most highly technical talk of the day, and gave a lot of background of index internals.

Very very informative especially if you run Enterprise and large database systems.

The last event of the day (after the swag giveaway) was SQL Karaoke. All I have to say about this is there were some fun, and crazy individuals. However, they probably shouldn’t quit their day jobs <grin>.

Again, I would like to thank the organizers, presenters, DeVry staff, and all the attendees.

Everyone made this out-of-towner feel very welcome and my love for the SQL community grew that much more. Thanks so very much!

 

Share and Enjoy

SQL Saturday #67: Beginnings [pre-con]

The SQL Server Party Wagon

Well, I made it. After a long trek through Nebraska, Iowa, and part of Illinios I arrived early into Warrenville and to the Quest offices for Kevin Kline’s (blog | twitter) pre-conference on Performance Tuning & Troubleshooting.

Kevin ran us through his “Rocks, Gravel, and Sand” methodology. This is an approach by which you take performance troubleshooting and go from tackling the large problems to the very small.

Some key takeaways today were:

1. Check for the stupid stuff first. We sometimes find ourselves jumping in too deep when the problem was really superficial. Check the Windows Event Viewer and PerfMon. See if there are any problems that are occurring that have nothing to do with SQL Server first then start looking at SQL specific issues.

2. Insure your setup is correct. Are the data and log files physically separate? Are you treating your TempDB like a second class citizen? Is your SAN/Disc configured properly? Finally don’t just talk to your SAN admin about disc size, talk about I/O too.

3. Use SQL Profiler but be aware of its overheard. Script out your traces in Profiler and run them in a query versus in Profiler itself.

4. Use DMVs to check SQL Server performance and waits. People like Glenn Barry and Jimmy May have great resources on their blogs to help.

5. Baselining. The hardest question to answer is “how can you know if performance is abnormal if you don’t know what normal is”?  Know how your SQL Server is trending and be able to easily answer inquiry about why it’s slow or fast during different times. You can also easily tell if something is really going awry if you have that data on-hand.

6. There are a ton of tools at a DBA’s disposal, maybe too much at times. If you get in the habit of “sifting down” to where the problem resides rather than spend your time hunting willy-nilly you will be better off.

It was a very productive and informative first day for SQL Saturday #67. I feel like I walked away armed and ready to go back and attack my SQL Servers in a new (and possibly less stressful) way.

Tomorrow is the actual conference and I’m looking forward to a good night sleep, meeting tons of new people tomorrow, and learning even more.

Finally a big thanks to Kevin (blog | twitter) for all the tasty SQL goodness today.

Share and Enjoy