Keep It Simple and Fast

My favorite SQL Server Management Studio settings

2009-11-01T23:48:00.000+01:00

I work a lot with SQL Server Management Studio (SSMS). From a performance perspective I'm not so happy with the default settings of this application. Therefor I made some changes to the settings of SSMS.

To made changes to the setting of SSMS start SSMS and select Tools, Options in the menu.

Startup with an empty environment. (Environment, General, At startup: Open empty environment)
To avoid to lock other users when you execute long running SELECT queries. Set the default transaction isolation level to: READ UNCOMMITTED (Query Execution, SQL Server, Advanced, SET TRANSACTION ISOLATION LEVEL)
Include column headers when copying or saving results. (Query Results, SQL Server, Result to Grid)
Quote strings containing list seperators when saving to .csv results. (Query Results, SQL Server, Result to Grid)
Word wrap. This is a setting which I changed a lot depending on the kind of queries I'm analyzing. (Text editor, Plain Text, General, Settings)

Enjoy using SSMS

Using SQL Profiler Trace without the SA server role

2009-10-13T21:39:00.002+02:00

By default users who has the SA role (SysAdmin) server role on the SQL server are able to trace activity on the SQL server with SQL Profiler. However not all users who need to trace SQL server activity will have the SA server role. To give users permissions to run SQL Profiler traces without the SA role, you need to grant ALTER TRACE permissions to the these users.

For instance grant SQL Profiler permissions to the user account Mark from domain MyCompany:

GRANT ALTER TRACE to [MyCompany\Mark]

For more information see: Permissions required to run SQL Profiler trace.

IIS Database Manager to easily manage your local and remote databases within IIS Manager

2009-10-05T21:54:00.001+02:00

IIS Database Manager allows you to easily manage your local and remote databases from within IIS Manager. IIS Database Manager automatically discovers databases based on the Web server or application configuration and also provides the ability to connect to any database on the network. Once connected, IIS Database Manager provides a full array of management options including managing tables, views, stored procedures and data, as well as running ad hoc queries.

IIS Database Manager provides native support for SQL Server and is also fully extensible for developers to add support for other database systems. In addition, because IIS Database Manager is an extension of IIS Manager, administrators can securely delegate the management of databases to authorized local or remote users, without having to open additional management ports on the server.

First of all install the IIS Database Manager of your web server.
Start the IIS Manager.
Select your web server
In the management view click on the Database Manager icon

Create a database connection to the database you want:

Open your connection:

Press on the new query button:

Now you can start executing queries on your database

Be aware this is a simple query analyzer. You can't for instance retrieve query plans etc. But for simple checks it can be a nice tool without the need to install the full SQL Server Management Studio (SSMS)

Exact Globe compatible with Windows 7

2009-09-30T21:00:00.000+02:00

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

2009-09-15T22:54:00.001+02:00

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

It will not create one big transaction.
It avoids a table lock.
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

The report definition has an invalid target namespace 'http://schema.microsoft.com/sqlserver/reporting/2009/01/reportdefinition' which cannot be upgraded.

2009-08-31T21:31:00.000+02:00

This week I installed the CTP version of SQL Server 2008 R2 as a seperate instance on my laptop to test. This laptop is also used to build and deploy reports to SQL 2008 reporting servers. The installation can be done without any problem, however the tools can't be installed seperate. The SQL 2008 R2 tools make use of the same components as the tools of SQL Server 2008. After having installed these SQL 2008 R2 tools you are able to open, modify and save your SQL 2008 reports. However after saving these reports you can't deploy them anymore to a SQL 2008 reporting server. If you try to do this you will get next error:

The report definition has an invalid target namespace 'http://schema.microsoft.com/sqlserver/reporting/2009/01/reportdefinition' which cannot be upgraded.

To be able to deploy reports to SQL 2008 on my laptop, I have unstalled the tools of SQL 2008 R2 and reinstalled the SQL 2008 tools. After that I restored a backup of my SQL 2008 reports.

Microsoft it would be nice if these tools do not have shared components with previous releases. This will make testing of these products much easier. Now I need to install a seperate machine to test the CTP version of SQL 2008 R2. Or does anybody have another solution for this?

Index statistics and suggestions of a table in one query.

2009-08-28T20:05:00.004+02:00

To optimize the index structure of tables in your database, you need to get information how these indexes are used. On the other hand you want to know if new indexes can improve the performance of your database. Index usage statistics and index suggestions are stored by the SQL Server engine as of SQL 2005. With next query you can combine the index usages statistics and suggestions in one query. It will help you to improve the performance of your database.

Be aware to look very critical to all index suggestions. Suggestions have no relation to each other. Therefor do not add all index suggestions at the same. Add one index and analyze the results after a while and re-execute the query.

To use this script specify the table you want to analyze in the second line. In this example the table GBKMUT is analyzed. If the table you specified does not exist, it will return all statistics and suggestions of all table in your database. A lot of success with improving your database.

DECLARE @ObjectID int

SELECT @ObjectID = OBJECT_ID('GBKMUT')

;WITH preIndexAnalysis
AS (
SELECT
OBJECT_SCHEMA_NAME(t.object_id) as schema_name
,t.name as table_name
,COALESCE(i.name, 'N/A') as index_name
,CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc as type_desc
,NULL as impact
,ROW_NUMBER()
OVER (PARTITION BY i.object_id ORDER BY i.is_primary_key desc, ius.user_seeks + ius.user_scans + ius.user_lookups desc) as ranking
,ius.user_seeks + ius.user_scans + ius.user_lookups as user_total
,COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
/(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)
OVER(PARTITION BY i.object_id), 0) * 1.) as decimal(6,2)),0) as user_total_pct
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, '') AS indexed_columns
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, '') AS included_columns
,i.object_id
,i.index_id
,STUFF((SELECT ', ' + QUOTENAME(ic.column_id,'(')
FROM sys.index_columns ic
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, '') AS indexed_columns_compare
,COALESCE(STUFF((SELECT ', ' + QUOTENAME(ic.column_id, '(')
FROM sys.index_columns ic
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, ''), SPACE(0)) AS included_columns_compare
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = db_id()
WHERE t.object_id = @ObjectID OR @ObjectID IS NULL
UNION ALL
SELECT
OBJECT_SCHEMA_NAME(mid.object_id) as schema_name
,OBJECT_NAME(mid.object_id) as table_name
,'--MISSING--'
,'--NONCLUSTERED--'
,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
,0 as ranking
,migs.user_seeks + migs.user_scans as user_total
,NULL as user_total_pct
,migs.user_seeks
,migs.user_scans
,0 as user_lookups
,COALESCE(equality_columns + ', ', SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
,included_columns
,mid.object_id
,NULL
,NULL
,NULL
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE database_id = db_id()
AND mid.object_id = @ObjectID OR @ObjectID IS NULL
)
SELECT schema_name
,table_name
,index_name
,type_desc
,impact
,user_total
,user_total_pct
,CAST(100 * (user_seeks + user_scans + user_lookups)
/(NULLIF(SUM(user_seeks + user_scans + user_lookups)
OVER(PARTITION BY schema_name, table_name), 0) * 1.) as decimal(6,2)) as estimated_percent
,user_seeks
,user_scans
,user_lookups
,indexed_columns
,included_columns
,STUFF((SELECT ', ' + index_name AS [data()]
FROM preIndexAnalysis iia
WHERE ia.object_id = iia.object_id
AND ia.index_id <> iia.index_id
AND ia.indexed_columns_compare = iia.indexed_columns_compare
AND ia.included_columns_compare = iia.included_columns_compare
FOR XML PATH('')), 1, 2, '') AS duplicate_indexes
,STUFF((SELECT ', ' + index_name AS [data()]
FROM preIndexAnalysis iia
WHERE ia.object_id = iia.object_id
AND ia.index_id <> iia.index_id
AND (ia.indexed_columns_compare LIKE iia.indexed_columns_compare + '%'
OR iia.indexed_columns_compare LIKE ia.indexed_columns_compare + '%')
AND ia.indexed_columns_compare <> iia.indexed_columns_compare
FOR XML PATH('')), 1, 2, '') AS overlapping_indexes
FROM preIndexAnalysis ia
ORDER BY schema_name, table_name, ROW_NUMBER() OVER (PARTITION BY schema_name, table_name ORDER BY user_total desc, ranking), index_name

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

2009-08-05T21:45:00.001+02:00

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

Book review: SQL Server 2008 Internals

2009-07-14T12:34:00.000+02:00

One of the topics I'm interested in is performance of a database. Based on this topic I read the book SQL Server 2008 Internals. I enjoyed to read this book. It is easy to read and usefull for people with and without knowledge of previous versions of SQL Server. After reading this book it will help you to better understand the performance of your SQL Server configuration and databases. I can imagine that everybody has it's own favorite topics when reading this book. This is the list of my favorite topics:

SQL Server Resource Governor
Indexes: Internals and Management

Tools for analyzing indexes.
Data modification Internals
Managing index structures

Special Storage

Sparse columns
Data compression

The query optimizer

Optimizer architecture
Statistics, Cardinality Estimation and Costing
Index selection
Plan Hinting

Plan Caching and Recompilation

Cache mechanisme
Plan cache internals.

DBCC Internals.

I think it is a usefull investment of only 52 Euro (60 US Dollar). It is a must to have for every DBA'er who wants to deliver the best performance of his SQL server environment to his users. This also applies to software engineers who want to build well performing applications on SQL Server.

Happy reading.

Performance improvement: Disk Partition alignment Best Practices for SQL Server

2009-07-07T10:49:00.001+02:00

Disk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science. A best practice that is essential yet often overlooked is disk partition alignment. Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows. This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions. The following topics are also included: background information, implementation, vendor considerations, two essential correlations, valid starting partition offsets, and the simple protocol to align partitions, define file allocation unit size, and assign drive letters. It includes results from tests that show how partition alignment can affect performance for SQL Server 2008.

Microsoft has published a usefull best practices document about this topic. Take your time to read and understand it. It can improve your performance of your disks up to 30% depending on your storage configuration.

Within my company Exact we have seen a performance improvement of 10% with aligned partitions on a HP EVA 5000 storage system.

The good news is that partition alignment is simple to perform; The bad news is that partition alignment must be done at partition creation time, prior to partitions being formatted. This is great if you have a new SAN, but it might be painful to convert large amounts of existing data on misaligned partitions. Correct disk partition alignment is done by default on Windows Server 2008. On prior versions of Windows Server partitions are misaligned by default.

More details and test results about EVA Partition alignment can be found here

My visit to the first tweetup of Dutch Minister Verhagen of Foreign Affairs.

2009-07-03T22:15:00.000+02:00

Today, I was invited by our Dutch Minister of Foreign Affairs Maxime Verhagen to join his first tweetup. He organized this tweetup to celebrate the fact that he has more than 10.000 followers. It was a nice event. Maxime told about his experience with Twitter.

In the Dutch House of Representatives it is not allowed to Twitter because representatives should listen. However it is allowed to write 140 characters on paper. A messenger will bring this paper to his spokesman. His spokesman is now able to tweet the message. I think the chairmain of the Dutch House of Representatives should allow tweets. Accept this new way of communication. People will always find their way to be able to tweet. Maxime reaction on this: 'I do not listen with my fingers.'
People do not understand that Maxime has time to tweet. As Minister of Foreign Affairs Maxime travels a lot. During travelling by car, plane, ..... you always have some time to tweet.
It is nice way to get feedback on what you are doing. Postive feedback is nice, but you can learn from negative feedback and take action on it.
He told about his twitpic from the Treves zaal. This was not allowed. He was suprised because a lot of pictures are available on the internet of the Treves zaal. Only official pictures of the Treves zaal are allowed. The end result of this discussion resulted in a lot more followers for Maxime.

My observations during this event:

Average age of the attendees was around 35. This was higher as what I expected. Maybe because the generation Y is not interested in politics and there for not following politicans like Maxime. I saw a womam which could be my mother. Nice to see that people from that generation can make the shift to the new way of communication.
The spokesman of Maxime is not twittering.
Most guests are twittering since the last 6 months.
Most guests are twittering from a personal perspective, not from a business perspective.
Some guests are following Maxime to understand what he is doing as Minister of Foreign Affairs others are following Maxime to understand his political point of view and a group who are interested in the way Maxime uses social media.
Mixture of Nokia Smartphone, Blackberries, IPhones to tweet live. But also a group of people which only make their tweets via a PC.
Everybody is looking forward to the next tweetup. For instance a BBQ on the Binnenhof

The Twitters: @rotjong, @kruithoph @tonyjansen @jacokoster @zorg20 made a nice certificate for Maxime: Minister Tweep. The first minister which is using Twitter in a formal way.

Thanks Maxime for organizing this tweetup. I have enjoyed it. More politicans should follow your way of using social media.

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

2009-07-01T08:54:00.000+02:00

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.

How to debug your SQL server query?

2009-06-14T13:46:00.000+02:00

Have you ever wondered why your query is not doing as what you expected?
Have you ever add extra lines in your query to print parameter values, so you better understand what is happening?

If you answer one of these questions with No, you are a really good query builder or you never build queries. If you answer one of these questions with Yes, this blog post can be something for you.

Within SQL 2008 a debug option is introduced in the SQL Server Management Studio (SSMS).

It is the green arrow. In previous versions of SSMS, this green button is the execute button.
To demonstrate the debug option I will use a simple query:

DECLARE @Rownumber INT = 0

WHILE @Rownumber < 10
BEGIN
SELECT @@SERVERNAME
END

This query will never end. This is because @Rownumber does not change.
Now we will start debugging this query.

Set your cursor on SELECT @@SERVERNAME and
Pres F9 to set a breakpoint. A red circle will appear.
Press ALT F5. In your 'locals' window the @Rownumber will appear without any value.
Press ALT F5. Debugging is started and will stop on the breakpoint. The @Rownumber has now a value of 0. This is what you expect.

Press ALT F5 to continue. The servername will be returned.
Press ALT F5 to continue. Another time the servername will be returned. However the value of @Rownumber is still on zero. This explains why the query never stops. The @Rownumber will never get 10.
Stop debugging with SHIFT F5
Add next statement SET @Rownumber = @Rownumber + 1
So we get next script:

DECLARE @Rownumber INT = 0

WHILE @Rownumber < 10
BEGIN
SELECT @@SERVERNAME
SET @Rownumber = @Rownumber + 1
END
Set breakpoint on the same line: SELECT @@SERVERNAME
Start debugging by pressing ALT F5 a few times. You will see that the value of @Rownumber is growing and the query will stop when the value reaches 9.

Enjoy this feature when you do not understand what your query is doing. Unfortunately, this debug feature is only available in SQL Server 2008. Maybe another reason to upgrade to SQL Server 2008.

Update of Books Online SQL 2008 available

2009-06-05T15:29:00.000+02:00

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.

SP_HelpIndexALL procedure with INCLUDE and Filtered columns

2009-06-02T22:17:00.012+02:00

As of SQL 2005 you can create indexes with the INCLUDE option. As of SQL 2008 you can create filtered indexes. Both options are a nice way to improve your index structure. To retrieve the index structure on your table, you can make use of SP_HELPINDEX </ tablename>. However this stored procedure does not display the INCLUDE columns and do the display the WHERE clause of your Filtered index. On the blog of Kimberly L. Tripp I found a nice script which displayed these INCLUDE columns and WHERE Clause. However I found one bug on the Include columns. Assume Indexid 43 has an include columns. Indexid 44 has NO Include column. For IndexID 44 the first INCLUDE column of IndexID 43 was displayed as INCLUDE column for IndexID44.

With next script this bug is fixed.

CREATE PROCEDURE Sp_helpindexall
                @objname NVARCHAR(776)
                -- the table to check for indexes
AS
  -- Based on the stored procedure sp_helpindex2
-- of Kimberly L.Tripp,
  -- http://www.SQLskills.com/blogs/Kimberly
  -- Only fixed a bug for the Include Columns
-- 2 June 2009
  -- There for renamed the stored procedure
-- to SP_HelpindexALL to add included columns
-- AND filter_definition to the output.
  -- See http://www.KeepItSimpleAndFast.com
-- blog of André van de Graaf
  -- PRELIM
  SET nocount  ON

  DECLARE  @objid             INT,
            -- the object id of the table
           @indid             SMALLINT,
            -- the index id of an index
           @groupid           INT,
            -- the filegroup id of an index
           @indname           SYSNAME,
           @groupname         SYSNAME,
           @status            INT,
           @keys              NVARCHAR(2126),
           @inc_columns       NVARCHAR(MAX),
           @inc_Count         SMALLINT,
           @loop_inc_Count    SMALLINT,
           @dbname            SYSNAME,
           @ignore_dup_key    BIT,
           @is_unique         BIT,
           @is_hypothetical   BIT,
           @is_primary_key    BIT,
           @is_unique_key     BIT,
           @auto_created      BIT,
           @no_recompute      BIT,
           @filter_definition NVARCHAR(MAX)

  -- Check to see that the object names are
-- local to the current database.
  SELECT @dbname = Parsename(@objname,3)

  IF @dbname IS NULL
    SELECT @dbname = Db_name()
  ELSE
    IF @dbname <> Db_name()
      BEGIN
        RAISERROR (15250,-1,-1)

        RETURN (1)
      END

  -- Check to see the the table exists and initialize @objid.
  SELECT @objid = Object_id(@objname)

  IF @objid IS NULL
    BEGIN
      RAISERROR (15009,-1,-1,@objname,@dbname)

      RETURN (1)
    END

  -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
  DECLARE ms_crs_ind CURSOR LOCAL STATIC FOR
  SELECT i.index_id,
         i.data_space_id,
         i.name,
         i.ignore_dup_key,
         i.is_unique,
         i.is_hypothetical,
         i.is_primary_key,
         i.is_unique_constraint,
         s.auto_created,
         s.no_recompute,
         i.filter_definition
  FROM   sys.indexes i
         JOIN sys.stats s
           ON i.object_id = s.object_id
              AND i.index_id = s.stats_id
  WHERE  i.object_id = @objid

  OPEN ms_crs_ind

  FETCH  ms_crs_ind
  INTO @indid,
       @groupid,
       @indname,
       @ignore_dup_key,
       @is_unique,
       @is_hypothetical,
       @is_primary_key,
       @is_unique_key,
       @auto_created,
       @no_recompute,
       @filter_definition

  -- IF NO INDEX, QUIT
  IF @@FETCH_STATUS < 0
    BEGIN
      DEALLOCATE ms_crs_ind
      RAISERROR (15472,-1,-1,@objname)
        -- Object does not have any indexes.
     RETURN (0)
    END

  -- create temp tables
  CREATE TABLE #spindtab (
    index_name SYSNAME
        COLLATE database_default NOT NULL,
    index_id          INT,
    ignore_dup_key    BIT,
    is_unique         BIT,
    is_hypothetical   BIT,
    is_primary_key    BIT,
    is_unique_key     BIT,
    auto_created      BIT,
    no_recompute      BIT,
    groupname SYSNAME
        COLLATE database_default NULL,
    index_keys NVARCHAR(2126)
        COLLATE database_default   NOT NULL,
    -- see @keys above for length descr
    filter_definition NVARCHAR(MAX),
    inc_count         SMALLINT,
    inc_columns       NVARCHAR(MAX))

  CREATE TABLE #includedcolumns (
    rownumber SMALLINT,
    [Name]    NVARCHAR(128))

  -- Now check out each index,
-- figure out its type and keys and
  -- save the info in a temporary table
-- that we'll print out at the end.
  WHILE @@FETCH_STATUS >= 0
    BEGIN
      -- First we'll figure out
      -- what the keys are.
      DECLARE  @i       INT,
               @thiskey NVARCHAR(131)

      SELECT @keys = Index_col(@objname,@indid,1),
             @i = 2

IF (Indexkey_property(@objid,@indid,1,'isdescending') = 1)
SELECT @keys = @keys + '(-)'

SELECT @thiskey = Index_col(@objname,@indid,@i)

IF ((@thiskey IS NOT NULL)
AND (Indexkey_property(@objid,@indid,@i,'isdescending') = 1))
SELECT @thiskey = @thiskey + '(-)'

WHILE (@thiskey IS NOT NULL)
BEGIN
SELECT @keys = @keys + ', ' + @thiskey,
        @i = @i + 1

SELECT @thiskey = Index_col(@objname,@indid,@i)

IF ((@thiskey IS NOT NULL)
AND (Indexkey_property(@objid,@indid,@i,'isdescending') = 1))
   SELECT @thiskey = @thiskey + '(-)'
        END

      -- Second, we'll figure out
      -- what the included columns are.
      SELECT @inc_Count = Count(* )
      FROM   sys.tables AS tbl
             INNER JOIN sys.indexes AS si
               ON (si.index_id > 0
                   AND si.is_hypothetical = 0)
                  AND (si.object_id = tbl.object_id)
             INNER JOIN sys.index_columns AS ic
               ON (ic.column_id > 0
                   AND (ic.key_ordinal > 0
                         OR ic.partition_ordinal = 0
                         OR ic.is_included_column != 0))
                  AND (ic.index_id = Cast(si.index_id AS INT)
                       AND ic.object_id = si.object_id)
             INNER JOIN sys.columns AS clmns
               ON clmns.object_id = ic.object_id
                  AND clmns.column_id = ic.column_id
      WHERE  ic.is_included_column = 1
             AND (si.index_id = @indid)
             AND (tbl.object_id = @objid)

      -- ADD Set included columns
      -- to NULL to script of Kimberly
      -- otherwise an index without
      -- an include column
      -- will get the first include
      -- of the previous index
      SET @inc_columns = NULL

      IF @inc_Count > 0
        -- Add begin to script of Kimberly
        BEGIN
          INSERT #includedcolumns
          SELECT Row_number()
                   OVER(ORDER BY clmns.column_id),
                 clmns.name
          FROM   sys.tables AS tbl
           INNER JOIN sys.indexes AS si
            ON (si.index_id > 0
                AND si.is_hypothetical = 0)
                AND (si.object_id = tbl.object_id)
           INNER JOIN sys.index_columns AS ic
            ON (ic.column_id > 0
                AND (ic.key_ordinal > 0
                OR ic.partition_ordinal = 0
                OR ic.is_included_column != 0))
                AND (ic.index_id = Cast(si.index_id AS INT)
                AND ic.object_id = si.object_id)
           INNER JOIN sys.columns AS clmns
            ON clmns.object_id = ic.object_id
                AND clmns.column_id = ic.column_id
   WHERE  ic.is_included_column = 1
         AND (si.index_id = @indid)
         AND (tbl.object_id = @objid)

  SELECT @inc_columns = [Name]
  FROM   #includedcolumns
  WHERE  rownumber = 1

  SET @loop_inc_Count = 1

  WHILE @loop_inc_Count < @inc_Count
     BEGIN
     SELECT @inc_columns = @inc_columns + ', ' + [Name]
     FROM   #includedcolumns
     WHERE  rownumber = @loop_inc_Count + 1

     SET @loop_inc_Count = @loop_inc_Count + 1
    END
-- Add END to script of Kimberly
END

      SELECT @groupname = NULL

      SELECT @groupname = name
      FROM   sys.data_spaces
      WHERE  data_space_id = @groupid

      -- INSERT ROW FOR INDEX
      INSERT INTO #spindtab
      VALUES     (@indname,
                  @indid,
                  @ignore_dup_key,
                  @is_unique,
                  @is_hypothetical,
                  @is_primary_key,
                  @is_unique_key,
                  @auto_created,
                  @no_recompute,
                  @groupname,
                  @keys,
                  @filter_definition,
                  @inc_Count,
                  @inc_columns)

      -- Next index
      FETCH  ms_crs_ind
      INTO @indid,
           @groupid,
           @indname,
           @ignore_dup_key,
           @is_unique,
           @is_hypothetical,
           @is_primary_key,
           @is_unique_key,
           @auto_created,
           @no_recompute,
           @filter_definition
    END

  DEALLOCATE ms_crs_ind

  -- DISPLAY THE RESULTS
  SELECT 'index_name' = index_name,
    'index_description' = Convert(VARCHAR(210),
    CASE WHEN index_id = 1 THEN 'clustered'
      ELSE 'nonclustered' END +
    CASE WHEN ignore_dup_key <> 0
      THEN ', ignore duplicate keys'
      ELSE '' END +
    CASE WHEN is_unique <> 0
      THEN ', unique'
      ELSE '' END +
    CASE WHEN is_hypothetical <> 0
      THEN ', hypothetical'
      ELSE '' END +
    CASE WHEN is_primary_key <> 0
      THEN ', primary key'
      ELSE '' END +
    CASE WHEN is_unique_key <> 0
      THEN ', unique key'
      ELSE '' END +
    CASE WHEN auto_created <> 0
      THEN ', auto create'
      ELSE '' END +
    CASE WHEN no_recompute <> 0
      THEN ', stats no recompute'
      ELSE '' END + ' located on ' + groupname),
     'index_keys' = index_keys,
     --'num_included_columns' = inc_Count,
     'included_columns' = inc_columns,
     'filter_definition' = filter_definition
  FROM     #spindtab
  ORDER BY index_name

  RETURN (0) -- sp_helpindexwinc2

