Top 10 steps to optimize data access in SQL Server. Part I (Use Indexing)

Introduction

“It’s been months since you and your team have developed and deployed a site successfully in the internet. You have a pretty satisfied client so far as the site was able to attract thousands of users to register and use the site within a small amount of time. Your client, management, team and you – everybody is happy. 

Life is not a bed of roses. As the number of users in the site was started growing at a rapid rate day by day, problem started occurring. E-mails started to arrive from the client complaining that the site is performing too slowly (Some of them ware angry mails). The client claimed that, they started losing users.

You started investigating the application. Soon you discovered that, the production database was performing extremely slowly when application was trying to access/update data. Looking into the database you found that the database tables have grown large in size and some of them were containing hundreds of thousands of rows. The testing team performed a test on the production site and they found that the order submission process was taking 5 long minutes to complete whereas it used to take only 2/3 seconds to complete in the test site before production launch”.

This is the same old story for thousands of application projects developed worldwide. Almost every developer including me has taken part in the story sometime in his/her development life. So, I know why such situation took place, and, I can tell you what to do to overcome this.

Let’s face it. If you are part of this story, you must have not written the data access routines in your application in the best possible way and it’s time to optimize those now. I want to help you doing this by sharing my data access optimization experiences and findings with you in this series of articles. I just hope, this might enable you to optimize your data access routines in the existing systems, or, to develop the data access routines in the optimized way in your future projects.

Scope

Please note that, the primary focus of these series of articles is “Data access performance optimization in transactional (OLTP) SQL Server databases”. But, most of the optimization techniques are roughly the same for other database platforms.  

Also, the optimization techniques I am going to discuss are applicable for the software application developers only. That is, as a developer, I’ll focus on the issues that you need to follow to make sure that you have done everything that you could do to optimize the data access codes you have written or you are going to write in future. The Database Administrators (DBA) also has great roles to play in optimizing and tuning the database performance. But, optimization scopes that fall into a DBA’s area are out of scope for these articles.

We have a database to optimize, let’s start it!

When a database based application performs slowly, there is a 90% probability that, the data access routines of that application are not optimized, or, not written in the best possible way. So, you need to review and optimize your data access/manipulation routines for improving the overall application’s performance.

So, let us start our optimization mission in a step-by-step process:

Step1: Apply proper indexing in the table columns in the database

Well, some could argue whether implementing proper indexing should be the first step in performance optimization process in the database. But, I would prefer applying indexing properly in the database in the first place, because of following two reasons: 

1. This will allow you to improve the best possible performance in the quickest amount of time in a production system. 
2. Applying/creating indexes in the database will not require you to do any application modification and thus will not require any build and deployment.

Of course, this quick performance improvement can be achieved if you find that, indexing is not properly done in the current database. However, if indexing is already done, I would still recommend you to go through this step.

What is indexing?

I believe, you know what indexing is. But, I’ve seen many people being unclear on this. So, let us try to understand indexing once again. Lets us read a small story.

Long ago there was a big library in an ancient city. It had thousands of books, but, the books ware not arranged in any order in the book shelves. So, each time a person asked for a book to the librarian, the librarian had no way but to check every book to find the required book that the person wants. Finding the desired book used to take hours for the librarian, and, most of the times the persons who ask for books had to wait for a long time.

[Hm..seems like a table that has no primary key. So, when any data is searched in the table, the database engine has to scan through the entire table to find the corresponding row, which performs very slow.] 

Life was getting miserable for that librarian as the number of books and persons asking for books were increasing day by day. Then one day, a wise guy came to the library, and, seeing the librarian’s measurable life, he advised him to number each book and arrange these in the book shelves according to their numbers. “What benefit would I get?” Asked the librarian. The wise guy answered, “well, now if somebody gives you a book number and ask for that book, you will be able to find the shelves quickly that contains the book’s number, and, within that shelve, you can find that book very quickly as these are arranged according to their number”.

[Numbering the books sounds like creating primary key in a database table. When you create a primary key in a table, a clustered index tree is created and all data pages containing the table rows are physically sorted in the file system according to their primary key values. Each data page contains rows which are also sorted within the data page according to their primary key values. So, each time you ask any row from the table, the database server finds the corresponding data page first using the clustered index tree (Like, finding the book shelve first) and then finds the desired row within the data page that contains the primary key value (Like, finding the book within the shelve)]

