SQL query tuning; with Brian Mason and John Nelson (number 2)
It was mostly demo; which was great. Especially for newer SMS admins, where SQL is not your first technical expertise (even for supposed old timers, like me, where I just stumble along, and lean heavily on sql experts out there), the demo of Query Analyzer & using views was useful.
After that demo, which they were careful to point out took ~5 minutes, John pointed out several steps to take to optimize the query, like:
-
Reduce the # of joins, if possible. For example, the same information is often available in multiple views.
-
If possible (sometimes it is not), if you are using a % or _ for a wildcard in "where something.whatever0 like .." statement, try not to put the % or _ at the front of the search phrase. I.e., if you are looking for Adobe products, try to use Adobe%, not %Adobe%.
-
Avoid Functions in the where clause, i.e., where something.whatever0 UPPER like ..."
-
Return only the columns and rows you need (no select *)
-
And my personal favorite, use indexed fields (or even create indexes -- with the assistance of your SQL DBA, because you'll lose them in an upgrade) for your where clauses. The example was in v_gs_system, netbios_name0 is indexed, and name0 is not. Use the indexed field for faster searching.
During the demo, highlighted a few SQL query building tips, like going to wangz.net, SQLFormatter. After you've built your query (and tested it), use that free web tool to reformat your query into an easier to read syntax. They also mentioned (and I've been guilty of this), when using aliases, like v_gs_system sys, I should really use v_gs_system as sys -- it's compliant to some kind of standard...
When in Query Analyzer, click "Ctrl+L" to see the "cost" of your query. That way you can find any bottlenecks, like searching on %adobe%; if you switch it to adobe%, you'll shave off some execution time.
Ctrl + R, and Shoft + Alt + Enter to toggle some QA visual modes.
After optimizing the query (and switching to a database that had a non-indexed field indexed), the query which used to take 5 minutes to run, took less than 5 seconds!
They also shared their reasoning for deciding to use x64 OS, and x64 SQL 2005 SP2 for their ConfigMgr07 rollout; and why they'll not have SQL on a separate box.