StoredProcedure 調查

SELECT P.name
     , P.object_id
     , PS.cached_time
     , PS.execution_count
     , PS.total_worker_time
     , PS.total_worker_time/PS.execution_count AS avg_worker_time
     , PS.last_worker_time
     , PS.min_worker_time
     , PS.max_worker_time
     , PS.total_elapsed_time
     , PS.total_elapsed_time/PS.execution_count AS avg_elapsed_time
     , PS.last_elapsed_time
     , PS.min_elapsed_time
     , PS.max_elapsed_time
     , PS.total_physical_reads
     , PS.total_physical_reads / PS.execution_count as avg_physical_reads
     , PS.last_physical_reads
     , PS.min_physical_reads
     , PS.max_physical_reads
     , PS.total_logical_writes
     , PS.total_logical_writes / PS.execution_count as avg_logical_writes
     , PS.last_logical_writes
     , PS.min_logical_writes
     , PS.max_logical_writes
     , PS.last_execution_time
FROM sys.dm_exec_procedure_stats PS RIGHT OUTER JOIN sys.procedures P ON PS.object_id = P.object_id
ORDER BY PS.total_worker_time/PS.execution_count DESC;
GO


SELECT OBJECT_NAME(I.object_id) AS TableName
    , I.name AS IndexName
    , case I.Type when 1 then N'Clustered' when 2 then N'NonClustered' end AS type
    , user_scans
    , user_seeks
    , user_lookups
    , user_updates
    , last_user_scan
    , last_user_seek
    , last_user_lookup
    , last_user_update
FROM sys.dm_db_index_usage_stats IUS RIGHT OUTER JOIN sys.indexes I ON IUS.object_id = I.object_id AND IUS.index_id = I.index_id 
WHERE I.object_id > 100;
GO

T-SQL 效能相關

In this article, I will discuss some T-SQL query performance tips and tricks for SQL server programmers. The tips mentioned in this article may sound obvious to most of you, but I have seen professional developers who don't think before using them.

My first tip is not using a WHERE clause in your SELECT statement to narrow the number of rows returned. If you don't use a WHERE clause, then SQL Server will perform a table scan of your table and return all of the rows. In some case you may want to return all rows, and not using a WHERE clause is appropriate in this case. But if you don't need all the rows returned, use a WHERE clause to limit the number of rows returned.
By returning data you don't need, you are causing SQL Server to perform I/O it doesn't need to perform, wasting SQL Server resources. In addition, it increases network traffic, which can also lead to reduced performance. And if the table is very large, a table scan will lock the table during the time-consuming scan, preventing other users from accessing it, hurting concurrency.
Another negative aspect of a table scan is that it will tend to flush out data pages from the cache with useless data, which reduces SQL Server's ability to reuse useful data in the cache, which increases disk I/O and hurts performance. [6.5, 7.0, 2000] 

To help identify long running queries, use the SQL Server Profiler Create Trace Wizard to run the "TSQL By Duration" trace. You can specify the length of the long running queries you are trying to identify (such as over 1000 milliseconds), and then have these recorded in a log for you to investigate later. [7.0] 

When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset. If you know that there are duplicate records, and this presents a problem for your application, and then by all means use the UNION statement to eliminate the duplicate rows.
On the other hand, if you know that there will never be any duplicate rows, or if there are, and this presents no problem to your application, then you should use the UNION ALL statement instead of the UNION statement. The advantage of the UNION ALL is that is does not perform the SELECT DISTINCT function, which saves a lot of unnecessary SQL Server resources from being using. [6.5, 7.0, 2000] 

Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example:
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_valueUNIONSELECT column_name1, column_name2
FROM table_name1
WHERE column_name2 = some_value
This same query can be rewritten, like the following example, and when doing so, performance will be boosted:
SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value
And if you can assume that neither criteria will return duplicate rows, you can even further boost the performance of this query by removing the DISTINCT clause. [6.5, 7.0, 2000]
Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary. This is a bad habit that should be stopped.

The DISTINCT clause should only be used in SELECT statements if you know that duplicate returned rows are a possibility, and that having duplicate rows in the result set would cause problems with your application.
The DISTINCT clause creates a lot of extra work for SQL Server, and reduces the physical resources that other SQL statements have at their disposal. Because of this, only use the DISTINCT clause if it is necessary. [6.5, 7.0, 2000]

In your queries, don't return column data you don't need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column. In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance. [6.5, 7.0, 2000] 

If your application allows users to run queries, but you are unable in your application to easily prevent users from returning hundreds, even thousands of unnecessary rows of data they don't need, consider using the TOP operator within the SELECT statement. This way, you can limit how may rows are returned, even if the user doesn't enter any criteria to help reduce the number or rows returned to the client. For example, the statement:
SELECT TOP 100 fname, lname FROM customers
WHERE state = 'AP'
limits the results to the first 100 rows returned, even if 10,000 rows actually meet the criteria of the WHERE clause. When the specified number of rows is reached, all processing on the query stops, potentially saving SQL Server overhead, and boosting performance.
The TOP operator works by allowing you to specify a specific number of rows to be returned, like the example above, or by specifying a percentage value, like this:
SELECT TOP 10 PERCENT fname, lname FROM customers
WHERE state = 'AP’
In the above example, only 10 percent of the available rows would be returned. Keep in mind that using this option may prevent the user from getting the data they need. For example, the data the are looking for may be in record 101, but they only get to see the first 100 records. Because of this, use this option with discretion. [7.0, 2000] 

You may have heard of a SET command called SET ROWCOUNT. Like the TOP operator, it is designed to limit how many rows are returned from a SELECT statement. In effect, the SET ROWCOUNT and the TOP operator perform the same function.
While is most cases, using either option works equally efficiently, there are some instances (such as rows returned from an unsorted heap) where the TOP operator is more efficient than using SET ROWCOUNT. Because of this, using the TOP operator is preferable to using SET ROWCOUNT to limit the number of rows returned by a query. [6.5, 7.0, 2000] 

In a WHERE clause, the various operators used directly affect how fast a query is run. This is because some operators lend themselves to speed over other operators. Of course, you may not have any choice of which operator you use in your WHERE clauses, but sometimes you do.
Here are the key operators used in the WHERE clause, ordered by their performance. Those operators at the top will produce results faster than those listed at the bottom.

•  =
• >, >=, <, <=
• LIKE
• <>

This lesson here is to use = as much as possible, and <> as least as possible. [6.5, 7.0, 2000] 

In a WHERE clause, the various operands used directly affect how fast a query is run. This is because some operands lend themselves to speed over other operands. Of course, you may not have any choice of which operand you use in your WHERE clauses, but sometimes you do.
Here are the key operands used in the WHERE clause, ordered by their performance. Those operands at the top will produce results faster than those listed at the bottom.