How to find the bottleneck in your application with a SQL Profiler trace file?

2009-05-07T20:38:00.020+02:00

Introduction

Performance bottlenecks can have different causes. Every cause can be recognized on its own way and will have its own solution. To solve your performance issue in your application you need to know on which part you need to focus. We have 4 different types of performance bottlenecks. This blog will describe what kind of performance bottlenecks we can have in applications and how we can recognize them. If you find situations as described in this document, it is not said that this is wrong. It will help you to analyze very critical what is happening in your application and maybe you can rewrite a part of your application to avoid specific behavior. It is like rebuilding your house, it will cost you a lot of headache but at the end you’re happy with the result.

Types of performance bottlenecks

1) Long running queries

Incomplete WHERE clauses. For instance an MRP record can be recognized by column1 = 'MRP' and Column2 = ‘B’. So both fields should be specified in the WHERE clause. In that case indexes created for MRP records will be used by the optimizer. In SQL Profiler you will see queries with a big amount of reads. Amount of reads depends on your database size. In a database smaller than 5 GB 100.000 reads is high. For bigger databases higher values are acceptable however 1 million reads are always big and should be analyzed.

Wrong data types in the query. For instance Column Periode in table Transactions is of type INT. Query syntax should be Transactions.Periode = 5 instead of Transactions.Periode = ‘ 5’ First syntax is correct. When wrong datatypes are used SQL server need to convert this data to the types which are used in the datamodel. This will cost CPU resources and not efficient query plans. Always double check the data type of a field with next command SP_HELP Transactions

Unneccassry JOINS. Check for every JOIN if it is used in the SELECT or WHERE part of your query. If this only applies for the SELECT part of your query check if this column is used for a calculation or displayed to the user. If not remove this part of the query.

SELECT TOP 100 TR.FinYear, TR.Periode, TR.Journalnr, TR.Description, TR.Amount
FROM Transactions TR
INNER JOIN Journals JR ON JR.Journalnr = TR.Journalnr
WHERE TR.FinYear = 2009

Queries which retrieve a lot of data without using it. For instance retrieve 50 columns: SELECT Column1, Columns2, Column3.....,Column 50 FROM Transactions. but show only 6 columns to the user. In this situation 44 columns of data are not used. Do you buy all newspapers in the store and select at home which one your are going to read and throw all other newspapers in the bin? Data transfer of 50 columns will takes more time than data transfer of 6 columns. When profiling the Amount of reads in SQL Profiler the profiled values can be the same because SQL already has done a bookmark lookup to retrieve the data of the 6 columns. However the physical data transferred to the user is much lower. This can be measured with the Query Analyzer of SQL 2005. (Include Client Statistics SHIFT+ALT+S) After completing the query an extra tab Client Statistics is presented.

Trial 1 (SELECT TOP 100 * FROM Transactions with all records already in memory) The 50 milliseconds were used to transfer all data to the client. The time needed for SQL server is the difference between Total execution time and the client processing time. In this case 50 - 50 = 0 milliseconds. (Not measurable)

Trial 2 (SELECT TOP 1000 * FROM Transactions with NOT all records in memory). The 1101 milliseconds where used to transfer all data to the user. Only 10 milliseconds where used by SQL Server to retrieve the requested data from the database. (792Kb)

Trial 3 (select top 1000 Finyear, Periode, Journalnr, Description, Amount from Transactions) 5 colomns of data will now only take 94Kb bytes of data
More tips can be found on my blog: Guidelines to write well performing queries

2) Big amount of (recurring) queries.

Every time the same query. For instance retrieve the default currency in every order line. One time when starting a new order is enough. Every query which can be avoided should be avoided.
After you have executed your application stop the SQL profile trace and save this trace to a file. Open this trace in SQL profiler and order the traces lines via the organize columns. Group on the column Textdata.

All recurring queries will be ordered together. In next example you see that exec efwGetOrkrg ‘ 5555’ is executed 4 times with one second.

A lot of queries with almost the same syntax. For instance

UPDATE Transactions
SET SYSMODIFIED = '2006-03-10 09:00:00.000'
WHERE ID = 21436

UPDATE Transactions
SET SYSMODIFIED = '2006-03-10 09:00:00.001'
WHERE ID = 21437

UPDATE Transactions
SET SYSMODIFIED = '2006-03-10 09:00:00.002'
WHERE ID = 21438

These queries can be combined in one query:
UPDATE Transactions

SET SYSMODIFIED = '2006-03-10 09:00:00.001'
WHERE ID IN (21436,21437,21438)
This will save a lot of round trips from your application to the server. What is faster: Buying one box on beer with 24 bottles with your bike or buying 24 times with a Ferrari one bottle of beer?

3) High resource usage at the client side. (CPU and or memory)

Application can retrieve a lot of data from the database and do a lot off calculations with it to present a result to the user. (High CPU usage) Try to rebuild your queries so SQL server can do these calculations. This will save the transfer of a lot of data from the server to the client.

Memory leaks. Memory used by the application is growing and only a part of the memory is released. After a while the application will crash. Use Windows Performance monitor to see if this happens.

High memory usage. For instance more than 250 Mb. On a client with a total memory of 512 Mb this will result in disk swapping. Because the operating itself and other open applications (like Outlook, Internet Explorer etc) need more than 262 Mb. If disk swapping occurs the performance will drop enormously. Reading from disk is 10.000 times slower than reading from memory.

4) Time gaps between the end time of query X and the start time of query X+1.
In this period SQL server is not busy with your application. At the client site, your application is doing something. For instance building the spread for an entry application. Debug your application to look why this takes so much time. See the end of this blog for a script to find the time gaps between queries.

The tools I use to check my application?

There are 2 tools I uses to identify your bottleneck.
a. SQL Profiler, to analyze all queries which are executed.
b. Windows Performance monitor, to analyze the resource usage at the client and server side.

Both tools should be used at the same time.

SQL Profiler: Create a trace file with a filter on your hostname, so you get a trace file with only the queries which are executed by your application.

Performance monitor: With the performance monitor it is easy to see which resources are used on client and server side. To have a clear view between client side and server do not use SQL Server and Globe on one machine. Use SQL server on a separate machine. Performance monitor can be started via START, RUN, Perfmon. Add next counters to the performance monitor for the client computer:

Performance object: Processor Counter: % Processor Time Instance: Total
Process Page File Bytes (Your application)
Process Working Set (Your application)

To be able to select your application in the performance monitor the application should be started otherwise it can’t be selected.

For the server side add next counters to the performance monitor:
Performance object: Processor Counter: % Processor Time Instance: Total
Physical Disk Counter:
Avg. Disk Read Queue Length Instance: (Your database)
Avg. Disk Write Queue Length (Your database)

To be able to select performance counters of a machine you should be a member of the local administrators group on that machine. If so you can add counters of both SQL server and Globe client computer in one performance monitor.

Start the performance monitor and the SQL profiler as described and start your application which you want to improve.

After profiling your application. Stop the trace and save it. Reload the trace file and add filter to your trace. For instance on CPU to find CPU intensive queries, on Reads to find queries which retrieve a lot of data.

To find the time gaps between queries store your trace file in a SQL table.

Use next script to retrieve statistics about your traced application:

DECLARE @AnalyzeTime VARCHAR(20)
SET @AnalyzeTime = '2009-05-07 11:16:06'
-- Profile statistics of application
SELECT SUM(DATEDIFF(ms, A.Starttime,A.Endtime)) AS SUMQueryTime,
SUM(DATEDIFF(ms, B.Endtime,A.Starttime)) AS SUMTimeDiffPrevQuery
,DATEDIFF(ms, MIN(A.Starttime),Max(A.Endtime)) AS TimeSpend, COunt(*) As NumberOfQueries
FROM [Profilehome2] A
INNER JOIN [Profilehome2] B ON A.Rownumber = B.Rownumber + 1
WHERE A.TEXTDATA IS NOT NULL
AND A.starttime > @AnalyzeTime

Use next script to find the time gaps between queries:

