SQL Indexing – July 2009 PASS Session

Just attended the Victoria.NET – Professional Association for SQL Server (PASS) session hosted by Greg Low, with future meeting listed on the user group site: melbourne.sqlpass.org

Greg’s a very knowledgeable database specialist (don’t know his actual title). I’ve seen him present previously at RDN sessions, and really enjoy the “run throughs” using SQL query editor, and his ability to answer tricky questions raised by the audience.

I took a heap of notes, and managed to type them reasonably well on landscape mode on the iPhone, while still focusing on the presentation. I’ll try and transcribe some of it here, for it to be a somewhat useful summary.

The presentation focussed on Clustered and Non-Clustered indexes in SQL Server, with 1 key tip off the bat: index selection should be a fundamental part of the design. Combining that with the final statement of the presentation “tuning is never over” – can be interpreted as during system development indexes should not be ignored and should be re-factored as development moves forward.

General Tips:

  • Incorporating a bit in an index is a wise choice if that field has a significant selectivity impact reduction for certain queries.
  • Guid’s are where you draw the line as primary key’s anything larger such as string’s isn’t wise due to lack of efficiency, and blob like data such as varchar(1000)’s are just shooting yourself in the foot.

Another key take away was a simple list in order of priority what to look for and check when fine tuning performance:

  1. Selecting an appropriate clustering strategy
  2. Primary Key
  3. Uniqueness declared
  4. Foreign Key Indexes
  5. Then begin with usage tools

    Missing Indexes – Careful not to blindly accept all recommendations

    – Dynamic Management View (DMV)

Point 3 being a common index that offers a performance boost, but is overlooked as it’s not auto-created like those for primary keys.

The previous list can be combined with this list in columns of concern for indexes, in order of priority to improve performance:

  1. JOINS
  2. WHERE clause members
  3. ORDER BY members
  4. SELECTed columns

The final tip I walked away with is; if making use of the “Books Online” then update them every 2 months, as on average they are updated every 6 weeks, with Greg providing the example of a “Bit Field” cannot be indexed being explicitly mentioned in online documentation up until a recent correction.

The upcoming presentations of the PASS group will focus on Reporting Services and Profiling.

Sketching an actual solution.

It’s been a while since I’ve posted, project commitments have been drowning me. But the tide has now receded.

Last night (Tue 30th of June) I attended the Melbourne Silverlight Designer and Developer Network (SDDN) event, always great fun and interesting and always has free pizza (if you get there early).

With presentations from Mahesh Krishnan @ blogesh.wordpress.com about new features in Expression Blend 3. The most interesting to developers of business systems being; sample data generation, and IntelliSense support.

Which was followed by a great demo of SketchFlow – A rapid prototyping tool (that works) by Shane Morris a User eXperience (UX) guru from Microsoft.

SketchFlow looks to fill a gap well for designing something that can actually be played with and fine tuned by designers, users and business analysts. Having been involved in projects where UI’s left up to the control of developers have resulted in lots of rework when clients begin to use them as part of UAT such a tool if used properly should improve the process greatly.

Some of the great uses for SketchFlow are to quickly explore possible flow paths in an application, screen layout design and actually begin to setup associated actions such as animations and transitions. The deal-maker being it’s ability to create actual, usable code. Without the designer/business analyst writing code. This code can then be “harvested” out when development begins. It also includes a feature designed for testers and other lovers of long documents, it can output a word document of the screen designs, annotations and possibly feedback elements. The feedback element of the system allows a redistributable (via silverlight package or central web location) for users to navigate around the application screens, and draw and add notes. With the most typical example of a “simple request” – *big circle around logo* with the text: “can we make our company logo bigger?!”.

There should be a Beta release coming soon, so get a hold of it to actually witness it’s potential. It’s worth checking out the AU Remix 09 videos including the key note where SketchFlow is mentioned.

I won a copy of ‘Sketching User Experiences: Getting the Design Right and the Right Design’ (Interactive Technologies) available at Amazon. I hope to get a chance soon to start reading parts of this, and may make a post out of it.

I won the book by raising a question about how one could go about using SketchFlow to help design a “Version 2.0” of an existing system. Attempting to incorporate actual real world looking components from the real operational system side by side with new additions, including mixes and matches for modifications/overhauls on existing system screens. The flexibility of the tool seems to lend itself to using it in a variety of scenarios and project types.

Update (10 Jul 2009): Download Blend 3 RC & SketchFlow Release Candidate here.
Update 2: That link isn’t a wise choice… Stay tuned to : seethelight.com