• A single literal used by itself on one side of an operator
• A single column name used by itself on one side of an operator, a single parameter used by itself on one side of an operator
• A multi-operand expression on one side of an operator
• A single exact number on one side of an operator
• Other numeric number (other than exact), date and time
• Character data, NULLs

The simpler the operand, and using exact numbers, provides the best overall performance. [6.5, 7.0, 2000] 

If a WHERE clause includes multiple expressions, there is generally no performance benefit gained by ordering the various expressions in any particular order. This is because the SQL Server Query Optimizer does this for you, saving you the effort. There are a few exceptions to this, which are discussed on this web site[7.0, 2000] 

Don't include code that doesn't do anything. This may sound obvious, but I have seen this in some off-the-shelf SQL Server-based applications. For example, you may see code like this:
SELECT column_name FROM table_name
WHERE 1 = 0
When this query is run, no rows will be returned. Obviously, this is a simple example (and most of the cases where I have seen this done have been very long queries), a query like this (or part of a larger query) like this doesn't perform anything useful, and shouldn't be run. It is just wasting SQL Server resources. In addition, I have seen more than one case where such dead code actually causes SQL Server to through errors, preventing the code from even running. [6.5, 7.0, 2000]
By default, some developers, especially those who have not worked with SQL Server before, routinely include code similar to this in their WHERE clauses when they make string comparisons:
SELECT column_name FROM table_name
WHERE LOWER(column_name) = 'name'
In other words, these developers are making the assuming that the data in SQL Server is case-sensitive, which it generally is not. If your SQL Server database is not configured to be case sensitive, you don't need to use LOWER or UPPER to force the case of text to be equal for a comparison to be performed. Just leave these functions out of your code. This will speed up the performance of your query, as any use of text functions in a WHERE clause hurts performance.
But what if your database has been configured to be case-sensitive? Should you then use the LOWER and UPPER functions to ensure that comparisons are properly compared? No. The above example is still poor coding. If you have to deal with ensuring case is consistent for proper comparisons, use the technique described below, along with appropriate indexes on the column in question:
SELECT column_name FROM table_name
WHERE column_name = 'NAME' or column_name = 'name'
This code will run much faster than the first example. [6.5, 7.0, 2000]
Try to avoid WHERE clauses that are non-sargable. The term "sargable" (which is in effect a made-up word) comes from the pseudo-acronym "SARG", which stands for "Search ARGument," which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available) to speed completion of the query. If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query's performance to suffer.
Non-sargable search arguments in the WHERE clause, such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" generally prevents (but not always) the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.
But not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use an index (if one exists) to help access the data quickly.
In many cases, if there is a covering index on the table, which includes all of the columns in the SELECT, JOIN, and WHERE clauses in a query, then the covering index can be used instead of a table/index scan to return a query's data, even if it has a non-sargable WHERE clause. But keep in mind that covering indexes have their own drawbacks, such as producing very wide indexes that increase disk I/O when they are read. 
In some cases, it may be possible to rewrite a non-sargable WHERE clause into one that is sargable. For example, the clause:
WHERE SUBSTRING(firstname,1,1) = 'm'
can be rewritten like this:
WHERE firstname like 'm%
Both of these WHERE clauses produce the same result, but the first one is non-sargable (it uses a function) and will run slow, while the second one is sargable, and will run much faster.
WHERE clauses that perform some function on a column are non-sargable. On the other hand, if you can rewrite the WHERE clause so that the column and function are separate, then the query can use an available index, greatly boosting performance. for example: 
Function Acts Directly on Column, and Index Cannot Be Used:
SELECT member_number, first_name, last_name
FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 

Function Has Been Separated From Column, and an Index Can Be Used: 

SELECT member_number, first_name, last_name
FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
Each of the above queries produces the same results, but the second query will use an index because the function is not performed directly on the column, as it is in the first example. The moral of this story is to try to rewrite WHERE clauses that have functions so that the function does not act directly on the column.
WHERE clauses that use NOT are not sargable, but can often be rewritten to remove the NOT from the WHERE clause, for example:
WHERE NOT column_name > 5
to
WHERE column_name <= 5
Each of the above clauses produce the same results, but the second one is sargable. If you don't know if a particular WHERE clause is sargable or non-sargable, check out the query's execution plan in Query Analyzer. Doing this, you can very quickly see if the query will be using index lookups or table/index scans to return your results. 

With some careful analysis, and some clever thought, many non-sargable queries can be written so that they are sargable. Your goal for best performance (assuming it is possible) is to get the left side of a search condition to be a single column name, and the right side an easy to look up value. [6.5, 7.0, 2000] 

If you run into a situation where a WHERE clause is not sargable because of the use of a function on the right side of an equality sign (and there is no other way to rewrite the WHERE clause), consider creating an index on a computed column instead. This way, you avoid the non-sargable WHERE clause altogether, using the results of the function in your WHERE clause instead. Because of the additional overhead required for indexes on computed columns, you will only want to do this if you need to run this same query over and over in your application, thereby justifying the overhead of the indexed computed column. [2000]

If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

• Use EXISTS or NOT EXISTS
• Use IN
• Perform a LEFT OUTER JOIN and check for a NULL condition

[6.5, 7.0, 2000] 

When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is usually more efficient and performs faster. [6.5, 7.0, 2000]

If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you use an IN or OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index. 
For example
SELECT * FROM tblTaskProcesses WHERE nextprocess = 1 AND processid IN (8,32,45)
takes about 3 seconds, while:
SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45) returns in under a second. [7.0, 2000]
If you use LIKE in your WHERE clause, try to use one or more leading character in the clause, if at all possible. For example, use:
LIKE 'm%' 
not: 
LIKE '%m'
If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query, speeding performance and reducing the load on SQL Server.
But if the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a table scan must be run, reducing performance and taking more time.
The more leading characters you can use in the LIKE clause, the more likely the Query Optimizer will find and use a suitable index. [6.5, 7.0, 2000] 

If your application needs to retrieve summary data often, but you don't want to have the overhead of calculating it on the fly every time it is needed, consider using a trigger that updates summary values after each transaction into a summary table.
While the trigger has some overhead, overall, it may be less that having to calculate the data every time the summary data is needed. You may have to experiment to see which methods is fastest for your environment. [6.5, 7.0, 2000] 

If your application needs to insert a large binary value into an image data column, perform this task using a stored procedure, not using an INSERT statement embedded in your application.
The reason for this is because the application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time). 

Using a stored procedure avoids all this because all the activity occurs on the SQL Server, and little data is transmitted over the network. [6.5, 7.0, 2000] 

When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. For example:
SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004)
is much less efficient than this:
SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004
Assuming there is a useful index on customer_number, the Query Optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause (which is really just another form of the OR clause). [6.5, 7.0, 2000] 

If possible, try to avoid using the SUBSTRING function in your WHERE clauses. Depending on how it is constructed, using the SUBSTRING function can force a table scan instead of allowing the optimizer to use an index (assuming there is one). If the substring you are searching for does not include the first character of the column you are searching for, then a table scan is performed.
If possible, you should avoid using the SUBSTRING function and use the LIKE condition instead, for better performance.
Instead of doing this:
WHERE SUBSTRING(column_name,1,1) = 'b'
Try using this instead:
WHERE column_name LIKE 'b%'
If you decide to make this choice, keep in mind that you will want your LIKE condition to be sargable, which means that you cannot place a wildcard in the first position. [6.5, 7.0, 2000] 

Where possible, avoid string concatenation in your Transact-SQL code, as it is not a fast process, contributing to overall slower performance of your application. [6.5, 7.0, 2000] 

Generally, avoid using optimizer hints in your queries. This is because it is generally very hard to outguess the Query Optimizer. Optimizer hints are special keywords that you include with your query to force how the Query Optimizer runs. If you decide to include a hint in a query, this forces the Query Optimizer to become static, preventing the Query Optimizer from dynamically adapting to the current environment for the given query. More often than not, this hurts, not helps performance.
If you think that a hint might be necessary to optimize your query, be sure you first do all of the following first:

• Update the statistics on the relevant tables.
• If the problem query is inside a stored procedure, recompile it.
• Review the search arguments to see if they are sargable, and if not, try to rewrite them so that they are sargable.
• Review the current indexes, and make changes if necessary.

If you have done all of the above, and the query is not running as you expect, then you may want to consider using an appropriate optimizer hint. If you haven't heeded my advice and have decided to use some hints, keep in mind that as your data changes, and as the Query Optimizer changes (through service packs and new releases of SQL Server), your hard-coded hints may no longer offer the benefits they once did. So if you use hints, you need to periodically review them to see if they are still performing as expected. [6.5, 7.0, 2000]
If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written. This assumes that no parenthesis have been used to change the order of execution. Because of this, you may want to consider one of the following when using AND:

• Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.
• If both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression. 

You may want to consider using Query Analyzer to look at the execution plans of your queries to see which is best for your situation. [6.5, 7.0, 2000]
If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:

• Of the search criterions in the WHERE clause, at least one of them should be based on a highly selective column that has an index.
• If at least one of the search criterions in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.
• If none of the column in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query. 
  [7.0, 2000]

The Query Optimizer will perform a table scan or a clustered index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or does not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column in the WHERE clause has a useful index. [7.0, 2000]
A query with one or more OR clauses can sometimes be rewritten as a series of queries that are combined with a UNION ALL statement, in order to boost the performance of the query. For example, let's take a look at the following query:
SELECT employeeID, firstname, lastname
FROM names
WHERE dept = 'prod' or city = 'Orlando' or division = 'food'
This query has three separate conditions in the WHERE clause. In order for this query to use an index, then there must be an index on all three columns found in the WHERE clause.
This same query can be written using UNION ALL instead of OR, like this example:
SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod'UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando'UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE division = 'food'
Each of these queries will produce the same results. If there is only an index on dept, but not the other columns in the WHERE clause, then the first version will not use any index and a table scan must be performed. But in the second version of the query will use the index for part of the query, but not for all of the query.
Admittedly, this is a very simple example, but even so, it does demonstrate how rewriting a query can affect whether or not an index is used or not. If this query was much more complex, then the approach of using UNION ALL might be must more efficient, as it allows you to tune each part of the index individually, something that cannot be done if you use only ORs in your query.
Note, that I am using UNION ALL instead of UNION. The reason for this is to prevent the UNION statement from trying to sort the data and remove duplicates, which hurts performance. Of course, if there is the possibility of duplicates, and you want to remove them, then of course you can use just UNION.
If you have a query that uses ORs and it not making the best use of indexes, consider rewriting it as a UNION ALL, and then testing performance. Only through testing can you be sure that one version of your query will be faster than another. [7.0, 2000]
Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead. For example, perhaps it may be more efficient to sort the data at the client than at the server. In other cases, perhaps the client doesn't even need sorted data to achieve its goal. The key here is to remember that you shouldn't automatically sort data, unless you know it is necessary. [6.5, 7.0, 2000]
Whenever SQL Server has to perform a sorting operation, additional resources have to be used to perform this task. Sorting often occurs when any of the following Transact-SQL statements are executed:

• ORDER BY
• GROUP BY
• SELECT DISTINCT
• UNION
• CREATE INDEX (generally not as critical as happens much less often)

In many cases, these commands cannot be avoided. On the other hand, there are few ways that sorting overhead can be reduced. These include:

• Keep the number of rows to be sorted to a minimum. Do this by only returning those rows that absolutely need to be sorted.
• Keep the number of columns to be sorted to the minimum. In other words, don't sort more columns that required.
• Keep the width (physical size) of the columns to be sorted to a minimum.
• Sort column with number datatypes instead of character datatypes.
  When using any of the above Transact-SQL commands, try to keep the above performance-boosting suggestions in mind. [6.5, 7.0, 2000]

If you have to sort by a particular column often, consider making that column a clustered index. This is because the data is already presorted for you and SQL Server is smart enough not to resort the data. [6.5, 7.0, 2000]
If your SELECT statement includes an IN operator along with a list of values to be tested in the query, order the list of values so that the most frequently found values are placed at the first of the list, and the less frequently found values are placed at the end of the list. This can speed performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes. [6.5, 7.0, 2000]
If you need to use the SELECT INTO option, keep in mind that it can lock system tables, preventing others users from accessing the data they need. If you do need to use SELECT INTO, try to schedule it when your SQL Server is less busy, and try to keep the amount of data inserted to a minimum. [6.5, 7.0, 2000]
If your SELECT statement contains a HAVING clause, write your query so that the WHERE clause does most of the work (removing undesired rows) instead of the HAVING clause do the work of removing undesired rows. Using the WHERE clause appropriately can eliminate unnecessary rows before they get to the GROUP BY and HAVING clause, saving some unnecessary work, and boosting performance.
For example, in a SELECT statement with WHERE, GROUP BY, and HAVING clauses, here's what happens. First, the WHERE clause is used to select the appropriate rows that need to be grouped. Next, the GROUP BY clause divides the rows into sets of grouped rows, and then aggregates their values. And last, the HAVING clause then eliminates undesired aggregated groups. If the WHERE clause is used to eliminate as many of the undesired rows as possible, this means the GROUP BY and the HAVING clauses will have less work to do, boosting the overall performance of the query. [6.5, 7.0, 2000]
If your application performs many wildcard (LIKE %) text searches on CHAR or VARCHAR columns, consider using SQL Server's full-text search option. The Search Service can significantly speed up wildcard searches of text stored in a database. [7.0, 2000]
The GROUP BY clause can be used with or without an aggregate function. But if you want optimum performance, don't use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster.
For example, you could write your query two different ways:
USE Northwind
SELECT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
GROUP BY OrderID
or
USE Northwind
SELECT DISTINCT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
Both of the above queries produce the same results, but the second one will use less resources and perform faster. [6.5, 7.0, 2000]
The GROUP BY clause can be sped up if you follow these suggestion:

• Keep the number of rows returned by the query as small as possible.
• Keep the number of groupings as few as possible.
• Don't group redundant columns.
• If there is a JOIN in the same SELECT statement that has a GROUP BY, try to rewrite the query to use a subquery instead of using a JOIN. If this is possible, performance will be faster. If you have to use a JOIN, try to make the GROUP BY column from the same table as the column or columns on which the set function is used.

Consider adding an ORDER BY clause to the SELECT statement that orders by the same column as the GROUP BY. This may cause the GROUP BY to perform faster. Test this to see if is true in your particular situation. 
[7.0, 2000]
Sometimes perception is more important that reality. For example, which of the following two queries is the fastest:

• A query that takes 30 seconds to run, and then displays all of the required results.
• A query that takes 60 seconds to run, but displays the first screen full of records in less than 1 second.

Most DBAs would choose the first option as it takes less server resources and performs faster. But from many user's point-of-view, the second one may be more palatable. By getting immediate feedback, the user gets the impression that the application is fast, even though in the background, it is not.
If you run into situations where perception is more important than raw performance, consider using the FAST query hint. The FAST query hint is used with the SELECT statement using this form:
OPTION(FAST number_of_rows)
where number_of_rows is the number of rows that are to be displayed as fast as possible.
When this hint is added to a SELECT statement, it tells the Query Optimizer to return the specified number of rows as fast as possible, without regard to how long it will take to perform the overall query. Before rolling out an application using this hint, I would suggest you test it thoroughly to see that it performs as you expect. You may find out that the query may take about the same amount of time whether the hint is used or not. If this the case, then don't use the hint. [7.0, 2000]
Instead of using temporary tables, consider using a derived table instead. A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, we can reduce I/O and boost our application's performance. [7.0, 2000]
SQL Server 2000 offers a new data type called "table." Its main purpose is for the temporary storage of a set of rows. A variable, of type "table," behaves as if it is a local variable. And like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared. In most cases, a table variable can be used like a normal table. SELECTs, INSERTs, UPDATEs, and DELETEs can all be made against a table variable.
For best performance, if you need a temporary table in your Transact-SQL code, try to use a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them much faster. In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000]
It is fairly common request to write a Transact-SQL query to to compare a parent table and a child table and find out if there are any parent records that don't have a match in the child table. Generally, there are three ways this can be done:
Using a NOT EXISTS
SELECT a.hdr_key
FROM hdr_tbl a
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
Using a Left Join
SELECT a.hdr_key
FROM hdr_tbl a
LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key
WHERE b.hdr_key IS NULL
Using a NOT IN
SELECT hdr_key
FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
In each case, the above query will return identical results. But, which of these three variations of the same query produces the best performance? Assuming everything else is equal, the best performing version through the worst performing version will be from top to bottom, as displayed above. In other words, the NOT EXISTS variation of this query is generally the most efficient.
I say generally, because the indexes found on the tables, along with the number of rows in each table, can influence the results. If you are not sure which variation to try yourself, you can try them all and see which produces the best results in your particular circumstances. [7.0, 2000] 

Be careful when using OR in your WHERE clause, it is fairly simple to accidentally retrieve much more data than you need, which hurts performance. For example, take a look at the query below:
SELECT companyid, plantid, formulaid
FROM batchrecords
WHERE companyid = '0001' and plantid = '0202' and formulaid = '39988773'
OR

companyid = '0001' and plantid = '0202'
As you can see from this query, the WHERE clause is redundant, as:
companyid = '0001' and plantid = '0202' and formulaid = '39988773' 
is a subset of:
companyid = '0001' and plantid = '0202'
In other words, this query is redundant. Unfortuantely, the SQL Server Query Optimizer isn't smart enough to know this, and will do exactly what you tell it to. What will happen is that SQL Server will have to retrieve all the data you have requested, then in effect do a SELECT DISTINCT to remove redundant rows it unnecessarily finds. 

In this case, if you drop this code from the query: 

OR
companyid = '0001' and plantid = '0202' 

then run the query, you will receive the same results, but with much faster performance. [6.5, 7.0, 2000] 

If you need to verify the existence of a record in a table, don't use SELECT COUNT(*) in your Transact-SQL code to identify it, which is very inefficient and wastes server resources. Instead, use the Transact-SQL IF EXITS to determine if the record in question exits, which is much more efficient. For example:

Here's how you might use COUNT(*):
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
Here's a faster way, using IF EXISTS:
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
The reason IF EXISTS is faster than COUNT(*) is because the query can end immediately when the text is proven true, while COUNT(*) must count go through every record, whether there is only one, or thousands, before it can be found to be true. [7.0, 2000] 

Let's say that you often need to INSERT the same value into a column. For example, perhaps you have to perform 100,000 INSERTs a day into a particular table, and that 90% of the time the data INSERTed into one of the columns of the table is the same value.
If this the case, you can reduce network traffic (along with some SQL Server overhead) by creating this particular column with a default value of the most common value. This way, when you INSERT your data, and the data is the default value, you don't INSERT any data into this column, instead allowing the default value to automatically be filled in for you. But when the value needs to be different, you will of course INSERT that value into the column. [6.5, 7.0, 2000] 

Performing UPDATES takes extra resources for SQL Server to perform. When performing an UPDATE, try to do as many of the following recommendations as you can in order to reduce the amount of resources required to perform an UPDATE. The more of the following suggestions you can do, the faster the UPDATE will perform.

• If you are UPDATing a column of a row that has an unique index, try to only update one row at a time.
• Try not to change the value of a column that is also the primary key.
• When updating VARCHAR columns, try to replace the contents with contents of the same length.
• Try to minimize the UPDATing of tables that have UPDATE triggers.
• Try to avoid UPDATing columns that will be replicated to other databases.
• Try to avoid UPDATing heavily indexed columns.
• Try to avoid UPDATing a column that has a reference in the WHERE clause to the column being updated.

Of course, you may have very little choice when UPDATing your data, but at least give the above suggestions a thought. [6.5, 7.0, 2000] 

If you have created a complex transaction that includes several parts, one part of which has a higher probability of rolling back the transaction than the others, better performance will be provided if you locate the most likely to fail part of the transaction at the front of the greater transaction. This way, if this more-likely-to-fail transaction has to roll back because of a failure, there has been no resources wasted on the other less-likely-to-fail transactions. [6.5, 7.0, 2000]

SQL 效能相關 II

摘自 http://www.wretch.cc/blog/hcu16b/10264132


有些程式員在撰寫前端的應用程式時，會透過各種 OOP 語言將存取資料庫的 SQL 陳述式串接起來，卻忽略了 SQL 語法的效能問題。版工曾聽過某半導體大廠的新進程式員，所兜出來的一段 PL/SQL 跑了好幾分鐘還跑不完；想當然爾，即使他前端的 AJAX 用得再漂亮，程式效能頂多也只是差強人意而已。以下是版工整理出的一些簡單心得，讓長年鑽究 ASP.NET / JSP / AJAX 等前端應用程式，卻無暇研究 SQL 語法的程式員，避免踩到一些 SQL 的效能地雷。


1、資料庫設計與規劃

• Primary Key 欄位的長度儘量小，能用 small integer 就不要用 integer。例如員工資料表，若能用員工編號當主鍵，就不要用身分證字號。

• 一般欄位亦同。若該資料表要存放的資料不會超過 3 萬筆，用 small integer 即可，不必用 integer。

• 文字資料欄位若長度固定，如：身分證字號，就不要用 varchar 或 nvarchar，應該用 char 或 nchar。

• 文字資料欄位若長度不固定，如：地址，則應該用 varchar 或 nvarchar。除了可節省儲存空間外，存取磁碟時也會較有效率。

• 設計欄位時，若其值可有可無，最好也給一個預設值，並設成「不允許 NULL」(一般欄位預設為「允許 NULL」)。因為 SQL Server 在存放和查詢有 NULL 的資料表時，會花費額外的運算動作 [2]。

• 若一個資料表的欄位過多，應垂直切割成兩個以上的資料表，並用同名的 Primary Key 一對多連結起來，如：Northwind 的 Orders、Order Details 資料表。以避免在存取資料時，以叢集索引掃描時會載入過多的資料，或修改資料時造成互相鎖定或鎖定過久。

------------------------------

2、適當地建立索引

• 記得自行幫 Foreign Key 欄位建立索引，即使是很少被 JOIN 的資料表亦然。

• 替常被查詢或排序的欄位建立索引，如：常被當作 WHERE 子句條件的欄位。

• 用來建立索引的欄位，長度不宜過長，不要用超過 20 個位元組的欄位，如：地址。

• 不要替內容重複性高的欄位建立索引，如：性別；反之，若重複性低的欄位則適合建立索引，如：姓名。

• 不要替使用率低的欄位建立索引。

• 不宜替過多欄位建立索引，否則反而會影響到新增、修改、刪除的效能，尤其是以線上交易 (OLTP) 為主的網站資料庫。

• 若資料表存放的資料很少，就不必刻意建立索引。否則可能資料庫沿著索引樹狀結構去搜尋索引中的資料，反而比掃描整個資料表還慢。

• 若查詢時符合條件的資料很多，則透過「非叢集索引」搜尋的效能，可能反而不如整個資料表逐筆掃描。

• 建立「叢集索引」的欄位選擇至為重要，會影響到整個索引結構的效能。要用來建立「叢集索引」的欄位，務必選擇「整數」型別 (鍵值會較小)、唯一、不可為 NULL。

------------------------------

3、適當地使用索引

• 有些書籍會提到，使用 LIKE、% 做模糊查詢時，即使您已替某個欄位建立索引 (如下方例子的 CustomerID)，但以常數字元開頭才會使用到索引，若以萬用字元 (%) 開頭則不會使用索引，如下所示：

USE Northwind;
GO
SELECT * FROM Orders WHERE CustomerID LIKE 'D%'; --使用索引
SELECT * FROM Orders WHERE CustomerID LIKE '%D'; --不使用索引


執行完成後按 Ctrl+L，可檢閱如下圖的「執行計畫」。


圖 1　可看出「查詢最佳化程式」有使用到索引做搜尋


圖 2　在此的叢集索引掃描，並未直接使用索引，效能上幾乎只等於掃描整個資料表

但經版工反覆測試，這種語法是否會使用到索引，抑或會逐筆掃描，並非絕對的。仍要看所下的查詢關鍵字，以及欄位內儲存的資料內容而定。但對於儲存資料筆數龐大的資料表，最好還是少用 LIKE 做模糊查詢。


• 以下的運算子會造成「負向查詢」，常會讓「查詢最佳化程式」無法有效地使用索引，最好能用其他運算子和語法改寫 (經版工測試，並非有負向運算子，就絕對無法使用索引)：
NOT 、 != 、 <> 、 !> 、 !< 、 NOT EXISTS 、 NOT IN 、 NOT LIKE

• 避免讓 WHERE 子句中的欄位，去做字串串接或數字運算，否則可能導致「查詢最佳化程式」無法直接使用索引，而改採叢集索引掃描 (經版工測試並非絕對)。

• 資料表中的資料，會依照「叢集索引」欄位的順序存放，因此當您下 BETWEEN、GROUP BY、ORDER BY 時若有包含「叢集索引」欄位，由於資料已在資料表中排序好，因此可提升查詢速度。

• 若使用「複合索引」，要注意索引順序上的第一個欄位，才適合當作過濾條件。

------------------------------

4、避免在 WHERE 子句中對欄位使用函數

對欄位使用函數，也等於對欄位做運算或串接的動作，一樣可能會讓「查詢最佳化程式」無法有效地使用索引。但真正對效能影響最重大的，是當您的資料表內若有 10 萬筆資料，則在查詢時就需要呼叫函數 10 萬次，這點才是真正的效能殺手。程式員應注意，在系統開發初期可能感覺不出差異，但當系統上線且資料持續累積後，這些語法細節所造成的效能問題就會逐步浮 現。

SELECT * FROM Orders WHERE DATEPART(yyyy, OrderDate) = 1996 AND DATEPART(mm, OrderDate)=7
可改成
SELECT * FROM Orders WHERE OrderDate BETWEEN '19960701' AND '19960731'



SELECT * FROM Orders WHERE SUBSTRING(CustomerID, 1, 1) = 'D'
可改成
SELECT * FROM Orders WHERE CustomerID LIKE 'D%'

注意當您在下 UPDATE、DELETE 陳述式時，若有採用 WHERE 子句，也應符合上述原則。

------------------------------

