Hope the reader is in good state while going through this article!!!!
Indexing, a very important technique to master, when it comes to SQL Server DB Administration.
What is an Index????
English Definition: Something that serves as a guide or facilitates the path to the destination.
* The above definition, if understood properly, provides the entire structure of an index in SQL Server 2005.
* An index is something, that facilitates the MS SQL Server to find the desired data by utlizing less amount of system resources and time.
Heap Data Structure present in SQL Server 2005:
* In order to understand a heap structure, i'll take you across a scenario, which will inscribe heaps in your memory. This one is for the people who doesn't know what Indian City lanes are made up of !!
* Imagine you're at Hyderabad, India and you're looking at Charminar and simultaneously adoring its art and beauty.
* A person steals your cell phone (Say O2 or BlackBerry [With BlackBerry Messenger activated.] and you find that man running away. Your reflexes gets you started n you start chasing the theif. And after sometime the theif vanishes into the lanes around Charminar ( If anyone is unaware then always remember that the Charminar area contains around 200 lanes approx....).
* Now you are panic stricken as you do not know your exact location. And again your reflexes compells you to start finding a way out.
* Let the epicenter be some lane 10. From lane 10 you go across each and every lane present in that area to find a way out..
* You come across 80 lanes, but in vain. And in the 81 lane, you find some way out. But that lane turns out to be a dead end lane and you again come back to lane 10.
* And from lane 120, you again find a way out to the middle of the road. On following it, you're out of that area, safe and sound.
* This is what happens to SQL Server, when the data to be searched is in a heap structure.
* The server has to scan each and every data page to find the desired data. This becomes a time consuming process if the size of the database is huge (say yahoo mail users database).
* The INDID (Index Id) of a heap structure is '0' (Zero). And you can find the heap structured objects in 'dbo.sysindexes' table.
SELECT
OBJ.name AS [INDEX NAME],
INDXDETS.indid AS [INDEX ID],
INDXDETS.FirstIAM AS [FIRST PAGE ADDRESS],
INDX.type_desc AS [STRUCTURE]
FROM
dbo.sysindexes INDXDETS
INNER JOIN sys.indexes INDX ON INDX.object_id = INDXDETS.id
INNER JOIN dbo.sysobjects OBJ ON OBJ.id = INDXDETS.id
WHERE
INDXDETS.indid = 0
GO
* When the user issues a select statement against a heap, the server goes to dbo.sysindexes table to fetch the starting point(Address) to search. This address is given in FirstIAM column of dbo.sysindexes table.
* The data page at that address contains a pointer to the address of subsequent data pages, which in turn will contain pointers pointing to the address of different data pages.
* Unless server fetches the desired data, it keeps on scanning these pages. Thus wasting a lot of system resources as well as time.
* Thus, in order to improve the performance of your server, try implementing database indexing on the highly used columns.
* The basic working of an heap is depicted in the following picture.
Akshay Sahu
Microsoft Certified Technology Specialist
(SQL Server 2005)
No comments:
Post a Comment