DECLARE @AnalyzeTime VARCHAR(20)
SET @AnalyzeTime = '2008-09-01 11:16:06'
-- Time difference between previous query
SELECT A.Textdata,DATEDIFF(ms, B.Endtime,A.Starttime) AS TimeDiffPrevQuery,A.Starttime, A.Endtime
FROM [Profilehome2] A
INNER JOIN [Profilehome2] B ON A.Rownumber = B.Rownumber + 1
Where A.starttime > @AnalyzeTime

Performance improvements with filtered indexes in SQL 2008

2009-05-03T23:06:00.000+02:00

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:

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

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

Columns which contains mostly NULL values and where queries retrieve only the rows where the data is NOT NULL.
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
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:

CREATE INDEX YEAR ON Transactions (Year,Period, Amount)
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

Exact Globe and Exact Synergy supports SQL Server 2008 as of product update 393.

2009-04-22T11:08:00.000+02:00

As of 21 april 2009 product update 393 for Exact Globe and Exact Synergy is generally available for all customers worldwide with an active maintenance agreement, except for customers with custom solutions .

As of product update 393 both Exact Globe and Exact Synergy supports the use of SQL Server 2008. This means that Exact starts 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. This is also important to know for customers purchasing or using Exact Synergy Enterprise: they are adviced to purchase SQL Server 2008 licenses and use SQL Server 2005 until product update 241 of Exact Synergy Enterprise, which suports SQL Server 2008, is available.

Exact System Information application: Why should you use it?

2009-04-21T23:32:00.001+02:00

This is my third blog post about the Exact System Information application. In the first 2 blogposts I introduced the reason of the Exact System Information application and what kind of information is retrieved by the Exact System Information application. In this blogpost I will explain why you as valuable customer should start this application on your SQL Server.
With the information we received from your Exact databases, we are able to improve our software. However improvements in the software based on this data will cost time and you will not directly benefit of it.

So the question is: What kind of improvements are directly possible for you to implement?

I will give some examples to:
SQL Server configuration improvements:

Example: Database configuration improvement scripts:

Example: Not used Exact functionality: Clean up historical journal records to save database size.

These are some examples. To experience all possible improvements, please start the application on your SQL server. The application is available on : ftp://ftp.exact.nl/Software/ESI/ExactSysInfo.zip. Extract the zip file on your SQL server and start the ExactSysInfo.exe

After we have received your data we will analyze the data. Via the support department you can request the improvement report. In the future we will distribute this improvement report via our portals.

Thank you already for your cooperation and trust in Exact Software.

Troubleshooting Performance Problems in SQL Server 2008

2009-04-15T11:03:00.000+02:00

Microsoft has published an interesting article (97 pages) about troubleshooting Performance Problems in SQL Server 2008. This is a must to read for people interesting in performance analyzing and optimization of your SQL Server. It provide information to find:

Resource bottlenecks
CPU bottlenecks
Memory bottlenecks
I/O bottlenecks
Tempdb issues
Slow Running Queries.

The full article can be found here: http://msdn.microsoft.com/en-us/library/dd672789.aspx

Enjoy it.

SQL Server maintenance on your SQL databases to improve performance.

2009-04-07T22:48:00.014+02:00

Image credit: Gopal1035

Introduction:

Performance of applications on SQL Server depends on different factors. This can be hardware configuration, hardware capacity, application problems, SQL server configuration and maintenance on SQL Server database. See for instance documents:

How to measure performance of your SQL server?

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

Memory configurations for SQL 2005 and SQL 2008.

This document will describe what kind of database maintenance activities can be done to get better performance of your SQL database. Be aware that this will contribute to a better performing system, but this will not the solution for all performance problems.

Goal:

Better performance of the database with the same hardware and software.

Solution:

To get better performance of your SQL database next maintenance activities can be executed.

Defragmentation of clustered indexes
Update statistics of most important tables with full scan
Defragmentation indexes

Ad 1) Defragmentation of clustered indexes.

How to:

A clustered index indicates the order in which table data should be physically stored. The table data is sorted and stored according to the key column or columns specified for the clustered index. Because the data is physically stored in a specific order, you can create only one clustered index per table. For instance the table GBKMUT of Exact Globe or Exact Synergy Enterprise has a clustered index on the field ID. All records will be physically stored in the order of ID. New records will be added at the end of the table. All data rows are stored in data pages. One data page contains multiple data rows. When GBKMUT records are deleted, for instance MRP records, you will get caps between the ID’s. A part of the data page to which the deleted GBKMUT records belongs is now empty. This is called fragmentation of the clustered index. This will result in more reads to retrieve the same amount of data. With next command you can retrieve the fragmentation percentage of a clustered index:

dbcc showcontig ('<tablename>', 1)

Example for table GBKMUT

dbcc showcontig ('GBKMUT', 1)

When executing dbcc showcontig ('GBKMUT', 1) next results are displayed.

DBCC SHOWCONTIG scanning 'gbkmut' table...

Table: 'gbkmut' (1019866700); index ID: 1, database ID: 5

TABLE level scan performed.

- Pages Scanned................................: 3037060

- Extents Scanned..............................: 385999

- Extent Switches..............................: 535838

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 70.85% [379633:535839]

- Logical Scan Fragmentation ..................: 3.53%

- Extent Scan Fragmentation ...................: 37.17%

- Avg. Bytes Free per Page.....................: 612.5

- Avg. Page Density (full).....................: 92.43%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The most optimal situation is a table with a scan density of 100 %. In this example 30 % of the pages are not used. To optimize the scan density use next command for a unique clustered index:

CREATE UNIQUE CLUSTERED INDEX <Clustered index name> ON <tablename> (<fields) with drop_existing

To optimize the scan density use next command for a non unique clustered index:

CREATE CLUSTERED INDEX <Clustered index name> ON <tablename> (<fields) with drop_existing

Example for table GBKMUT batch 360 which has a unique clustered index.

CREATE UNIQUE CLUSTERED INDEX PK_gbkmut ON gbkmut (ID) with drop_existing

When:

Not scheduled. Check can be executed on a weekly or monthly basis. Rebuild should be done when the specific table is not used by other users. Therefore a repair can’t be scheduled on a regular basis.

Ad 2) Update statistics of most important tables with full scan

How to:

Statistics are the basis for the SQL optimizer to generate the most optimal query plan. By default statistics are updated automatically. These statistics are updated by using a sample of the data. This is correct but sometimes it can happen that the default sample rate is too small. In these cases an update of the statistics should be execute.

Statistics can be updated with next command:

UPDATE STATISTICS <Table name> WITH FULLSCAN

To update table gbkmut with a fullscan use next command:

UPDATE STATISTICS GBKMUT WITH FULLSCAN

Depending on the amount of indexes and the amount of records this can take a while.

When:
Updating statistics of a table can be done while the table is used by other people. However it will have a negative impact on performance because all records in the table are retrieved. Therefore this can be scheduled on a daily or weekly basis when the use of the system is minimal. For instance during the night or during the weekend.

Ad 3) Defragmentation of indexes.

During the use of a table (Update, insert and deletes) fragmentation of data and indexes can occur. This will result in more reads to retrieve the same amount of data.

How to:

With next script all indexes of all tables in the database with a fragmentation > 30 % will be optimized.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/

-- Declare variables

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @Query NVARCHAR(255)
DECLARE @IxName VARCHAR (255)
DECLARE @IxId Int
DECLARE @ObjId Int
DECLARE @ScanDensity DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the mimimum fragmentation to allow
SELECT @maxfrag = 70.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT Name FROM sysobjects where xtype='U'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexName, IndexId, ScanDensity
FROM #fraglist
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @TableName, @ObjID, @IxName, @IxId, @ScanDensity

WHILE @@FETCH_STATUS = 0
BEGIN
set @Query = 'DBCC IndexDefrag (0,' + rtrim(@tablename) +','+ rtrim(@IxName) + ') with no_infomsgs --Current ScanDensity = ' + rtrim(@ScanDensity)
print @Query
EXEC sp_executesql @Query
FETCH NEXT
FROM indexes
INTO @TableName, @ObjID, @IxName, @IxId, @ScanDensity
END
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO

When:

Defragmentation of indexes can be done while the table is used by other people. However it will have a negative impact on performance because all indexes in the table are retrieved and updated. Therefore this can be scheduled on a weekly basis when the use of the system is minimal. For instance during the weekend.

Conclusion:

However SQL Server is largely self-configuring, self-tuning, and self-managing some maintenance is still necessary to get better performance. Defragmentation of clustered indexes and Update statistics of most important tables with full scan should be done manually if necessary. Defragmentation of indexes should be done on a weekly basis and can be scheduled.

Running a SQL Profiler trace via SQL Server Management Studio (SSMS)

2009-03-30T22:16:00.000+02:00

SQL Profiler is a tool which every respected DBA should use. It is a tool to analyze performance, analyze what a kind of queries are executed by an application etc.... Without the SQL profiler application, you can still make profile traces. This is possible via SQL Server Management Studio (SSMS).

Easiest way to do this is to start SQL Profiler and define the events,columns and filters you want to profile. Now you can export the trace definition. (File,Export, Script Trace Definition, For SQL 2005-2008)

Now I will show you a script which I uses a lot with some additional comments and an automatic stop of the profile trace of 1 minute.
-- start script to run a profiler trace
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Extra from default export
DECLARE @EndTime DATETIME
DECLARE @OutputFileName NVARCHAR(256)
SET @OutputFileName = 'C:\Traces\tracefile'

-- Set the time the profile trace should run in minutes
SET @EndTime = DATEADD(mi,1,getdate()) -- trace will stop after 1 minute

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

EXEC @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
-- 15: Endtime
exec sp_trace_setevent @TraceID, 10, 15, @on
-- 16: Reads
exec sp_trace_setevent @TraceID, 10, 16, @on
-- 9:ClientProcessID
exec sp_trace_setevent @TraceID, 10, 9, @on
-- 17:Writes
exec sp_trace_setevent @TraceID, 10, 17, @on
-- 2:BinaryData
exec sp_trace_setevent @TraceID, 10, 2, @on
-- 10:ApplicationName
exec sp_trace_setevent @TraceID, 10, 10, @on
-- 18: CPU
exec sp_trace_setevent @TraceID, 10, 18, @on
-- 3:DatabaseID
exec sp_trace_setevent @TraceID, 10, 3, @on
-- 11:LoginName
exec sp_trace_setevent @TraceID, 10, 11, @on
-- 12:Spid
exec sp_trace_setevent @TraceID, 10, 12, @on
-- 13:Duration
exec sp_trace_setevent @TraceID, 10, 13, @on
-- 6:NTUserName
exec sp_trace_setevent @TraceID, 10, 6, @on
-- 14:Starttime
exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- TraceID 3 = DatabaseID
-- 0 = equal
set @intfilter = 8
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 53952710-77cd-4820-9710-533bb536e38b'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c8d5b4e9-a748-4c71-8cce-4c105fa2141f'

-- TraceID 12 = SPID
-- 1: SPID <> 51
set @intfilter = 51
exec sp_trace_setfilter @TraceID, 12, 0, 1, @intfilter

-- TraceID 13 = Duration
-- 4: Duration >= 2000000
set @bigintfilter = 2000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- TraceID 16 = Reads
-- 3: < 5000 Reads
set @bigintfilter = 5000
exec sp_trace_setfilter @TraceID, 16, 0, 3, @bigintfilter

-- TraceID 18 = CPU
-- 2: CPU > 100
set @intfilter = 100
exec sp_trace_setfilter @TraceID, 18, 0, 2, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID

goto finish
error:
select ErrorCode=@rc
finish:
go

-- End Script to run a profiler trace

Additional information about all events and columns can be found in the books online. Search for: sp_trace_setstatus, sp_trace_setfilter

After starting the trace you get a TraceID. This TraceID is needed if you want to stop the trace before the endtime has occured. For instance, you want to stop a trace after 5 minutes but the @Endtime is configured for 30 minutes.

With next statement you can stop the trace for traceID 6:

-- Stop the trace: sp_trace_setstatus 'TraceID',0
sp_trace_setstatus 6,0
-- Close the trace sp_trace_setstatus 'Traceid',2
sp_trace_setstatus 6,2

After the trace is completed you can import the trace file in a SQL table for futher analyze:

CREATE TABLE TraceResults (

TextData VARCHAR(4000),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
StartTime DATETIME,
ProcedureName VARCHAR(100)
)
GO

-- store traced files in a table
INSERT INTO TraceResults
(TextData, Duration, Reads, Writes, CPU, StartTime)
SELECT TextData, Duration/1000, Reads, Writes, CPU, StartTime
FROM fn_trace_gettable('c:\traces\tracefile.trc',1)

How to measure performance of your SQL Server?

2009-03-25T20:07:00.004+01:00

Image credit: Lauren Nelson

In situations where I need to analyze the performance of a SQL Server I used a checklist to get an overview of the configuration of the SQL Server. This will costs 10 minutes to fill in but it will is a good start to find the bottleneck in the SQL Server configuration. In this blog you will find a checklist for the hardware configuration and database settings. At the end you will find the Windows Performance counters and a SQL profile trace definition I uses as starting point.

Performance problems can consist of one or more of the following:

Hardware problems: Hardware components could be malfunctioning. Faulty components can cause severe performance problems.
Hardware capacity: You might be exceeding the capacity of the system components. You might need post capacity planning or to reconfigure existing hardware.
Application problems: SQL statements might be inefficient, causing excessive usage of system resources

Hardware problems:
Check the event log for errors; check the event log of your storage system

Hardware capacity:

The three most common components on your system that require tuning:

Processor
Disk subsystem
Memory

Processor: A general rule is that if your processor utilization stays continuously at 80 percent or above, or if it peaks often at this rate, you might have a CPU bottleneck. Performance counter: Processor object, % Processor Time counter.
Disk subsystem: SQL server uses the following three categories of files for databases:

• Primary data files. A primary data file contains startup information for a database, points to other files used by the database, stores system tables and objects, and can also store database data and objects. Each database has exactly one primary file. (File extension .MDF)
• Secondary data files. Secondary files are optional for each database. They can be used to hold data and objects, such as tables and indexes, which are not in the primary file. A database might need one or more secondary files placed on separate disks to spread data across the disks. (File extension .NDF)
• Log files. A log file holds all the transaction log information for the database and cannot be used to hold any other data. (File extension .LDF). For performance reasons it is recommended to place the LDF file on a separate hard disk. The LDF should be the only file on this hard disk, otherwise the advantage of a separate hard disk for log operations is lost.
• You can combine two or more disks into a RAID array. The main characteristic of a RAID array is that physical disk drives are combined to form a logical disk drive, which is a virtual disk drive. When configuring logical drives you need to select the RAID level:
• RAID 0. Does not support redundancy. There is no fault tolerance, so if one disk fails, all data will be lost. RAID 0 is not recommended for storing SQL Server data files.
• RAID 1. Also known as mirroring. Use RAID 1 when data fits on one disk drive. Use RAID 1 for your operating system disk. It can be time consuming to rebuild an OS in the event of a failure. Since the OS usually fits on one disk, RAID 1 is a good choice. Use RAID 1 for the transaction log. Typically the SQL Server transaction log can fit on one disk drive. In addition, the transaction log performs mostly sequential writes. Only rollback operations cause reads from the transaction log. Thus you can achieve a high rate of performance by isolating the transaction log to its own RAID I volume. Use write caching on RAID I volumes. Because RAID 1 writes will not finish until both writes have been done, you can improve performance of writes by using a write cache. When you use a write cache be sure that it is backed up by a battery.
• RAID 5. Any disk volume that does more than 10 percent writes is not a good candidate for RAID. Use write caching on RAID 5 volumes. Since a RAID 5 write is not complete until two reads and two writes have been performed, you can improve the response time of writes through the use of a write cache. When you use a write cache, be sure that it is backed up with a battery. However, the write cache is not a cure for overdriving your disk drives. You must still stay within the capacity of those disks. As you can see, RAID 5 is economical, but at a performance price.
• RAID 10. RAID 10 is a combination of RAD 0 and RAID 1. RAID 10 involves mirroring a disk stripe. Each disk has an exact duplicate, but each disk contains only a part of the data. This configuration gives you the fault—tolerant advantages of RAID 1 with the convenience and performance advantages of RAID 0. Use RAID 10 whenever the array experiences more than 10 percent writes. RAID 5 does not perform well with large numbers of writes. Use RAID 10 when performance is critical. Since RAID 10 supports split seeks, performance is very good. Use write caching on RAID 10 volumes. Since RAID 10 writes are not complete until both writes are done, you can improve the performance of writes by using a write cache. Write caching is only safe when used in conjunction with caches that are backed up with batteries.
To determine the load placed on the individual disk drives in the system, you must perform some calculations based on next performance counters:

Physical Disk Disk Reads/sec
Physical Disk Disk Writes/sec

