关于sql server trace的警告信息
关于sql server trace中的警告信息：
Sort Warnings, Hash Warning, Missing Column Statistics 等。
These SQL Server Profiler event classes can be used to help you find out if SQL Server is running as efficiently as it can. They include:
- Execution Warnings: For very busy servers, it is possible that a query may have to wait a short time before there are available resources for it to run. This event class can be watched to find out if and how long queries have to wait for needed resources. This event class has two possible values. "Query Wait" is used to indicate how often a query has to wait. "Query Time-Out" is used to indicate how often a query has timed out waiting for the necessary resources. Obviously, you don't want to see either of these events. If you do, and they occur regularly, then you need to reduce the load on your server, beef up the server hardware, rewrite the queries, select more appropriate indexes to resolve the problem, or some combination of all these actions.
- Hash Warning: This event is used to measure hash recursions or hash bails that have occurred on your server. A hash recursion (event 0) happens when the input of the query does not fit entirely into memory, forcing SQL Server to split the input into multiple parts, which are then processed individually. A hash bail (event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion depth, which forces the query to run under an alternative query plan, one that is much less than optimal. Think of a hash bail as a hash recursion's worst nightmare. As you can imagine, these two events can really slow down a query. Some options to correct these problem events include: insure that the Index Statistics are up-to-date, rewrite the query, experiment with optimizer hints, or add more RAM to the server.
While the purpose of this warning is to let you know when hash operations go awry, it also serves another purpose. If you want optimum performance, you want to avoid hash operations of any type, even ones that work properly. So if you identify any hash warnings, you might want to try to find out why a hash operation is being performed in the first place, and try to fix this problem. This of course, will also fix your hash warning problem. - Missing Column Statistics: Tells you which columns in your query are missing statistics, such as the Index Statistics used by the Query Optimizer to help it evaluate the most efficient query plan. If one or more columns of statistics are missing, the Query Optimizer may not select the best query plan, hurting performance. To help prevent this problem, consider the following: Be sure you have "auto create statistics" turned on for your database, use the CREATE STATISTICS statement to manually create the statistics on the missing columns, or use the Index Tuning Wizard or Database Engine Tuning Advisor to identify and automatically create the needed statistics.
- Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. To fix this, add a join predicate.
- Sort Warnings: Tells you if sort operations can be done completely in memory or if the sorting operation has to be divided into two or more steps in order to complete. Obviously, a one step operation will be much faster. Potential solutions to this problem include reducing the number of rows to be returned and sorted, reducing the number of columns being sorted, getting rid of the sort, or adding more RAM to the server.
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p1.aspx
继续追寻。。。。。。
