SQL Server best practices (index optimization,Database Performance)

SQL Server 2005 Index Optimization Best Practices

Do you have a maintenance window to perform database reindexing?
Have you ever performed full set of re-organize and re-index process on a bigger database?
Do you know there are best practices to deploy in this regard?
On large database systems, with large numbers of insert and update commands, the problem of index fragmentation is one of the main causes of performance degradation and a proper index optimization strategy is a must. Also following are set of counters you need to keep in mind:
· Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on column which are used in JOIN Condition.
· Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of all the insert, updates, deletes to the table.
· Smaller Index Key gives better performance than Index key which covers large data or many columns
· Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
· Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.
Also there are few basic guidelines when you need to create a database from scratch such as,
· Design a normalized database.
· Optimize a database design by denormalizing.
· Optimize data storage.
· Manage concurrency - by selecting the appropriate transaction isolation level.
· Select a locking granularity level.
· Optimize and tune queries for performance.
· Optimize an indexing strategy.
· Decide when cursors are appropriate.
· Identify and resolve performance-limiting problems.
· Be familiar with index structures and index utilization. Specifically, they must understand the interaction between non-clustered indexes, clustered indexes and heaps. A must know why a covering index can improve performance.
· Be able to design a database to third normal form (3NF) and know the trade offs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.

SQL SERVER - 15 Best Practices for Better Database Performance

I have written 14 best practices here, read them all and let me know what is as per your opinion should be the 15th best practice.
1. Store relevant and necessary information in the database instead of application structure or array.
2. Use normalized tables in the database. Small multiple tables are usually better than one large table.
3. If you use any enumerated field create look up for it in the database itself to maintain database integrity.
4. Keep primary key of lesser chars or integer. It is easier to process small width keys.
5. Store image paths or URLs in database instead of images. It has less overhead.
6. Use proper database types for the fields. If StartDate is database filed use datetime as datatypes instead of VARCHAR(20).
7. Specify column names instead of using * in SELECT statement.
8. Use LIKE clause properly. If you are looking for exact match use “=” instead.
9. Write SQL keyword in capital letters for readability purpose.
10. Using JOIN is better for performance then using sub queries or nested queries.
11. Use stored procedures. They are faster and help in maintainability as well security of the database.
12. Use comments for readability as well guidelines for next developer who comes to modify the same code. Proper documentation of application will also aid help too.
13. Proper indexing will improve the speed of operations in the database.
14. Make sure to test it any of the database programming as well administrative changes.

Difference between factory and abstract factory pattern

Abstract Factory:- Creates an instance of several families of classes. we are using an interface to create a family of related or dependant classes without specifying their concrete classes.
abstract factory uses factory method to create the object of familites class.
abstract factory pattern focus on the delegation of the instantiation to another object

The classes and/or objects participating in this pattern are:
AbstractFactory (interface)
declares an interface for operations that create abstract products.
ex.- ContinentFactory
ConcreteFactory (class)
implements the operations to create concrete product objects.
basically it the object of various concrete product and it will return the type of abstractProduct.
ex.- AfricaFactory, AmericaFactory
AbstractProduct (interface)
declares an interface for a type of product object. basically it contain the definition of product or we can say it is the template of product.
ex.- Herbivore, Carnivore
Product ( class)
defines a product object to be created by the corresponding concrete factory
implements the AbstractProduct interface.
it is a kind of implementation of template product.
ex.- Wildebeest, Lion, Bison, Wolf
Client (class)
uses interfaces declared by AbstractFactory and AbstractProduct classes
it's a real user of the patterns.
ex.- AnimalWorld

// Abstract Factory pattern -- Real World example
using System;

