Keep It Simple and Fast

Overview performance improvements in Exact Globe in the last years.

2010-07-28T21:25:00.007+02:00

Photo credit Alancleaver_2000

Over the years a lot new release have become commercial available. A lot of new functionality has come available. With the information we received from customers who have used the Exact System Information tool we have seen that they are not aware of available functionality. The Exact System Information tool is made for customers to help them in improving the performance of their Exact solution. A lot of customers uses Exact Globe already for years. In their administration they have a lot of historical data. Some clean up applications has been made to clean up the database. This will result in a smaller backup and can help to improve the overall performance.

This blog post will give an overview of all performance improvements and cleanup functions.

Clean up log files. Release 360. Functionality is made to clean up ‘Application log’ or ‘Masterdata log’
Clean up of historical journal records. Release 360. Historical data in Globe is stored in multiple tables in the database. In the previous release of Globe application, there is no process in place to perform the clearing of old records. As the database size grows from period to period and from year to year, the system performance will be deteriorated
Database structure optimized. Release 370. Exact Globe 2003 database used data type CHAR instead of VARCHAR to store data with length more than 10 bytes. CHAR always uses fixed length to store data whereas VARCHAR uses dynamic length which depends on the actual size of the data. Therefore from a database structure standpoint, the database was not optimized. As a result, the following drawbacks were introduced. Record was unnecessarily bigger which caused slower queries because less record can be retrieved in one disk I/O. Indexes were bigger which again reduced query performance
Clean Up XML Import Logs. Product update 395. Whenever you import XML files to Exact Globe, the XML import results which are the log of the relevant events will be recorded. An option to remove the XML logs are added to the functionality for cleaning up logs.
Cleanup tool to delete obsolete logistic records and MRP planning records. Product update 395. Logistic processes in Exact Globe generate MRP planning records. Examples of logistic processes are sales orders, blanket sales orders, return to merchant authorization (RMA) orders, purchase orders, blanket purchase orders, return to vendor (RTV) orders, interbranch transfers, and quotations. Over the years, as customers’ databases become larger with logistic transactions, the database actions on these records become slower. Historical records such as completed sales or purchase orders may be considered obsolete after the completion of the logistic processes over a certain period of time. In order to enhance the performance of the administration, these obsolete logistics and planning records are best deleted.
Retrieving Balance Totals. Product update 395. Due to the database structure of Exact Globe, the retrieval of transaction totals requires the system to totalize all transactions. Over time, as your database becomes bigger and filled with transactions, the retrieval of balance totals becomes slower. Faster retrievals are therefore necessary to help you work more efficiently with the system. Enhancements have been made to improve the performance of retrieving balance totals
Database Performance by Optimizing Index. Product update 397. In every Exact Globe product update, system performance is improved by optimizing the database indices in Exact Globe. In this product update, a tool is implemented to optimize your database instead of the standard indices in Exact Globe. With this method, the indices will be deployed only when it is required by your SQL server. This method also reduces deployment risk because you can easily add or remove the indices if you are not satisfied with the performance result. There will also be performance improvement for functions that use the new index.
Cleanup tool to delete obsolete logistics records extended to Production Orders and Stock Allocations.Product update 398. The scope of the cleaning up tool is extended to completed production orders and all the respective allocation entries. The allocation entries refer to stock allocation records generated from back-to-back orders and sales order enrichments. Consequently, the absence of production orders and stock allocation is no longer the prerequisite for executing the tool.

Performance improvement Globe (398): Logistic MRP Cleanup Tool Extended to Production Orders and stock allocations.

2010-07-27T21:32:00.000+02:00

Photo Credit: Donger

As of Exact Globe product update 398 is is possible to delete the obsolete logistics and planning records for production orders and allocation entries. This is a good extension to the MRP cleanup tool which is released as of product update 395.

In Exact Globe product update 395, the logistics MRP cleanup tool for cleaning up obsolete logistic records and budget entries without affecting the related processes was introduced. The records included completed sales orders, blanket sales orders, RMA orders, purchase orders, blanket purchase orders, RTV orders, interbranch transfer requests, and quotations. For more information, see Product Update 395: Improved Performance with Obsolete Logistic Records and MRP Planning Records Cleanup Tool .

However as of product update 398, in order to enhance the performance of the administration, system administrators also need to delete the obsolete logistics and planning records for production orders and allocation entries. As such, in this implementation, the scope of the cleaning up tool is extended to completed production orders and all the respective allocation entries. The allocation entries refer to stock allocation records generated from back-to-back orders and sales order enrichments. Consequently, the absence of production orders and stock allocation is no longer the prerequisite for executing the tool. This enhancement will definitely assist administrators in boosting the performance of the administration with ease. For more information about the updated prerequisites, how to use the cleanup tool, and the latest list of functionalities that are affected by the cleaning up process, see How-to: Cleaning Up Obsolete Logistic Records and Budget Entries.

To get an estimate how much records can be cleaned up, please run the Exact System Information tool and request an improvement report. More information about the improvement reports of the Exact System Information tool can be found here.

Enjoy the update to Globe product update 398.

Exact Globe supports SQL Server 2008 R2 as of product update 398

2010-07-27T09:31:00.000+02:00

