1)
Index Analysis
Indexes are considered valuable for
performance improvements. Analyse all your indexes to identify non-performing
indexes and missing indexes that can improve performance.
2)
TempDB Space Review
Review the size and usage of your TempDB
database.
3)
Database Files (MDF, NDF) and
Log File Inspection
Review all the files and filegroups of each
of your databases and analysis them to identify any object or files that are
causing bottlenecks.
Reduce LDF File Size Steps:
CHECKPOINT -- flushes dirty pages to disk
DBCC DROPCLEANBUFFERS -- clears data cache
DBCC FREEPROCCACHE
1.right click on the database
2.goto properties--> click
option--->Recovery model change to simple
3.Then run the query in query window example
( dbcc
shrinkdatabase('SchoolManagementWORKING'))
4. Then right click database --> goto
properties --> click option--> Recovery model change to full
4)
DBCC Best Practices
Implementations
There are few DBCC commands to be avoided and
few very crucial for system. Understand the usage of DBCC FREEPROCCACHE, DBCC
SRHINKDATABASE, DBCC SHRINKFILE, DBCC DROPCLEANBUFFER, DBCC REINDEX, as well as
the usage of few system stored procedures like SP_UPDATESTATS. If you are
currently using any of the above mentioned and a few other DBCC maintenance
task commands carefully review their usage.
5)
Specify the column name instead
of * in select clause
6)
For filtering we need to use
where clause at the same time should not
use Having clause until unless
We required to use aggregate
functions.
7)
Instead of NOT In Operator we
have to use Exists Operator.
8)
Avoid
Indexing Small Tables
9)
Create
Multiple-Column Indexes
Multiple-column indexes are natural
extensions of single-column indexes. Multiple-column indexes are useful for
evaluating filter expressions that match a prefix set of key columns. For
example, the composite index CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC,
"First Name" ASC) helps
evaluate the following queries:
·
... WHERE "Last Name"
= 'Doe'
·
... WHERE "Last Name"
= 'Doe' AND "First Name" = 'John'
·
... WHERE "First
Name" = 'John' AND "Last Name" = 'Doe'
However,
it is not useful for this query:
·
... WHERE "First
Name" = 'John'
When
you create a multiple-column index, you should put the most selective columns
leftmost in the key. This makes the index more selective when matching several
expressions.
We
recommend that you always create indexes on primary keys. It is frequently
useful to also create indexes on foreign keys. This is because primary keys and
foreign keys are frequently used to join tables. Indexes on these keys lets the
optimizer consider more efficient index join algorithms. If your query joins
tables by using other columns, it is frequently helpful to create indexes on
those columns for the same reason.
When
primary key and foreign key constraints are created, SQL Server Compact
automatically creates indexes for them and takes advantage of them when optimizing
queries. Remember to keep primary keys and foreign keys small. Joins run faster
this way.
Use
Indexes with Filter Clauses
Indexes
can be used to speed up the evaluation of certain types of filter clauses.
Although all filter clauses reduce the final result set of a query, some can
also help reduce the amount of data that must be scanned.
A
search argument (SARG) limits a search because it specifies an exact match, a
range of values, or a conjunction of two or more items joined by AND. It has
one of the following forms:
·
Column operator
·
operator Column
SARG
operators include =, >, <, >=, <=, IN, BETWEEN, and sometimes LIKE
(in cases of prefix matching, such as LIKE 'John%'). A SARG can include
multiple conditions joined with an AND. SARGs can be queries that match a
specific value, such as:
·
"Customer ID" =
'ANTON'
·
'Doe' = "Last Name"
SARGs
can also be queries that match a range of values, such as:
·
"Order Date" >
'1/1/2002'
·
"Customer ID" >
'ABCDE' AND "Customer ID" < 'EDCBA'
·
"Customer ID" IN
('ANTON', 'AROUT')
An
expression that does not use SARG operators does not improve performance,
because the SQL Server Compact query processor has to evaluate every row to
determine whether it meets the filter clause. Therefore, an index is not useful
on expressions that do not use SARG operators. Non-SARG operators include NOT,
<>, NOT EXISTS, NOT IN, NOT LIKE, and intrinsic functions.
Sometimes
you can rewrite a subquery to use JOIN and achieve better performance. The
advantage of creating a JOIN is that you can evaluate tables in a different
order from that defined by the query. The advantage of using a subquery is that
it is frequently not necessary to scan all rows from the subquery to evaluate
the subquery expression. For example, an EXISTS subquery can return TRUE upon
seeing the first qualifying row.
 Note |
|
The SQL Server Compact
query processor always rewrites the IN subquery to use JOIN. You do not have
to try this approach with queries that contain the IN subquery clause.
|
For
example, to determine all the orders that have at least one item with a
25 percent discount or more, you can use the following EXISTS subquery:
SELECT "Order ID" FROM Orders O
WHERE EXISTS (SELECT "Order ID"
FROM "Order Details" OD
WHERE O."Order ID" = OD."Order ID"
AND Discount >= 0.25)
You
can also rewrite this by using JOIN:
SELECT DISTINCT O."Order ID" FROM Orders O INNER
JOIN "Order Details"
OD ON O."Order ID" = OD."Order ID" WHERE
Discount >= 0.25
Limit
Using Outer JOINs
OUTER
JOINs are treated differently from INNER JOINs in that the optimizer does not
try to rearrange the join order of OUTER JOIN tables as it does to INNER JOIN
tables. The outer table (the left table in LEFT OUTER JOIN and the right table
in RIGHT OUTER JOIN) is accessed first, followed by the inner table. This fixed
join order could lead to execution plans that are less than optimal.
12) Use Parameterized Queries
If your application runs a series of
queries that are only different in some constants, you can improve performance
by using a parameterized query. For example, to return orders by different customers,
you can run the following query:
SELECT "Customer ID" FROM Orders WHERE "Order
ID" = ?
Parameterized
queries yield better performance by compiling the query only once and executing
the compiled plan multiple times. Programmatically, you must hold on to the
command object that contains the cached query plan. Destroying the previous
command object and creating a new one destroys the cached plan. This requires
the query to be re-compiled. If you must run several parameterized queries in
interleaved manner, you can create several command objects, each caching the
execution plan for a parameterized query. This way, you effectively avoid
re-compilations for all of them.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
13)
While there is case to use IN or
BETWEEN clauses in the query, it is always advisable to use BETWEEN for better
result.
14)
Always avoid the use of
SUBSTRING function in the query.
