Featured Article(s)
Business Continuity planning for SQL Server
Business Continuity planning for SQL server. Find out tips and an approach to your planning.
SQL Server Execution Plan and Tuning
When was the last time you reviewed the execution plan for your popular queries in the system? This seems like one of the tools that gets attention when there are problems, but much less frequently as a more consistent maintenance element.
The basics – you can run your query in the Query Analyzer/Management Studio environment and ask SQL Server to show you how it would interpret and execute the statement. It will also show you the relative cost of all of the different steps in evaluating the statement, so you can see if there are pieces of the statement (or stored procedure, etc.) that you can optimize. One thing to look for of course are indexes on JOINed columns, things like that.
One thing that I see people overlooking quite frequently when tuning statements, stored procedures and the like is the fact that you can actually use this ability to test multiple approaches against one-another. It’s simple, really – you just put all of your query candidates into the query window, turn on the execution plan option, then run them all at once.
Quick note: if these are UPDATE statements, be warned that you’re running real code and it will update your tables. Be careful what you execute and make sure you understand exactly what it’s meant to do.
When you do run them all at once, you’ll get the relative costs for each statement and can literally compare them side-by-side to see the best statement approach from your various candidate statements. It’s a solid way to approach testing on your system to see the best way to formulate that query.
Featured White Paper(s)
Java Database Connectivity
Database connections are the lifeblood of enterprise applications, administrating the secure and steady flow of information b… (read more)