With next formulas you can determine how many I/Os are actually going to each disk in the array. RAID 0: I/Os per Disk = (Reads + Writes) Number of Disks
RAID 1: I/Os per Disk = (Reads + (2 * Writes))/2
RAID 5: I/Os per Disk = (Reads + (4 * Writes))/ Number of Disks
RAID 10: I/Os per Disk = (Reads + (2 * Writes))/ Number of Disks

RAID level	Performance	Fault Tolerance	Cost
RAID 0	Best	No fault tolerance	Most economical
RAID 1	Good	Good	Most expensive
RAID 5	Good reads slow writes	Ok	Most economical Fault tolerant
RAID 10	Good	Excellent	Most expensive

How to calculate the maximum I/O per second for next disk specifications:

Specification	Value	Notes
Disk capacity	18 Gb	Unformatted disk capacity
Rotational speed	15.000 rpm	Speed at which the disk is spinning
Transfer rate	40 M/b sec	Speed of the SCSI bus
Average seek time	3,9 ms (read) 4,5 ms (write)	Times it takes to seek between tracks during random I/Os

A random I/O on a typical system takes approximately 4.2 ms ((seek time read + seek time write)/2) for the disk to seek to where the data is held and an additional 2 ms in rotational latency, for a total of 6.2 ms. This gives a theoretical maximum of 161 I/Os per second (since 6.2 ins can occur 161 times per second). As noted earlier, if you run a disk drive at more than 85 percent of its capacity, queuing occurs. Therefore, the maximum recommended I/O rate is 137 I/Os per second. Taking into account overhead in the controller, a general rule is to drive these disk drives at no more than 125 1/ Os per second. So the closer the number
of actually I/O’s per second gets to maximum capacity the longer the latencies get. The disk queue lengths will growth which results in performance lose.

Memory:
It is best to dedicate your database server to SQL Server applications only, if possible. That allows SQL Server to use as much memory as possible in the system without having to share it with other applications. SQL Server 2008 supports the maximum support memory of the operating system.

Operating System	SQL Server 2008
Windows Server 2003/2008 X86	32 GB
Windows Server 2003/2008 X64 Web or Standard	32 GB
Windows Server 2003/2008 X64 Enterprise or Datacenter	2 TB

To allow applications on Windows 2003/2008 X86 to address more than 4 GB, these editions support Address Windowing Extensions (AWE). AWE allows physical memory pages above the standard 4 GB memory space to be acquired by applications. To allow AWE with Windows 2000, you must add the /PAE flag to the Boot.ini file. If you have between 4 GB and 16 GB in your system, you can also add the /3GB flag to the Boot file to allow only 1 GB of virtual memory space
for the operating system, instead of 2 GB

Amount of system memory	Flag to add to the boot.ini file
4 GB	/3GB
8 GB	/3GB /PAE
16 GB	/3GB /PAE
Above 16 GB (16 GB to 64 GB)	/PAE

You must enable the AWE enabled option by setting it to I (its default is 0). Restart SQL Server for the
setting to take effect. The AWE option is an advanced option. You must have show advanced options set to 1 to view the current values of advanced options or to change an advanced option using sp_configure. To configure show advanced op/ions, use the following statement: sp_configure “show advanced options”, 1

To enable AWE use the following statement: EXEC sp_configure 'awe enabled', '1'

For more details about enable and configure advanced options see the books online of SQL server.

Before you can analyze the performance monitor log files you need to know the hardware configuration of the SQL server.

Hardware configuration:

Number of CPHU’s
CPU MHz
Physical RAM amount
If physical memory is more than 4 GB, what is the contents of the BOOT.INI
Total number of physical drives in each array
RAID level of array used for SQL server databases
Hardware versus Software RAID
Location of operating system
Location of SQL server executables
Location of SWAP file
Location of Tempdb
Location of System databases
Location of user databases (MDF)
Location of log files (LDF)
What is the size of all user databases
How many free disk space is available on each array
Is write back cache in Disk controller On or Off
Speed of disk drives (RPM)
What is the average seek time of the hard disk
What is the rotational latency of the hard disk
What is the speed of the network card
Are the network cards hard coded for Speed/Duplex
How many database are attached in SQL server
Is this Physical server dedicated to SQL server
Is “NTFS data file encryption and compression” turned off
Is the Windows 2003 server configured as stand alone server
Is the “application response” setting, set to “Optimize Performance for background services”

Location of operating system.
For best performance, operating system should be on a disk array that does not
include the SQL server data files.

Location of SQL server executables.
The location of the SQL server executables, is not critical, as long as they are not located on the same array as the SQL server data files.

Location of the swap file.
The location of the swap file is not critical, as long as they are not located on the same array as the SQL server data files.

Location of the Tempdb.
If the tempdb is heavily used, consider moving it to an array of its own, either RADI 1 or RAID 10, to boost disk I/O performance. Avoid RAID 5 arrays as they can be slow when writing data.

Location of User databases.
For best performance, user databases should be located on their own array (RAID 1,5 or 10), separate from all other data files, including log files.

How many free disk space is available ? While the performance effect isn’t huge, it is important that all of your disk arrays have at least 20% of free space. This is because NTFS needs extra space to work efficiently. If space is not available, then NTFS is not able to function at its full capacity and performance can degrade. It also leads to more disk fragmentation.

Is write back cache in Disk Controller on or off ?

Write back cache will boost the performance of SQL server, however you should have a controller that offers battery backup. Otherwise you will get a corrupt database in case of a power failure. This is because SQL server already thinks that the data is stored, while it is still in cache. All data in cache is cleared after a power failure.

Are the network cards hard coded for speed/Duplex ?
It is fairly common for a network card to auto-sense incorrectly, setting a less than optimum speed or duplex setting, which can significantly hurt network performance. So set manually the card’s speed and duplex setting.

Is this physical server dedicated to SQL server ?
SQL server should run on a dedicated physical server, not shared with other application software. When you share SQL server with other software, you force SQL server to fight over physical resources, which make it much more difficult to tune your server for optimum SQL server performance.

Database configuration settings:

Database configuration setting	Default value	Current value, if other than default specify reason.
Auto_close	Off
Auto_create_statistics	On
Auto_update_statistics	On
Auto_shrink	Off
Database auto grow	On
Transaction log auto grow	On
Recovery model	Full

Auto_close:
By default set to OFF, only useful to set On when you have a lot of SQL databases on your server which are used incidental. When set to ON, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. The database reopens automatically when a user tries to use the database again. If the database was shut down cleanly, the database is not reopened until a user tries to use the database the next time SQL Server is restarted. When set to OFF, the database remains open even if no users are currently using the database. The Auto_close option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.

Auto_create_ statistics:
When set to On, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query.

Auto_update_statitics:
When set to On, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.

Auto_shrink:
By default set to Off, because mostly every database will growth in size during usages.

Database auto grow:
By default set to On, the databases can growth whenever necessary. There is only one restriction, you need to have diskspace available on the array where the database is stored.

Transaction log auto grow: By default set to On,
The transaction log can growth whenever necessary. There is only one restriction, you need to have diskspace available on the array where the transaction log is stored.

Recovery model:
Can be set to simple if only full back ups are made and no transaction log back ups are made. Use the performance monitor to collect data of the SQL server. Define a trace log with next counters and set the interval time on 1 minute.

Performance monitor counters:

Memory	Available Bytes
Memory	Page faults/sec
Network Interface	Current Bandwidth
Network Interface	Bytes Sent/sec
Network Interface	Bytes Received/sec
Physical Disk	Avg. Disk sec/Read
Physical Disk	Avg. Disk sec/Write
Physical Disk	Read queue length
Physical Disk	Write queue length
Physical Disk	Avg. Disk Reads/sec
Physical Disk	Avg. Disk Writes/sec
Processor	% Processor Time
Processor	Processor Queue length
Server	Server sessions
SQL Server: Cache manager	Cache Hit ratio
SQL Server: Databases	Transactions/sec
SQL Server: General statistics	User connections
System	Processor Queue length

Explanation of performance monitor counters:

Memory

· Available bytes The amount of free space in memory

· Page faults/sec The number of page faults per second for code pages and data pages, averaged
over the interval period.

Network interface

· Current Bandwidth The current size of the line

· Bytes Sent/sec The number of bytes sent by the system per second, averaged over the interval
period.

· Bytes Received/sec The number of bytes received by the system per second, averaged over the
interval period.

Physical disk

· Avg. Disk sec/Read The average time (in milliseconds) a read operation takes. This time is
important because prolonged read and write operations indicate an over utilized
disk.

