블로그 이미지
LifeisSimple

calendar

1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

Notice

2011. 10. 4. 21:06 Brain Trainning/DataBase


Problem
Indexing a SQL Server database in some respects is considered both an art and a science.  Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production?  Are these the same types of steps or not?  Do any best practices really exist when it comes to indexing?  Where does indexing fall in the priority list from an application or production support perspective?

Solution
You have a number of good questions related to indexing a SQL Server database.  The reality is that many of the decisions are situational, although some of the decisions should be approached from a best practices perspective then evaluated as they pertain to your specific environment.  Although many best practices do exist, application, business, technology, resource and time constraints can pose significant challenges to address the need, which is the fastest data access possible.

Art vs. Science

In terms of the art vs. science question, in many respects this is true in many circumstances.  The reality is that 100% of the time there is not 1 right answer.  What I have seen and done on a number of occasions is start off with the industry best practices approach and then based on the business, technology, application, etc. needs begin to figure out the best practices for the environment.  A single solution to solve all problems is not feasible, but the best solution is to be knowledgeable about the options available in SQL Server, have an open mind, test a variety of options, then select the best option based on the results not the theory or conjecture in that particular situation.

In terms of recommendations, it is best to have an open mind and a clear understanding of the data access code.  Hopefully this will enable you to choose some non-traditional options vs. traditional options to see what indexing strategy is going to best meet the need.  As an example, knowing that the database is accessed via a middle tier application that generates cursor based T-SQL code versus stored procedures with set based logic, could change how the database is indexed to ensure the lowest cost for your queries.  Understanding these intricacies is where the art portion of the equation can be essential.

New vs. Existing Systems

In some respects the process to identify and build the correct indexes is the same between a new versus existing database.  One key differentiator is a new system typically has a clean slate where an efficient database can be designed and built.  When you compare this to an existing system, they typically have a great deal of baggage and application intricacies may exist.  The reality is that these items are difficult to change without impacting a number of different applications.

Let's take a look at the process of identifying and\or correcting SQL Server indexes:

  • Review the business, technical, performance, etc. requirements.
  • Review the hardware to validate it is generally sufficient to meet the needs.
    • For example, make sure a 2 CPU machine with 1 GB of memory and a few disks is not supporting terabytes of data and thousands of users.  If this is the case, the hardware is probably inadequate in the first place.
    • Additional information - Hard Drive Configurations for SQL Server
  • Review the database design for primary keys, foreign keys, correct data types, reasonable usage of NULL, etc.
  • Validate that the system is not overloaded or taxed from a design perspective.
    • For example, the database design is not supporting OLTP, batch, reporting and analytical processing all via a single database design.
  • Review the current indexes to understand what is currently in place, to include:
  • Capture the most costly code with Profiler or a third party tool to focus on the most offensive queries.
  • Analyze the code and review the query plan to understand how the optimizer is processing the query, once this is understood start to think about various approaches to improve the query.
  • Take a big step back and think about what is functionally trying to be accomplished by the code and see if any new or different techniques are available to address the need.
  • Pull together as many reasonable options as possible to address the issue then begin to test each option and record the metrics for each approach.
    • Let the numbers speak for themselves and select the best performing options to meet the need.
    • Be sure that a change to the indexes in one query or one portion of the application will improve one area and not hurt another area.
    • Additional information - MSSQLTips Category - Testing
  • Deployment
    • Once the indexes have been finalized, deploy them and continue to monitor the performance for no unexpected results.

Index Best Practices

When in comes to best practices, in some respects I consider them a set of ground rules that should be used as a means to start the design and development process as opposed to an absolute set of rules that are always correct.  With that being said, let's outline some points of reference to use as a starting point to address your indexing needs:

  • Data Access - Determine how the data is being accessed (SELECT) and maintained (INSERT, UPDATE, DELETE) then index accordingly.  Keep in mind that data may not always be accessed in the same way, so you may need to prioritize or compromise to have the best net performance across the application.
  • Plan - Have a process to analyze the indexes versus haphazardly reviewing tables or T-SQL code to build your indexing strategy.  Start with a functional area or based on the application flow so no tables fall through the cracks.  Keep in mind that indexes on a subset of the tables do not make up for little or no indexes in another portion of the database design.
  • Index selection - As a starting point, be sure to identify a clustered index and one or more non clustered indexes for each table.  During the testing and validation phases, fine tune or eliminate the indexes based on the data access.
  • Covering indexes - Do not be afraid to have an index with numerous columns to improve the data access and potentially avoid bookmark lookup operations.  This topic is well covered in the Avoid Index Redundancy in SQL Server Tables tip.
  • ASC vs. DESC order - The order of the indexes can make a significant difference in the data access for covering indexes.  Check out the Building SQL Server Indexes in Ascending vs Descending Order tip as an good point of reference.
  • Fill Factor - Depending on how the data is maintained (INSERT, UPDATE, DELETE), the fill factor could significantly impact the performance, page splits and storage requirements.  What is necessary is to select the appropriate fill factor as outlined in the Fragmentation Station - Stop #4 - How to avoid it blog entry.
  • Balance - Indexes are not free when it comes to having SQL Server maintain and support the storage.  Be sure to have the needed indexes, but not excessive or repeating indexes as outlined in the Avoid Index Redundancy in SQL Server Tables tip.

