The new world of work

Enjoy this nice video about the new world of work. I will mention some nice quotes in this video:

• Technology has flattened our world.
• People are loyal to their skill, not their employer.
• Our people are our greatest asset...
• People used to learn things, now they use Google.
• We are currently preparing students for jobs and technologies that don't yet exists ... to solve problems we don't even know yet

Happy viewing.

8 reasons why I uses a SaaS accounting solution for my financial administration?

At the moment that my first child was born I started to do my own private bookkeeping in Exact for Windows. I only know the amount of money on my bank account every month. I had no idea how much money I spend for different things this like food, clothes, housing, holidays, my car etc. I started, 9 years ago, with Exact for Windows. 4 years ago, I switched to my first SaaS solution. It was the alpha version of our current SaaS solution Exact Online. (http://www.exactonline.nl/ dutch only) Since 2 years, I'm a very happy user of Exact Online. A lot of people ask me the question, why are you using a SaaS solution for it? Is it safe to store your data somewhere else? Of course, before I start using Exact Online, I asked myself the same questions. Why did I decide to use it?

1. I realized that already a lot of my 'private' data is stored somewhere in the cloud. It's all about TRUST. Everybody is doing their banking stuff via Internet. Web shopping is very popular. Invoices of my telecom provider can only be retrieved via the internet. In the Netherlands, the government started with the Electronic Patient Dossier. This is your medical file, available via the web for doctors. So a lot of my data is already in the cloud and I'm happy with it. It's a running train which will never stop.
2. At home I have multiple PC's in a small network. My previous financial bookkeeping solution was installed on one of these PC's. Therefore it could only be used from that single PC. This is not a problem if you are alone, but with more people in the family it can happen that the financial bookkeeping PC is in use. With Exact Online, you can use it from every PC with a browser. Not only at home but everywhere in the world.
3. Backup of my data. How much times do you make a backup of all your home PC's. I did it almost not. With the hosted solution, I have no worries about that.
4. Availability of your data. A few months ago, I visited my dentist and she asked for the insurance number of my kids. I started Exact Online, from the PC of the dentist, to retrieve the insurance numbers. When I travel with my family, all my e-tickets and reservations are stored in Exact Online. If I need them, they are available for me.
5. Functionality which is the advantage of a SaaS solution. I will give one example: direct automatic import of your bank statements during the night. Exact Online is directly importing my bank statements from my bank. I do not have to download my bank statements and import them manually.
6. I'm working for the research and innovation center of Exact in Delft. I'm in favor of the principle: Eat your own dog food.
7. With the current financial crisis, what is the impact on my financial situation? In one overview, I can see all my revenue and cost in comparison with previous months and years.  
8. Last but not least, Exact Online complies with my personal statement: Keep It Simple and Fast.

Identifying the performance bottleneck is already 50% of the solution.

In my previous blog post I'm in favor to visit customers as a product manager. You get useful feedback how customers are using your product. This week I did, as Principal Research Engineering, a consult to find the bottleneck in the Globe environment of a customer. It was an interesting consult with useful feedback. I will summarize some experiences:

1. SQL 2000 (X86) was installed on a Windows Server 2003 (X64). The server has 8 GB memory. However only 2 GB was used by the 32 bits version of SQL Server. It is possible to allocate more memory for SQL Server (X86) but only on a 32 bits operating system. To use all memory on Windows Server 2003 (X64), install an X64 version of SQL Server. After installing SQL 2005 (X64) all server memory was used. The more memory SQL server can use, the better is the performance.
2. An application took a while to display the information. With SQL profiler, one query was identified as the bottleneck. Two suggestions to improve the query will be investigated. Hopefully are these suggestions possible. The development organization is currently analyzing these suggestions.
3. The Intrastat application resulted almost always in a query time out. Also in this case, SQL Profiler was used to identify the slow query. The query itself was correct however in the query plan; a clustered index scan was executed on the biggest transaction table GBKMUT.  After creating an index, the application executed within 2 minutes. This index will be added to the index structure of GBKMUT for our logistic Globe customers.

Conclusion: Identifying the performance bottleneck is already 50% of the solution. In this case, a wrong SQL Server installation, a query which can be improved and an improvement of the standard index structure of Globe. This consult resulted in a win-win situation. For the customer, the performance of his Globe environment is improved. For Exact, our customers can benefit of the improvements in the application and index structure.

Customer experiences and social media: Product manager should visit customers every 2 weeks.

Last week, I did two invoiced customer visits. In the past I have done a lot of customer visits as a support expert. During these troubleshoot consults, I had one mission. Solve the issue and make the customer happy. When I arrived at the customer site the atmosphere was already set by the things which had happened in the past. You start the visit with a meeting with the director, ICT manager, accountant and one or more users. They all have a story to tell. You NEED only to listen. When they are finished their story, you need to summarize their story in 2 sentences. If you are able to do that, you have already 50 % of the solution. The customer wants to get the acknowledgement that YOU understand his situation. With an invoiced visit, on request of the customer, the atmosphere is different. It’s more positive. The software is running and they need some specific help or advice. The conversation is much easier, however YOU need to listen and to observe. During your consult you will use your software with the customer. Look critical how these users are using your software. Is this the way you have designed it? If not, do not say: The customer is using it wrong. For what kind of reason, it is not clear for the customer. Find the reason and change the software, so the customer will use it in the right way. Sometimes you are surprised why a user is doing something. Ask him why he is doing this. He will tell you a workaround, which he has accepted in the past. This is a nice opportunity to improve the software. Customer experience will be great, if you can tell your customer one week after your visit, that his ‘accepted workaround’ is fixed in the next release.


As product manager, it will be your lucky day when this customer is using social media to tell this great customer experience to the community. This is a win - win situation. More and more people will use social media. At one day it will happen that you will get positive feedback on your product in a Google search. This is the best advertisement you can get for your product.

Positive experiences told by YOUR happy customer to the community.

As mentioned in one of my previous blogs: What is the URL of your company? The homepage of your company is Google, Yahoo or MSN. As mentioned in You can't stop it anyway, once people become more active in social media, there’s no way how you can control how things go. Who will write, who will find and who will comment on these experiences. This will even more happen with negative experiences.

As a product manager take the opportunity to visit every 2 weeks one customer. Do not only talk, but really do something with your software and the user. You want to see the 'accepted workarounds'. Remember: Think from a user perspective instead from a technical perspective. If you do these kind of visits for free, you have already the first positive customer experience. It will cost some time but you will get paid for it.

Do you agree on this? Please let me know YOUR opinion about this.

Optimize index structure: Online nonclustered index defragmentation

In SQL 2005 a lot of statistical information is stored about the indexes in your database. This is the third post in a serie about optimizing your index structure.

Previous posts in this serie:

1. Which indexes are not used in my database? Index usage statistics and disk space usage.
2. Defragmentation clustered indexes.

 
Tables in a database can contain a lot of nonclustered indexes per table. These indexes can get fragmented after a while. This will occur when a lot of records are inserted and deleted. Fragmented indexes will have a negative impact on the performance when these indexes are read.
 
Online index defragmentation can be done in 2 different ways:
1) Alter index statement. Can only be performed in Enterprise edition of SQL Server. 
2) DBCC INDEXDEFRAG. Not supported as of SQL 2008 but it executed on SQL 2005 Standard or Enterprise edition.