· Avg. Disk sec/Write The average time (in milliseconds) a write operation takes. This time is
important because prolonged read and write operations indicate an over utilized
disk.

· Read queue length The actual queue length for read operations. A disk queue of 2 (per disk) is the maximum recommended value for this counter. If the MDF file stored on 5 disks the maximum allowed queue length is 10 (5*2).

· Write queue length The actual queue length for write operations. A disk queue of 2 (per disk) is
the maximum recommended value for this counter. If the MDF file stored on 5 disks the maximum allowed queue length is 10 (5*2).

· Avg. Disk Reads/sec Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.

· Avg. Disk Writes/sec Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.

Processor

· % Processor Time The percentage of time the processor was busy. If more than 80 % you can have a CPU bottleneck.

Server

· Server sessions The number of user sessions currently going on within the server.

SQL Server: Cache Manager

· Cache Hit ratio The percentage of time a record was found in cache. The recommended cache hit
ratio is 90 percent or more.

SQL Server: Databases

· Transactions/sec The number of transactions started for the database. These transactions come in the form of requests from client machines that are serviced by the database.

SQL Server: General statistics

· User connections The number of users connected to the database.

System

· Processor Queue length Processor Queue Length is the number of threads in the processor queue. There is a single queue for processor time even on computers with multiple processors. Unlike the disk counters, this counter counts ready threads only, not threads that are running. A sustained processor queue of greater than two threads generally indicates processor congestion. This counter displays the last observed value only; it is not an average.

Application problems:

Use the SQL profiler to search for queries which results in a lot of reads or
writes. Define a trace file with

Events: Stored procedures: RPC Completed

TSQL: SQL: Batchcompleted

Selected Data Groups: Reads

Columns: Eventclass

Textdata

ApplicationName

NTusername

Loginname

CPU

Duration

Writes

Starttime

Filter: Reads Greater than or equal 100.000

Database ID: the database ID of the specific database you want to profile.

Analyze this profile to see if queries can be optimized

Performance improvement for parameterized queries in SQL Server 2008: Optimze for Unknown

2009-03-18T15:27:00.000+01:00

Parameterized queries are a well known SQL Server practice. This technique ensures caching and reuse of existing query execution plans (instead of constantly compiling new plans). This will improve performance of your application. However it can have a negative impact if the initial parameter is not representative of the values passed in the subsequent re-execution of the query. For the initial query, SQL Server compiled and cached a 'good' plan for the first parameter value. However this 'good' cached execution plan is performing very badly for all subsequent parameters values. The overall performance of this parameterized query depends on the parameter which is used for the first time. This is out of the control of the developer.

In SQL 2008 a new query hint option is introduced which is called: Optimize for Unknown

This hint directs the query optimizer to use the standard algorithms it has always used if no parameters values had been passed to the query at all. In this case the optimizer will look at all available statistical data to reach a determination of what the values of the local variables used to generate the query plan should be, instead of looking at the specific parameter values that were passed to the query by the application.

To further explain I will use a script which can be used in SQL 2008

CREATE DATABASE TEST

SET STATISTICS PROFILE OFF
GO
USE TEST
GO
-- Create a table with content.
CREATE TABLE Test(COL1 INT, COL2 CHAR(4),COL3 VARCHAR(25))
DECLARE @COL1 int, @COL2 INT
SET @COL1 = 0
WHILE @COL1 < 10000
BEGIN
INSERT INTO Test(col1,col2,col3) VALUES (@COL1,'Scan','THIS IS A TEST')
SET @COL1 = @COL1 + 1
END

SET @COL2 = @COL1
WHILE @COL2 < @COL1 + 11
BEGIN
INSERT INTO Test(COL1,COL2,COL3) VALUES (@COL2,'Seek','THIS IS A TEST')
SET @COL2 = @COL2 + 1
END
GO

-- Create indexes on generated table
CREATE UNIQUE CLUSTERED INDEX IX_CLUS ON TEST(COL1)
CREATE INDEX IX_2 ON TEST(COL2)
GO

DBCC SHOW_STATISTICS(TEST,IX_2) WITH HISTOGRAM
-- Show statistics for index IX_2.
-- How many times do the values for Column2 occur.
-- 'Seek' 11 times
-- 'Scan' 10000 times
GO

-- Stored procedure with SQL 2005\2000 behavior
CREATE PROCEDURE SLAP (@COL2 CHAR(4)) AS
SELECT COL3
FROM Test
WHERE COL2 = @COL2
GO

-- Stored procedure with SQL 2008
--Optimize for Unknown behavior
CREATE PROCEDURE COOL (@COL2 CHAR(4)) AS
SELECT COL3
FROM Test
WHERE COL2 = @COL2 OPTION (OPTIMIZE FOR (@COL2 UNKNOWN))
GO

-- Stored procedure with SQL 2008
-- Optimize for configured parameter value behavior
CREATE PROCEDURE COOLParDef (@COL2 CHAR(4)) AS
SELECT COL3
FROM Test
WHERE COL2 = @COL2 OPTION (OPTIMIZE FOR (@COL2 = 'Seek'))
GO

-- Display execution plan after execution
SET STATISTICS PROFILE ON

-- Clear procedure cache
DBCC FREEPROCCACHE

-- Start demo SQL 2000/2005 behavior
EXEC SLAP Seek -- Parameter 'Seek' is leading for query plan.
--It will use an Index Seek

GO

EXEC SLAP Scan
-- Stored procedure SLAP is executed for parameter value 'Scan'
--with an Index Seek (cached)

GO
-- Clear procedure cache
DBCC FREEPROCCACHE
EXEC SLAP Scan
-- Parameter 'Scan' is leading for query plan.
--It will use an Index Scan
GO
EXEC SLAP Seek
-- Stored procedure SLAP is executed for parameter value 'Seek'
--with an Index Scan (cached)
GO

DBCC FREEPROCCACHE
GO
EXEC COOL Seek
-- Stored procedure COOL is executed for parameter value 'Seek'
-- with an Index Scan.
-- Parameter value 'Seek' is ignored and the statistics are used. In this case value scan occurs 10.000 times.
-- Value Seek occurs only 11 times.
-- For overall performance of this query the Index Scan is the best.
GO
EXEC COOL Scan
GO

DBCC FREEPROCCACHE
GO
EXEC COOLParDef Scan
GO
-- Stored procedure is compiled with OPTIMIZE FOR (@COL2 = 'Seek'
-- Execution plan is always based on the parameter value 'Seek'
GO
EXEC COOLParDef Seek

-- Clean up all created stuff
USE MASTER
GO
DROP DATABASE TEST

Conclusion: This Optimize for Unknown option is a performance improvement. However I do not understand why this option is not default bahviour. Has somebody experience (good or bad) with this parameter?

Exact System Information application: Which information is retrieved?

2009-03-17T21:14:00.000+01:00

In my previous blog I introduced the Exact System Information application. The goal of this application is to retrieve statistics how customers are using our software. With this information we can improve the software. We do NOT want to know how a customer is doing their business. Company sensitive data is not useful for us to improve the software. We want to minimize the valuable time of the customer to get this information in a consistent way. This blog post will explain what kind of information is retrieved:

Index usage statistics. Every table can have multiple indexes. SQL Server stores information how these indexes are used. This information is useful to optimize the index structure of the database.
Index suggestions. Every database is used in a different way. Exact is a standard package which can be used in different ways. Sometimes the standard index structure is not good enough and can be optimized. SQL Server stores information how the index structure can be optimized. The combination of index usage statistics and index suggestion will enable Exact to improve the standard index structure.
Logged errors. This information is useful to fix errors in the software.
Number of times an application is started. This will give inside in the most used applications and which functionality is used.
Number of records per tables. This will give inside in the most used applications and which functionality is used. No information is retrieved about the content of the individual records. In combination with the hardware statistics we can make better hardware requirements for the customer situation.
Background processes which are started in the last 60 days. This information will be used to give feedback if all necessary background jobs are configured.
Installed Exact software package
Size of the database and log file.
Configured Exact settings. This information is useful to know which functionality is used.
Hardware statistics like Amount of Memory, Number of CPU's, SQL Server edition etc..

The information which is send to Exact is also stored as a CSV file on the desktop of the user who has started the Exact System Information application. It can also be seen in the menu Logs, View CSV file.

In a later post I will describe the advantage of this application for the customer on the short and long term.
If you are interested to take part of the controlled release phase of this application, please send an email to me