Indexing Priorities

When it comes to new development projects versus production support it is hard to have an absolute answer where indexing falls into the priority list.  In terms of a new development project, it would be wise to spend equal time on building the database design, the indexing strategy and the data access code.  Without it, the application can become a maintenance nightmare requiring a great deal of attention in the production environment.  In many respects this is easier said than done if the database is just considered a black box that is expected to run forever without any attention.  If you are faced with that issue, then it is necessary to change that mentality first and make sure the time, energy and resources properly address the database needs moving forward.

For production applications, reviewing the indexes and query plans should be a high priority when a performance problem is occurring.  In many respects changing or adding indexes should be one of many items that are reviewed, analyzed, tested and deployed as opposed to considering indexes a magic bullet to resolve performance problems.

Another important consideration with the indexes is that as the application changes, the indexes may need to change as well.  Do not consider the indexes stagnant, but rather review the code with index implications in mind.  So as the application changes do not be afraid to change, test and deploy an improved indexing strategy.

Next Steps

  • In terms of SQL Server databases performance, indexes can make or break an application.  I have personally seen a new and simple indexing strategy breath the life into an application and give users confidence in the application.
  • Although indexes are not always a winner when it comes to a quick performance fix, they should be one aspect of the database that is reviewed to see if a quick fix can be applied to help the application.  Keep in mind that a number of quick fixes can cause a big mess in the long term, so be sure to balance the needs and build a long term solution.
  • With this being said, if the proper time, energy and resources are originally spent building the application, then the need for a quick fix should be minimal.
  • Check out the MSSQLTips index category for related indexing tips.

Last Update: 3/11/2008
 
posted by LifeisSimple
2011. 6. 16. 13:50 Brain Trainning/DataBase

인덱스 관련해서 반드시 알아야 하는.... 

출처 : http://www.databasejournal.com/features/mssql/sql-server-index-t-sql-statements.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+DatabaseJournalNews+%28Database+Journal+News%29&utm_content=Google+International

June 15, 2011

Top 10 Index T-SQL Statements That SQL Server DBAs Should Know

By Muthusamy Anantha Kumar aka The MAK

SQL Server DBAs know that indexes in databases are very similar to indexes in libraries. An index in a database is a structure associated with a table or view that speeds retrieval of rows from the table or view.

This article lists the top index-related T-SQL statements that are useful for SQL Server database administrators. The T-SQL statements covered in this article are classified into three categories: Index Definition or Create, Query — Query index related information and Maintenance.

Definition - Create Index

1. Clustered Index

Clustered indexes store the data rows in sorted order in the table based on their key values. Only one clustered index can be created per table, because the data rows themselves can only be sorted in one order.

A clustered index can be created while creating constraints like primary key on an existing table. Example:


ALTER TABLE [MyAddress]
ADD CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
) ON [PRIMARY]
GO

A clustered index can also be created on a column with no constraints related clause. Example:


CREATE CLUSTERED INDEX [MyAddress_id_CIX] ON [MyAddress1]
(
[ID] ASC
)ON [PRIMARY]
GO

2. Non Clustered Index

Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index. In a nonclustered index, the logical order of the index does not match the physical stored order of the rows on disk.

A nonclustered Index can be created on an existing table covering columns not covered by clustered index. Example:


CREATE UNIQUE NONCLUSTERED INDEX
[NIX_col5_col2_col3_col4_col6]
ON [MyAddress]
(
[AddressLine1] ASC,
[AddressLine2] ASC,
[City] ASC,
[StateProvinceID] ASC,
[PostalCode] ASC
)ON [PRIMARY]
GO

A nonclustered index can also be created while creating constraints on the existing table. Example:


ALTER TABLE [MyAddressType]
ADD CONSTRAINT [DEFF_MyAddressType_ModifiedDate]
DEFAULT (getdate()) FOR [ModifiedDate]
GO

3. XML Index

An XML index can be created on an XML column and the table must have a clustered index on the primary key. The XML index can be primary or secondary.

A primary XML index can be created as shown below:


CREATE PRIMARY XML INDEX idx_xCol_MyTable on MyTable (xCol)

A secondary XML index can be created as shown below:


CREATE TABLE MyTable (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_MyTable_XmlCol
ON T(XmlCol)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_MyTable_XmlCol_PATH ON MyTable(XmlCol)
USING XML INDEX PIdx_MyTable_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_MyTable_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_MyTable_XmlCol
FOR VALUE
GO

4. Spatial Index

SQL Server 2008 provided a special type of column called a spatial column, which is a table column that contains data of a spatial data type, such as geometry or geography.

A spatial index can be created using the following syntax:


CREATE TABLE MySpatialTable(id int primary key, geometry_col geometry);
CREATE SPATIAL INDEX SIndx_MySpatialTable_geometry_col1
ON MySpatialTable(geometry_col)
WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );

Query Index related metadata

5. Find all Indexes

The following query can be used to query all the tables, columns and indexes on the current database:


SELECT OBJECT_SCHEMA_NAME(BaseT.[object_id],DB_ID()) AS [Schema],
BaseT.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],
I.[type_desc]
FROM sys.[tables] AS BaseT
INNER JOIN sys.[indexes] I ON BaseT.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON BaseT.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE BaseT.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
ORDER BY BaseT.[name], I.[index_id], IC.[key_ordinal]

6. Fragmentation

The following query can be used to find the index fragmentation on all the tables in the current database:


SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
order by IPS.avg_fragment_size_in_pages desc

7. Missing index

SQL Server keeps track of the indexes that it thinks you should create that will help in improving the performance of queries. The following query list all missing indexes.


SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC

8. Unused index

The following statement lists all the indexes that have not been used. This also generates the DROP index statement which can come handy when deleting the indexes.


SELECT o.name, indexname=i.name, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

Index Maintainenance

9. Rebuild index

When an index gets fragmented, it requires defragmentation. Defragmentation can be done using the rebuild clause when altering a table. This command is equivalent to DBCC DBREINDEX in SQL Server versions prior to 2005. The command that can be used to rebuild the index is as follows:


USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

If ALL is not specified in rebuild, it will not rebuild a nonclustered index.

10. REORGANIZE index

Specifies that the index leaf level will be reorganized. The REORGANIZE statement is always performed online. This command is equivalent to DBCC INDEXDEFRAG in SQL Server versions prior to 2005.


USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Conclusion

I hope you've found useful this list of the top Index-related T-SQL statements that are useful for SQL Server database administrators.

For more on T-SQL programming, see our T-SQL programming series

See all articles by MAK

posted by LifeisSimple
2011. 2. 24. 13:56 Brain Trainning/DataBase

SELECT

o.name

, indexname=i.name

, i.index_id  

, reads=user_seeks + user_scans + user_lookups  

, writes =  user_updates  

, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)

, CASE

                  WHEN s.user_updates < 1 THEN 100

                  ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates

  END AS reads_per_write

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'

FROM sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  

INNER JOIN sys.objects o on s.object_id = o.object_id

INNER JOIN sys.schemas c on o.schema_id = c.schema_id

WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

AND s.database_id = DB_ID()  

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000

ORDER BY reads

posted by LifeisSimple
2011. 1. 11. 16:55 Brain Trainning/NoSQL

인덱스 (Index)

1. 인덱스 생성

-       동일 인덱스를 생성할 경우 1개만 생성됨. (동일한 인덱스 생성문을 입력해도 의미 없음)

-     컬럼의 유무와 관계없이 인덱스 생성됨. (컬럼이 없어도 인덱스 생성)

-       인덱스 정렬순서 1: ASC, -1: DESC

-       인덱스는 하위 도큐먼트 단위로도 생성 가능

   > db.factories.insert( { name: "xyz", metro: { city: "New York", state: "NY" } } );
   > db.factories.ensureIndex( { metro : 1 } );
     // this query can use the above index:
   > db.factories.find( { metro: { city: "New York", state: "NY" } } );

-     온라인 인덱스 생성가능 (1.3.2 버전 이상) / Unique Key 생성가능

> db.things.ensureIndex({x:1}, {background:true});

> db.things.ensureIndex({name:1}, {background:true, unique:true,

... dropDups:true});

 

예제)

1)    [ SQL ]
create index IX_department_dptCode_dptName on department (dptcode, dptname desc)

2)    [ MongoDB 콘솔 ]
db.department.ensureIndex({dptcode:1}, dptname:-1))

3)     [ JavaCode ]

BasicDBObject keys = new BasicDBObject();

 

keys.put("dptname", -1);

keys.put("dptcode", 1);

 

dept.ensureIndex(keys);

4)    [ 출력 ]
db.department.getIndexes()



추가 내용은 http://www.mongodb.org/display/DOCS/Indexes 참조


2. 인덱스 삭제

1)      [SQL] 

Drop index IX_department_dptCode_dptName on department

2)       [ MongoDB 콘솔]

 db.department.dropIndex({dptname:-1,dptcode:1})

3)       [ Java Code ]

                  BasicDBObject keys = new BasicDBObject();

                  keys.put("dptname", -1);

                  keys.put("dptcode", 1);

                  //dept.ensureIndex(keys);

                 

                  dept.dropIndex(keys);


이외에 index Rebuild 등의 기능이 있음. 메뉴얼 참조.

'Brain Trainning > NoSQL' 카테고리의 다른 글

[MongoDB] Start Options  (0) 2011.01.18
[MongoDB] Mac 용 UI 툴 MongoHub  (0) 2011.01.18
[MongoDB] MongoDB 한글 검색 문제 ...  (0) 2011.01.10
[MongoDB] Java Code Sample #1  (0) 2011.01.07
[MongoDB] MongoDB GridFS 부하테스트  (2) 2011.01.07
posted by LifeisSimple
prev 1 next