“This is what I exactly need!” The excited librarian instantly starts numbering the books and arranging these across different book shelves. He spent a whole day to do this arrangement, but, at the end of the day, he tested and found that that a book now could be found using the number within no time at all! The librarian was extremely happy. 

[That’s exactly what happens when you create a primary key in a table. Internally, a clustered index tree is created, and, the data pages are physically sorted within the data file according to the primary key values. As you can easily understand, only one clustered index can be created for a table as the data can be physically arranged only using one column value as the criteria (Primary key). It’s like the books can only be arranged using one criterion (Book number here)]

Wait! The problem was not completely solved yet. In the very next day, a person asked a book by the book’s name (He didn’t have the book’s number, so, all he had the book’s name). The poor librarian had no way but to scan all numbered book from 1 to N to find the one the person asked for. He found the book in the 67^th shelves. It took 20 minutes for the librarian to find the book. Earlier, he used to take 2-3 hours to find a book when these were not arranged in the shelves, so, that’s an improvement still. But, comparing to the time to search a book using it’s number (30 seconds), this 20 minute seemed to be a very high amount of time to the librarian. So, he asked the wise man how to improve on this.

[This happens when you have a Product table where you have a primary key ProductID, but, you have no other index in the table. So, when a product is to be searched using the Product Name, the database engine has no way but to scan all physically sorted data pages in the file to find the desired named book]

The wise man told the librarian “Well, as you already have arranged your books using their serial numbers, you cannot re-arrange these. So, better create a catalog or index where you will have all the book’s names and their corresponding serial numbers. But, in this catalog, arrange the book names in their alphabetic number and group the book names using each alphabet so that, if any one wants to find a book named “Database Management System”, you just follow these steps to find the book

1. Jump into the section “D” of your “Book name “catalog” and find the book name there 
2. Read the corresponding serial number of the book and find the book using the serial number (You already know how to do this)

“You are a genius! Exclaimed the librarian. Spending some hours he immediately created the “Book name” catalog and on a quick test he found that he only required 1 minute (30 seconds to find the book’s serial number in the “Book name” catalog and another 30 seconds to find the book using the serial number) to find a book using the book name.

The librarian thought that, people might ask for books using several other criteria like book name and/or author’s name etc. so, he created another similar catalog for author names and after creating these catalogs the librarian could find any book using the some common book finding criteria (Serial number, Book name, Author’s name) within a maximum 1 minute of time. The miseries of the librarian ended soon and lots of persons started gathering at the library for books as they could get the book really fast and the library became very popular.

The librarian started passing his life happily ever after. The story ends.

By this time, I am sure you have understood what indexes really are, why they are important and what their inner workings are. For example if we have a “Products” table, along with creating a clustered index (That is automatically created when creating the primary key in the table), we should create a non-clustered index on the ProductName column. If we do this, the database engine creates an index tree for the non-clustered index (Like, the “book name” catalog in the story) where the product names will be sorted within the index pages. Each index page will contain some range of product names along with their corresponding primary key values. So, when a Product is searched using the product name in the search criteria, the database engine will first seeks the non-clustered index tree for Product name to find the primary key value of the book. Once found, the database engine then searches the clustered index tree with the primary key to find the row for the actual book that is being searched.

Following is how an index tree looks like: 

Figure : Index tree structure

