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

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.

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

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)

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 SQL Server coming in a yearly release cycle

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.

Main differences between SQL Azure and SQL Server 2008

SQL Azure is a service which makes the administration slightly different.  Unlike administration for an on-premise instance of SQL Server, SQL Azure abstracts the logical administration from the physical administration; As an database administrator you continue to administer databases, logins, users, and roles, but Microsoft administers the physical hardware such as hard drives, servers, and storage. Because Microsoft handles all of the physical administration, there are some differences between SQL Azure and an on-premise instance of SQL Server in terms of administration, provisioning, Transact-SQL support, programming model, and features.
 
Logical Administration vs. Physical Administration
To provide this level of physical administration, you cannot control the physical resources of SQL Azure. For example, you cannot specify the physical hard drive or file group where a database or index will reside. Because the computer file system is not accessible and all data is automatically replicated, SQL Server backup and restore commands are not applicable to SQL Azure. Many SQL Server Transact-SQL statements have parameters that allow you to specify file groups or physical file paths. These types of parameters are not supported in SQL Azure because they have dependencies on the physical configuration.

Because SQL Azure performs the physical administration, any statements and options that attempt to directly manipulate physical resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It is also not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.

SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, Reporting Services, and Service Broker are not currently provided as services on the SQL Azure.

Microsoft SQL Azure Database supports a subset of Transact-SQL for SQL Server 2008.

Transact-SQL Features Supported on SQL Azure
• Constants
• Constraints
• Cursors
• Index management and rebuilding indexes
• Local temporary tables
• Reserved keywords
• Stored procedures
• Statistics management
• Transactions
• Triggers
• Tables, joins, and table variables
• Transact-SQL language elements such as
o Create/drop databases
o Create/alter/drop tables
o Create/alter/drop users and logins
o and so on.
• User-defined functions
• Views, including sys.synonyms view

Transact-SQL Features Unsupported on SQL Azure
• Common Language Runtime (CLR)
• Database file placement
• Database mirroring
• Distributed queries
• Distributed transactions
• Filegroup management
• Global temporary tables
• Spatial data and indexes
• SQL Server configuration options
• SQL Server Service Broker
• System tables
• Trace Flags

Query to retrieve missing indexes (suggestions) and existing indexes of a table in one resultset.

Performance of your application can change over time. This can have various reasons like:

1. New functionality added to the product.
2. Changes in the implementation of the product.
3. Customer specific usage.

Because of these reasons it can happen that the current index structure is not optimal. Some new indexes are needed to create and some indexes can be dropped because they are not used. For index suggestions and index usage statitstics you can run seperate queries. However analyzing 2 different resultsets is not always easy. Download next query to combine both results sets in one resultset.

Have fun in optimizing your index structure.

Exact Synergy Enterprise certified for Windows Server 2008 R2

As of product update 240, Exact Synergy Enterprise is certified for the latest  Microsoft server platform Windows Server 2008 R2. This is another great step of Exact in supporting the latest technology of Microsoft. Exact Synergy Enterprise can be used in 32 bits or 64 bits mode.
Exact Synergy Enterprise and Exact Globe now supports the latest available Microsoft operatings systems like Windows 7, SQL2008 and Windows Server 2008 R2.

Exact Globe compatible with Windows 7

On 22 october 2009 Windows 7 will become commercial available. Exact Software has become one of the first European software vendors to receive this certification. By the time the Microsoft Worldwide Partner Conference took place in July, Exact Globe was certified and ready to run on Windows 7. More information about Exact Globe running on Windows 7 can be found on the Exact Product Blog and on the Ready.Set.7 site of Microsoft.
On the Ready.Set.7 website you’ll find the Exact logo and behind it, a short piece describing the advantages.
A short film featuring Aad ‘t Hart can also be viewed, in which he gives his views on what Windows 7 can do for our customers. “Windows 7 increases the productivity of the Exact Globe end user,” Aad says.

With Exact Globe you are now able to run on the most recent available technology with Windows 7 and SQL Server 2008. Don't forget that you can buy SQL 2008 licenses for the use with Exact products with a big discount via Exact.

Use batches to delete large number of records in a table

Sometimes it can happen that you need to delete a lot of records in a table. This can happen for instance in log tables. With a simple DELETE statement like:

DELETE FROM MyTable WHERE COL1 = 1

it can take a while when you have for instance 1 million records to delete. It can results in a table lock which has a negative impact on the performance of your application.

As of SQL2005/2008 you can delete records in a table in batches with the DELETE TOP (BatchSize) statement.  This method has 3 advantages

1. It will not create one big transaction.
2. It avoids a table lock.
3. If the delete statement is canceled, only the last batch is rolled back. Records in previous batches are deleted.

You can use next script to test this DELETE TOP () statement.

CREATE TABLE DEMO (COL1 INT,COL2 INT)


DECLARE @COUNTER INT
SET @COUNTER = 1

INSERT INTO DEMO (COL1,COL2) Values (2,2)

WHILE @COUNTER < 50000
BEGIN

INSERT INTO DEMO (COL1,COL2) Values (1,@COUNTER)
SET @COUNTER = @COUNTER + 1

END

/*
-- Show content of the table
SELECT COL1, COUNT(*) FROM DEMO GROUP BY COL1
*/

-- Deleting records in batches of 1000 records

DECLARE @BatchSize INT
SET @BatchSize = 1000

WHILE @BatchSize <> 0

BEGIN

DELETE TOP (@BatchSize)
FROM DEMO
WHERE COL1 = 1

SET @BatchSize = @@rowcount

END

-- SELECT * FROM Demo -- Now we have only 1 record left

White paper: Using the Resource Governor to manage performance of your SQL 2008 Enterprise Edition server

Microsoft has published a white paper about the Resource Governor. The Resource Governor provides enterprise customers the ability to both monitor and control the way different workloads utilize CPU and memory resources on their SQL Server instances. With the Resource Governor you, as DBA, have a new way to better manage the performance of your SQL server.
This white paper is targeted at IT professionals and database administrators responsible for managing scale, concurrency, high availability, consolidation, virtualization, or service level agreements for the SQL Server instances in their production environments. In this paper you are shown various ways in which you can use the Resource Governor to analyze and streamline the way resources are utilized within your SQL Server instances, by explaining:

• How the Resource Governor works, including limitations and best practices.
• Methods for building a consolidation plan, and testing those methods.
• Techniques for preventing runaway queries, or at least minimizing their impact.
• Approaches to meeting service level agreements (SLAs) driven by response time and concurrency.
• Basic ideas about implementing a chargeback system to recover resource costs.
• Ways to monitor SQL Server using new Resource Governor functionality.

The paper will highlight several common usage scenarios, help you decide when and where to use the technology, and outline best practices when using the Resource Governor.

Enjoy reading of this document

Exact Synergy Enterprise supports SQL Server 2008 as of product update 241

As of 30 June 2009, Exact Synergy Enterprise product update 241 is general available. In this product update SQL Server 2008 is supported. Exact is selling this new version of SQL Server. Please contact your local Exact office or reseller. If you want to have a look at the unique selling points of SQL Server 2008, please have a look at the overview on the Microsoft website. For an overview of technical documentation for SQL Server 2008, please refer to the Books Online.

SQL Server 2008 licenses grant customers the right to downgrade, which means that customers wanting to use SQL Server 2005 can still do so when purchasing SQL Server 2008 licenses. The upgrade to SQL Server 2008 can then take place at their own convenience. So if you are currently using SQL 2000, you can buy SQL 2008 instead of SQL 2005. If one of your applications does not support SQL 2008 at this moment, you are allowed to install SQL 2005.
 
More information about the product update 241 of Exact Synergy Enterprise can be found on the Exact Product Blog.

Update of Books Online SQL 2008 available

Last month an update of the Books Online (BOL) of SQL 2008 is published.

Books Online is the primary documentation for SQL Server 2008 and includes the following types of information:

• Setup and upgrade instructions.
• Information about new features and backward compatibility.
• Conceptual descriptions of the technologies and features in SQL Server 2008.
• Procedural topics describing how to use the various features in SQL Server 2008.
• Tutorials that guide you through common tasks.
• Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2008.
• Descriptions of the sample databases and applications that are available with SQL Server 2008. You can download the sample databases from the SQL Server Community Projects and Samples page on CodePlex.

Performance improvements with filtered indexes in SQL 2008

In SQL 2008 filtered indexes are introduced. A filtered index allows us to create a filter to index a subset of rows within a table. A filtered index will:

1. Improve query performance. Statistics are more accurate which can result in better query plans. 
2. Reduce index maintenance costs. An index is only maintained when the data in the index is changed.
3. Reduce index storage costs.

Filtered indexes can be very useful if you have one of the following situations:

1. Columns which contains mostly NULL values and where queries retrieve only the rows where the data is NOT NULL. 
2. When you want to enforce uniqueness for a set of values. With a filtered index you can create a unique index for the set of values. For instance
   CREATE UNIQUE NONCLUSTERED INDEX CustomerID_NOTNULL ON Accounts (CustomerID) WHERE CustomerID IS NOT NULL 
3. When analyzing data on a specific data range. For instance you have a table with records for the last 4 years (2006,2007,2008,2009). You are analyzing only the year 2009. A filtered index will look like
   CREATE INDEX YEAR2009 ON Transactions (Year,Period, Amount) WHERE Year = 2009

To see the difference in index sizes on a table create a normal index and a filtered index. For instance:

1. CREATE INDEX YEAR ON Transactions (Year,Period, Amount)
2. CREATE INDEX YEAR2009 ON Transactions (Year,Period, Amount) WHERE Year = 2009

Use next script to see the difference in index size:


DECLARE @TableName VARCHAR(100)

SET @TableName = 'fill in the tablename'

SELECT db.index_id,si.name, IndexSizeKB = SUM(page_count * 8)
FROM sys.dm_db_index_physical_stats(db_id(),object_id(@TableName),NULL,NULL,'DETAILED') db
INNER JOIN sys.indexes SI ON SI.index_id = db.index_id AND SI.object_id = object_id(@TableName)
GROUP BY db.index_id ,si.name
ORDER BY db.index_id desc,si.name