USEFUL SITES :
Write for Us
The SQL Server 2000 Query Analyzer has a tool called "Manage Statistics". Normally, without doing anything, SQL Server automatically creates and maintains internal statistics on the rows in all of your tables that is used by the Query Optimizer to select the optimal execution plan for your query. Most of the time, SQL Server does a fine job doing this, and the Query Optimizer has the necessary information it needs to do its job.
But sometimes, the statistics SQL Server creates and maintains is not optimal, and that's what the Manage Statistics Tool is for. This tool, available from the Tools menu of Query Analyzer, allows you to modify how SQL Server automatically creates and maintains statistics. You can add, edit, or delete the various statistics maintained by SQL Server. And because this tool is available from the Query Analyzer, you can experiment with different sets of statistics and see how it affects the query optimizer's execution plans. [2000] Updated 1-10-2006
*****
If you run the same query in an application, and in Query Analyzer, and note that the performance of the query runs much faster in Query Analyzer than the application, this is an indication that there is a problem with the application causing this problem. One way to help diagnose the problem is to take a Profiler trace of the application running the query and see, in detail, what is really happening. [7.0, 2000] Updated 4-17-2006
When analyzing the performance of a query, viewing the query's Execution Plan can be helpful. An Execution Plan describes how the Query Optimizer plans to, or actual optimized, a particular query. This information is useful because it can be used to help optimize the performance of a query.
There are two ways to display the Execution Plan of a Query. First, you can use the "Display Estimated Execution Plan" option, which displays the proposed Execution Plan for the query you are examining. This plan is displayed for viewing without actually running the query.
Second, you can use the "Show Execution Plan" option. This option also displays a query's Execution Plan, but it is based on the actual Execution Plan used to run the query, not the estimated Execution Plan. In other words, the query is actually run in order for this Execution Plan to be displayed.
As you might expect, there can be a difference between an "estimated" plan and an "actual" plan, but not as much difference as you might think. The main difference between the two is that an "actual" Execution Plan takes into account current activity on your server, while the "estimated" Execution Plan does not. In most cases, the Execution Plan is the same, as server load does not usually make that much difference in a query's Execution Plan.
So which option should you use when analyzing queries? The advantage of the "Estimated" Execution plan is that it runs very quickly (the query does not actually run), which can save a lot of time if the query is a long running one. In addition, some DBAs believe that you should ignore the affect of the server's current performance on an Execution Plan because performance varies from time to time, and this makes it difficult to compare query performance fairly from one run to the next.
On the other hand, an "actual" Execution Plan is the one that really ran. Assuming that the server load, when the query ran and the Execution Plan created, is typical of your server's load, then its results should be ideal for tuning in the real world.
One option you might want to consider is to do your preliminary query analysis using "estimated" Execution Plans, then when you feel that you have optimized the query the best you can, run an "actual" Execution Plan, and take note of any differences, if any.
Whichever option you choose, using Execution Plans to analyze poorly performing queries is a valuable tool. [7.0, 2000] Updated 4-17-2006
In SQL Server 2000, Query Analyzer has a shortcut key feature that allows you to assign commonly run queries or stored procedures to a specific keyboard combination. For example, you might assign sp_helpdb to CTRL-5, for example. This feature can come in handy if you reuse the same code over and over when working in Query Analyzer.
To assign a query or stored procedure to a keyboard combination, go to Tools|Customize from Query Analyzer. [2000] Updated 4-17-2006
SQL Server 2000's Query Analyzer allows you to create Transact-SQL templates that you can use to boost your productivity when coding Transact-SQL. This feature is available from the "Templates" tab of the "Object Browser." Many templates come included with SQL Server 2000, and you can create your own. This makes it very easy for you to store and manage Transact-SQL templates that you can use over and over, saving you much typing time. You can also add as many as you need. To use a template, all you have to do is to drag it from the Object Browser to the code window. [2000] Updated 4-17-2006
<< Prev Page Next Page>>
