:::: MENU ::::

Posts Categorized / Performance Tunning and Optimization

  • Apr 21 / 2009
  • 0
Data Modeling and Database Design, dbDigger, Performance Tunning and Optimization

Is normalization always required in database design

Normalization is an important aspect of database design. It removes redundancy and ensures relational integrity of database. Most of OLTP databases are designed up to 3rd normal form. It is important to understand that all database designs do not require normalization as it is commonly implemented till third normal form.
OLTP databases or some times called production databases make more use of normalization than OLAP or data ware house databases. Benefits of normalized database cost processing power to resolve joins and relations.
For this reason in data ware house modeling, normalization is avoided. Due to large amount of data data ware housing architects are conscious of processing time of their scripts and reports. So they prefer to consume disk resources through redundancy in data but do not afford to use joins in their scripts because joins in such large amount of data would slow down the query processing.
So we can say that use of normalization depends upon amount of data, type of database and being used hardware resources.
More levels of normalization used more tables will be created and as a result more joins will be used.

  • Apr 21 / 2009
  • 0
dbDigger, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Considerations while using missing indexes information of SQL Srever

In my recent article information about missing indexes we discussed that how to use SQL Server suggestions for missing indexes. It seems logical to discuss some major issues while considering to create missing indexes.
Clustered index is automatically created for a table when primary key is created. Most of the time primary key is auto generated number. As clustered indexes are related to physical arrangement of records, so there may be just one clustered index in a table. Now if SQL Server suggests any column to create index upon, then a non clustered index may be created on that column after proper testing and consideration.
There may be as many non clustered indexes as required in a table.
Another important aspect to consider is that although indexes are very use full for search, update/retrieve operations but on the other hand indexes may degrade performance for insert/delete operations. So if a table undergoes heavy insert/delete operations occasionally, then it would be better to limit the indexes to just clustered index on primary key.

  • Apr 16 / 2009
  • 1
dbDigger, Dynamic Management Views DMV, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Get information about missing indexes in SQL Server 2005 and onwards databases

Indexes are core of optimization and performance of DBMS. Keen attention is required while planning indexes. Fortunately SQL Server contains a suggestion mechanism for creating indexes where required. While optimizing the submitted queries, Query optimizer notes down the columns where indexes are missing and their creation may boost the performance. First let us view the script to retrieve the missing indexes information in SQL Server 2005 databases

SELECT DB_NAME(database_id) AS [DATABASE],
equality_columns, inequality_columns,
included_columns, STATEMENT
FROM sys.dm_db_missing_index_details
ORDER BY 1

Result of above script will be generated in five columns.

  • [Database] is name of database where missing index is detected
  • [equality_columns] is list of columns that are used in equality comparison and index is required on these.
  • [inequality_columns] is list of columns that are used for inequality comparison and index is required on these.
  • [included_columns] is list of columns that are used in query for other than comparison (e.g. Select List). And covering index on these is suggested.
  • [Statement] is name of table along with column where index is missing

It is important to mention that this information is geather since the time when SQL Server Service starts. Existing information about missing indexes will be vanished with service stop/restart. So DBA should backup missing indexes information time to time for later analysis.
Also note that all this information is based on queries submitted to SQL Server query engine either by user or application. So there may be other objects where indexes are required but these are not suggested because of not being used in scripts.
For deep and detailed monitoring/analysis i would suggest Database Engine Tuning Advisor.

  • Jan 29 / 2009
  • 0
dbDigger, Performance Tunning and Optimization, T-SQL Tips and Tricks

Using GROUP BY and GROUP BY ALL in T-SQL scripts

As we know that GROUP BY clause is used to summarize the given columns in select statement. Example of using GROUP BY in AdventureWorks database is as follows. In this example we will calculate the total amount due of each date between July 01 2001 to July 31 2001.

 
USE AdventureWorks  
 GO  
 SELECT OrderDate,SUM(TotalDue) TotalDueByOrderDate  
 FROM Sales.SalesOrderHeader  
 WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'  
 GROUP BY OrderDate  

As a result 31 rows are returned in following format.

ResultOfGroupBy

Now If there is a situation that we have to include all row values in result, even if these do not qualify the WHERE clause, then we have to use GROUP BY ALL. Running above script with GROUP BY ALL provides different result set.

 
USE AdventureWorks  
 GO  
 SELECT OrderDate,SUM(TotalDue) TotalDueByOrderDate  
 FROM Sales.SalesOrderHeader  
 WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'  
 GROUP BY ALL OrderDate  

ResultOfGroupByAll
Now we get 1124 rows. Even the rows that do not qualify the where clause have been returned but not included in calculation.

Consult us to explore the Databases. Contact us