In July 2010 the Exact Globe product update 398 is released. As of this product update 398, Microsoft SQL Server 2008 R2 is supported. Beside this Office 2010 (32 bits) is supported. More information about this product update can be found here.

Be aware that a database which is upgraded to SQL 2008 R2 can't be used on a previous version of SQL Server.

Enjoy your upgrade to SQL 2008 R2.

Back from holiday, new blog posts will come soon.

2010-07-25T23:09:00.000+02:00

The last 3 weeks I have been on holiday. It was fantastic, good food, wine, relaxed a lot. We did a lot of nice activities with the children like swimming, playing tennis and golf, drive karts, climbing in trees and one of my favorites during holidays: fishing on carps.

Now the holiday is over, I will continue to post new articles about SQL Server. See you soon.

How to retrieve numbers of records in a table with high performance without index or tablescans?

2010-07-20T21:29:00.012+02:00

Photo Credit: dodge challenger1

The most common way to retrieve the number of records in table is done with a query like:
select count(*) from tableX where column1 = 'value'

Using this kind of queries will result in a (clustered) index scan. On tables with millions of records this can still take a while to execute and can result in unnecessary overhead on the SQL server. SQL server stores a lot of statistics of your tables. These statistics are used by the optimizer to generate the most efficient queryplan. You can use these statistics to retrieve the number of records in your database. This is always faster then to query the table itself.
SQL server uses his own mechanisme to update the statistics, so it can happen that there is a small mismatch between the number of records in a table and the number of records stored in the statistics. For big tables this is not always so important. For instance to display the growth of your tables.

You can use next query to retrieve the number of records per tables via the statistics:

SELECT sysobjects.name,rowcnt
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id=sysindexes.id
WHERE xtype='U' AND indid IN (0,1)
AND sysobjects.name = 'Your table'

To check for the number of records for a column with a specific value use next query. This query wil return the number of records per column value. Available columns are columns which exists as the first column in an index. Example you have a table: X with the column: Type. The column type is used as the first column in an index. Next query will return all existing values for the column type with the number of records.

DECLARE @TableName Nvarchar(200)
SET @TableName = 'Your Table'

DECLARE ColumnStatistics CURSOR FOR
SELECT Tbl.name AS TableName, SSID.name as IndexName ,
         INDEX_COL( tbl.[name], idk.indid, 1 ) AS IndexColumn
FROM SYSINDEXkeys idk
INNER JOIN SYSOBJECTS tbl ON idk.[id] = tbl.[id]
INNER JOIN SYSINDEXES SSID on SSID.[ID] = TBL.[id] and ssid.indid = IDK.indid
WHERE tbl.ID = (SELECT ID FROM SYSOBJECTS WHERE name = @Tablename)
           AND keyno = 1

OPEN ColumnStatistics

DECLARE @IndexName Nvarchar(200)
DECLARE @IndexColumn Nvarchar(200)

FETCH NEXT FROM ColumnStatistics INTO @TableName,@IndexName, @IndexColumn
WHILE @@FETCH_STATUS = 0
BEGIN

CREATE TABLE #TempTable (ColumnValue nvarchar(200),INT1 INT,EQ_ROWS INT,
    INT3 INT,INT4 INT,TableName nvarchar(200))
INSERT INTO #TempTable (ColumnValue, int1,EQ_ROWS,int3,int4)
EXEC ('dbcc show_statistics(' +@TableName+','+@IndexName+') with histogram')

SELECT @TableName AS Tablename,@IndexColumn AS IndexColumn,ColumnValue, EQ_ROWS AS Records

FROM #Temptable
DROP TABLE #TempTable

FETCH NEXT FROM ColumnStatistics INTO @TableName,@IndexName, @IndexColumn
END
CLOSE ColumnStatistics
DEALLOCATE ColumnStatistics

How to check if and when the statistics are updated?

execute next query:
EXEC sp_autostats 'Your table'

Check for the column AUTOSTATS. The value should be set to ON.
Check for the column Last Updated to see when the statistics for the index is updated for the last time.

Enjoy using the statistics.

SQL Azure available in West European Region and East Asia

2010-06-29T00:28:00.000+02:00

As announced in one of my previous blogs, the release of SQL Azure Update 3 is now available. This release of SQL Azure is now also available in 2 new data centers. These data centers are located in the West European Region and the East Asia region. In this SQL Azure Update 3 the following new features are available:

50 GB Database Support – You can now store even more data in a single SQL Azure database as the database size has been increased to 50 GB. This will provide your applications increased scalability. For detailed pricing information on SQL Azure and how to create or modify your database to take advantage of the new size, see this blog post. In general every GB of data will cost 10 $. For business edition started with 100 $.
Spatial Data Support - SQL Azure now offers support for the Geography and Geometry types as well as spatial query support via T-SQL. This opens the Windows Azure Platform to support spatial and location aware applications.
HierarchyID Data Type Support – The HierarchyID is a variable length system data type which provides you the ability to represent tree like structures in the database.

Enjoy it, and I'm already looking forward to the reporting server support, so we can make use of the all the maps in combination with the supported spatial data support.

SQL Server 2008 R2 Developers Training Kit (June 2010 update)

2010-06-23T21:00:00.003+02:00

SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server.

Image credit: Knurftendans

