Skip to content

SQL Azure performance optimization checklist

March 7, 2012

Database tuning is continuous and iterative process. It involves identifying the bottlenecks, find their cause, apply resolution and check if resolution works as expected.  QL Azure is same database engine as on premises SQL Server, most of the things apply equally on SQL Azure as well.

In order to make use of tools like SQL profiler it is always good idea to create and perform initial optimization using on premises instance and later move to SQL Azure. It might save a lot of time and cost.

It is always good idea to create performance baseline before starting up performance optimization task to measure the effectiveness of optimization.

For on premises database we focus on various areas as listed below.

1) Database design

2) Query design

3) Configuration setting

4) Hardware and software administration.

5) Database backup

In case of windows azure apart from first two items, other have become less relevant for performance tuning. These first two points are the area where best results are achieved in performance tuning . Which is also shown in figure below.

1) Database design:- Following is checklist for database design for better performance

a) Database design start with applying normalization and create highly normalized databases. These databases are good for write performance. Once we are done with normalization and ready for performance optimization phase, de normalization is applied appropriately to improve the performance.

b) Separate OLAP (Online Analytical processing ) and OLTP( Online transaction processing workloads.

c) Apply entity integrity constraints – this enable optimizer to come up with better query plans.

d) Apply domain and referential integrity constraints – similar to above it helps query optimizer.

e) Choose appropriate data type.

f) Use indexed views in de-normalization.

g)      Create indexes and follow index best practices : –

I.            Use narrow column for indexes.

II.            Use columns with high selectivity for indexes.

III.            For composite index use leading column with high selectivity.

IV.            Design indexes based on the where clause in queries.

V.            Use index covering with include clause.

VI.            Create clustered index very carefully since every non clustered indexes are based on the clustered index.

2) Query Design :- Following is a list of various items which should be kept in mind while designing queries.

a)      Avoid arithmetic operator and functions in where clause.

b)      Ensure no implicit type conversions.

c)       Adopt best practices for reusing the execution plans.

d)      Adopt best practices for database transactions.

e)      Eliminate or reduce overhead of  database cursors.

f)       Evaluate alternate to temp tables.

Above guide lines should help to stay streamlined.

Advertisements

From → Tech

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: