Trace flag 2371

I know many DBAs that are not yet aware of this trace flag and will certainly be very happy to hear about it as they can get an immediate benefit. I just run into it recently at this post and plan to implement soon.

Trace flag 2371 finally comes in to address the well know problem of the current 20% threshold used in the automatic update of statistics.

The source to the problem is that statistics are updated when 20% (+ 500 rows) of the data has changed. This means that a table with 10,000 rows will cross the threshold at 2,500 rows and a table with 100,000 rows will cross the threshold at 20,500 rows and so on. Once the threshold is crossed the statistics are marked as invalidated and remain in this state until the next time the Optimizer needs the statistics when generating an execution plan.

While this may be a good choice in most cases it becomes problematic when dealing with large tables. As the table size grows the 20% figure increases accordingly and so does the time interval between each time the threshold is crossed. This leads to stale statistics resulting in suboptimal execution plans which in large sets of data can be a disaster.

Therefore in such environments DBAs typically create their own update statistics job to get the stats manually updated thus eliminating the need to rely on crossing the 20% threshold of the auto update of stats. With the new 2371 trace flag these jobs that generate a lot of IO will no longer be needed.

For additional information on statistics check out the Microsoft article Statistics Used by the Query Optimizer in Microsoft SQL Server and this post at sqlskills.

-Yaniv

Automating SQL Server 2008 data compression decision taking

Recently, as part of an effort to reduce IO at a large DHW sizing several TB we were thinking  to implement data compression. The data compression feature can help a lot reducing IO and I had experienced that in the past. Before taking any action it makes sense to know a head if there is any benefit and in case there is then what is benefit so we can take the decision if we move forward with the compression feature or not.

This is when the Microsoft stored procedure sp_estimate_data_compression_savings comes in. The procedure accepts several parameters and returns an estimate size for the object requested if it was data compressed.

The procedure samples the source object and loads the sampled data into an equivalent table and index created in tempdb  The table and index created in tempdb are then compressed to the requested setting (page compression or row compression) and the estimated compression savings gets computed. Now based on these figures you should take your decision  weather to compress or not (unless of course you would rather perform the real test at a test environment, given you have the room and the time… ).

To automate this task I used the script I share here that executes the results of the sp_estimate_data_compression_savings stored procedure into a table which can then be queried and help you take the decision of what objects are worth to compress. In addition the script already creates the sql command to be run latter on if you do decide to go on and compress.

Note that in my script I create a persistent table to store the results in a database named DBA and not a temp table so you will have to modify the script just a tiny bit to your desired database or alternately to a temp table.

Also note that my search condition filters out tables with a size smaller than 500 MB and file groups that are read only simply because I cannot perform any work on these objects without some additional overhead that I cannot afford at this time.

SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

USE --< Enter user database name ;

DECLARE @compression_type sysname;

SELECT @compression_type = N'PAGE'; --<---- Valid values are: PAGE or ROW

IF OBJECT_ID('tempdb..#estimate_data_compression_savings') IS NOT NULL DROP TABLE #estimate_data_compression_savings; 

CREATE TABLE #estimate_data_compression_savings 

(

     Id int identity (1,1) PRIMARY KEY CLUSTERED NOT NULL

     ,[File_Group]                                                                                        sysname NULL

    ,[is_read_only]                                                                                    bit  NULL

    ,[index_name]                                                                                        sysname  NULL

    ,[type]                                                                                                    tinyint NULL

    ,[data_compression_desc]                                                                sysname  NULL

    ,[rows]                                                                                                    bigint NULL    

    ,[reserved_page_count]                                                                    bigint NULL

    ,[reserved_MB]                                                                                    bigint   NULL

    ,object_name                                                                                          sysname NOT NULL

    ,schema_name                                                                                          sysname NOT NULL

    ,index_id                                                                                                 int NOT NULL

    ,partition_number                                                                                 int NOT NULL

    ,partition_count                                                                                int  NULL

    ,[size_with_current_compression_setting (KB)]                         bigint NOT NULL

    ,[size_with_requested_compression_setting (KB)]                     bigint NOT NULL

    ,[sample_size_with_current_compression_setting (KB)]         bigint NOT NULL

    ,[sample_size_with_requested_compression_setting (KB)]     bigint NOT NULL

    ,[command]                                                                                            varchar(8000) NULL

    ,[error]                                                                                                varchar(8000) NULL

);

IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data; 

CREATE TABLE #Data  

(  

   schemaname sysname  

  ,tablename  sysname  

  ,objectid  int  

  ,indexname  sysname  

  ,indexid   int  

  ,[blob]   bit  

);  

-- Get meta data for the rebuild process.  

INSERT #Data (schemaname, tablename, objectid, indexname, indexid, blob)  

SELECT   

  Derived.schemaname  

 ,Derived.tablename  

 ,Derived.tableid  

 ,Derived.indexname  

 ,Derived.indexid  

 ,Derived.blob  

FROM  

  (  

   -- Get clustered indexes information   

   SELECT  

     SCHEMA_NAME(Derived2.[schema_id])    AS schemaname  

    ,Derived2.tablename                                    AS tablename  

    ,Derived2.[object_id]                                AS tableid  

    ,i.name                                                            AS indexname  

    ,i.index_id                                                    AS indexid  

    ,Derived2.blob                                            AS blob  

   FROM  

     (  

      SELECT  

         t.[object_id] AS [object_id]  

        ,t.[name]    AS tablename  

        ,t.[schema_id] AS [schema_id]  

        ,CASE WHEN EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND (system_type_id IN (35, 34, 241, 99) /* column type that doesn't support on-line index rebuild. (text,image,XML,ntext -> 35,34,241,99) */ OR max_length < 0) ) THEN 1 ELSE 0 END AS blob  

       FROM sys.tables t

      )Derived2  

   INNER JOIN sys.indexes i ON Derived2.[object_id] = i.[object_id]  

   WHERE i.type = 1 /* clustered */  

   UNION   

    -- Get nonclustered index information  

    SELECT  

      SCHEMA_NAME(t.schema_id)    AS schemaname  

     ,t.Name                              AS [tablename]   

     ,t.[object_id]                       AS tableid  

     ,i.name                              AS [indexname]  

     ,i.index_id                          AS indexid  

     ,CASE WHEN EXISTS ( SELECT 1 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 (c.system_type_id IN (35, 34, 241, 99) /* column type that doesn't support on-line index rebuild. (text,image,XML,ntext -> 35,34,241,99) */ OR c.max_length < 0) ) THEN 1 ELSE 0 END AS blob  

    FROM sys.tables t   

    INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]  

    WHERE i.type > 1 /* nonclustered */  

  )Derived  

ORDER BY Derived.tablename;  

DECLARE CUR CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 

SELECT DISTINCT 

    --TOP 2

     fg.name AS [File_Group]

    ,fg.is_read_only

    ,s.[name] AS [schema_name]

    --,o.[object_id]

    ,o.[name] AS [object_name]

    ,i.index_id

    ,i.[name] [index_name]

    ,i.[type]

    ,p.partition_number

    ,(SELECT COUNT(*) FROM sys.partitions part WHERE part.[object_id] = p.[object_id] AND part.[index_id] = p.[index_id]) AS partitions_count

    ,p.data_compression_desc

    ,p.[rows]

    --,au.total_pages 

    --,ps.used_page_count 

    --,ps.row_count 

    ,ps.reserved_page_count 

    ,(ps.reserved_page_count * 8) / 1024 AS reserved_MB 

    ,#Data.blob

    ,#Data.indexid

FROM  sys.indexes i

INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

INNER JOIN sys.partitions p ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id

INNER JOIN sys.allocation_units au ON  p.hobt_id = au.container_id

INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id   

INNER JOIN sys.dm_db_partition_stats ps ON ps.partition_id = p.partition_id      

INNER JOIN #Data ON #Data.tablename COLLATE DATABASE_DEFAULT = o.name AND #Data.schemaname COLLATE DATABASE_DEFAULT = s.name AND #Data.indexid = i.index_id

WHERE 1=1

AND fg.is_read_only = 0

AND p.[rows] > 0

AND (ps.reserved_page_count * 8) / 1024 > 500 /* MB */

/* Get the active partition */

AND

( 

    EXISTS(SELECT 1 FROM sys.partitions p_empty WHERE p.[object_id] = p_empty.[object_id] AND p.index_id = p_empty.index_id AND p.partition_number = p_empty.partition_number - 1 AND p_empty.[rows] = 0 ) 

    OR

    NOT EXISTS(SELECT 1 FROM sys.partitions p_empty2 WHERE p.[object_id] = p_empty2.[object_id] AND p.index_id = p_empty2.index_id AND p_empty2.partition_number = 2 )

)

ORDER BY o.[name] DESC;

OPEN CUR;

DECLARE @File_Group sysname, @is_read_only bit, @schema_name sysname, @object_name sysname, @index_id int, @index_name sysname, @type sysname

            , @partition_number int, @data_compression_desc sysname, @rows int, @reserved_page_count int, @reserved_MB int, @partition_count int;

DECLARE @Ident int, @command varchar(8000), @error varchar(8000), @blob BIT, @indexid    int;

    FETCH NEXT FROM CUR INTO @File_Group, @is_read_only, @schema_name, @object_name,@index_id, @index_name, @type, @partition_number, @partition_count, @data_compression_desc, @rows, @reserved_page_count, @reserved_MB, @blob, @indexid ;

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN;

    BEGIN TRY

    /* exec the procedure sp_estimate_data_compression_savings into a table */

    INSERT dbo.#estimate_data_compression_savings

             ([object_name]

             ,[schema_name]

             ,[index_id]

             ,[partition_number]

             ,[size_with_current_compression_setting (KB)]

             ,[size_with_requested_compression_setting (KB)]

             ,[sample_size_with_current_compression_setting (KB)]

             ,[sample_size_with_requested_compression_setting (KB)])

    EXEC sp_estimate_data_compression_savings 

                @schema_name             = @schema_name

             ,@object_name             = @object_name

             ,@index_id                    = @index_id

             ,@partition_number    = @partition_number 

             ,@data_compression    = @compression_type;

        SELECT @Ident = SCOPE_IDENTITY();

/*

SELECT @command = '

EXEC sp_estimate_data_compression_savings 

            @schema_name             = '''    + @schema_name + '''

             ,@object_name             = '''    + @object_name + '''

             ,@index_id                    = '        + CAST(@index_id AS varchar(20)) + '

             ,@partition_number    = '        + CAST(@partition_number AS varchar(20)) + '

             ,@data_compression    = ''' + @compression_type + ''';';

*/

/* If the table is not partitioned */

IF (@partition_count = 1)

BEGIN;

    /* If the index is clustered and the table has a blob column it can only be rebuild offline */

    IF (@indexid = 1 AND @blob = 1)

    BEGIN;

        SELECT @command = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' +  @object_name + '] REBUILD WITH (DATA_COMPRESSION = ' + @compression_type +  ', ONLINE = OFF );';

    END

    /* All other cases; clustered index with no blob and noneclsutered indexes can be rebuild online */

    ELSE IF (@indexid = 1 AND @blob = 0) OR (@indexid > 1)

    BEGIN

            SELECT @command = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' +  @object_name + '] REBUILD WITH (DATA_COMPRESSION = ' + @compression_type +  ', ONLINE = ON );';

    END;

END;

/* If the table is partitioned it can only be rebuild offline */

ELSE IF (@partition_count > 1)

    BEGIN;

        SELECT @command = 

        'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' +  @object_name + '] REBUILD PARTITION = ' + CAST(@partition_number AS varchar(20)) + ' WITH (DATA_COMPRESSION = ' + @compression_type + ', ONLINE = OFF );';

    END;

PRINT @command;

