SQL Server Indexes (Clustered Indexes, Non Clustered Indexes)

SQL Server Indexes (Clustered Indexes)

When you execute any query in SQL Server, it fetches records in two ways
a) Table Scan
b) Index Lookup

Table Scan – As name suggests when you search for any particular record in the table, the DB Engine has to touch each and individual record till it finds the requested value, this is known as table scan. When the size of table is very large the table scan may perform badly.

Index Lookup – When there is index defined on the table then index lookup will happen on the table. This index lookup returns results efficiently than table scan. One or more number of indexes can be added to one table. There are various types of indexes in SQL Server as below

1. Clustered Indexes
2. Non-Clustered Indexes etc.,

Clustered Index -- Clustered Index will change the physical order of the table, it arrange records in sorted order on the column on which you defined clustered Index, hence we can define only ONE clustered index per table. It has one row in sys.partitions table with IndexId = 1. A cluster index can contain multiple columns hence we call it as composite index.

The database uses B-Tree structure to organize the indexes; it has different types of nodes.

1) Root Node - Always root node will be one, which contains pointers to other nodes.
2) Branch Nodes / Intermediate nodes – Two or more branch nodes, It contains pointers to other branch nodes or leaf nodes
3) Leaf Nodes - A leaf node contains index items and horizontal points to other leaf nodes.

Indexes are organized as B-Trees, Each page in an index B-Tree is called a index node. The root and intermediate pages contains index pages holding index rows. Each index row will contain a key value and a pointer to either an intermediate page or a data row in the leaf level of the index.

The leaf node of clustered index will contain actual data rows. While searching a clustered index field the query engine will fetch records very fast.

 Clustered Index Considerations

 These are particularly useful while using relational operators such as =,<>,>,<,between, exists etc.,   
 Also it gives good performance when you use order by and group by clauses.

 While defining clustered indexed we need to choose a column in such a way that it should have 
  many distinct values and unique values.

  Defining clustered index are not advised on column that are frequently updated, because update
  potentially require re-ordering of the table data and it may cause page splits.

Non-Clustered Indexes

Like clustered index non-clustered index also follows B-Tree structure with few differences, the leaf node of non-clustered index doesn’t store data rows, and data is not stored in sorted order only index rows will be in sorted order. The leaf nodes of non-clustered index contain the non- clustered index key values and each key value has entry pointer to the data row that contains the key value. The pointer from index row in non-clustered index to data row is called row locator. A row locator structure is depends on whether it points to clustered index or to a heap. In simple words the data is stored in one place and index is stored in other place and will have pointers to the storage location of the data. Hence table can have more than one non-clustered index.

 If the table is heap which doesn’t have clustered index the row locator points to the row. 
 If the table has clustered index then row locator refers to cluster index key.
 Non-Clustered index has one row in sys.partitions table with IndexID>1

Prior to 2008 SQL Server can have 249 non clustered indexes per table and from 2008 onwards 999 clustered indexes can be created.
Unique constraint will create a non-clustered index by default.

Table Organization in SQL Server (What is Heap)

Table Organization in SQL Server (What is Heaps)

SQL Server uses either the two methods to organize data pages in the partition.

 1. Heap 

2. B-Tree

What is heap?

A heap is a table without a clustered Index, but it can have non-clustered index. Heap has only data not index pages. It has one row in sys.partitions table with IndexId=0. Heaps also have allocation units like IN_ROW_DATA, ROW_OVERFLOW_DATA, LOB_DATA based on the datatypes it is used. The data rows are not stored in a serial order and there is no particular order for data pages also. As there is no index on heaps only Table Scan will perform while searching of any records. If there is no index is defined on a table the search will do a sequential order.

For suppose if you want to search a number 256 in a ID column, when there is no index is the search engine searches for 255 rows then it displays 256th record, to increase the performance some how we need to minimize it that’s what Balanced-Tree does.


/* Index ID = 0 is a Heap Table , Index Id = 1 for Clustered Index and a high value for non-clustered index on Index Id column */

select ss.name as schema_name, so.name as table_name, si.index_id
from sys.indexes si, sys.objects so, sys.schemas ss
where si.object_id = so.object_id and ss.schema_id = so.schema_id
and si.index_id = 0 and so.type='U' 

sp_helpindex Table_Name (from the above result)

As we discussed there is no link between data pages that we can see using the DBCC IND undocumented command

DBCC IND (dbname, tablename, 0) – Observe the below result and ignore the first row as this row is a IAM page row (see pagetype should be 10), and from second row onwards data pages, if you observe clearly there will no data on the PrevPagePID and NextPagePID columns. 

