Close Menu

SQL-SERVER

 

Perfomance Tuning of SQL-Server


Before You Proceed we recommend you to
read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.

Performance issues That can be the result of an

improper  Database design


1) Normalization.

Data Normalization Reduces the data being stored on the physical disc. Normalized tables produce rapid, efficient joins, hence reduces Query time drastically, Normalizations makes it possible to create lesser indexes which makes the server more efficient. This will also reduce I/O issues, Memory issues, High CPU Usage issues because of lesser data being stored on the disk.

2) Query design to lower read times.

Queries Have to be designed in a way where there is the least load on the database server.
Making sure that every time there is a where clause for a column either we should have a clustered index nor a non-clustered index. Removal of functions inside a query reduces the query executions time and compensate by using computed columns by storing the computed values.

3) Table partitioning techniques.

If a table has 10 columns and only 5 out of ten columns are being use its better to portion the table into two with 5 column’s which are mostly read and other 5 least used into another table. This reduces I/O and increases efficiency, The same applies to row data too rows which will be used the most can be in one table and the ones read least can be into other table.

4) Proper Indexing of tables.

Indexing is a tricky situation. Its always advisable to create indexes where most of the where clause is applied, preferably a clustered index, and the rest if required should be a non-clustered index. Do not create indexes if deemed unnecessary.

5) Using temp tables or joins instead of cursors.

Temp tables are faster and does not lock tables hence reduces the Locks and Disk I/O which makes huge gains when performance is considered.

6) Improper maintenance schedule Design.

If maintenance activities are scheduled at high traffic times  and we perform a maintenance activity at that that time that will impact the performance of the server. Hence Maintenance should be scheduled to be done at the least usage time.

7) Improper isolation levels.

Improper isolation levels create lag in the query and improper data being displayed hence proper Isolations also should be implemented while designing the Database.

8) Inclusion of functions in the WHERE clause.

Instead of a simple selection of data, data will have to be computed if a function is involved. This drastically increase the query execution time. Hence its wise to design a query with the least amount functions. This can be achieved by using Temporary tables and computed columns.

9) No Separate Data and Log Files.

Rule no 1 while installation of SQL-server is to define separate drives for  Data and Log-Files. This reduces data contention, Space issues, File growth issues.

10) Improper capacity Planning.

Improper capacity planning depending on the expected transactions and data storage Cause not just perfomance but sometimes even policy and SLA related situations.Hence Capacity planning also could be come a reason for sudden bottlenecks popping up in time.

Continued to Next Page ------>>

Performance issues That can be the result of Improper Maintenance