Next script is an on line operation which can be executed in SQL 2005 Standard and Enterprise edition. It will have impact on the performance of the database. There for do not schedule this script on peak usage of the system.
 
SET NOCOUNT ON

DECLARE @Table NVARCHAR(512)
DECLARE @Index NVARCHAR(512)
DECLARE @Rebuild NVARCHAR(4000)
DECLARE DB CURSOR FOR

SELECT SO.Name, SI.Name
FROM SYS.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
INNER JOIN SYS.objects SO ON SO.object_id=SYS.dm_db_index_physical_stats.object_id
INNER JOIN SYS.indexes SI ON SI.index_id=SYS.dm_db_index_physical_stats.index_id AND
SI.object_id=sys.dm_db_index_physical_stats.object_id
-- Begin select all nonclustered indexes Index_id = 1
AND SI.Index_id <> 1
-- End select all nonclustered indexes Index_id = 1
WHERE avg_fragmentation_in_percent > 10.0 AND SYS.dm_db_index_physical_stats.index_id > 0
ORDER BY SO.Name

OPEN DB
FETCH NEXT FROM DB INTO @Table, @Index
WHILE @@FETCH_STATUS = 0

BEGIN
SET @Rebuild = 'DBCC INDEXDEFRAG (0,' + @Table + ',' + @Index + ')'

