SQL Server optimization
This is from reading a bunch of articles on http://www.sql-server-performance.com
Using Unicode double-byte datatypes such as NCHAR and NVARCHAR take up double (Duh!) the space, so avoid them unless you really need them.
1) Clustered Index Seek: A Clustered Index Seek uses the seeking ability of indexes to retrieve rows directly from a clustered index. In most cases, they provide the best performance on SELECT statements.
4) Scans: Scans (Table scans, Index scan, and Clustered Index scans) are usually bad unless the table has very few rows and the Query Optimizer determines that a table scan will outperform the use of an available index. Watch out for scans in your execution plans.
Index order plays an important role in many query plans. For example, in the authors table of the pubs database, a non-clustered index is defined in the order of au_lname, then au_fname.
-------------
Open Your Eyes (by DJ Encore feat. Engelina)
Fantasy Twins - check that out.
Using Unicode double-byte datatypes such as NCHAR and NVARCHAR take up double (Duh!) the space, so avoid them unless you really need them.
1) Clustered Index Seek: A Clustered Index Seek uses the seeking ability of indexes to retrieve rows directly from a clustered index. In most cases, they provide the best performance on SELECT statements.
2) Index Seek: An Index Seek uses a non-clustered index to retrieve data, and in some ways, acts like a clustered index. This is because all of the data retrieved is fetched from the leaf layer of the non-clustered index, not from any of the data pages. You often see this behavior in a covering index.
3) Bookmark Lookup: A Bookmark Lookup uses a non-clustered index to select the data. It starts with an index seek in the leaf nodes of the non-clustered index to identify the location of the data from the data pages, then it retrieves the necessary data directly from the data pages. Leaf nodes of non-clustered indexes contain row locator that point to the actual data on data pages.4) Scans: Scans (Table scans, Index scan, and Clustered Index scans) are usually bad unless the table has very few rows and the Query Optimizer determines that a table scan will outperform the use of an available index. Watch out for scans in your execution plans.
Index order plays an important role in many query plans. For example, in the authors table of the pubs database, a non-clustered index is defined in the order of au_lname, then au_fname.
In a small, simple database, one could simply poke around to locate these redundant indexes by using:
sp_helpindex 'myTable'An index on LastName, FirstName would fulfill the same function as an index on LastName alone, so it's not necessary to maintain both.
-------------
Open Your Eyes (by DJ Encore feat. Engelina)
Fantasy Twins - check that out.
posted by Eugene at
11:12 AM
0 Comments