This is called a B+ Tree (Balanced tree). The intermediate nodes contain range of values and direct the SQL engine where to go while searching for a specific index value in the tree starting from the root node. The leaf nodes are the nodes which contain the actual index values. If this is a clustered index tree, the leaf nodes are the physical data pages. If this is a non-clustered index tree, the leaf nodes contain index values along with clustered index keys (Which the database engine uses to find the corresponding row in the clustered index tree.

Usually, finding a desired value in the index tree and jumping to the actual row from there takes an extremely small amount of time for the database engine. So, indexing generally improves the data retrieval operations.

So, time to apply indexing in your database to retrieve results fast!

Follow these steps to ensure proper indexing in your database

Make sure that every table in your database has a primary key.

This will ensure that every table has a Clustered index created (And hence, the corresponding pages of the table are physically sorted in the disk according to the primary key field). So, any data retrieval operation from the table using primary key, or, any sorting operation on the primary key field or any range of primary key value specified in the where clause will retrieve data from the table very fast.

Create non-clustered indexes on columns which are:

• Frequently used in the search criteria
• Used to join other tables
• Used as foreign key fields  
• Of having high selectivity (Column which returns a low percentage (0-5%) of rows from a total number of rows on a particular value)
• Used in the ORDER BY clause
• Of type XML (Primary and secondary indexes need to be created. More on this in the coming articles)

Following is an example of an index creation command on a table:

CREATE INDEX
NCLIX_OrderDetails_ProductID ON
dbo.OrderDetails(ProductID) 

Alternatively, you can use the SQL Server Management Studio to create index on the desired table 

Figure : Creating index using SQL Server Management Studio

Step2 : Create appropriate covering indexes

So, you have created all appropriate indexes in your database, right? Suppose, in this process you have created an index on a foreign key column (ProductID) in the Sales(SelesID,SalesDate,SalesPersonID,ProductID,Qty) table. Now, assuming that, the ProductID column is a “Highly selective” column (Selects less than 5% of the total number of rows rows using any ProductID value in the search criteria) , any SELECT query that reads data from this table using the indexed column (ProductID) in the where clause should run fast, right?

Yes, it does, comparing to the situation where no index created on the foreign key column (ProductID) in which case, a full table scan (scanning all related pages in the table to retrieve desired data). But, still, there is further scope to improve this query.

Let’s assume that, the Sales table contains 10,000 rows, and, the following SQL selects 400 rows (4% of the total rows) 

SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112 

Let’s try to understand how this SQL gets executed in the SQL execution engine

1. The Sales table has a non-clustered index on ProductID column. So, it “seeks” the non-clustered index tree for finding the entry that contains ProductID=112
2. The index page that contains the entry ProductID = 112 also contains the all Clustered index  keys (All Primary key values, that is SalesIDs, that have ProductID = 112 assuming that primary  key is already created in the Sales table) 
3. For each primary key (400 here), the SQL server engine “seeks” into the clustered index tree to  find the actual row locations in the corresponding page.
4. For each primary key, when found, the SQL server engine selects the SalesDate and SalesPersonID column values from the corresponding rows.

Please note that, in the above steps, for each of the primary key entries (400 here) for ProductID = 112, the SQL server engine has to search the clustered index tree (400 times here) to retrieve the additional columns (SalesDate, SalesPersonID) in the query.

It seems that, along with containing clustered index keys (Primary key values) if the non-clustered index page could also contain two other column values specified in the query (SalesDate, SalesPersonID), the SQL server engine would not have to perform the step 3 and step 4 in the above steps, and, thus, would be able to select the desired results even faster just by “seeking” into the non clustered index tree for ProductID column, and, reading all three mentioned column values directly from that index page.

Fortunately, there is a way to implement this feature. This is what is called “Covered index”. You create “Covered indexes” in table columns to specify what are the additional column values the index page should store along with the clustered index key values (primary keys). Following is the example of creating a covered index on the ProductID column in Sales table:

CREATE INDEX NCLIX_Sales_ProductID--Index name
ON dbo.Sales(ProductID)--Column on which index is to be created
INCLUDE(SalesDate, SalesPersonID)--Additional column values to include 

Please note that, Covered index should be created including a few columns that are frequently used in the select queries. Including too many columns in the covered indexes would not give you too much benefit. Rather, doing this would require too much memory to store all the covered index column values resulting in over consumption of memory and slow performance.

Use Database Tuning Advisor’s help while creating covered index

We all know, when an SQL is issued, the optimizer in the SQL server engine dynamically generates different query plans based on: 

• Volume of Data 
• Statistics 
• Index variation
• Parameter value in TSQL
• Load on server

That means, for a particular SQL, the execution plan generated in the production server may not be the same execution plan that is generated in the test server, even though, the table and index structure is the same. This also indicates that, an index created in the test server might boost some of your TSQL performance in the test application, but, creating the same index in the production database might not give you any performance benefit in the production application! Why? Well, because, the SQL execution plans in the test environment utilizes the newly created indexes and thus gives you better performance. But, the execution plans that are being generated in the production server might not use the newly created index at all for some reasons (For example, a non-clustered index column is not “highly” selective in the production server database, which is not the case in the test server database).

So, while creating indexes, we need to make sure that, the index would be utilized by the execution engine to produce faster result. But, how can we do this?

The answer is, we have to simulate the production server’s load in the test server, and then need to create appropriate indexes and test those. Only then, if the newly created indexes improves performance in the test environment, these will most likely to improve performance in the production environment. 

Doing this should be hard, but, fortunately, we have some friendly tools to do this. Follow these instructions: 

1. Use SQL profiler to capture traces in the production server. Use the Tuning template (I know, it is advised not to use SQL profiler in the production database, but, sometimes you have to use it while diagnosing performance problem in the production). If you are not familiar with this tool, or, if you need to learn more about profiling and tracing using SQL profiler, read http://msdn.microsoft.com/en-us/library/ms181091.aspx.   
2. Use the trace file generated in the previous step to create a similar load in the test database server using the Database tuning advisor. Ask the Tuning advisor to give some advice (Index creation advice most of the cases). You are most likely to get good realistic (index creation) advice from the tuning advisor (Because, the Tuning advisor loaded the test database with the trace generated from the production database and then tried to generate best possible indexing suggestion) . Using the Tuning advisor tool, you can also create the indexes that it suggests. If you are not familiar with the Tuning advisor tool, or, if you need to learn more about using the Tuning advisor, read http://msdn.microsoft.com/en-us/library/ms166575.aspx.

Step3 : Defragment indexes if fragmentation occurs

OK, you created all appropriate indexes in your tables. Or, may be, indexes are already there in your database tables. But, you might not still get the desired good performance according to your expectation.

There is a strong chance that, index fragmentation have occurred.

What is index fragmentation?

Index fragmentation is a situation where index pages split due to heavy insert, update, and delete operations on the tables in the database. If indexes have high fragmentation, either scanning/seeking the indexes takes much time or the indexes are not used at all (Resulting in table scan) while executing queries. So, data retrieval operations perform slow.

Two types of fragmentation can occur:

Internal Fragmentation: Occurs due to the data deletion/update operation in the index pages which ends up in distribution of data as sparse matrix in the index/data pages (create lots of empty rows in the pages). Also results in increase of index/data pages that increase query execution time.

External Fragmentation: Occurs due to the data insert/update operation in the index/data pages which ends up in page splitting and allocation of new index/data pages that are not contiguous in the file system. That reduces performance in determining query result where ranges are specified in the “where” clauses. Also, the database server cannot take advantage of the read-ahead operations as, the next related data pages are not guaranteed to be contiguous, rather, these next pages could be anywhere in the data file.

How to know whether index fragmentation occurred or not? 

Execute the following SQL in your database (The following SQL will work in SQL Server 2005 or later databases. Replace the database name ‘AdventureWorks’ with the target database name in the following query):

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'
)
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