namespace DoFactory.GangOfFour.Abstract.RealWorld
{
///

/// MainApp startup class for Real-World
/// Abstract Factory Design Pattern.
///

class MainApp
{
///

/// Entry point into console application.
///

public static void Main()
{
// Create and run the African animal world
ContinentFactory africa = new AfricaFactory();
AnimalWorld world = new AnimalWorld(africa);
world.RunFoodChain();

// Create and run the American animal world
ContinentFactory america = new AmericaFactory();
world = new AnimalWorld(america);
world.RunFoodChain();

// Wait for user input
Console.ReadKey();
}
}


///

/// The 'AbstractFactory' abstract class
///

abstract class ContinentFactory
{
public abstract Herbivore CreateHerbivore();
public abstract Carnivore CreateCarnivore();
}

///

/// The 'ConcreteFactory1' class
///

class AfricaFactory : ContinentFactory
{
public override Herbivore CreateHerbivore()
{
return new Wildebeest();
}
public override Carnivore CreateCarnivore()
{
return new Lion();
}
}

///

/// The 'ConcreteFactory2' class
///

class AmericaFactory : ContinentFactory
{
public override Herbivore CreateHerbivore()
{
return new Bison();
}
public override Carnivore CreateCarnivore()
{
return new Wolf();
}
}

///

/// The 'AbstractProductA' abstract class
///

abstract class Herbivore
{
}

///

/// The 'AbstractProductB' abstract class
///

abstract class Carnivore
{
public abstract void Eat(Herbivore h);
}

///

/// The 'ProductA1' class
///

class Wildebeest : Herbivore
{
}

///

/// The 'ProductB1' class
///

class Lion : Carnivore
{
public override void Eat(Herbivore h)
{
// Eat Wildebeest
Console.WriteLine(this.GetType().Name +
" eats " + h.GetType().Name);
}
}

///

/// The 'ProductA2' class
///

class Bison : Herbivore
{
}

///

/// The 'ProductB2' class
///

class Wolf : Carnivore
{
public override void Eat(Herbivore h)
{
// Eat Bison
Console.WriteLine(this.GetType().Name +
" eats " + h.GetType().Name);
}
}

///

/// The 'Client' class
///

class AnimalWorld
{
private Herbivore _herbivore;
private Carnivore _carnivore;

// Constructor
public AnimalWorld(ContinentFactory factory)
{
_carnivore = factory.CreateCarnivore();
_herbivore = factory.CreateHerbivore();
}

public void RunFoodChain()
{
_carnivore.Eat(_herbivore);
}
}
}

Factory Method :- it Creates an instance of several derived classes (concrete class)
this pattern Defines an interface for creating An Object but let sub class decide which class to instantiate.
factory method/pattern uses inheritance to decide which object has to be instantiated.
it
focus on the use of inheritance to decide the object to be instantiated.
factories means " to decouple the creation of an object from their code"

The classes and/or objects participating in this pattern are:
Product (Page)
defines the interface of objects the factory method creates
ConcreteProduct (SkillsPage, EducationPage, ExperiencePage)
implements the Product interface

Creator (Document)
declares the factory method, which returns an object of type Product. Creator may also define a default implementation of the factory method that returns a default ConcreteProduct object. may call the factory method to create a Product object.

ConcreteCreator (Report, Resume)
overrides the factory method to return an instance of a ConcreteProduct.
Code :-


// Factory Method pattern -- Structural example
using System;

namespace DoFactory.GangOfFour.Factory.Structural
{
///

/// MainApp startup class for Structural
/// Factory Method Design Pattern.
///

class MainApp
{
///

/// Entry point into console application.
///

static void Main()
{
// An array of creators
Creator[] creators = new Creator[2];

creators[0] = new ConcreteCreatorA();
creators[1] = new ConcreteCreatorB();

// Iterate over creators and create products
foreach (Creator creator in creators)
{
Product product = creator.FactoryMethod();
Console.WriteLine("Created {0}",
product.GetType().Name);
}

// Wait for user
Console.ReadKey();
}
}

///

/// The 'Product' abstract class
///

abstract class Product
{
}

///

/// A 'ConcreteProduct' class
///

class ConcreteProductA : Product
{
}

///

/// A 'ConcreteProduct' class
///

class ConcreteProductB : Product
{
}

///

/// The 'Creator' abstract class
///

abstract class Creator
{
public abstract Product FactoryMethod();
}

///

/// A 'ConcreteCreator' class
///

class ConcreteCreatorA : Creator
{
public override Product FactoryMethod()
{
return new ConcreteProductA();
}
}

///

/// A 'ConcreteCreator' class
///

class ConcreteCreatorB : Creator
{
public override Product FactoryMethod()
{
return new ConcreteProductB();
}
}
}


more information download the below links :-
http://userpages.umbc.edu/~tarr/dp/lectures/Factory-2pp.pdf
http://www.dofactory.com/Patterns/PatternFactory.aspx
http://www.dofactory.com/Patterns/PatternAbstract.aspx

Golden Thoughts

"Legs down to earth and eyes looking beyon the sky..............."

You can never know how much good a simple smile can do................

The more you praise and celebrate your life, the more there is in life to celebrate.................

Dream what you want to dream,go where you want to go,be what you want to be,beacuse you have only one life and one chance to do all the things you want to do............

You thoughts is architect for your destiny………

“Attitude is just as important as ability.” Success is the ability to go from one failure to another with no loss of enthusiasm "

A good wife is one who serves her husband in the morning like a mother does, loves him in the day like a sister does and pleases him like a prostitute in the night.

A man is born alone and dies alone; and he experiences the good and bad consequences of his karma alone; and he goes alone to hell or the Supreme abode.

A man is great by deeds, not by birth.

"A person should not be too honest. Straight trees are cut first and honest people are screwed first. ..........."

"As a single withered tree, if set aflame, causes a whole forest to burn, so does a rascal son destroy a whole family."

As long as your body is healthy and under control and death is distant, try to save your soul; when death is immanent what can you do? As soon as the fear approaches near, attack and destroy it.

Before you start some work, always ask yourself three questions - Why am I doing it, What the results might be and Will I be successful. Only when you think deeply and find satisfactory answers to these questions, go ahead.