/* update the table with additional info */

        UPDATE dbo.#estimate_data_compression_savings SET 

             [File_Group]                            = @File_Group

      ,[is_read_only]                        = @is_read_only

      ,[index_name]                            = @index_name

      ,[type]                                        = @type

      ,[data_compression_desc]    = @data_compression_desc

      ,[rows]                                        = @rows

      ,[reserved_page_count]        = @reserved_page_count

      ,[reserved_MB]                        = @reserved_MB 

      ,[command]                                = @command

      ,[partition_count]                = @partition_count

        WHERE Id = @Ident;

END TRY

BEGIN CATCH;

    SELECT @error = ERROR_MESSAGE();

        UPDATE dbo.#estimate_data_compression_savings SET 

             [File_Group]                            = @File_Group

      ,[is_read_only]                        = @is_read_only

      ,[index_name]                            = @index_name

      ,[type]                                        = @type

      ,[data_compression_desc]    = @data_compression_desc

      ,[rows]                                        = @rows

      ,[reserved_page_count]        = @reserved_page_count

      ,[reserved_MB]                        = @reserved_MB 

      ,[command]                                = @command

      ,[partition_count]                = @partition_count

      ,[error]                                    = @error

        WHERE Id = @Ident;

END CATCH;

        SELECT @Ident = NULL, @error = NULL;

    FETCH NEXT FROM CUR INTO @File_Group, @is_read_only, @schema_name, @object_name, @index_id, @index_name, @type, @partition_number, @partition_count, @data_compression_desc, @rows, @reserved_page_count, @reserved_MB, @blob, @indexid ;    

    END;

CLOSE CUR; DEALLOCATE CUR;

GO

/* Get the final results */