-- To check the fragmentation --

select alloc_unit_type_desc,  index_depth, page_count, avg_page_space_used_in_percent,
record_count, forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('testindexes'),0, Null, 'detailed') 

Modifications on Heaps

Insert - Added record where there is space

Delete - Removed and marked record as ghost but it won’t de-allocate the pages so when there is any read operation on the table it reads all the pages (For example we have 1500 rows in a heap table which occupied 80 pages and we have deleted 1490 rows then we have only 10 rows left, as this is a heap table it wont de-allocate 80 pages, so while reading the engine has to go thru all the 80 pages which is very expensive operation). To avoid this we need to rebuild the heap.

Update - While updating record if the new record size is higher than the old record then it created forwarded records, it is much expensive while doing any IO operation.

Note –Rebuilding a heap is a very expensive operation when there is a big data on the heap table, because all non-clustered indexes being re-build while rebuilding heap

When there is heap table preferred?

Inserts in to heap table are faster than clustered index table.

SQL Server Storage Architecture IV

In continuation with previous post, 

Page Split -- A page is 8kb of data whether Index data, table data or LOB data. If new row could not fit into the page, then new page created and moves rows to the right or left of modifications on to new page. This is known as page split. For example we have a series of rows from 1 to 10 (except 4th row) in two pages 21, 22 that means we have 1, 2,3,5,6 in first page and 7,8,9,10 in second page. And assume we have a clustered index on that column. Then when a new row comes example ID-4 then it should keep after 3 in page 21 because it should be physically in that order in that case two pages becomes three pages as below

Page 21 consists 1,2,3,4 and It creates a new page (page 33) to store 5 and 6 and then page 22 consists 7, 8,9,10. This is known as page split. When there is page splits then all non-clustered indexes needs to be updated. The more page splits causes fragmentation and impacts performance.
Fill Factor -- In the above topic we discussed about page splits, A page split happen when there is no room for newly coming row then SQL Server will move rows into other page and creates a new page to fit the newly inserted data, To avoid this situation to some extent we need to specify Fill Factor, If you specify fill factor to 80% then SQL Server will fills only 80% of data and leaves 20% free, this will help to reduce the page splits. We need to specify fill factor percentage based on your table design and environment.

IAM Pages -- As discussed earlier the SQL Server will create one partition for every table and every index. Inside that partition we have three allocation units IN_ROW, ROW_OVERFLOW, LOB_DATA. For each allocation unit SQL Server will creates IAM Pages. This IAM Page will keep tracks of all data pages belongs to particular table. In the result of DBCC IND we have columns like PagePID, IAMPID and PageType etc., PagePID is unique (within the database file), IAMPID defines a parent/child relationship between pages and PageType defines type of page

Type 1 is Data Page
 2 is Index Page
 3 and 4 are Text Pages
 8 is GAM Page
 9 is SGAM Page
10 is IAM Page
11 is PFS Page

In our first part if we run the DBCC IND we got result as NULL for first two columns IAMFID, IAMPID and value for PagePID. That row indicates IAM Page (if you observe page type then it shows as IAM Page). From second rows onwards of that result IAMPID hold the first row pagePID and keep tracks of all pages information of that particular table. As discussed and single GAM Page can hold extents information of a 4GB space in data file, In similar to that IAM page also holds 4 GB of information about allocation units (pages / extents) for an object. If it gets filled then another IAM page will be created for the same allocation unit, see the below picture for detailed explanation (Table Organization picture).

 

SQL Server Storage Architecture III

In continuation with previous part

Database consists of one ore more data files, and each file is divided into extents and each extent is divided into pages (8 pages). The storage unit is extent in the file, SQL Server has to know what are the free extents available or on which extent it has free pages etc., those information will be managed by again three different pages.

For any database file first 4 pages 1) PageHeader, 2)GAM, 3)SGAM, and 4)PFS are fixed. Using those pages SQL Server will came to know free pages, free extents etc.,


GAM – Global Allocation Map

The GAM page has a flag what are the available extents. When the first time file is created it breaks into extents and creates GAM Page and marks all the flags as empty. If you start using then it marks flags as reserved. One GAM Page will hold information about 64000 extents ie., approximately 4GB, In other words for 7GB data file it maintains 2 GAM Pages. The status of GAM shown as below

In simple words GAM and SGAM will helps database engine to identify free pages and free extents. This helps in extent management. In any data file first page (page 0 is header page), second page (page 1 is PFS), third page (page no 2 is GAM) and forth page (page no 3 is SGAM).

To check the GAM Allocation Status on GAM Page, Earlier we discussed about DBCC PAGE command which shows information about the pages