Books are as useful to a stupid person as a mirror is useful to a blind person.

Do not be very upright in your dealings for you would see by going to the forest that straight trees are cut down while crooked ones are left standing.

Do not reveal what you have thought upon doing, but by wise council keep it secret being determined to carry it into execution.

"Jab dil ki hasrate dil me hi dafan hone lage to aise me hale dil wayan nahi hota...aise me kaise dikhaye apne jakhme ko dar lagta hai ,koi ise nasura na bana de..........."

God is not present in idols. Your feelings are your god. The soul is your temple.

"Never make friends with people who are above or below you in status. Such friendships will never give you any happiness. "

"Once you start a working on something, don't be afraid of failure and don't abandon it. People who work sincerely are the happiest.."

"The biggest guru-mantra is: never share your secrets with anybody. It will destroy you. "

The life of an uneducated man is as useless as the tail of a dog which neither covers its rear end, nor protects it from the bites of insects.

"The world's biggest power is the youth and beauty of a woman"

Whores don't live in company of poor men, citizens never support a weak company and birds don't build nests on a tree that doesn't bear fruits.

"You're the author of your own autobiography. If you want a better life, write a better script."

Manifest plainness,embrace simplicity,reduce selfishness, have few desires

Keep your goal focused in your mind at all times.....

"what you can achieve is limited only by how far you can see..."

Work while others are wishing.................................. :)

"We are responsible for what we are, and whatever we wish ourselves to be, we have the power to make ourselves. If what we are now has been the result of our own past actions, it certainly follows that whatever we wish to be in future can be produced by our present actions; so we have to know how to act.”Take up one idea. Make that one idea your life - think of it, dream of it, live on idea. Let the brain, muscles, nerves, every part of your body, be full of that idea, and just leave every other idea alone. This is the way to success. "

"winner stands alone..........."

Golden Words

if you win you need not explain.....but if you lose you should not be there to explain..

"Don't compare yourself with anyone in this world.if you do so,you are insulting yourself...
winning doesn't always mean being first,winning means you are doing better than you have done before..."

Slience is a true friend who never betrays.

"Duniya jise kehte hain jadoo ka khilona hai Mil jaye to mitti hai kho jaye to sona hai......."

"Koi deewana kehta hai, koi pagal samajhta hai Magar dharti ki bechaini ko bas badal samajhta hai Main tujhse door kaisa hoon, tu mujhse door kaisi haiYe tera dil samajhta hai ya mera dil samajhta hai….. "

"Mohbbat ek ehsaason ki pawan si kahani hai, Kabhi Kabira deewana tha, kabhi meera deewani hai, Yahan sab log kehte hain meri aankhon main aasoon hain, Jo tu samjhe to moti hain, jo na samjhe to paani hai "

"Bhramar koi kumudni par machal baitha to hungama, Hamare dil main koi khwaab pal baitha to hungama, Abhi tak doob ke sunte sab kissa mohhabat ka, Main kisse ko haqeekat main badal baitha to hungama ..."

"Bahut bikhra bahut toota thapede seh nahin paaya Hawaaon ke isharon par magar main beh nahin paaya Adhoora ansuna hi reh gaya yun pyar ka kissaKabhi tum sun nahin paaye, kabhi main keh nahin paaya .."

"Samay ke sila pe madhur chitr kitne........kisi ne likhe kisi ne mitay.........kisi ne likhe aasuo se kahani........... kisi ne padha sirf do bund pani....."

"If a drop of water falls in lake there is no identity. But if it falls on a leaf of lotus it shine like a pearl. So choose the best place where you would shine.."

Among those whom I like or admire, I can find no common denominator, but among those whom I love, I can: all of them make me laugh.
When dealing with the insane, the best method is to pretend to be sane.
The man said, 'I only wrote the truth. I said what you said but in a different way.'What he had written was: 'Today is a beautiful day and I cannot see it.'Do you think the first sign and the second sign were saying the same thing?Of course both signs told people the boy was blind. But the first sign simply said the boy was blind. The second sign told people they were so lucky that they were not blind. Should we be surprised that the second sign was more effective?
Moral of the Story: Be thankful for what you have. Be creative. Be innovative. Think differently and positively.Invite others towards good with wisdom. Live life with no excuse and love with no regrets. When life gives you a 100 reasons to cry, show life that you have 1000 reasons to smile. Face your past without regret. Handle your present with confidence. Prepare for the future without fear. Keep the faith and drop the fear.

Great men say, 'Life has to be an incessant process of repair and reconstruction, of discarding evil and developing goodness…. In the journey of life, if you want to travel without fear, you must have the ticket of a good conscience.'

The most beautiful thing is to see a person smiling… And even more beautiful is, knowing that you are the reason behind it!

"Dream is not that what you see in your sleep.. Dream is that thing which doesn't allow you to sleep"