SELECT [Id]

      ,[File_Group]

      ,[is_read_only]

      ,[index_name]

      ,[type]

      ,[data_compression_desc]

      ,[rows]

      ,[reserved_page_count]

      ,[reserved_MB]

      ,[object_name]

      ,[schema_name]

      ,[index_id]

      ,[partition_number]

      ,[partition_count]

      ,[size_with_current_compression_setting (KB)] / 1024                    AS [size_with_current_compression_setting (MB)]

      ,[size_with_requested_compression_setting (KB)] / 1024                AS [size_with_requested_compression_setting (MB)]

      ,([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS diff_MB

      ,[sample_size_with_current_compression_setting (KB)] / 1024        AS [sample_size_with_current_compression_setting (MB)]

      ,[sample_size_with_requested_compression_setting (KB)] / 1024 AS [sample_size_with_requested_compression_setting (MB)]

      ,[command]

FROM dbo.#estimate_data_compression_savings WHERE 1=1

AND (

                [size_with_requested_compression_setting (KB)] < [size_with_current_compression_setting (KB)]

         OR [sample_size_with_requested_compression_setting (KB)] < [sample_size_with_current_compression_setting (KB)]

        )

--AND File_Group = ''

ORDER BY ( [size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) DESC;

Improvements to sp_locks

sp_locks is a stored procedure I created to help me in my daily DBA tasks. The procedure shows detailed blocking information as well as the currently executed processes and more... For the complete story check out this post.

I recently added a small improvement to sp_locks that includes:

--displaying the job_name instead of the job_id that is retrieved form the program_name column of sys.sysprocesses

--show the diff in seconds from the start_time of the executing process until now

--add a third input parameter to the procedure that allows to filter on a specific spid

-Yaniv

sp_helpindex2 - an updated version available

There is a small modification I have recently done;

I modified the ORDER BY of the final result set to be ordered by the Columns column instead of the index name column making redundant indexes to be more easily noticed and generally I find it much more logical and practical to be sorted this way.

I added 4 important columns to the final result set showing index statistics information regarding the usage of the index: user_updates, user_seeks, user_scans and user_lookups.

When tuning applications workload I find this additional information very useful. For every index that you see existing on the table you are looking at while working to improve performance you also know how frequent the index is being used or if it is in use at all.

One thing to keep in mind when looking at these index statistics is that they are maintained since the last service restart. This is an important point to remember before taking decisions. For example, some indexes that appear as never in use can possibly be in use by a weekly or a monthly report never to be reached yet and so on.

To download sp_helpindex2:  http://blogs.microsoft.co.il/blogs/yaniv_etrogi/sp_helpindex2.txt

Transactional Replication - Transactions per hour

Knowing what goes inside the distribution database can be helpful for various reasons. Here is a script that groups transactions per hour and per database.

-- Transactions per hour
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @StartTime datetime, @EndTime datetime;
SELECT  @StartTime = DATEADD(minute, -60, CURRENT_TIMESTAMP), @EndTime = CURRENT_TIMESTAMP;

SELECT
     p.publisher_db
    ,p.publication
    ,DATEPART(Hour, t.entry_time) AS [time]
    ,COUNT(*) AS cnt
FROM MSrepl_transactions t
INNER JOIN MSrepl_commands c ON t.xact_seqno = c.xact_seqno AND t.publisher_database_id = c.publisher_database_id
INNER JOIN MSpublisher_databases d ON d.id = t.publisher_database_id
INNER JOIN MSpublications p ON p.publisher_db = d.publisher_db
WHERE 1=1
--AND t.entry_time > DATEADD (Hour, -1, CURRENT_TIMESTAMP)
GROUP BY DATEPART(Hour, entry_time), p.publisher_db, p.publication
ORDER BY DATEPART(Hour, entry_time),p.publisher_db, p.publication;

Blogging at sql-server-performance

I have recently started blogging at sql-server-performance.com.

Here is a link to my articles: http://www.sql-server-performance.com/authors/ytrogi.aspx

-Yaniv

----------------------------

Quickly build RESTORE LOG commands

Recently I needed to restore many transaction logs on SQL Server 2008. It was only then that I noticed that Microsoft has changed the naming convention of the backup files created by the Maintenance Plan in SQL Server 2008 from earlier versions and a .NET utility that I used in the past to quickly generate the RESTORE LOG commands does not work with the current naming convention.  Ommm. What do we do next… I had to prepare the command for around 200 files, time was not on my side, and that is one hell of a task if done manually.
Fortunately a colleague DBA told me about a command-line utility introduced by Microsoft with Vista and Windows Server 2008 I was not aware of and that is clip.exe that does just what I needed.
If you issue the following command in a cmd window: dir /b *.trn | clip you actually get all the files in the current directory with the .trn extension listed and sorted by name in the clip board. Now all that is left to do is paste in excel and use excel's concatenate function to build the desired string which makes your commands. Absolutely beautiful and powerful and sure came write on time!

Here is a link to a few examples and some other usages for the utility:
http://commandwindows.com/vista-tips.htm

-Yaniv

A fix to sp_helpindex2 is now available

There was a bug in the procedure where in some cases of a composite index the keys which are displayed in the 'Columns' column were not correctly ordered.

To download sp_helpindex2 go to:

http://blogs.microsoft.co.il/blogs/yaniv_etrogi/sp_helpindex2.txt

Monitor Merge Replication Triggers

When it comes to monitoring production systems I usually try to think in the direction of what else could possibly go wrong here ? (and no, I am not pessimistic but realistic) This leads me to new ideas of what else can be monitored and how to improve on the current situation and prevent future failures.

Merge replication is trigger based;

When a table is added to Merge replication the Snapshot agent creates 3 DML triggers (for INSERT, UPDATE and DELETE) when generating the snapshot.

The triggers captures the DML statements excecuted against the table and inserts them into two tables: MSmerge_contents which server as a queue table for the INSERT and UPDATE transactions and MSmerge_tombstone serving for the DELETE transactions.

The functionality of the triggers is therefore crucial and I found it is worth monitoring their existence.

To accomplish this task I created the bellow stored procedure that works on all Merge Publications existing on the publisher.

The code selects from sysobjects because we are looking for trigger objects and joins sysmergearticles so we work only on tables belonging to a Merge publication. We GROUP BY the tables and use HAVING to find only those tables where the count of triggers is smaller than 3.

USE PerfDB;
SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].MonitorReplicationTriggers') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].MonitorReplicationTriggers
GO

CREATE PROCEDURE MonitorReplicationTriggers
AS
/*
    Yaniv Etrogi
    http://www.sqlserverutilities.com
    Look for Merge articles miising a trigger.
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--EXEC PerfDB.dbo.MonitorReplicationTriggers;

CREATE TABLE #Data ([Database] sysname, [Schema] sysname, [Table] sysname, [NumberOfTriggers] int );

DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    --SELECT [publisher_db] FROM [distribution].[dbo].[MSpublisher_databases] ORDER BY 1;
        SELECT name FROM sys.databases WHERE is_merge_published = 1 ORDER BY name;

SET NOCOUNT ON;
OPEN cur;
DECLARE @Database sysname, @Command varchar(8000); 

    FETCH NEXT FROM cur INTO @Database ;

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN;

        SELECT @Command = 'USE [' + @Database + '];
        INSERT #Data ([Database], [Schema], [Table], [NumberOfTriggers])
        SELECT ''' +
             @Database + ''' AS [Database]
            ,SCHEMA_NAME(s.schema_id)            AS [Schema]
            ,OBJECT_NAME(o.parent_object_id)    AS [Table]
            ,COUNT(*)                            AS [NumberOfTriggers]
        FROM sys.objects o
        INNER JOIN dbo.sysmergearticles a ON a.name = OBJECT_NAME(o.parent_object_id)
        INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
        WHERE o.type = ''TR''
        AND o.name LIKE ''MSmerge_%''
        GROUP BY o.parent_object_id, s.schema_id
        HAVING COUNT(*) < 3 ;';       

        --PRINT @Command;
        EXEC (@Command);

        FETCH NEXT FROM cur INTO @Database ;
    END;
CLOSE cur; DEALLOCATE cur;

SELECT * FROM #Data ORDER BY [Database], [Schema], [Table];
GO

Monitor Identity Ranges in Merge Replication

Merge Replication has a built in Identity range management mechanism that automatically assigns identity values to identity columns of tables participating in the replication.

The identity ranges are stored in the published database at table MSmerge_identity_range and the history of the range allocations is stored in the distribution database at table MSmerge_identity_range_allocations.  For a more detailed explanation check out this post.

This mechanism works well but when things go wrong you may be in a deep trouble because the severity of such an incident is typically high resulting in INSERT statements failing and a downtime of a production system.

If you see any of the bellow errors you are probably facing an identity range problem and the simplest way out is to drop and recreate the publication:

Not enough range available to allocate a new range for a subscriber.

Failed to allocate new identity range.

The Publisher failed to allocate a new set of identity ranges for the subscription.
This can occur when a Publisher or a republishing Subscriber has run out of identity ranges
to allocate to its own Subscribers or when an identity column data type does not support an additional
identity range allocation.

To prevent such a situation you can run the following procedure by a monitoring tool once a day and get a notification if you cross a threshold you define a head of time.

USE [PerfDB];

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MonitorIdentityRanges]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].MonitorIdentityRanges;

CREATE PROCEDURE MonitorIdentityRanges

SET NOCOUNT ON;

IF EXISTS

SCHEMA_NAME(t.schema_id) AS [schema]

,p.name AS publication

,r.range_end

,a.pub_range

,r.max_used + a.pub_range AS max_used__pub_range

,r.next_range_begin

,r.next_range_end

FROM Ticketing.dbo.MSmerge_identity_range r

INNER JOIN Ticketing.dbo.sysmergearticles a ON r.artid = a.artid

INNER JOIN Ticketing.sys.tables t ON t.name = a.name

LEFT JOIN Ticketing.dbo.sysmergepublications p on p.pubid = a.pubid

WHERE is_pub_range = 1

AND r.range_end - (r.max_used + a.pub_range) < 1000000000 /* when this condition is met we still have a long way to go */

SELECT 1; ELSE SELECT 0;

GO

SQLTrace and Profiler

Many people get confused thinking that SQL Server Profiler is the actual tracing feature of SQL Server while in fact it is only an application, a great .NET application but an application.
The actual tracing feature of SQL Server is called SQL Trace and it is a powerful feature exposed by the database engine.

SQL Trace provides a real-time view into what’s going on inside the database engine and we can take a real advantage of that. Mastering this feature opens up a whole lot of new possibilities when it comes to troubleshooting, performance tuning and more.

SQL Trace uses trace I/O providers to send the captured data to consumers. The 2 available providers are the file provider and the rowset provider.

SQL Server Profiler makes use of the rowset provider and while we run a server side trace (from a script) we make use of the file provider.

I will not get into all the differences between the two providers but I do want to point out one key difference between the two and that is the important fact that the file provider guarantees to capture every event (SQL Server 2005 and latter) defined in the trace you are running while the rowset provider under a certain load starts dropping events. This can become very important when capturing data for later analysis or when trying to reproduce a problem.

Want to read more about SQL Server traces and see advanced usage from real world scenarios? Check out my latest articles at the bellow links....

Create a Performance Baseline Repository
This process creates a Baseline repository that holds summarized data of all SQL Executions executed on an instance of SQL Server.

TraceErrors Process
This process generates a report of all exceptions that took place on an instance of SQL Server along with other information such as the login, host, application and the statements that raised the exceptions.

To download a presentation on SQL Trace
http://sqlserverutilities.com/download/Tracing_SQL_Server.zip

Yaniv

sp_helpindex2

I created sp_helpindex2 which outputs 2 result sets.

The procedure accepts 3 input parameters; the first one @Table is simply the table name we work on.

Download the code: http://blogs.microsoft.co.il/blogs/yaniv_etrogi/sp_helpindex2.txt

Peer to Peer Transactional Replication using Pull Subscribers

http://blogs.technet.com/claudia_silva/archive/2010/01/04/replication-transactional-replication-over-wan.aspx

 Here are the replication scripts I used.

How to terminate all connections on a busy SQL Server

Let’s say we are talking about a production SQL Server servicing several products each accessing it’s own database and we run out of disk space on one of the drives holding the data files. This of course causes INSERT and UPDATE operations to fall and now we realize that we are facing an open production error at real-time that we need to address on the spot – the sooner the better.

What can be done?

My first choice would be to free up disk space on the drive in discussion and if that is not possible then next I would check out to see if the LUN on the SAN can be expanded.

In a lower end system perhaps the drive is a logical drive sharing space with another logical drive on the same physical disk so possibly that other logical drive can be deleted allowing you to expand the logical drive needing the additional disk space.

However, if some additional disk space cannot be added then one option is to move one data file from any of the databases to another disk location. This is done by detaching the database, copying the data file to a new location and then attaching back the database.

The command to detach a database requires exclusive access to the database which under a busy activity in many cases cannot be gained.

We can try to force and terminate the connections using the termination option ROLLBACK IMMEDIATE but that as well may work under some environments but will not work at other environments (for example, a database being accessed by a client-server application is most likely easier to gain the exclusive access required for the detach operation than a busy web environment or an environment where several processes simultaneously access the database for a queue table).

One trick I found is to issue the sql batch (see bellow) that terminates all connections and detaches the database after a short "sleep" first and just after you execute the batch you put the sql server service into a paused state (this can be done from SSMS at the "Service Control" menu when you write click on the server.

When the service is paused it allows the active connections to complete (thus allowing your batch to complete execution) but does not allow access to new connections. By the time the termination option is executed (after the sleep) there are only few connections to the server so we can successfully detach the database.

Here is the msg that is logged at the ERROLOG:

Once the command succeeded you resume the service so it goes back to a ‘started’ sate.

Here is the msg that is logged at the ERROLOG:

The fact that the services is paused and does not allow access for new connections helps sql server to acquire the exclusive database lock that is needed for the detach operation or any other operation that requires exclusive database access such as setting a database OFFLINE.

This trick allows for minimum interruption while allowing you to get out of the situation.

USE [master];

WAITFOR DELAY '00:00:30'; /* 30 seconds sleep – now pause the sql srv service */

ALTER DATABASE aaa SET SINGLE_USER WITH ROLLBACK IMMEDIATE; /* disconnect all connections to the specific database */

EXEC sp_detach_db @dbname = 'aaa';

Restoring a backup set of a published database under a new name removes replication settings from the published database