5、AND 與 OR 的使用

在 AND 運算中，「只要有一個」條件有用到索引 (如下方的 CustomerID)，即可大幅提升查詢速度，如下圖 3 所示：

SELECT * FROM Orders WHERE CustomerID='VINET' AND Freight=32.3800 --使用索引，會出現下圖 3 的畫面


SELECT * FROM Orders WHERE Freight=32.3800 --不使用索引，會出現上圖 2 的畫面


圖 3


但在 OR 運算中，則要「所有的」條件都有可用的索引，才能使用索引來提升查詢速度。因此 OR 運算子的使用必須特別小心。

若您將上方 AND 的範例，邏輯運算子改成 OR 的話，如下所示：

SELECT * FROM Orders WHERE CustomerID='VINET' OR Freight=32.3800


由於無法有效地使用索引，也會出現圖 2 的畫面。

在使用 OR 運算子時，只要有一個條件 (欄位) 沒有可用的索引，則其他所有的條件 (欄位) 都有索引也沒用，只能如圖 2 般，把整個資料表或整個叢集索引都掃描過，以逐筆比對是否有符合條件的資料。


據網路上文件的說法 [1]，上述的 OR 運算陳述式，我們還可用 UNION 聯集適當地改善，如下：

SELECT * FROM Orders WHERE CustomerID='VINET'
UNION
SELECT * FROM Orders WHERE Freight=32.3800


此時您再按 Ctrl+L 檢閱「執行計畫」，會發現上半段的查詢會使用索引，但下半段仍用叢集索引掃描，對效能不無小補。

------------------------------

6、適當地使用子查詢

相較於「子查詢 (Subquery)」，若能用 JOIN 完成的查詢，一般會比較建議使用後者。原因除了 JOIN 的語法較容易理解外，在多數的情況下，JOIN 的效能也會比子查詢較佳；但這並非絕對，也有的情況可能剛好相反。

我們知道子查詢可分為「獨立子查詢」和「關聯子查詢」兩種，前者指子查詢的內容可單獨執行，後者則無法單獨執行，亦即外層查詢的「每一次」查詢動作都需要引用內層查詢的資料，或內層查詢的「每一次」查詢動作都需要參考外層查詢的資料。

以下我們看一個比較極端的例子 [2]。若我們希望所有查詢出來的資料，都能另外給一個自動編號，版工我在之前的文章「用 SQL Server 2005 新增的 ROW_NUMBER 函數撰寫 GridView 分頁」中有介紹過，可用 SQL Server 2005 中新增的 ROW_NUMBER 函數輕易地達成，且 ROW_NUMBER 函數還能再加上「分群 (PARTITION BY)」等功能，而且執行效能極佳。


圖 4　將 Orders 資料表的 830 筆資料都撈出來，並在右側給一組自動編號

現在我們要如上圖 4 般，將 Northwind 中 Orders 資料表的 830 筆資料都撈出來，並自動給一組編號，若用 ROW_NUMBER 函數的寫法如下所示，而且效能極佳，只要 2 ms (毫秒)，亦即千分之二秒。

SET STATISTICS TIME ON
SELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS 編號
FROM dbo.Orders


但如果是在舊版的 SQL Server 2000 中，我們可能得用以下的「子查詢」寫法：

SET STATISTICS TIME ON
SELECT OrderID,
(SELECT COUNT(*) FROM dbo.Orders AS 內圈
WHERE 內圈.OrderID <= 外圈.OrderID) AS 編號
FROM dbo.Orders AS 外圈
ORDER BY 編號

但這種舊寫法，會像先前所提到的，外層查詢的「每一次」查詢動作都需要引用內層查詢的資料。以上方例子而言，外層查詢的每一筆資料，都要等內層查詢「掃描 整個資料表」並作比對和計數，因此 830 筆資料每一筆都要重複掃描整個資料表 830 次，所耗用的時間也因此爆增至 170 ms。

若您用相同的寫法，去查詢 AdventureWorks 資料庫中，有 31,465 筆資料的 Sales.SalesOrderHeader 資料表，用 ROW_NUMBER 函數要 677 ms，還不到 1 秒鐘；但用子查詢的話，居然要高達 225,735 ms，將近快 4 分鐘的時間。

雖然這是較極端的範例，但由此可知子查詢的撰寫，在使用上不可不慎，尤其是「關聯子查詢」。程式員在程式開發初期、資料量還很少時感受不到此種 SQL 語法的重大陷阱；但等到系統上線幾個月或一兩年後，可能就會有反應遲緩的現象。

------------------------------

7、其他查詢技巧

• DISTINCT、ORDER BY 語法，會讓資料庫做額外的計算。此外聯集的使用，若沒有要剔除重複資料的需求，使用 UNION ALL 會比 UNION 更佳，因為後者會加入類似 DISTINCT 的演算法。

• 在 SQL Server 2005 版本中，存取資料庫物件時，最好明確指定該物件的「結構描述 (Schema)」，也就是使用兩節式名稱。否則若呼叫者的預設 Schema 不是 dbo，則 SQL Server 在執行時，會先尋找該使用者預設 Schema 所搭配的物件，找不到的話才會轉而使用預設的 dbo，會多耗費尋找的時間。例如若要執行一個叫做 dbo.mySP1 的 Stored Procedure，應使用以下的兩節式名稱：

EXEC dbo.mySP1

------------------------------

8、儘可能用 Stored Procedure 取代前端應用程式直接存取資料表

Stored Procedure 除了經過事先編譯、效能較好以外，亦可節省 SQL 陳述式傳遞的頻寬，也方便商業邏輯的重複使用。再搭配自訂函數和 View 的使用，將來若要修改資料表結構、重新切割或反正規化時亦較方便。

------------------------------

9、儘可能在資料來源層，就先過濾資料

使用 SELECT 語法時，儘量避免傳回所有的資料至前端而不設定 WHERE 等過濾條件。雖然 ASP.NET 中 SqlDataSource、ObjectDataSource 控制項的 FilterExpression 可再做篩選，GridView 控制項的 SortExpression 可再做排序，但會多消耗掉資料庫的系統資源、Web server 的記憶體和網路頻寬。最好還是在資料庫和資料來源層，就先用 SQL 條件式篩選出所要的資料。

------------------------------

結論：
本文的觀念，不管是寫 SQL statement、Stored Procedure、自訂函數或 View 皆然。本文只是挑出程式員較容易犯的 SQL 語法效能問題，以期能在短時間瀏覽過本文後，在寫 ADO.NET 程式時能修正以往隨興的 SQL 撰寫習慣。文中提到的幾點，只不過是 SQL 語法效能議題的入門篇。後續有時間的話，版工會再補充在本帖的回應留言，或另開新主題。