DBCC PAGE (0, 1, 2, 3) -- 0 –Indicates current database, 1-Indicates FileId, 2-Indicates PageNumber( GAM Page number is fixed) 3 –Indicated displays complete information

 GAM : Extent Allocation Status

(1:0) - (1:20) - Allocated
(1:22) – (1:44) - Not Allocated

The above status indicates in file Id-1, 0 to 20 are allocation and 22 to 44 are not allocated.
SGAM – Shared Global Allocation Map

The SGAM has a flag which represents where the free pages are available in mixed extents. Like GAM it also holds information about 64000 extents ie., approximately 4GB, In other words for 7GB data file it maintains 2 SGAM Pages.

To view the SGAM Allocation status
DBCC PAGE (0, 1, 3, 3) -- 0 –Indicates current database, 1-Indicates FileId, 2-Indicates PageNumber( SGAM Page number is fixed) 3 –Indicated displays complete information

SGAM – Extent Allocation Status

 (1:0) - (1:3448) = NOT ALLOCATED                            
 (1:3456) - (1:3464) = ALLOCATED                               
 (1:3472) - (1:19952) = NOT ALLOCATED                               

The above not allocated mixed extents has one free page and which is Allocated doesn’t have any free pages

PFS -- Page free space

It records allocation status of each page whether the page has been allocated or how much free percentage of space left on that page. Every PFS page covers 8088 pages. It is the 1st page in datafile.

SQL Server Storage Architecture II

In continuation with previous post,

The page size is 8kb and 96 bytes are reserved for page header and data rows will be stored after the header and rows can stored in sequential or any order. 

Page Header (96 bytes)
Data Rows
RowOffset –(36 bytes) It contains address of all records that are stored in data page.

When we use DBCC IND command it displays the pageIDs, we can see what actually is stored on those pages using DBCC Page command. 

DBCC Page (CurrentDBID, FileID, PageID, PrintOptions)

CurrentDBID - Zero (0) refers current database ID

FileID - If you have more than one MDF file to database then we need to specify that file ID, the default is 1 for mdf file

PageId - Get the page Id from DBCC IND command

PrintOption - 0– Only Page header information will be displayed

-1 – Page header plus row hex dumps and dump of page slot array
-2 – Page header and whole page hex dump
-3— Page header and detailed per row interpretation

dbcc TRACEON (3604)
go
dbcc ind('Credit','testPages',1)
go
dbcc page(0,1,3453,3) 
dbcc page(0,1,3453,3) with tableresults -- displays result in tabular format

Extents – The SQL Server doesn’t manage space at the level of page (as 8kb is very little space) so it uses EXTENTS, Extents are the higher level unit which space is managed; an extent is eight continuous pages or 64kb. 16 extents for MB. Initially the space will allocated page by page but once it reach size more than 1 extent then it starts allocating extent by extent not page by page. There are two types of extents

 1. Uniform Extents -- Extent belongs to one particular object (one table)

 2. Mixed Extents -- Extent shared across multiple objects (table1, table2, Index4)

SQL Server Storage Architecture

About Storage Unit Data Page in SQL Server

The fundamental unit of data storage in SQL Server is pages. All the data that stored inside the SQL Server is on data pages. A page has fixed size 8Kb. Data is stored in a data file with extension .mdf. All information stored in the pages, 128 pages will store per 1 MB. There are different types of pages, Data pages, Index pages etc.,

Each page has page header of 96 bytes which includes, Page Number, Page Type, Free Space and the allocation unit of the object that owns a page. Only data file has pages, Log file doesn’t have any page it contains only log records. Data rows are inserted into page sequentially after 96 bytes header.

Note – Row cant span across the pages. Then how SQL Server is managing large volumes in the tables, let see If you create a table with row size more than 8 kb then SQL Server wont allow you to create beyond 8096 bytes.

Create table test(id int, name char(2000), designation char(2000), address char(5000))
The above create table statement throws error because the row size limit got exceeded. Then how it is managing large volume of information in the table, based on the table data it will data in different allocation units (page groups)

IN_ROW_DATA -- If the table is very small (record size is less than 8000 bytes) all records stored in the pages as IN_ROW_DATA pages.

LOB_DATA -- Large Object Data like text not stored in data pages, it stored under LOB_DATA Pages, the 16 byte pointer is stored in data page to refer the LOB_DATA page.

ROW_OVERFLOW_DATA -- If the row size is greater than 8096 byes and varchar or nvarchar or varbinary columns grown to store more than 8096 then ROW_OVERFLOW_DATA comes into picture the excess data will be moved to ROW_OVERFLOW_DATA page, and reference of that page will be stored in the data page.


