Get the maximum out of your Exact solution: Self service in one click.

In the past I blogged about the Exact System Information tool. With this tool you can optimize your Exact solution in one click. Please have a look to the embedded PowerPoint Web app presentation to see what the Exact System Information can do for you as an Exact user.  



If you are unable to see the presentation you can see it here


Enjoy it!

Support policy for SQL Server 2000 on newer Operating Systems

This blog post applies to customers who uses Microsoft SQL 2000 as database server. In the past Exact Globe started with Microsoft SQL Server 2000 as database server. As of today, still a lot of our customers uses Microsoft SQL Server 2000 as database server. On 14 September Microsoft announced the support policy for the newer operating systems. This means Microsoft SQL Server 2000 is not supported on the latest operatings systems like:

1. Windows Vista (all editions)
2. Windows Server 2008 (all editions)
3. Windows Server 2008 R2 (all editions)
4. Windows 7 (all editions)

Click here to find more information about the Operating Systems Supported by the Editions of SQL Server 2000

In case you want to use Windows Server 2008 or Windows Server 2008 R2, as operating system for your new server, you need to upgrade your SQL Server version. Please contact your Exact reseller for an SQL Server license with a big discount in case you are using the SQL server only for Exact databases. As of release 398 Exact Globe supports Microsoft SQL Server 2008 R2.

Enjoy your upgrade to SQL Server 2008 R2

How to: Hide a SSRS report item like tablix, table, charts etc. if no data is found.

In your SQL Server Reporting Server (SSRS) reports you can add multiple report items. In some situations it can happen that no data is available for one of the data sets which are linked to these report items. Displaying empty report items can be confusing for the user. It is possible to display report items only if data is available to display. This can be done by defining an expression for the visibility of the report item. The result of this expression is the trigger to show or hide the report item. To configure the show or hide of a report item, you need to do the following:

• Open the properties of the report item
• Select visibility
• Set when the report is initially run: Show or hide based on an expression.
• Press the fx button to define the expression
• Next example is an expression which will not display the tablix if the columns Hostname of dataset 'IndexImprovements' returns 0 records.
  
  =IIf(Count(Fields!hostname.Value, "IndexImprovements")=0,True,False)
  Enjoy building your reports.
  
  Picture credit: André Speek

Slow performance because of outdated index statistics.

Index statistics are the basis for the SQL Server database engine to generate the most efficient query plan. By default statistics are updated automaticly. The query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view. In the situation you encounter a performance problem and you do not understand the generated execution plan you can doubt if the statistics are up to date. Now you can do 2 things. First directly execute an update statistics on the table, but much better check how recent your statistics are. In case they are very recent, it is not necessary to execute an update statistics.

First check if indexes are disabled for auto update statistcs  (No_recompute = 0). Next query will retrieve all indexes for which auto update statistics are disabled: 
SELECT o.name AS [Table], i.name AS [Index Name],
STATS_DATE(i.object_id, i.index_id) AS [Update Statistics date],
s.auto_created AS [Created by QueryProcessor], s.no_recompute AS [Disabled Auto Update Statistics],
s.user_created AS [Created by user]
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.object_id = s.object_id AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
AND no_recompute = 1
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC;

Use next query to enable the Auto Update Statistics for IndexA of TableX.
ALTER INDEX
ON dbo.
SET (STATISTICS_NORECOMPUTE = OFF);

Use next query to see the update statistics date.
SELECT o.name AS [Table], i.name AS [Index Name],
STATS_DATE(i.object_id, i.index_id) AS [Update Statistics date],
s.auto_created AS [Created by QueryProcessor], s.no_recompute AS [Disabled Auto Update Statistics],
s.user_created AS [Created by user]
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.object_id = s.object_id AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC;

In case your indexes are outdated use next query to update the statistics:
UPDATE STATISTICS TableX IndexA

Enjoy your performance tuning.