PRINT @Rebuild
EXEC SP_EXECUTESQL @Rebuild
FETCH NEXT FROM DB INTO @Table, @Index

END
CLOSE DB
DEALLOCATE DB
SET NOCOUNT OFF 
 

To run this script on SQL Server 2008 change the syntax of @Rebuild to:
SET @Rebuild = 'ALTER INDEX ' + @Index + ' ON ' + @Table + ' REBUILD WITH (ONLINE = ON)'

On big tables this script can take a while to execute. In the software solutions of my company Exact Software we have Exact Globe and Exact Synergy Enterprise. In these solutions next tables can takes a while to defrag: GBKMUT, BacoAppLog, Absences, ItemNumbers, EBCDataLog, WordReferences, BacoSettings. AMUTAK, AMUTAS, ORSRG, ORKRG, FRSRG, FRKRG, ORHSRG, ORHKRG, FRHSRG, FRHKRG.

Optimize index structure: Defragmentation clustered indexes

In SQL 2005 a lot of statistical information is stored about the indexes in your database. This is the second post in a serie about optimizing your index structure.
A clustered indexes determines the physical order of data in a table. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. Fragmentation affects disk performance and the effectiveness of the SQL Server read-ahead manager. The SQL Server read-ahead manager can read less records in a fragmented clustered index. A non fragmented clustered index will maximize the number of records the SQL Server read-ahead manager can retrieve in one disk IO.
With next script you can rebuild your clustered indexes which have a fragmentation of more than 10 %.

Be aware that a rebuild of a clustered indexes will results in a table lock. It is a off line operation. Execute this script when you know that the database is not used be other applications. Depending on the size of your database it can take a while to execute.

To generate the SQL script without rebuilding the clustered index directly, remark:
-- EXEC SP_EXECUTESQL @Rebuild
in the script.

The script can also be used for all non clustered indexes, however rebuilding of non clustered indexes can be done in an online operations. In another post I will make a script to rebuild non clustered indexes online

SET NOCOUNT ON
DECLARE @Table NVARCHAR(512)
DECLARE @Index NVARCHAR(512)
DECLARE @Rebuild NVARCHAR(4000)

DECLARE DB CURSOR FOR
SELECT SO.Name, SI.Name
FROM SYS.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
INNER JOIN SYS.objects SO ON SO.object_id=SYS.dm_db_index_physical_stats.object_id
INNER JOIN SYS.indexes SI ON SI.index_id=SYS.dm_db_index_physical_stats.index_id AND
SI.object_id=sys.dm_db_index_physical_stats.object_id
-- Begin select only clustered indexes Index_id = 1
AND SI.Index_id = 1
-- End select only clustered indexes Index_id = 1
WHERE avg_fragmentation_in_percent > 10.0 AND SYS.dm_db_index_physical_stats.index_id > 0
ORDER BY SO.Name

OPEN DB
FETCH NEXT FROM DB INTO @Table, @Index
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Rebuild = 'ALTER INDEX ' + @Index + ' ON ' + @Table + ' REBUILD'

PRINT @Rebuild
EXEC SP_EXECUTESQL @Rebuild
FETCH NEXT FROM DB INTO @Table, @Index

END
CLOSE DB
DEALLOCATE DB
SET NOCOUNT OFF


Previous post in this serie:
Which indexes are not used in my database? SQL 2005 Index usage statistics and disk space usage.

Ultimate guide to upgrading to SQL Server 2008

Microsoft has published a guide to upgrading to SQL Server 2008, please refer to the SQL Server 2008 Upgrade Technical Reference Guide. It is a big Word document of 490 pages which is a good starting point for your own upgrade plan from SQL 2000/2005 to SQL 2008.

