Answer / sravan kumar vemuri

The most important option to increase the query performance
is to create index on the table.
First one should concerntrate on the columns used in the
where cluase.

But indexes can hit the performance for insert queries. So
before giving an insert query, better to remove the index
and then to recreate the index.

Answer / ponguru

It is very big thing...Let me explain in short..Run the
profiler and catch the Logical reads/Physical reads/CUP
values.

find out where values are more(huge) and take Excution plan
and study it ...here you can find out where the problem.

In general scenarios Data also matters that means If your
Dev/Test enviroment not having that much Data which has
Prod.

Get the large Data and do above scenarios and you came know
what is cause.

Answer / samba shiva reddy . m

1.In the select statement give which ever the columns you need
don't give select * from
2.Do not use count()function
3.use indexes and drop indexes regularly it B tree structure we are not going to drop means it will take time for creating indexes it self.
4.when joining the tables use the id's(integer type)not string
5.Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
6.Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
7.Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
8.Try to use UNION ALL in place of UNION.
9.Avoid the 'like' and notlike in where clause.
10.Use non-column expression on one side of the query because it will be processed earlier.
11.To store large binary objects, first place them in the file system and add the file path in the database.

Answer / khalid moin

try to use function based indexes and use it when needed..
it will not be used unnecessorily.

use clustering concept while creating table it will make you
join operation fast if more join operation u r going to
perform on the tables

Answer / murtaza

first we will check the columns which r there in table.
then the frequency of usage of each columns.Index should be
created on those columns which has maximum usage or columns
which can be use frequently.
so to check which columns should have the index and how we
can optimize the query can be done by
1.SQL Profiler
2.By Normalization

Answer / pushpendra kumar

The important factors for performance optimization are as
follows:
1. Proper data type assignment;
2. Selection of proper data reader;
3. Proper command to retrieve the data from the database.

Answer / bata

i think above 2 answers arent much betr.wil try to give
perfect answer soon

What the different types of Replication and why are they used?

0 Answers  

Is resultset an interface?

0 Answers  

What are translations and its use? : sql server analysis services, ssas

0 Answers  

What stored procedure can you use to display the current processes?

0 Answers  

Tell me about Triggers?

3 Answers   Cap Gemini,

What method is used by the Command classes to execute SQL statements that return single values?

0 Answers   B-Ways TecnoSoft,

what is an sql server?

1 Answers  

Difference b/w Clustered & non-clustered index? Not the bookish definition, but how they internally works in SQL Server?

1 Answers   United Healthcare,

Do you know data definition language, data control language and data manipulation language?

0 Answers  

Which are the third-party tools used in sql server and why would you use them?

0 Answers  

How can i Relate Tables in SSIS

0 Answers   HCL,

What options are there to delete rows on the publisher and not on the subscriber? : sql server replication

0 Answers