Create table test(id int, name varchar(2000), designation varchar(2000), address varchar(5000))
 Lets see practically whether allocation units are created or not

Create table TestPages(Id Int, col1 varchar(2000), col2 varchar(2000), col3 varchar(5000))

In the above table we created varchar columns with 9000 bytes, If the row is completely filled then the excess data will be stored in ROW_OVERFLOW_DATA Page, otherwise it will store in the IN_ROW_DATA page.

Note - If you create a table there is no page or memory allocated, Its just a metadata, so you cannot query the page information using DBCC command.

Insert into TestPages values (1,’kalyan’,’kumar’,’dba’)

 Let see whether the data is fit in the IN_ROW_DATA allocation unit or not

DBCC TRACEON(3604) Go

DBCC IND(‘DB_Name’,Table_Name’,1) – Displays information about the page and allocation units.

 If you observe the iam_chain_type column in the below result it has only IN_ROW_DATA unit, because the data is very small and fit in 8000 bytes

Insert into TestPages values (1,replicate(‘a’,2000),replicate(‘b’,2000),replicate(‘c’,5000))

After Insert is completed run the above DBCC IND command and verify, then the ROW_OVERFLOW_DATA allocation unit will be created.

To verify LOB_DATA page then we need to add LOB column into the above table

Alter table TestPages add col4 Ntext;

Insert into TestPages values (1,’test’,’test’,’test’,N’test’)

If you execute DBCC IND then you will be able to see LOB_DATA allocation unit is created.

If you observe the result of DBCC IND the first pagePID with NULL values on IAMFID and IAMPID is the primary page for this particular object that means all the pages which are related to this object will be stored on that pagePID

Once the table is created the object related information is stored under various system views
Sys.objects, sys.indexes, sys.partitions, sys.allocation_units
select name, object_id, type_desc, create_date from sys.objects where name='TestPAges'

Once the table is created in the database without any indexes (HEAP table) all pages are assigned to one partition.

When indexes defined on the table then partitions are framed for those indexes and those index pages are assigned to respective partitions. If you want to query number of rows in the object (table) those information will stored in the sys.partitions table

select OBJECT_NAME(object_id), partition_id, hobt_id, rows from sys.partitions where object_id = object_id('TestPages')

Partition ID – Container ID that holds data

Hobt_ID –Heap of B-Tree ID that contains rows for this partition.

Select * from sys.allocation_units where container_id = specify partitionId from the above query

SELECT so.name AS table_name, si.name AS index_name , sa.type_desc allocation_type, sa.data_pages, sp.partition_number, sp.rows
FROM sys.allocation_units sa, sys.partitions sp, sys.objects so, sys.indexes si
where so.object_id = si.object_id and si.index_id = sp.index_id and sp.partition_id = sa.container_id and so.object_id = sp.object_id and so.name = N'TestPages'

Login failed for user Reason: Server is in single user mode. Only one administrator can connect at this time.

I forgot my "sa" password and lost administrator account so I have started sql server using single user mode as below

1. Open sql server configuration manager
2. Added ;-m as a last parameter in startup parameters in advanced tab
3. Restarted the service
4. Start - run - cmd -
5. SQLCMD -SServerName

Then I got the below error message
Login failed for user Reason: Server is in single user mode. Only one administrator can connect at this time.

To fix the issue I have modified -m to -mSQLCMD in step 2 and it got fixed

After login into SQLCMD then issue the below commands to create new login

create login kalyansa with password='password1'
sp_addsrvrolemember 'kalyansa','sysadmin

or

create login [domain\kalyan] from windows;
sp_addsrvrolemember [domain\kalyan],'sysadmin'

You need to remember below points
1. Stop the SQL Server Agent
2. Make sure no other account is logged into SQL Server

 

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

You may receive the below error and couple of other errors while working with SQL Server 2012

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)
Index was outside the bounds of array (Microsoft.SQLServer.SMO)

Workaround

The errors may happen because you are trying to access SQL Server 2012 databases from SQL Server 2008 or SQL Server 2008 R2 management tools. To fix the issue you need to download SQL 2012 management tools.
 

New Folder is missing from right click menu

I spent many hours on the Internet to find the solution for this, Finally I find solution in microsoft site, below link shows the perfect solution provided by Linda Yan in technet windows forums

http://social.technet.microsoft.com/Forums/windows/en-US/9ee6ecdf-5958-4af4-b640-c659ada57bc4/option-to-create-new-folder-on-desktop-is-missing

Please logoff or restart the machine after completion of steps
 

