Increase your sql performance with DBCC DBREINDEX

Recently i have come across an issue with page time out. There was stored procedure that performs updates and inserts to 3 database tables on save. Usually this worked fine but gives a time out expired error when there are any sql jobs running in the background at the time of action. It was quite clear that this is a performance issue and will get solved if the stored procedure was optimized. But the thing is stored procedure was already optimized and there was nothing else to optimize it. I researched for about 1 week but no luck. Then i found out about DBCC DBREINDEX in sql that solved the issue at last. :-)

Those who are not aware of DBCC DBREINDEX, lets have some knowledge about it.

When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.

Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.

The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database

TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.

Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)

Example
DBCC DBREINDEX ('Vendor')
DBCC DBREINDEX ('Vendor','IX_Vendor',80)

Believe me! this increases the performance of the actions that we perform on these tables.

SQL Reports Report Viewer alignment issue in Firefox and Safari

Guys, i have faced an alignment issue with sql reporting services reports in Firefox and Safari browser. Below are the details of the issues and the solution that i could find. Hope this is helpful.

Issue in Firefox: Report frame width and height is shrinked to the top left.
Solution:
1. Go to Program Files → Microsoft SQL Server → MSSQL3 (Differs for different versions of sql server. Purpose is to locate "Reporting Services" folder) → Reporting Services → Report Server → Pages
2. Open ReportViewer.aspx
3. Modify as <> style="display:table; margin: 0px; overflow: auto" ID="ReportViewerControl" runat="server" />

Issue in Safari: Report body is compressed to the left.
Solution:
This can be solved by placing a rectangle and including the tables / header elements present in the report in that rectangle.
The report layout should be modified in such a way that
1. all the header elements are included in a Rectangle.
2. all tables are included in a Rectangle.
There is no perfect solution for this. Hope microsoft fixes this in the latest versions.

Import dbf to sql server 2005

Introduction:

dbf file or dbase file can be imported direclty in SQL Server 2000 by selecting the option in the wizard. But SQL Server 2005 does not provide such an option. There are few methods with which this task can be accomplished. For example, using SSIS (SQL Server Integration Services). Below is another method of doing it which i found to be simpler than using SSIS.



Steps to import dbase file (.dbf) to SQL Server 2005:

1. Create a System DSN

2. Open SQL Server 2005 Import/Export Wizard

3. Select ".Net Framework Data Provider for ODBC" in Data Source.

4. Enter the DSN Name that you have created. Click Next

5. Choose Destination database. Click Next

6. You will given an option to write a query. Click Next. Here you should write the query to get the data from dbf file. For example: SELECT * FROM Test.dbf

7. Click Next. Select the destination table or create a new one.

8. Click Finish. The data is imported to the selected destination table.


How to create a system DSN?

1. Go to Control Panel
2. Go to Administrative Tools
3. Click Data Sources (ODBC)
4. Select System DSN Tab.
5. Click "Add"
6. Select "Microsoft dbase driver (*.dbf)"
7. Click Finish
8. Enter name for the new system dsn created. (This name is used during importing in sql server)
9. Select the directory where the .dbf file is located. (Deselect Current directory if the file is located else where and then browse the location)
10. Click Ok. System DSN is created.