A successful upgrade to SQL Server 2008 should be smooth and trouble-free. To achieve that smooth transition, you must devote plan sufficiently for the upgrade, and match the complexity of your database application. Otherwise, you risk costly and stressful errors and upgrade problems. Like all IT projects, planning for every contingency and then testing your plan gives you confidence that you will succeed. But if you ignore the planning process, you increase the chances of running into difficulties that can derail and delay your upgrade. This document covers the essential phases and steps involved in upgrading existing SQL Server 2000 and 2005 instances to SQL Server 2008 by using best practices. These include preparation tasks, upgrade tasks, and post-upgrade tasks.

• Chapter 1 gives an overview of the technical issues and decisions that are involved in an upgrade to SQL Server 2008, as well as recommendations for planning and deploying an upgrade.
• Chapter 2 addresses issues related to upgrading to SQL Server 2008 Management Tools.
• Chapters 3 through 8 focus on upgrade issues for SQL Server relational databases.
• Chapter 9 addresses upgrading to SQL Server 2008 Express.
• Chapters 10 through 14 focus on upgrading to SQL Server 2008 Business Intelligence components: Analysis Services, Data Mining, Integration Services, and Reporting Services.
• Chapter 15 addresses the implications of upgrading to SQL Server 2008 for other Microsoft applications and platforms.
• Appendix 1 contains a table of allowed SQL Server 2008 version and edition upgrade paths.
• Appendix 2 contains an upgrade planning checklist.

What is the URL of your Corporate Homepage ?

Last month I viewed a nice video in which Jeremiah Owyang interviews Bob Pearson about Why DELL uses Social media. A presentation of this interview can be found on here. It takes 5 minutes to listen. In this interview Bob made some nice quotes. Bob has a really nice answer on the question:

What is the URL of your Corporate Homepage?

The real homepage is Google, MSN or Yahoo. People go to search. Type in your brand name and the result set is your new homepage. The web reflects the reality. You can't ignore it. If there is something negative, your reaction should be: How do we solve the problem?

Social media is becoming more popular. As a company you CAN'T ignore it.  Monitor what the community is saying about your company and act of it. It is not a question if you want to act on it. You need to act on it. The community will decide on how they think about your company.

Optimize index structure: Which indexes are not used in my database? SQL 2005 Index usage statistics and disk space usage.

In SQL 2005 a lot information is stored about how indexes are used and which indexes can be useful to create. This is the first post in a serie about optimzing your index structure.

In SQL 2005 you can retrieve index usage statistics of all indexes in your database. This is a really good feature which allow you to tune your index structure of your database. Every database is used on it's own way. For a standard software package like Exact Globe, an index structure is shipped with the product. This index structure is not always optimal. It depends on how you are using the software. For instance, an index for costunits is only useful if you make use of costunits in your application.
With the index usage statistics you can see how much times an index is used. The most easiest ones are the indexes which are never used for reading but are only updated.

This script will display the index usage statistics for the table 'Gbkmut' which are never read.

-- Used Indexes with only updates and No reads
SELECT object_name(s.object_id) AS ObjectName,ISNULL(i.name, '(Heap)') AS IndexName,
user_seeks AS IndexSeeks,user_scans AS IndexScans,user_lookups AS LookUps,
user_seeks + user_scans + user_lookups AS TotalReads,user_updates AS Updates,
CASE WHEN (user_seeks + user_scans - user_updates < 0) THEN'Updates > Reads'
WHEN (user_seeks < user_scans) THEN'Scans > Seeks'
ELSE' '
END AS Warning
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE database_id = DB_ID() AND objectproperty(s.object_id,'IsUserTable') = 1
AND object_name(s.object_id) = 'gbkmut'
AND (user_seeks + user_scans + user_lookups)=0

The result set of this query are indexes which maybe can be deleted. Monitor these indexes for a period in which you know that all functionality in your software packages is used.

Please be aware that index statistics are stored in Dynamic Views. After a restart of SQL Server the index usage statistics are reset to 0. Index statistics for a table are also reset to 0 when index changes are executed on this table.

With next query you can see how much diskspace the indexes for table 'Gbkmut' are using.
-- Indes sizes of an table
SELECT Name as Index_name, Type_desc AS IndexType, Space_used_in_mb = (page_count * 8.0 / 1024.0)
FROM SYS.INDEXES I
INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(db_id(), object_id('gbkmut'), null, null, null) IP
ON I.object_id = IP.[object_id] and I.[index_id] = IP.[index_id]
ORDER BY I.index_id