woensdag 19 september 2012

Optimize your SQL statements

It is always good practise to optimize your SQL query statements for a better performance. Just making sure your query does what it needs to, isn't enough. You should consider the performance impact of your code and try to optimize it for the best performance.

Below is a short list of steps to take when optimizing your query statements.
More details on the steps can be found in this blog.

  1. Check if you're missing any required table joins.
    This could result in a Cartesian join.

  2. Check if you're missing any required WHERE clause to prevent retrieving too much data.

  3. Check if statistics are being created & updated automatically.

  4. Check if the statistics are up-to-date.
    The DBCC SHOW_STATISTICS command can be used to view the last updated date-time, total table rows and the number of rows sampled.
    Outdated statistics can be renewed using  the sp_updatestats stored procedure, or By using the FULLSCAN option to update all statistics of a table.

  5. Check for any missing table or Index scans by using the execution plans.
    you can use these DMV's to check for missing indexes: sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups.

  6. Check for RID Lookups, also by using the execution plans.
    These cannot always be eliminated, but by making use of covering indexes, RID Lookups can be reduced.

  7. Check for any sort operator, again by using the execution plans.
    There are 3 options:
    • Modify the underlining tables to create a CLUSTERED index on the required sort columns. It could be worth trying out creating the CLUSTERED index on another column which is not the Primary Key.
    • Create an Indexed view on the underlining tables and sort the view by creating a CLUSTERED Index.
    • Create a NON CLUSTERED Index on the specified columns and Include all other columns which will be returned.


  8. Check for excessive index fragmentation.
    The DMV sys.dm_db_index_physical_stats can be used for this matter.

  9. Check table locks.
    To prevent locking problems, follow these guidelines:
    • Keep transactions as short as possible.
    • Review the transaction isolation level, and consider minimizing locking contention, thus increasing concurrency by changing to 'Read Committed using row versioning' or 'Snapshot'.
    • Specify table hints such as READUNCOMMITTED or READPAST on the select statements. Although both of these table hints do increase concurrency, both have disadvantages such as 'dirty reads' when using the READUNCOMMITTED or returning an incomplete data set when using the READPAST and therefore they may not be acceptable to use in all circumstances.


Source: http://www.sqlservercentral.com/articles/Performance+Tuning/70647/

Speeding Up Database Access

I found a complete serie on speeding up acces to your SQL Database.

So far, there are only 4 parts published of an 8 part series of articles on speeding up access to a SQL Server Database.
Here's an overview of the content of all 8 parts (including links of already published parts):

  • Part 1 Pinpointing missing indexes and expensive queries
    • Find expensive queries (with trace log)
    • Locate missing indexes with the query optimizer (or with Tuning Advisor)
    • Find unused indexes
  • Part 2 Pinpointing other bottlenecks
    • Find Locks and Latches (via counters in perfmon)
    • Execution plan reusability (using counters or dynamic management views (DMV))
    • Fragmentation of data and indexes
    • Memory check (using counters to verify if there’s a lack of memory)
    • Disk usage (locate possible disk bottlenecks using counters)
    • CPU (use counters to see if the CPU is stressed)
  • Part 3 Fixing missing indexes
    • Indexes explained (clustered and nonclustered indexes)
    • Index usage (when / when not)
    • Maintaining indexes
  • Part 4 Fixing expensive queries
    • Cache aggregation queries
    • Keep records short
    • Considering Denormalization
    • Be careful with triggers
    • Use table variables for small temporary result sets
    • Use Full Text Search instead of LIKE
    • Replacing cursors with set based code
    • Minimise traffic from SQL Server to Web Server
    • Object Naming
    • Use SET NOCOUNT ON
    • Use FILESTREAM for values over 1MB
    • Avoid functions on columns in WHERE clauses
    • Use UNION ALL instead of UNION
    • Use EXISTS instead of COUNT to find existence of records
    • Combine SELECT and UPDATE
  • Part 5 Fixing locking issues
  • Part 6 Fixing execution plan reuse
  • Part 7 Fixing fragmentation
  • Part 8 Fixing memory, disk and CPU issues

I'll update this post when new parts are published.

woensdag 5 september 2012

Formatting code to HTML

I like to share a website I frequently use to format any code (C#, XML or T-SQL) to HTML for using on my blog. Other possibilities are to format VB, HTML or msh, but I haven't used these myself yet.
You can also add things like line numbers, or use alternating backgrounds. And to modify the colors of the output, you'll just need to change the css style sheet.

Also because everytime I need it, I'll have to search the website again on google. So this way I'll save some time for me too :).

Here's the link: http://www.manoli.net/csharpformat/

Delete BizTalk backup files

The SQL Server agent job "Backup BizTalk Server" will not delete the generated backup files automatically. The job does clear the backup history table in the database, but it will never delete the backup files from the disk. Which will of course result in the disk to fill up eventually and the backup job will fail from then on.

Here's a simple stored procedure to call from the "Backup BizTalk Server" job in the "Clear Backup History" step. Just follow these steps:
  1. Open SQL Server management Studio
  2. Open a new query window and connect to the BizTalkMgmtDb database
  3. Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles
    USE [BizTalkMgmtDb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null, @UseLocalTime bit = 0
    
    AS
     BEGIN
    
     set nocount on
    
      IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
      RETURN
    
         /*
            Only delete full sets
            If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set
    
            Delete history only if history of full Backup exists at a later point of time
            why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
            If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
        
        */
            
      declare @PurgeDateTime datetime
    
      if (@UseLocalTime = 0)
        set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
      else
        set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())
    
      DECLARE DeleteBackupFiles CURSOR
    
      FOR SELECT 'del "' + [BackupFileLocation] +  case right(BackupFileLocation,1) when '\' then '' else '\' end + [BackupFileName] + '"' FROM [adm_BackupHistory] [h1]
        WHERE     [BackupDateTime] < @PurgeDateTime
        AND    [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
        AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')
    
      DECLARE @cmd varchar(400)
    
      OPEN DeleteBackupFiles
    
       FETCH NEXT FROM DeleteBackupFiles INTO @cmd
    
      WHILE (@@fetch_status <> -1)
      BEGIN
                IF (@@fetch_status <> -2)
                BEGIN
    
                            EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
    
                            delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
    
                            print @cmd
                END
    
                FETCH NEXT FROM DeleteBackupFiles INTO @cmd
      END
    
      CLOSE DeleteBackupFiles
    
      DEALLOCATE DeleteBackupFiles
    
      END
    
  4. 5.Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles, instead of calling the stored procedure sp_DeleteBackupHistory
  5. Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script:
    EXEC master.dbo.sp_configure 'show advanced options', 1
    
    RECONFIGURE
    
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    
    RECONFIGURE
    
    


This stored procedure is made for BizTalk Server 2010.
Because with the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures. They added a parameter to the stored procedure to use local time and changed the query to prevent the deletion of the history from the last full backup set forward.

For older versions of BizTalk you should use this script instead:
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
  IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
  RETURN
/*
  Only delete full sets
  If a set spans a day such that some items fall into the deleted group and the other doesn't, do not delete the set
*/ 

DECLARE DeleteBackupFiles CURSOR
FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory]
WHERE  datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
DECLARE @cmd varchar(400)
OPEN DeleteBackupFiles
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN 

            IF (@@fetch_status <> -2)
            BEGIN
                        EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
                        delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
                        print @cmd
            END 

            FETCH NEXT FROM DeleteBackupFiles INTO @cmd
END 

CLOSE DeleteBackupFiles
DEALLOCATE DeleteBackupFiles
  END
GO



Source: http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx