That was deviation towards the world of BI. I was helping my co-worker to build a SSIS Package to import the data from AS400 into SQL Server.
At first I selected OLE DB Source component for getting the data but started getting odd errors of driver compatibilty. Then I read that there is a newish driver called DB2OLEDB which unfortunately works only on Enterprise/Developer Editions ,so I had only Standtard...
After some investigations I solved the problem by choosing DataReader Source control and selected ODBC driver for Net.Providers. It looks obvious for experienced BI consultants but for me, who rarely use SSIS, I felt great when co-worker was so happy to get all the data:-))
Sunday, October 18, 2009
Monday, October 5, 2009
Does the fragmentation have impact on performance on Heaps?
I have been working on some project that involves using very huge tables. Actually most of tables are heaps and to reduce IO we decided to compress those tables on PAGE level because the database is READ-ONLY. Fortunately in SQL Server 2008 we can issue ALTER TABLE... REBUILD.. to rebild the heaps to remove forwarding pointers ,please read Tibor's article (http://sqlblog.com/blogs/tibor_karaszi/archive/2009/08/28/heaps-forwarding-pointers-alter-table-rebuild-and-non-clustered-indexes.aspx)
Well ,after running the scripts we ended up with almost 500GB empty space. Usually it is not good to shrink the database as you gain nothing (it will grow again) and that caused fragmentation which does not help in terms of performance. Shrinking VLDB is another topic but what I did NOT know if your queries are doing INDEX SEEK SQL Server can get to any row easily no matter how fragmented the table is.
In that case fragmentation has no impact on performance.
If you think about it does perfect sense. Fragmentation is a measure of how closely the logical order matches the physical order. So if you do a scan (following the logical order) on a fragmented table, you can be jumping all over the to get the rows you need,instead of accessing them nice and neat and contiguously.
Many thanks to Kalen Delaney.
Well ,after running the scripts we ended up with almost 500GB empty space. Usually it is not good to shrink the database as you gain nothing (it will grow again) and that caused fragmentation which does not help in terms of performance. Shrinking VLDB is another topic but what I did NOT know if your queries are doing INDEX SEEK SQL Server can get to any row easily no matter how fragmented the table is.
In that case fragmentation has no impact on performance.
If you think about it does perfect sense. Fragmentation is a measure of how closely the logical order matches the physical order. So if you do a scan (following the logical order) on a fragmented table, you can be jumping all over the to get the rows you need,instead of accessing them nice and neat and contiguously.
Many thanks to Kalen Delaney.
Subscribe to:
Posts (Atom)