SQL 效能相關

摘自 ITHOME

1.建議先使用SQL PROFILE去錄製語法，然後再丟到database engine tuning advisor
去分析，並參考SQL所建議的方式(大都是叫你建INDEX)一個資料表建議最多不要超過8個 INDEX。
2.stored procedure可以使用
3.開始收集相關的監控資訊
4.使用執行計劃去分析語法，改寫語法，避免TABLE SCAN。
5.不要對資料欄位做運算，LIKE使用方法將會造成資料表掃描而減低效能,叢集索引掃描 CLUSTERED INDEX SCAN與資料表掃描(Table Scan)幾近相同,沒有什麼效率可言
儘量避免使用<>的運算子,該運算子會造成查詢速度的下降
非必要少用NOT BETWEEN,因會該算子會成系統執行效能的下降,NOT IN也是
6.T-SQL使用SARG的寫法
7.勿在where子句對欄位使用函數，對資料欄位使用函數當然是在對資欄位做運算,所以這些都不算是SARG, 使用函數後SQL SERVER需要將資料表內所有紀錄的相關欄位輸入到函數中,若有100萬筆紀錄就需要呼叫函數100萬次,這將是效能殺手，建立完全不作事的函數，僅大 量呼叫函數，本身就耗資源
8.小心使用OR運算子，在AND運算中,只要一個子句有合適的索引就可以大幅提升查詢的速度

在使用OR布林運算子時,多個條中若有一個條件沒有合適的索引,則其他再多的欄位有索引也沒有月,只有整個資料表或是叢集索引掃描一遍,以確定全部的資料是否有符合的記錄

不要認為只要負向運算子出現在查詢中,SQL SERVER就一定無法使用索引.在WHERE條件中使用非SARG並不一定導致資料表掃描.SQL SERVER可以在某些非SARG狀況中使用索引,以及查詢中雖然包含了部份非SARG但仍可以對此查詢的SARG部份使用索引,但最好還是使用SARG
9.可以利用索引的搜尋條件
=
>
>=
<
<=
Between
Like ‘xx%’
10.無法利用索引的搜尋條件
<>
!=
!>
!<
OR
NOT IN
NOT LIKE IN
NOT EXIST
LIKE ‘%xx’
10.SELECT * 儘量少用，會造成無謂提供給應用程式多餘的資料行
因為資料表的結構改變,造成應用程式抓取資料行次序錯誤
會造成資料庫引擎要先搜尋所有的資料行,再進行作業,浪費執行時間
會造成無法使用索引進行資料查詢,降低效能
會造成文件不清楚,解讀文件時無法從中得知明確資料行名稱。
11.適當的使用子查詢雖然可以加速整個程式撰寫的速度,但是過度使用子查詢的下場,就是會拖垮整個查詢的效能，首選的方法就是使用合併聯結查詢的陳述式（INNER JOIN），聯結與子查詢可以達到相同的執行結果，但效能卻不一定相同。
12.將經常要查詢或更新的資料表，指定存放於不同磁碟組的檔案群組，將非叢集索引，指定存放於不同磁碟組的檔案群組，將常用的現有資料與歷史資料分割儲存至不同的資料表，並指定存放於不同磁碟組的檔案群組，或考慮採用分割資料表與資料壓縮
13.Tempdb之用途
暫存資料表、子查詢、HASH JOIN、ORDER BY、GROUP BY、SELECT DISTINCT、快照式交易隔離等級、線上索引維護作業…
大量使用 Tempdb 時之設定，確保 Tempdb 有足夠的資料檔大小，將 Tempdb 的資料檔指定至不同的磁碟組，如果是 SQL Server 有多個 CPU 時，建議 Tempdb 的資料檔個數與 CPU 核心數相同。
14.必要的監控資訊CPU

Process(_Total)\% Processor Time :

CPU執行非閒置執行緒的時間百分比，常用來觀察CPU使用的情形。其計算方式是:先計算在某個取樣區間，處理器執行閒置處理程序所花費的時間，減去這個時間後，再換算成百分率。

CPU

Processor\%Privileged Time: CPU執行Windows 核心指令的時間百分比，例如:處理SQL Server I/O時，當Physical Disk計數器值很高時，Privileged Time數值相對提高，可以考慮換用較有效率的磁碟子系統，提高整體的處理能力。
CPU

Processor\%User Time: 相對於處理器用於執行使用者處理序(如SQL SERVER)的時間百分比
CPU

System\Processor Queue Length 等候處理器時間的執行緒數目。當執行緒所需的處理器循環超過可用數量時，將會形成處理器瓶頸。若許多處理序都在等待處理器時間，可能需要安裝較快或額外的 處理器。假如連續監控的過程中，此數值長時間達處理器數量2倍以上，則可能面臨處理器瓶頸，例如:一台資料庫伺服器有四顆CPU，則整體值不應超過8。
Memory

Memory\Available MBytes

當下仍閒置可立即使用的實體記憶體總數
Memory

Memory\Committed Bytes 已使用的虛擬記憶體數量，其值為使用中的實體記憶體加上虛擬記憶體的總量，單位是位元組。如果數量超過系統中實體記憶體的數量，則代表需要Hard Page Fault機制運作，也就是大量實體記憶體與硬碟之間的資料切換。
Memory

Memory\Pages/sec

代表hard Page Faults的數量，每秒產生多少分頁到硬碟的虛擬記憶體。其值是Pages Input/Sec 和Pages Output/sec 二個計數器的總和。它具有指標意義，若有越多分頁發生，表示主機是處於繁忙的狀態，所以不該長時間處於很大的值，平均值建議介於0-20之間。有值不一定 代表有問題，因為WINDOWS有很多的機制在自我調節時都會做分頁切換，如系統對檔案和網路的存取。如果某個程式已經十分忙錄，但是該程式還有大量的分 頁切換，就表示問題。
PhysicalDisk

PhysicalDisk(_Total)\% Disk Time

監看磁碟處理讀取/寫入活動的時間百分比。若%DISK TIME計數器值很高超過85%，請檢視Physical DISK : Current Disk Queue Length計數器來觀察有多少系統要求(System Request)正在等候磁碟存取。
PhysicalDisk

PhysicalDisk\Avg.Disk Queue Length

等候的I/Q要求數不應持續超過實體磁碟所包含讀寫頭數的1.5~2倍。大多數的磁碟都有一個讀寫頭，而通常磁碟陣列RAID裝置則有多個讀寫頭。硬體RAID裝置在系統監控中將顯示成一台實體磁碟；而透過軟體建立的RAID裝置則顯示成多重執行個體。
SQLServer

Buffer Manager\Buffer cache hit ratio

通常SQL Server在執行時，100%直接由記憶體取得資料分頁。通常這個值超過95%代表記憶體足夠。
可在記憶體的快取找到資料分頁，而不需要讀取磁碟的百分比。輸出值應該盡可能的接近100%，但一般來說，大於90%就是可接受的範圍了；90%或低於90%代表SQL Server作業已受限於記憶體的限制了。
SQL Server Access Methods\Full Scans / sec 每次完整掃描數次數，可能是資料表或完整索引掃描。若輸出明顯高於平常所觀測的基準線。若輸出值明顯高於平常所觀測的基準線，則可能需要留意。
SQLServer Databases\Transaction/sec 每秒針對資料庫啟動的交易數。若輸出值明顯高於平常所觀測的基準線，則資料庫活動可能會有問題。
SQLServer General Statistics\User Connections 計算目前已連接SQL Server的使用者數目。若輸出的值明顯高於基準線，則使用者的活動增加，將導致SQL Server的效能惡化。
SQLServer

Databases(_Total)\Percent Log Used

使用中的記錄檔空間百分比。
SQLServer

Log Growths 資料庫之交易記錄檔的擴大總次數。
SQLServer

Databases(_Total)\Data File(s) Size (KB)

資料庫內所有的資料檔總計大小，包含任何自動的成長。
SQLServer

SQL Errors(User Errors)\*



每秒使用者產生的錯誤量，可藉此觀察資料庫的可用性。它包含引起SQL Server離線、觸發SQL SERVER 關閉連線、使用者錯誤等。
SQLServer

Locks(_Total)\Lock Waits/sec

使用者無法取得鎖定而需要等候的數目。若值明顯高於日常觀測的基準線，則可能有並行存取的問題。
SQLServer

Locks(_Total)\Number of Deadlocks/sec

每秒發生deadlock的數目，若值大於0，則使用者可能遭遇到查詢未完成，以及應用程式可能執行失敗。
SQLServer

Memory Manager\Total Server Memory (KB)

已配置給SQL SERVER的記憶體數量。當值等於這台機器的所有實記憶體的數量，可能會遭遇到資源競爭Contention的問題，因為作業系統較難配置記憶體來執行一般性作業。
SQLServer

SQL Statistics\SQL Re-Compilations/Sec 每秒SQL陳述式重新編譯的次數。若值明顯高於基準線，預存程序的撰寫方式可能不適合執行計畫的快取。
SQL SERVER User Settable\Query 最多提供10個由SP_USER_COUNTERX(其中X由1~10)系統預存程序產生的客製化計數器執行個體，這些計數器可以用來追蹤客製化的工作。

15.效能不好，處理速度慢，當然會有很多的LOCK，連線數無法快速消化相然也就變多了，PF也會跟著增加，升級硬體只能治標不能治本，改善語法才是最重要的，但須要很長的時間去做監控。
開SQL PROFILE會浪費主機約6分之1的效能。

資安硬體

fortigate
radware
tipping point
IPS
喜流量

債券兩三事

殖利率升高  ：  通膨升高、經濟展望好、升息、債券價格變低
殖利率降低  ：  通膨降低、經濟展望差、降息、債券價格變高

經濟展望差 ： 殖利率降低、債券價格變高
經濟展望好 ： 殖利率升高、債券價格變低

降息：殖利率降低、債券價格變高
(新債票面利率降低、舊債高票面利率也會使得舊債價格變高)

• 如 果市場利率持續下滑的話，已發行且債息相對較高的債券顯得較吸引人，因此該債券價格就會節節攀高，甚至會高過債券的面值，這時新的投資者通常要以高於面額 的價格，買入債券，而當這檔債券到期時，投資者僅會收到等同於債券面值的償還金額，所以當債券價格攀高時，新的投資者買入債券成本較高，使得其「殖利率」 即實質投資報酬率較之前下降

升息：殖利率升高、債券價格變低

• 當市場利率下跌，債券價格上漲；相反地，當市場利率上升，債券價格便會下跌。

殖利率對還沒持有債券卻想買的人來說當然是越高越好，因為可以賺的債息收益會比較高；

但是對於已經持有的人來說，代表的是手上的債券價值縮水，反而會希望殖利率越來越低。

如果市場利率持續下滑的話，已發行且債息相對較高的債券顯得較吸引人，因此該債券價格就會節節攀高，甚至會高過債券的面值，這時新的投資者通常要以高於面額的價格，買入債券，而當這檔債券到期時，投資者僅會收到等同於債券面值的償還金額，所以當債券價格攀高時，新的投資者買入債券成本較高，使得其「殖利率」即實質投資報酬率較之前下降


債券種類：  

美國財政部公債(政府公債)種類：

1. 國庫券(Treasury Bills)：到期日為90天至1年
2. 中期公債(Treasury Notes)：到期日為1年至10年
3. 長期公債(Treasury Bonds)：到期日為10年以上

上個月美國經濟數據墜落懸崖，經濟情勢宛如恐怖片。周三 (1 日) 美國公債價格飆升，殖利率大跌，刺激市場預期前景晦暗，除了美債以外的投資標的，如股票、期貨或其他高收益資產，恐陷落跌勢。

穆迪 〈 Moody ' s 〉 標準普爾 〈 S&P 〉 定義 Aaa AAA 品質極佳 可投資等級債等 Aa AA 品質佳 A A 品質優於平均 Baa BBB 品質中等 Ba BB 有些投機成分 高收益債券 B B 投機品質 Caa CCC 品質不佳 Ca CC 高度投機品質 C C 接近違約品質 - D 倒帳等級

http://wiki.mbalib.com/zh-tw/%E5%80%BA%E5%88%B8%E5%8F%91%E8%A1%8C%E5%88%A9%E7%8E%87

五段動詞表及動詞變化(五)

五段動詞表及動詞變化(五)

上接五段動詞表及動詞變化(四)

※第三類動詞(不規則動詞/サ変、カ変)：此類動詞只有兩個(来る以及する)。(暗記するしかない)

詳細變化請參照下表：

下表為(来ます(来る)/きます(くる))的各類變換：

こない/来ない (否定形)	き/きます (動詞連用/ます)	くる/来る (辭書形)	こい/来い (命令形)	こよう/来よう (意向形/未然形)
こられる/来られる (受身形/尊敬)	きて/きた (て形/た形)		くれば/来れば (假定形/ば形)
こさせる/来させる (使役形)			こられる/来られる (可能形)

下表為(します(する))的各類變換：

しない (否定形)	し/します (動詞連用/ます)	する (辭書形)	しろ (命令形)	しよう (意向形/未然形)
される (受身形/尊敬)	して/した (て形/た形)		すれば (假定形/ば形)
させる (使役形)			できる/できます (可能形)