Adding Secondary Data File To Mirrored Database Steps

Steps to add secondary data file to Mirrored Database

1. Make sure mirrored database is in synchronized state using below query - at principal server

SELECT db_name(database_id) as database_name, mirroring_state_desc,
mirroring_role_desc,  mirroring_partner_name,  mirroring_partner_instance
FROM sys.database_mirroring where mirroring_state_desc = 'SYNCHRONIZED'
2. Disable backup jobs at principal server if any

3. Run the below command to stop the mirroring -- at principal server
  Alter database MirrorNew set partner off

Now you can observe db mirroring will be removed and mirror server database state becomes restoring

4. Add secondary data file / filegroup to principal server database - at principal server
alter database mirrornew add file
 (name='second_datafile',
  filename='E:\Program Files\Microsoft SQL Server\DATA\MirrorNew_second.ndf',
  size = 50mb)
5. Make sure file got added to principal server database -- at principal server
select * from MirrorNew..sysfiles

6. Generate log backup of principal database  -- at principal server
backup log mirrornew to disk='e:\backups\mirrornewwithsecondary.trn' with init

7. You can verify whether log backup has newly added file or not using the below command
restore filelistonly from disk='e:\backups\mirrornewwithsecondary.trn'

8. Copy the log backup to mirror server

9. Run the below command to verify the file location of the database
select * from sysaltfiles where dbid = DB_ID('mirrornew')

10. Restore the log file on mirror server database using with move option -- at mirror server

restore log mirrornew from disk='e:\backups\mirrornewwithsecondary.trn'
with norecovery, move 'second_datafile' to 'E:\Program Files\Microsoft SQL Server\\DATA\MirrorNew_second.ndf'
11. Verify newly created file added to database or nore   -- at mirror server
select * from sysaltfiles where dbid = DB_ID('mirrornew')

12. Reinitiate the mirroring from mirror server -- at mirror server
alter database mirrornew set partner ='tcp://kalyanmirror:5022' -- It moves database into Inrecovery mode

13. Finally initiate mirroring step from principal server -- at principal server
alter database mirrornew set partner='tcp://kalyanprimary:5023'

Now database mirroring will established between principal and mirror, Execute the step 1 to make sure both databases are in sync state.

Adding Secondary Data File To Mirrored Database

We can add data files to mirrored database without break mirroring, in otherwords no need to reconfigure whole mirroring.

In this post we will see the steps

1. Make sure database mirroring is synchronized state

2. Disable Logbackup jobs if any

3. Remove existing mirror server from mirroring

4. Add secondary file or filegroup to database on principal server

5. Generate transaction log backup and restore it on mirror server with MOVE option

6. Reinitiate the mirroring

This will save a lot of time because we are not breaking the mirror completely and it is very much helpful while we are dealing with large database.

The database is enabled for database mirroring. Database mirroring must be removed before you drop the database. (Microsoft SQL Server, Error: 3743)

The database is enabled for database mirroring. Database mirroring must be removed before you drop the database. (Microsoft SQL Server, Error: 3743)

Workaround

1. Disable Mirroring
2. Remove endpoints
3. Drop databases

If database mirroring is failed while doing configuration it wont allow you drop the database on mirror server directly, follow the below steps to drop the mirroring

Alter database database_name set partner off     -- Disable mirroring

restore database database_name with recovery    -- Bring database to online

drop database database_name     -- To drop database from instance


 

SQL Server 2008 R2 service pack update failed with WMI Service error (Setup rule railed while upgrading SQL Server 2008 R2)

SQL Server 2008 R2 service pack patch update failed with WMI Service error

The Instance is a standalone still it is showing error related to cluster rule and path update failed at intial rule check.

The WMI is the database that stores meta data and defintions for WMI Classes.
Repository - %windir%System32\Wbem\Repository


The below solution works for me for Windows 2008

1. Stop and disable the WMI service in services.msc
2. You can run the below command to verify the consistency of WMI
winmgmt /verifyrepository
3. If the repository found inconsistent run the below command from elevated command prompt
Start - Programs - Accessories - Command Prompt - Run As Administrator
4. Winmgmt /resetrepository %windir%\System32\wbem
The above command will rebuilt repository if it is readable
5. Execute step 4 one more time
6. Enable the WMI service and make it automatic
7. Restart WMI service and Re-Run the SQL Patch rule check
8. If still failing then follow below steps
9. Execute step 1
10. Rename %windir%\System32\wbem\repository to some other name
11. Reboot the server
12. Execute step 6
13. Re-run the SQL Server rule check

If you are using Windows 2003, XP then you need to build repository manually, please go through steps provided my Microsoft.