Index fragmentation in SQL Server 2005

When data is inserted, updated, or deleted in a database table, if indexes exist that operate on the table, they must be maintained to reflect the table data changes. The maintenance of these indexes will eventually cause the indexes to become less efficient. This inefficiency is typically due to the index becoming fragmented.

There are two types of fragmentation:
 external and internal. External fragmentation occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server 2005 to perform extra work to return ordered results. For the most part, external fragmentation isn’t too big of a deal for specific searches that return very few records or queries that return result sets that do not need to be ordered.


Internal fragmentation occurs when there is too much free space in the index pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the index pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find data) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits, which also require additional system resources to perform.

Difference between a cluster and a grid....

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.

This article walks you through how to delete multiple duplicate rows..

CREATE DATAVASE MANOJ

--------

USE MANOJ

------

CREATE TABLE AKKI

(ID INT,

FNAME VARCHAR(50),

LNAME VARCHAR(50)

)

------

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 11,'Jiong','Hong'

insert into AKKI select 25,'Sandra','Mator'

insert into AKKI select 35,'Chun','Chang'

insert into AKKI select 21,'Yuki','Fukushima'

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 25,'Sandra','Mator'

insert into AKKI select 25,'Sandra','Mator'

--------

SELECT * FROM AKKI ORDER BY ID

---------

The first step in deleting duplicate rows is to generate a unique row id for the entire table. This can be done using the Row_Number() function.

 

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

Step 2

The second step in deleting duplicate rows is to generate unique row ids for every group.

Now, by using a co-related sub-query we can produce unique row ids for each group.

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(

SELECT COUNT(*) FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY ID

) AS ROW,ID,LNAME,FNAME FROM AKKI

) AS A

WHERE A.ID=B.ID AND A.ROW<B.ROW)+1

END ,ID,FNAME,LNAME FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

)AS B

---------
--------By using a co-related sub-query we genrate unique row id for each row--- row grouprow id fname lname
1 1 1 Jen Ambelang
2 2 1 Jen Ambelang
3 3 1 Jen Ambelang
4 1 11 Jiong Hong
5 1 21 Yuki Fukushima
6 1 25 Sandra Mator
7 2 25 Sandra Mator
8 3 25 Sandra Mator
9 1 35 Chun Chang

 

Step 3

The last step in deleting duplicate rows is to use the common table expression, as shown below

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)

AS

(

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

) AS A WHERE A.ID=B.ID AND

A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER

BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

)AS B

)

DELETE FROM DUPLICATE WHERE GROUPROW<>1
---(DELETE FROM DUPLICATE WHERE GROUPROW<>1--(to remove the duplicate records))

--------

VERIFY THE OUTPUT

--------

SELECT * FROM AKKI
----------
select * from AKKI-----GROUPROW IS ALIAS WHICH USE IN THE SELECT QUERY
ID FNAME LNAME
1 Jen Ambelang
11 Jiong Hong
25 Sandra Mator
35 Chun Chang
21 Yuki Fukushima

Step 1

what is the oracle instance?

Instance is a combination of memory structure and process structure. Memory structure is SGA and Process structure is background processes.

SGA - System or Shared Global Area.

Components of SGA:-

DBBC - Database Buffer Cache

SP - Shared Pool. It is futher divided into Library Cache(LC) and Data Dictionary Cache(DDC) or Row Cache.

RLB - Redolog Buffer

Background Process:-

1. Mandatory Processes-

a. SMON - System Monitor

b. PMON - Process Monitor

c. DBWR - Database writer

d. LGWR - Log Writer

e. CKPT - Check point

f. RECO - Recoverer

2. Optional Process:-

a. ARCN

b. RBAC

c. MMAN

d. MMON

e. MMNL

sharepoint - service not available

resolved the issue by restarting the IIS service under the
administrator account default for the service. followed these methods
for answer:

CAUSE
This issue may occur if the application pool for the virtual server is
configured incorrectly in Microsoft Internet Information Services
(IIS) 6.0. This issue may occur if one or more of the following
conditions are true: * The application pool is not running.
* The application pool account uses an incorrect password.
* The application pool account is not a member of both the IIS_WPG
group and the STS_WPG group on the server.

RESOLUTION
To resolve this problem, follow these steps: 1. Verify that the
application pool is configured for the virtual server. The default
application pool is MSSharePointPortalAppPool.

Follow these steps to determine the application pool that the virtual
server is using: a. Click Start, point to Administrative Tools, and
then click Internet Information Services (IIS) Manager.
b. Expand ServerName, expand Web Sites, right-click the virtual
server, and then click Properties.
c. Click the Home Directory tab.

The application pool that is configured for the virtual server is
listed in the Application pool box.
d. Click OK.

2. Verify that the password for the application pool account is
correct. IIS does not automatically poll password changes in Active
Directory directory service. If the application pool account is a
domain account, and the password expires, you may receive the error
message that is described in the "Symptoms" section of this article
after a new password is specified for the account.

Follow these steps to verify that the password for the application
pool account is correct: a. In Internet Information Services (IIS)
Manager, expand Application Pools.
b. Right-click the application pool that is configured for the
virtual server (for example, right-click MSSharePointPortalAppPool),
and then click Properties.
c. Click the Identity tab.
d. In the Password box, type the password of the application pool
account that is listed in the User name box, and then click OK.
e. In the Confirm Password dialog box, type the password again, and
then click OK.

3. Verify that the application pool account is a member of both the
IIS_WPG group and the STS_WPG group on the server:

Use one of the following methods as appropriate to your situation:a.
If Windows SharePoint Services 2.0 is installed on a member server: 1.
Click Start, point to Administrative Tools, and then click Computer
Management.
2. Expand Local Users and Groups, and then expand Users.
3. Right-click the account that is used by the application pool for
the virtual server, and then click Properties.
4. Click the Member of tab.

Verify that both IIS_WPG and STS_WPG appear in the Member of list. If
one or both groups are not listed, add the IIS_WPG group, the STS_WPG
group, or both groups (as appropriate) to the list.

b. If Windows SharePoint Services 2.0 is installed on a domain
controller: 1. Start Active Directory Users and Computers.
2. Expand Users.
3. Right-click the account that is used by the application pool for
the virtual server, and then click Properties.
4. Click the Member of tab.

Verify that both IIS_WPG and STS_WPG appear in the Member of list. If
one or both groups are not listed, add the IIS_WPG group, the STS_WPG
group, or both groups (as appropriate) to the list.


4. Restart IIS to recycle the application pools: a. In Internet
Information Services (IIS) Manager, right-click ServerName, point to
All Tasks, and then click Restart IIS.
b. Click Restart Internet Information Services on ServerName, and
then click OK

.