Figure : Index fragment information

Analyzing the result, you can determine where index fragmentation have occurred, using the following rules: 

• ExternalFragmentation value > 10 indicates External fragmentation occurred for corresponding index 
• InternalFragmentation value < 75 indicates Internal fragmentation occurred for corresponding index

How to defragment indexes?

You can do this in two ways: 

• Reorganize fragmented indexes: Execute the following command to do this:

        ALTER INDEX ALL ON TableName REORGANIZE

• Rebuild indexes: Execute the following command to do this:

        ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) 

You can also rebuild or reorganize individual indexes in the tables by using the index name instead of the ‘ALL’ keyword in the above queries. Alternatively, you can also use the SQL Server Management Studio to do index defragmentation.

Figure : Rebuilding index using SQL Server Management Studio

When to reorganize and when to rebuild indexes?

You should “Reorganize” indexes when the External Fragmentation value for the corresponding index is in between 10-15 and Internal Fragmentation value is in between 60-75. Otherwise, you should rebuild indexes.

One important thing with index rebuilding is, while rebuilding indexes for a particular table, the entire table will be locked (Which does not occur in case of index reorganization). So, for a large table in the production database, this locking may not be desired, because, rebuilding indexes for that table might take hours to complete. Fortunately, in SQL Server 2005, there is a solution. You can use the ONLINE option as ON while rebuilding indexes for a table (See index rebuild command given above). This will rebuild the indexes for the table along with making the table available for transactions.

Last words 

It's really tempting to create index on all eligible columns in your database tables. But, if you are working with a transactional database (An OLTP system where update operations take place most of the times), creating indexes on all eligible columns might not be desirable every time. In fact, creating heavy indexing on OLTP systems might reduce overall database performance (As most operations are update operations, updating data means updating indexes as well).

A rule of thumb can be suggested as follows: If you work on a transactional database, you should not create more than 5 indexes on the tables on an average. On the other hand, if you work on a Data warehouse application, you should be able to create up to 10 indexes on the tables on an average.

What's next? 

Applying indexing properly in your database would enable you to increase performance a lot, in a quick amount of time. But there are lots of other things you should do to optimize your database, including some advance indexing features in the SQL Server database. These will be covered in the other optimization steps provided in the next articles.  

Take a look at the next optimization steps in the article “Top 10 steps to optimize data access in SQL Server. Part II (Re-factor TSQLs and apply best practices)”. Have fun.

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 25 of 31 (Total in Forum: 31) (Refresh) FirstPrevNext

Primary Key != Clustered Index Ian Yates 17:59 15 Jun '09   Nice introduction-style article - I like your analogies as they're easy to grasp for the layman. However, whilst the default in SQL Server is to create a clustered index for the primary key of the table, this doesn't always have to be the case. For example, I could have a "persons" table that has a primary key as the personNumber but the clustered index is on surname, firstname as that's the common order in which I like to retrieve people. Granted this has other effects such as bookmark lookups, etc when joining to other tables using the key values, but it's important to note that a clustered index is NOT always the same as the primary key. Anyway, apart from that false equation of concepts (which many articles seem to contain) it was a great article Sign In·View Thread·PermaLink You SQL Code for the fragmentation Doesn't Run jamesklett 6:16 28 May '09   Error Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '('. Sign In·View Thread·PermaLink Re: You SQL Code for the fragmentation Doesn't Run [modified] M.M.Al-Farooque Shubho 19:02 30 May '09   This is due to the Compatibility issue of SQL server. SQL server 2000 runs on compatibility level 80. SQL server 2005 support both 80 and 90.So if your database is running on compatibility level 80 then the above SQL will throw syntax error. To solve this issue you need to change the compatibility level to 90. Just execute following command to do so: EXEC sp_dbcmptlevel <DatabaseName>, 90; You can also do the same using the SQL Server management studio as as follows: Right click on the target database-->Click Properties-->Click on Options-->Select SQL Server 2005(90) in the Compatibility level drop-down box-->Click "OK" Hope that helps. modified on Sunday, May 31, 2009 1:22 PM Sign In·View Thread·PermaLink Note that Indexes can be rebuilt while the table remains online when using SQL Server Enterprise Edition Member 4735426 17:04 26 May '09   From http://technet.microsoft.com/en-us/library/ms188388.aspx?ppud=4[^] ONLINE = { ON | OFF } Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF. For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error is raised. Note: Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions. ON Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table. OFF Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements. For more information, see How Online Index Operations Work. For more information about locks, see Lock Modes. Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions: Disabled indexes XML indexes Indexes on local temp tables Partitioned indexes Clustered indexes if the underlying table contains LOB data types Nonclustered indexes that are defined with LOB data type columns Nonclustered indexes can be rebuilt online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey columns. Sign In·View Thread·PermaLink Thanks for sharing. Hemant.Kamalakar 21:58 4 May '09   Thanks for sharing. Sign In·View Thread·PermaLink Cost of query question Michael Dolittle 10:52 30 Apr '09   First of all, i found your article very useful. 5 points from me. So, i immediately reviewed some of the larger tables i have, which are queried very often using 'where' clauses. Upon creating a non-clustered index for a 'Locations' table (geographical data, with a primary key on the Id field), on the 'Name' Field , i recieved the following error message: 'Locations' table - Unable to create index 'IX_Locations_Name'. The query has been canceled because the estimated cost of this query (715) exceeds the configured threshold of 300. Contact the system administrator. The statement has been terminated. Unfortunately, i am that guy. Might the cause of this be the fact that the table has over 200.000 rows? And is there any way to work around this (apart from breaking up the table into a number of smaller ones, which will force me to modify the data access layer?) Thanks in advance! Michael Michael Bakker michaelbakker1@gmail.com Sign In·View Thread·PermaLink Re: Cost of query question M.M.Al-Farooque Shubho 19:36 30 Apr '09   This is from MSDN "The query governor configuration option can be used to prevent long-running queries from executing, thus preventing system resources from being consumed. By default, the query governor configuration option allows all queries to execute, no matter how long they take. However, the query governor can be set to the maximum number of seconds that all queries for all connections, or just the queries for a specific connection, are allowed to execute" See http://msdn.microsoft.com/en-us/library/ms176100.aspx[^] Sign In·View Thread·PermaLink very good Donsw 8:27 28 Apr '09   Very good article, I agree that not everyone understand the role of a DBA and what types there are but this article may halp them to understand the need of all . cheers, Donsw My Recent Article : Ajax Calendar Control Sign In·View Thread·PermaLink Re: very good M.M.Al-Farooque Shubho 9:33 28 Apr '09   Thanks. As I believe, every developer needs to know how to write optimized data access codes, doesn't matter if this falls into a DBA's area or not. But, DBAs are always more than welcome to this article. Sign In·View Thread·PermaLink RECOGNIZE ==> REORGANIZE Mark Frishman 4:53 28 Apr '09   Small typo Sign In·View Thread·PermaLink Re: RECOGNIZE ==> REORGANIZE M.M.Al-Farooque Shubho 8:03 28 Apr '09   Corrected, and, many thanks. Sign In·View Thread·PermaLink Excelent article, but... [modified] Nikola Ilijev 0:24 22 Apr '09   I gave you mark 4 instead of 5 (which is fully deserved). The problem is in basic postulates of application development and in not understanding role of DBA. Let's see few sentences from article: "It’s been months since you and your team have developed and deployed a site successfully in the internet. ... Soon you discovered that, the production database was performing extremely slowly when application was trying to access/update data." and The Database Administrators (DBA) also has great roles to play in optimizing and tuning the database performance. But, optimization scopes that fall into a DBA’s area are out of scope for these articles. It's clear that you, as the huge number of developers don't understand DBA roles and responsibilities. First of all, there are two types of DBA: 1. Production DBA 2. Development DBA or Database Developer Development DBA should be in charge of logical (relations, entities and relationships) and physical database design (tables, views, data types, keys and indexes), does recommendations for best practices in SQL code as well as develop complex queries and revise/tune the rest of the queries. If you hire or contract development DBA in the early stages of project you'll very rarely end up with serious performance issues, except in case of really enormous increase of database size, in which case production DBA comes in focus. Production DBA is in charge to keep production databases available. Availability of production database does not mean server is up or not, but also how fast database response is. Only production DBA should be in charge of creating/modifying/defragmenting/dropping indexes, recreating statistics etc. Those are just some of many more DBA's responsibilities. Practically, all the recommendations you wrote in this article are very useful for developers to understand what's going on behind the scene and to help them to write a better code, but development and production DBAs should be in charge to enforce it. Unfortunately it's quite common practice software developement companies don't want to hire development DBAs aka database developers (in some cases even separate database architects should be in place) and then, faced with terrible performance issues once application is in production, DBA is hired to to do some magic trick and boost performance. Most often, fireman DBA can't do a lot - create few indexes, drop some of them, tune some database parameters or suggest to scale up the hardware, but for the real causes of problem they can't do anything. Usually it's too late and too expensive to deal with bad database design and inefficient queries. Therefore, my suggestion is, again, hire or contract database designers in early stage of project, use database developers in whole application development process and pass responsibility to the production DBA once application is up and running. And at the end, this is a great article and I highly recommend it. p.s. measurable dos not have same meaning as miserable modified on Thursday, April 23, 2009 4:01 AM Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: Excelent article, but... M.M.Al-Farooque Shubho 8:55 22 Apr '09   Thanks for your well-thought suggestions. To be true, when I referred the "DBA" role, I certainly had the "Production DBA" role in my mind. What I see, if you are a developer, you got to know how to do an optimized data design, and how to write optimized code. I have no problem if you call such a good (database) developer as a "Development DBA" because, I also like the idea of somebody taking the responsibility of the data access optimization stuffs before development begins (Or, when development continues). Lastly, thanks for identifying the typo mistake. I really appreciate your microscopic look. Sign In·View Thread·PermaLink 2.00/5 (1 vote) Awesome Article! CCMint 15:00 21 Apr '09   I've read a few articles on indexes and this is the first article I've read where I feel I'm left with a content understanding. I love your examples. Thank you and have an awesome day!!! Sign In·View Thread·PermaLink Re: Awesome Article! M.M.Al-Farooque Shubho 18:22 21 Apr '09   Thanks. It really feels great if you know that your effort didn't go in vain and someone was able to learn something from you. You too have a nice day! Sign In·View Thread·PermaLink can I make 3 indexes on same table easywalker 5:28 14 Apr '09   hello. I just want to know if I can create more than one index on the same table. Assume that I have a table where there are three main queries one that search according to PID ( foriegn key) and another query that search according to title and another one that searches ccording to another criteria. It seems that i have to create 3 indexex. but how does the system knows that for SP1 it should uses the index one and for SP2 uses index2 .... thanks.. Sign In·View Thread·PermaLink Re: can I make 3 indexes on same table [modified] M.M.Al-Farooque Shubho 5:34 14 Apr '09   Of course you can create more than one index on the same table.In fact, you should create 3 indexes on the table you mentioned (Other than the clustered index that is automatically created for the primary key field). System determines the index to use when the query optimizer generates the query execution plan for the TSQLs in the Stored procedures. modified on Tuesday, April 14, 2009 10:59 PM Sign In·View Thread·PermaLink I am not clear that not more than 5 indexes is for a particular table or for whole database? Arun Senthil Kumar 10:33 4 Apr '09   you should not create more than 5 indexes on the tables on an average. On the other hand, if you work on a Data warehouse application, you should be able to create up to 10 indexes on the tables on an average I am not clear that not more than 5 indexes is for a particular table or for whole database? "A nice article. Expecting next one" Regards, Arun Sign In·View Thread·PermaLink Re: I am not clear that not more than 5 indexes is for a particular table or for whole database? M.M.Al-Farooque Shubho 18:06 4 Apr '09   More than 5 indexes should not be created for a table (On an average, as a rule of thumb) in a transactional database. Thanks for your appreciation. Hope to deliver the next one soon. Sign In·View Thread·PermaLink Re: I am not clear that not more than 5 indexes is for a particular table or for whole database? spoodygoon 3:45 29 Apr '09   Weather or not to add indexes is generally determined by your query execution plans and the performance of your databases. If you have optimized your set based query to the best of your ability and you still have performance problems brought on by index scans and especially table scans as opposed to index seeks, you will be wise to alter or add an index to make sure you are getting as many index seeks as is reasonable. I don't feel that indexes should not be determined by an arbitrary number during design but by the performance of your database after you have enough data to make an informed decision. Sign In·View Thread·PermaLink Re: I am not clear that not more than 5 indexes is for a particular table or for whole database? M.M.Al-Farooque Shubho 8:37 29 Apr '09   You are right. Indexes should be created "on demand" rather than based on some fixed numbers. But still, as a rule of thumb (Which, of course should vary case by case, based upon situation) you should have some kind of a "marker" that can give you some idea whether you are doing "over" or "less" indexing. I mentioned the number "5" for that purpose only. This surely doesn't bound you creating more than 5 indexes on a table in a transactional database if it really improves performance. Thanks for your feedback. Sign In·View Thread·PermaLink There's a typo in the title. stano 17:04 3 Apr '09   Just thought you should know. steps not setps Good article keep it up. Sign In·View Thread·PermaLink Re: There's a typo in the title. M.M.Al-Farooque Shubho 18:00 3 Apr '09   Oops! That was bad. I've corrected this. Thanks a lot man. Sign In·View Thread·PermaLink I liked it! Moim Hossain 23:59 25 Mar '09   Hi Shuvo Bhai, it's really a cool article. To me it was really useful to have a "memory-refreshing" tour into the topic of "database Indexing"..and ofcourse with a nice scenario of the library Yet I would like to mention one thing, besides the proper indexing it's also worth to re-exmine the database schemas and more importantly the query design and analyzing their execution plans- which is often neglected by many developers but can improve the performance significantly. Looking forward, to read more interesting articles from you Moim Hossain R&D Project Manager BlueCielo ECM Solutions BV Sign In·View Thread·PermaLink Re: I liked it! M.M.Al-Farooque Shubho 22:02 27 Mar '09   Thanks, My next articles in this series will focus on these issues. Sign In·View Thread·PermaLink

Last Visit: 6:46 8 Nov '09     Last Update: 6:46 8 Nov '09 12 Next »