The training kit offers the following benefits:

Learn how to build applications that exploit the unique features and capabilities of SQL Server 2008 and SQL Server 2008 R2.
Provides a comprehensive set of videos, presentations, demos and hands-on labs
Contains new content for developers who are new to SQL Server.
Contains new content for SQL Server 2008 R2.
Contains all of the existing content from the SQL Server 2008 Developer Training Kit.
Easy to download and install.

The training kit is designed for developers, technical specialists and consultants.

Content of the training kit:
32 presentations
39 demos
24 hands-on labs
55 videos'

Click here to download the SQL Server 2008 R2 Update for Developers Training kit.

I think you will enjoy it.

Windows 7 upgrade from XP or Vista: Yes or No looking from a user perspective

2010-06-10T20:37:00.003+02:00

Windows 7 is commercial avaliable since 22 October. A lot is already written about Windows 7. In this blog I will share my personal experiences about Windows 7. I have used a lot of different operating systems in the past on my laptop. Windows NT 4 workstation, Windows 95, Windows Server 2003, Windows XP, Windows Vista and Windows 7.

Photo credit: Oskay

During my upgrade to Windows Vista is was the first time that I had a negative experience in the upgrade of my laptop. Of course some things improved but I also lost some things which works perfectly in Windows XP. I will give my 4 biggest disappointments:

Power usage. Impossible to configure brightness difference of my screen between plugged in and running on batteries. I'm always looking to the optimal power management configuration when running on batteries. It gives me a lot of flexibility. The longer I can run on batteries the better is my user experience. Overall I experience 20 percent less time running on batteries.
Wireless network connectivity. I work a lot at home and in the office. Both have their own wireless network. To travel between home and the office I uses the sleep or hybernate function to close my laptop. In 60 percent of the situations reconnecting to the internet works fine however in 40 % I need to turn of my wireless card or reboot my machine. As you can imagine this is a bad user experience especially because rebooting takes a lot of time to do.
Hibernate does not work in combination with Sleep. My laptop is configured to sleep when I close the lid. At the end of the working day I hibernate my laptop with the power button. If I close the lid to early, my laptop switch to sleep. At home, I open my laptop which wakes up from sleep and continues the hybernation process. On XP the hybernation process is not interupted by the sleep process.
Availabilty of drivers. For instance for printers and camera's

Because of my negative experiences with the upgrade from Windows XP to Vista, I decided to wait a few weeks with upgrading my laptop to Windows 7. I wanted to hear the experiences of other people. After hearing mostly good stories from a lot of people I upgrade my laptop to the 64 bits version of Windows 7. Unfortunaltly, I need to do a reinstall because an upgrade from Vista 32 bits to Windows 7 64 bits is not supported. The new install of Windows 7 was amazing fast. Within 15 minutes I was connected to the internet. However the rest of the day I was busy with reinstalling the rest of my applications, including service packs and security updates. For instance: MS Office, Live Meeting, Communicator, Antivirus Software, Visual Basic, SQL Server 2008 including Reporting Server and Analysis Server etc..

Are my 4 biggest disappointments solved with the upgrade to Windows 7 ?

Power usage is not solved. Power usage is the same as on Windows Vista.
Wireless network connectivity is solved. I do not experience reconnections problems anymore between different wireless networks.
Hibernate does not work in combination with Sleep. This is not solved.
Availability of drivers. This is solved. I decided to upgrade to the 64 bits version of Windows 7. Without any problems I could find all 64 bits drivers I needed.

Should you upgrade to Windows 7? The answer is Yes but it depends.

Are you running on Windows Vista? Yes upgrade to Windows 7 without any doubt. It will improve you overall user experience.
Are you running on Windows XP, my advice is to upgrade to Windows 7 when you planned to replace the hardware.
Buying new hardware: Vista or Windows 7. Buy Windows 7. In case you have more the 3 GB of memory, select the 64 bits version. This will avoid manually memory configurations to use all memory.

I realize that a lot of users can't make the choice them selves to upgrade. There are more factors to consider, like application compatibility. For what I have seen, if your application runs on Vista it will also run on Windows 7. Within my company we did not make any changes to our application Globe to get certified for Windows 7. We only add some typical Windows 7 features to it like the jumplist.

What do I like the most of Windows 7?

The answer is simple: Live taskbar previews. Look to this movie if you do not know what live taskbar previews are.

A dutch version of this blog post is available on the computable site.
Enjoy using Windows 7.

Did you already request an improvement report for your Exact solution?

2010-06-03T21:20:00.000+02:00

As mentioned in a previous blog post, Exact started developing the Exact System Information (ESI) tool. With this tool you can receive an improvement report via e-mail. What can you expect in the report?

1. Server improvements.

The server improvement section is related to your SQL server configurations and answers questions like: Have you installed the latest Service packs? Can you make changes in your SQL Server configuration to make use of all available memory? Etcetera.

Screenshot of Server Improvements:

2. Database improvements.

The database improvements section is related to the database itself and gives advice about new available product updates, clean up of historical journal records, clean up of voided entry record, background jobs, clean up of temporary tables etcetera.

Screenshot of database improvements:

The number of requested improvement reports are growing every month. The Exact System Information tool can be downloaded and used for free by all organizations that uses Exact Globe and/or Exact Synergy (Enterprise). Did you already request an improvement report? Do not forget to specify your email address when running the tool.

If you have feedback please comment on this blog or send an email to Andre.van.de.Graaf@exact.com
The ESI tool can be downloaded here. This blogpost is also published on the Exact Product Blog.

SQL Server Report Builder 3.0 for SQL 2008 R2 is available for download.

2010-05-30T20:01:00.000+02:00

With every new version of SQL Server, a new version of the report builder is made. For SQL 2008 R2, version 3.0 is made. Report Builder 3.0 introduces additional visualizations including maps, sparklines and databars which can help produce new insights well beyond what can be achieved with standard tables and charts. The Report Part Gallery is also included in this release. With report parts, work groups can now take advantage of the different strengths and roles of their team members. For example, if you are responsible for creating charts, you can save your charts as separate parts that you and your coworkers can reuse in other reports. You can publish report parts on a report server or SharePoint site integrated with a report server. You can reuse them in multiple reports, and you can update them on the server.

Here are some screen shots of the Report Builder 3.0:
The New Report wizards for Table or Matrix, Charts, Maps.

The toolbar:

Maintaining a report:

Report Builder is a good tool for customers and consultants to build and maintain their reports. It's much more user friendly in comparison with SQL Server Business Intelligence Development Studio. To download Report Builder 3.0 click here. Unfortunately, their is only a 32 bits version available of Report Builder 3.0.

Enjoy building nice reports for your customers.

When to use the INCLUDE option in the CREATE INDEX statement.

2010-05-26T21:04:00.003+02:00

In SQL 2005\2008\2008 R2 you can create non clustered indexes with the INCLUDE option. Sometimes I got questions why you should use the INCLUDE option. In this blog I will explain why and when you should use it. An index will store all data in the order of the content of the indexed columns. Assume we have created next index:

CREATE INDEX MyIndexName ON MyTable (ColumnA, ColumnB, ColumnC)

All data will be stored first on ColumnA and then on ColumnB and then on ColumnC. Let's take next records:
ColumnA, ColumnB, ColumnC
Record 1: XX,YY,1
Record 2: XX,YY,6
Record 3: XX,YY,10
Record 4: XX,YY,15
Record 5: XX,YY,50

Assume the value 50 of columnC of record 5 will change to 2. With the MyIndexName on MyTable the records in the index will be re-ordered.

ColumnA, ColumnB, ColumnC

Record 1: XX,YY,1
Record 5: XX,YY,2
Record 2: XX,YY,6
Record 3: XX,YY,10
Record 4: XX,YY,15

This will cost disk I/O to change the index order and can result in index defragmentation.

With the INCLUDE option the index records will not be re-ordered for changes in the values of INCLUDE columns.
CREATE INDEX MyIndexName2 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnC)

So a change in the value of ColumnC will not result in a index re order. Only the value of Column C is updated.

ColumnA, ColumnB, ColumnC

Record 1: XX,YY,1
Record 2: XX,YY,6
Record 3: XX,YY,10
Record 4: XX,YY,15
Record 5: XX,YY,2

When to use the INCLUDE option? Answer to this question is really simple. In case you want to create a covering index:
Anything in the WHERE clause would be a key, anything in the SELECT would be an included column.

Example next query:
SELECT ColumnC FROM MyTable WHERE ColumA = 'XX' AND ColumnB = 'YY'

For this query MyIndexName2 is much more efficient.

Exact Synergy Enterprise with MS Reporting services (SSRS) now available.

2010-05-24T22:00:00.000+02:00

As of 20 may 2010 Exact Synergy Enterprise product update 243 is general available. In this product update the first step is made in the integration of Microsoft Reporting Services. This enables the users of Exact Synergy Enterprise to build their own reports and run the reports within the Exact Synergy Enterprise interface. In combination with the latest SQL 2008 R2 release, you can transform data into information. All new added features in SQL 2008 R2 like indicators, data bars, spark lines, maps etc can be used in your reports.
For a small preview about this integration please have a look to this video.

Enjoy building nice reports on your Synergy database please keep in mind some tips to improve the performance of your SSRS reports.

Deprecated Database Engine Features in SQL 2008 R2

2010-05-17T21:15:00.005+02:00

Every time a new SQL Version is coming to the market people are interested in the new features. I did the same like, 'My favorite top 10 Features of SQL 2008 R2' However, it is also important to know which features are not supported anymore. In the upgrade from SQL 2008 to SQL 2008 R2 most changes are done in the BI stack. The database engine is not changed so much. You can see this when you look to the available database compatibility levels. For SQL 2008 R2, there is no new database compatibility level introduced. After your upgrade to SQL 2008 R2 your database will still stay on compatibility level 100 (SQL2008)

Here you can find a full list of all deprecated database engine features in SQL 2008 R2

Tips to improve performance of reporting server reports (SSRS).

2010-05-06T21:22:00.001+02:00

Photo credit: visuloqik

A lot of times I got questions from people how to improve SSRS reports. Of course every report is different, but some tips can be applied to every report. Assume we have a report which takes X seconds to display all data on your screen. The total time to generate a SSRS reports can be split into 2 main parts:

Time to executed all queries on the SQL Server. Use the SQL profiler to measure the duration of all executed queries. (Y seconds)
Time to generated the report on the reporting server, based on the result set of the executed queries. (Z seconds)

Because we know the total time to execute the report and the total duration of all executed queries, you can calculate the time to generate the report (Z seconds). (X = Y + Z). After you know the values for X, Y and Z, you can start focusing on the biggest part.

Use the SQL Profiler to see which queries are executed when the report is generated. Sometimes you will see more queries being executed than you expected. Every dataset in the report will be executed. A lot of times new datasets are added during building of reports. Check if all datasets are still being used. For instance, datasets for available parameter values. Remove all datasets which are not used anymore.
A dataset contains more columns than used in the Tablix\list. A lot of times you will see datasets with next syntax:

SELECT * FROM TableX WHERE Column1 = 'Something'.

This is easy when start building the report. It will retrieve all columns of the table. However all columns are not useful and creates a lot of overhead. Assume you uses 3 columns in the tablix\list. Change the syntax of the dataset to only these 3 columns. This can save bookmark lookups and it will save disk I/O on the SQL Server. So you get a dataset with next syntax:

SELECT Column1, Column5, Column 8 FROM TableX WHERE Column1 = 'Something'
ORDER BY in the dataset differs from the ORDER BY in the Tablix\list. You need to decide where the data will be sorted. It can be done within SQL Server with an ORDER BY clause in the dataset or in by the Reporting server engine. It is not useful to do it on both sites. If an index is available use the ORDER BY in your dataset.
Use the SQL Profiler to measure the performance of all datasets (Reads, CPU and Duration). Use the SQL Server Management Studio (SSMS) to analyze the execution plan of every dataset.
Avoid datasets with result sets with a lot of records like more than 250 records. Greater result sets are more like a data export (dump). A lot of times data is GROUPED in the report without an Drill down option. In that scenario do the group by already in your dataset. This will save a lot of data transfer to the SQL Server and it will save the reporting server engine to group the resultset.
Rendering of the report can take a while if the resultset is very big. Look very critical if such a big resultset is necessary. If details are used in only 5 % of the situations, create another report to display the details. This will avoid the retrieval of all details in 95 % of the situations.
Use the database documentation of your applications to understand how to retrieve information from the database. For Exact Globe click here to find the database documentation. For Exact Synergy Enterprise click here to find the database documentattion.

Enjoy building high performing reports.

What is Optimize for ad hoc workloads option in SQL 2008 and how to configure

2010-05-03T20:39:00.001+02:00

The optimize for ad hoc workloads option can improve the overall performance of your SQL Server. This option is introduced in SQL 2008 to improve the efficiency of the plan cache. At the moment a query is started SQL Server will first analyze the query to find the best way to execute the query. This is called the execution plan. In the execution plan information is stored about for instance which index to use to retrieve the data. All execution plans are stored in the plan cache. If a new query is executed, SQL server will look if an execution plan is available for this query in the plan cache. If it is available it will be re-used. This will save the generation of the execution plan. For every execution plan SQL server counts how much time it is used.
With next query you can see the content of the plan cache.

SELECT TOP 50 usecounts, cacheobjtype, objtype, TEXT, CAST(size_in_bytes AS BIGINT)/1024 as Size_in_Kb
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

What you will see is a lot of queries which are executed ones. (Column Usecount = 1). With next query you can retrieve the number of queries and the size in the plan cache which are executed ones:

SELECT COUNT(*), SUM(CAST(size_in_bytes AS BIGINT))/1024 AS Size_in_Kb
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts = 1

You will see a big number of queries which are executed ones. Now compare these values with the queries which are executed more than ones.

SELECT COUNT(*), SUM(CAST(size_in_bytes AS BIGINT))/1024 AS Size_in_Kb
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1

Because a lot of queries are executed ones it is not so useful to store the full execution plan in the plan cache. The optimize for ad hoc workloads will store a small compiled plan stub in the plan cache when a query is compiled for the first time, instead of the full compiled plan. The compiled plan stub allows the Database Engine to recognize that this ad hoc query has been compiled before but has only stored a compiled plan stub, so when this query is invoked (compiled or executed) again, the Database Engine compiles the query, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache. One stub plan takes about 0.25 Kb. A full compiled plan is much bigger. For complex queries is can be more than 100 Kb. This is a factor 400 bigger.
Run next query to get an overview of the different cache objects in the plan cache.

SELECT objtype, cacheobjtype,COUNT(*) AS Number_of_plans, SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS size_in_MBs,AVG(usecounts) AS avg_use_count, (SUM(CAST(size_in_bytes AS BIGINT))/1024)/COUNT(*) AS average_size
FROM Sys.dm_exec_cached_plans
GROUP BY Objtype, cacheobjtype
ORDER BY Objtype, cacheobjtype

Save the result set of this query. Now we have the plan cache statistics of your server. Now it's time to enable the optimize for ad hoc workloads option. Enable the optimize for ad hoc workloads option with next query:

'show advanced options',1
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO

Re run the query to get an overview of the different cache objects in the plan cache after a while. Now you will see the number of Compiled plans will be much lower becaues these will only containts queries which are executed more than ones. The number of Compiled Plan Stub will still be high, however the average size is very small.
Photo credit: HIRATA Yasuyuki

Backup compression in SQL Server 2008 (R2)

2010-04-30T09:34:00.005+02:00

What is backup compression?
In the previous versions of SQL Server you can only make an uncompressed backup to disk. The size of this backup is almost the same as the size of the database. To decrease the size of the backup file, you can use compression software like Winzip, WinRar etc. This requires additional CPU power and disk space. As of SQL 2008 a new backup option is introduced called 'Compression' which directly create a compressed backup.

Why using backup compression?

I did some tests with Exact Globe database which resulted in next graph. The uncompressed backup results are set to 100 to compare with the uncompressed results.

Decrease in backup size up to 65 %. This depends of course on the content of the data.
CPU usage will increase during the backup process. The more data can be compressed the more increase in CPU usage.
Faster backup speed (25%) because it requires less disk I/O.

In which SQL editions is this available?Unfortunately backup compression is not available in all editions of SQL 2008 (R2).
In SQL 2008 it is only available in the Enterprise edition.
In SQL 2008 R2 it is available in the Standard and Enterprise edition.

How to use backup compression?

Server level: Backup compression is off by default on server level. If you enable it on server level all your backups will be compressed by default. Use next SQL script to enable by default backup compression on server level.

USE master;
GO
EXEC sp_configure ‘backup compression default’, '1';
RECONFIGURE WITH OVERRIDE;

Database level: If you do not want to enable this option on server level you can create a backup by making use of the compression option in the backup. In the Object Explorer of SSMS Select Database, Properties, Tasks, Backup, Options, Compression.

Or using a script. In next example for database 'Mydatabase' :

[MyDatabase] DISK=N'D:\Backup\Mydatabase.bak'
WITH NOFORMAT, INIT, NAME = N'Mydatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

More information about Backup Compression can be found here.
Enjoy using SQL Server Backup Compression.

Is Microsoft moving to the Enterprise market and how important is the enterpreneur for them?

2010-04-28T21:58:00.001+02:00

My company Exact has next mission:

Serving entrepreneurial businesses is at the heart of what we do. We serve local and international companies in more than 125 countries and offer our solutions in more than 40 languages. Our solutions provide our customers the freedom to successfully address challenges and opportunities, creating value for their customers and ultimately for themselves.

With this mission in mind, I look to all new products of Microsoft. Last week SQL Server 2008 R2 is released to manufacturing. I'm indeed enthousiastic about this product. Some new features can be very useful for our customers like data compression and the powerpivots. However our customers are enterpreneurs which do not have a lot of IT knowledge. If you look to PowerPivot, this products is too complex for them to install and configure. Please have a look to the one page architecture. It explains everything:

A readable PDF version of the Power Pivot Architecture can be downloaded here.

If we look to data compression it is only available as of the Enterprise edition. This feature can be very useful for them to save diskspace and improve performance. However 95% of our customers is using the Express, Workgroup or Standard edition of SQL server. The price difference between these versions with the Enterprise edition is too big for them.

In SQL Server 2008 R2 2 new editions are introduced however all for the high end market: SQL Server 2008 R2 Datacenter edition and SQL Server 2008 R2 Parallel Data Warehouse edition.

I hope that this is not a new trend that Microsoft is moving to the high end market. I hope they will not forget the entrepeneur. Let's see what the future will bring.

Photo credit: selmerv

Exact moves to new Corporate Headquarters.

2010-04-23T09:23:00.000+02:00

This weekend my company is moving to a really nice new building. The new building is located in Delft’s Technopolis Innovation Park, an area that aims to attract companies with a high R&D component. “Technopolis”, as the park is referred to, is one of Europe’s leading knowledge centers, and hopes to become the IT center of the Netherlands. The new building’s proximity to the TU Delft, with which we have a strategic partnership, will enhance collaboration with the university.

The new Exact Headquarters inspires leadership and innovation; it will be a place where knowledge exchange and teamwork thrive, and where collaboration between business and education flourish.

For the nearly 500 employees based in Delft, the new building means a flexible, open work environment, in terms of both time and space. Flexible working hours, private meeting areas, open working spaces, and at the same time, a welcoming, informal gathering place to meet with colleagues, business partners, customers and visitors.

I'm really looking forward to the first working day on Monday 26 april 2010 in this new inspiring building.

SQL Server 2008 R2 Released to Manufacturing. (RTM)

2010-04-21T17:24:00.002+02:00

Today 21 april 2010, the SQL Server team announced on their SQL Server team blog that SQL 2008 R2 is released to manufacturing. Customers can expect availability in the next few weeks through Microsoft’s distribution channels. For more information, visit www.sqlserverlaunch.com. SQL Server 2008 R2 will be available to TechNet and MSDN subscribers on May 3 and available for download worldwide on May 13. For more information visit The Official Microsoft Blog. I'm looking forward to these RTM bits so we can start testing our commercial products on these RTM bits.

Free ebook: Introducing Microsoft SQL 2008 R2

2010-04-17T22:03:00.000+02:00

Microsoft Press has made a free ebook available for everybody who is interested in SQL Server 2008 R2. It is an introduction in all new features of SQL 2008 R2. The book contains 10 chapters and 216 pages:

Part I Database Administration

Chapter 1 SQL Server 2008 R2 Editions and Enhancements 3
Chapter 2 Multi-Server Administration 21
Chapter 3 Data-Tier Applications 41
Chapter 4 High Availability and Virtualization Enhancements 63
Chapter 5 Consolidation and Monitoring 85

Part II Business Intelligence Development

Chapter 6 Scalable Data Warehousing 109
Chapter 7 Master Data Services 125
Chapter 8 Complex Event Processing with StreamInsight 145
Chapter 9 Reporting Services Enhancements 165
Chapter 10 Self-Service Analysis with PowerPivot 189

You can download the ebook in XPS format and in PDF format. This is a good initiative of Microsoft Press. I think all Microsoft Press books should come free available as ebooks. Let see what will happen.
Enjoy reading the topics you like.

My favorite Top 10 features of SQL 2008 R2

2010-04-12T21:36:00.000+02:00

Photo credit: Sam_Churchill

In May 2010 the new version of SQL Server will be launched named: SQL 2008 R2. In this blog I will list my favorite 8 features.

Report Builder 3.0. Report Builder 3.0 is a report authoring tool that you can use to design and publish your own reports. It's the successor of Report Builder 2.0 and it's support all new SQL 2008 R2 reporting services features. When you design a report, you specify where to get the data, which data to get, and how to display the data. When you run the report, the report processor takes all the information you have specified, retrieves the data and combines it with the report layout to generate each page as you view it. You can preview your reports in Report Builder, or you can publish your report to a report server where others can run it.
Reporting server support for Databars, Sparklines, Indicators and Maps. These visualization features enables a user to build nice dashboard.

These visualization features enables you to build nice dashboards which can be used in web applications which have integrated SQL Server Reporting Services (SSRS) like Exact Synergy Enterprise.
Compressed backup support in SQL 2008 R2 Standard Edition. Backup compression is introduced in SQL 2008 Enterprise edition. In SQL 2008 R2 it is also available in the Standard edition.
Unicode compression. As more and more businesses developing global customer base, applications are being developed/modified to use unicode based data types such as NCHAR and NVARCHAR. SQL Server uses UCS-2 encoding scheme that takes two bytes of storage regardless of the locale. For example, in ASCII character set when stored as NCHAR, each character only needs 1 byte of storage but it is stored using 2 bytes with the significant byte being 0. In fact most European languages need only 1 byte of storage. When an application is either converted or written to user unicode based data types, it can, depending on the size of strings, increase the storage requirements significantly. Unfortunatly Unicode compression is not supported for NVARCHAR (MAX) type, including in-row values or for NTEXT. This is something Microsoft will consider in the future release. Unicode compression is automatically included in Page or Row compression. The compression is handled by the SQL server engine so no application changes are needed to use it.
Power Pivot for Excel. PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. It’s the user-friendly way to perform data analysis using PivotTable and, PivotChart views, and slicers
Master Data Services. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time. This is the first step in centrally manage master data. The challenge in your organization will always be: Who is allowed to change what master data. The bigger your company is, the more complex it will be.
SQL Server Utility and Multi-Server Dashboards. SQL Server utility and multi-server management will help organizations proactively manage database environments efficiently at scale through centralized visibility into resource utilization and streamlined consolidation and upgrade initiatives across the application lifecycle.
SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse. These 2 new premium editions are introduced to meet the needs of large scale datacenters and data warehouses.

What are your favorite features to complete this TOP 10 list?
Enjoy using SQL 2008 R2

New features in coming SQL Azure updates

2010-03-27T14:12:00.002+01:00

In one of my previous blogs I already told about my first experiences with SQL Azure. 2 weeks after the launch of SQL Azure the first Service Updates was coming available. At the MIX 10 conference, the Microsoft SQL Azure team announced some new features in the upcoming releases.

Support for MARS
In SU2 (April) Support for Multiple Active Row Sets. Mutilpe Active Row Sets (MARS) allows you to execute multiple batches in a single connection.

50GB Databases
In SU3 (June) 50 Gb Database will be available. If you would like to become an early adopter of this new size option before SU3 is generally available, send an email to EngageSA@microsoft.com and it will auto-reply with instructions to fill out a survey. Fill the survey out to nominate your application that requires greater than 10 Gb of storage.

Support for Spatial Data
In SU3 (June) Support for spatial data. This feature will support the Geography and Geometry types as well as query support in T-SQL. This is a nice feature which opens the Windows Azure Platform to support spatial and location aware applications.

SQL Azure Labs
A new site called SQL Azure labs is launched. SQL Azure Labs provides a place where you can access incubations and early preview bits for products and enhancements to SQL Azure. The goal is to gather feedback on the features you want to see in the product. All technologies on this site are for testing and are not ready for production use. Some of these features might not even make it into production – it’s all based upon the feedback of the SQL Azure community. Be aware that these features are actively being worked on, you should not use them against any production SQL Azure databases.

It is great to see that the development on SQL Azure is moving on. What will be the next feature in the upcoming Service Update (SU). If you have ideas you can submit them on: http://www.mygreatsqlazureidea.com/

My SQL LDF file is so big in comparison with the MDF file, what can I do ?

2010-03-04T22:39:00.004+01:00

At a lot of customer sites I have seen situations in which the transaction log file (LDF) is multiple times bigger than the MDF file. This is not a 'normal' situation. In this blog I will explain what you can do if you have an LDF file of your SQL server database which is too big in comparison with the size of your MDF file.

What is the difference between the MDF and LDF file ?
The .MDF file is the primary data file of a SQL database. The .LDF file is the transaction log file of a SQL database. The transaction log is used to guarantee the data integrity of the database and for data recovery. Data integrity is implemented by design and can't be configured by the database administrator. Data recovery is implemented by design and is configurable by the database administrator.

In SQL Server you have 3 different data recovery models: Simple, Full and bulk-logged. Typically, a database uses the Full recovery model or Simple recovery model.

What is the main difference between Full and Simple?

Simple: No transaction log backups. Changes since the most recent backup are unprotected. In the event of a disaster, those changes are lost and need to be redone. You can only recover to the end of a backup.
Full: Requiers transaction log backups. No work is lost due to a lost or damaged data file. Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For example, prior to application or user error.

What is in the transaction log in the Simple recovery model ?
All open transactions. This means not committed transactions. This is called the active part of the transaction log. At the moment a transaction is committed it will be truncated from the transaction log. Log truncation automatically frees disk space for reuse by the transaction log. The biggest size of the transaction log will be the size of all open transactions at one moment. Therefor in a 'normal' SQL production database the transaction log file will never get very big.

What is in the transaction log in the Full recovery model ?
All committed transaction (inactive part) and open transactions (active part). The log records cannot be truncated untill all its log records have been captured in a transaction log backup. The log is truncated when you backup the transaction log.

What can we do to lower the size of the transactional log file (LDF)?
It depends on the configured recovery model of your database, what you can do. The recovery model of your database can be found in the properties of the database:

or via SSMS:
Open a query window and select the database.

USE
SELECT Recovery_model_desc FROM sys.databases WHERE Name = db_name()

What to do if your recovery model is: FULL?
Check if you are making transactional backups on a reguler time interval. If you only make full backups your transaction log will always grow and will never be truncated. In case you never want to lose data in case of a application or user error. Start making transactional log backups. If a database restore to the latest full backup is acceptable, you can switch the recovery model of your database to simple. Before switching the recovery model of your database please read next document: Considerations from switching the recovery model of your database. After switching to the recovery model Simple , you can shrink the log file. See the end of this blog how to do this.

What to do if your recovery model is: Simple?
Shrink the log file.

To set the recovery model to Simple you can run next query:
ALTER DATABASE
SET RECOVERY SIMPLE

To set the recovery model to FULL you can run next query:
ALTER DATABASE
SET RECOVERY FULL

How many free space is available in my log file?

Select the database in the Object Explorer
Point to Tasks, Point to Shrink and then click Files.
Select File Type: Log
Currently allocated: The current size of your log file
Available free space: The free space in your log file

To shrink the log file:

Select the database in the Object Explorer
Point to Tasks, Point to Shrink and then click Files.
Select File Type: Log
Select Release unused space
Press OK to start the shrink process. If you do not want to shrink the log file press the cancel button.

First service update for SQL Azure Database is live.

2010-02-21T21:00:00.000+01:00

Photo credit: Jakob Montrasio

The first Service update for SQL Azure is live. This update is coming 2 weeks after the general availability of SQL Azure. It contains some bug fixes but more important it contains some new features based on the feedback they got. I'm happy to see that the SQL Azure team is looking seriously to the provided feedback. In my previous blog: "My first experiences with SQL Azure, the SQL Server database in the cloud" I talked about my experience with SQL Azure. Some things for improvement are already implementend in the first service update for SQL Azure like:

First dynamic views to monitor the health of a database, diagnose problems, and tune performance. It is not complete but the start is made.
Ability to move between editions. You can now upgrade your database fro 1 Gb to 10 Gb. 10 Gb is still the maximum size, but I think this is a matter of time.
The idle connection timeout is increased from 5 to 30 minutes. This is much better.
Improved algorithm for terminating long running transactions. Import and export of larger date is possible with having to break down you data in smaller chunks.

All details about this first service update of SQL Azure can be read here.

The first service update within 2 weeks, is a really good start. I hope that the SQL Azure team can keep on delivering this kind of service updates with this speed. I can't wait for the second service update.
Enjoy it.

Is SQL Server coming in a yearly release cycle

2010-02-19T16:36:00.000+01:00

As mentioned in my earlier post, SQL Server 2008 R2 will come commercial available in May 2010. It looks like every new SQL server release is coming sooner and sooner.
We have had SQL 2000,
5 years later SQL 2005,
3 years later SQL 2008,
2 years later SQL 2008 R2 and now it looks like
1 year later SQL 2011 codename 'Denali'.

On the Microsoft Blog MSDN site of SQL Server Releases an overview is giving of the different SQL Server related dates. The support for SQL 2005 will enter into extended support on 13 april 2011. Based on this date and the Microsoft Support Lifecycle policy, I expect that there is a big chance that the new SQL server version is planned for commercial release around this date. Microsoft is starting a new trend to come with new releases much earlier as in the past. See for instance the rumors about Windows 8.

Will this become a new trend in the release cycles of Microsoft? Let see what will happen in the coming years.