Archive

Archive for the ‘Uncategorized’ Category

Install WordPress on Windows

August 29, 2015 Leave a comment

Install WordPress from below link

 

http://www.microsoft.com/web/wordpress/

 

You can also download it from Microsoft Web Platform Installer

 

image

Categories: Uncategorized

sp_whoisactive cutomization

November 9, 2014 Leave a comment

We all mostly know about WhoIsActive custom stored procedure which give a full view of running sessions and much more based on parameter values.

Attached is the WhoIsActive_1 code in which I have done a bit of customization to have little more information

 

How to execute the Stored Procedure?

Create the procedure in master database.Execute the procedure from Query Analyser as below.

Code Sample :
use master
go
dbo.sp_WhoIsActive1 @get_plans=1, @get_transaction_info =1,@find_block_leaders=1,@get_locks=1

Attachment :WhoIsActive_1_Code

Categories: SQL Scripts, Uncategorized

RECOMPILE in SQL Server

November 8, 2012 Leave a comment

Before we get into the Subject we need to understand the definition of RECOMPILE.

From BOL

RECOMPILE Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuse them. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.

RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. For more information, see Recompile a Stored Procedure. RECOMPILE is also useful when you create plan guides.

“SQL Server internally decided to use a new plan and generates a new plan based on multiple factors ”

Multiple ways to FORCE RECOMPILE

  1. Passing Parameter Values
    CREATE PROCEDURE <Procedure Name> <Parameter>
    WITH RECOMPILEAS

    BEGIN

    ……

    END

  2. Fixed Parameter Values

    CREATE PROCEDURE <Procedure Name> <Parameter @AC_NO INTEGER= NULL>

    WITH RECOMPILE

    AS

    BEGIN

    ……

    END

  3. SQL Statement Query HintOPTION(RECOMPILE)

As we have learnt what recompile does let’s do a demo to prove what is said. In this demo I am using [AdventureWorks] database.

  • SQL statement without RECOMPILE (Passed multiple parameter values to check how many plans are generated and cached)

    DECLARE @P NUMERIC(10,2)

    SELECT @P=SUM(LINETOTALFROM [SALES].[SALESORDERDETAIL] A
    JOIN [SALES].[SALESORDERHEADER] B
    ON A.SALESORDERID=B.SALESORDERID WHERE A.ORDERQTY=1 –-2 –3
    GROUP BYA.SALESORDERID


Executing the T-SQL again with the same parameters to check the reuse of plan

Execute the SP or T-SQL using Recompile and see if you get the cached plan. The plan will not be cached for next time use.

Categories: Uncategorized

Change Tracking Internals (SQL2008R2) Part 4

November 21, 2011 Leave a comment

Change Tracking Overhead

When change tracking is enabled for a table, some administration operations are affected. The following table lists the operations and the effects you should consider.

Operation

When change tracking is enabled

DROP TABLE

All change tracking information for the dropped table is removed.

ALTER TABLE DROP CONSTRAINT

An attempt to drop the PRIMARY KEY constraint will fail. Change tracking must be disabled before a PRIMARY KEY constraint can be dropped.

ALTER TABLE DROP COLUMN

If a column that is being dropped is part of the primary key, dropping the column is not allowed, regardless of change tracking.

If the column that is being dropped is not part of the primary key, dropping the column succeeds. However, the effect on any application that is synchronizing this data should be understood first. If column change tracking is enabled for the table, the dropped column might still be returned as part of the change tracking information. It is the responsibility of the application to handle the dropped column.

ALTER TABLE ADD COLUMN

If a new column is added to the change tracked table, the addition of the column is not tracked. Only the updates and changes that are made to the new column are tracked.

ALTER TABLE ALTER COLUMN

Data type changes of non-primary key columns are not tracked.

ALTER TABLE SWITCH

Switching a partition fails if one or both of the tables has change tracking enabled.

DROP INDEX, or ALTER INDEX DISABLE

The index that enforces the primary key cannot be dropped or disabled.

TRUNCATE TABLE

Truncating a table can be performed on a table that has change tracking enabled. However, the rows that are deleted by the operation are not tracked, and the minimum valid version is updated. When an application checks its version, the check indicates that the version is too old and a re-initialization is required. This is the same as change tracking being disabled, and then re-enabled for the table.

Effects on DML

Change tracking has been optimized to minimize the performance overhead on DML operations. The incremental performance overhead that is associated with using change tracking on a table is similar to the overhead incurred when an index is created for a table and needs to be maintained.

For each row that is changed by a DML operation, a row is added to the internal change tracking table. The effect of this relative to the DML operation depends on various factors, such as the following:

  • The number of primary key columns
  • The amount of data that is being changed in the user table row
  • The number of operations that are being performed in a transaction

Snapshot isolation, if used, also has an effect on performance for all DML operations, whether change tracking is enabled or not.

 

 


 

Categories: Uncategorized

Change Tracking Internals (SQL2008R2) Part 3

November 14, 2011 Leave a comment

Storage with Change Tracking

Here we will see how Data Storage happens with Change Tracking and what effect does it carry. For Each User Table enabled for Change Tacking there is one internal change table and Internal Transactional Table.

There is one internal transaction table for the database.These internal tables affect storage requirements:



 

 

Internal change table for each table enabled for Change Tracking and Internal Transactional Table.

 

 


 

 

 

I have enabled a Table for CT; let’s see what the space is used for by internals tables. Initially Internal change Table will be of Zero Size.

 


 

Now have some transactions done on the table (Delete or Insert) and check the space used by internal tables.

 

 


 

For each change to each row in the user table, a row is added to the internal change table. This row has a small fixed overhead plus a variable overhead equal to the size of the

Primary key columns. The row can contain optional context information set by an application. And, if column tracking is enabled, each changed column requires 4 bytes in the tracking table.

For each committed transaction, a row is added to an internal transaction table.

 

sys.syscommittab :

Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking. The sys.dm_tran_commit_table management view, which is provided for supportability purposes and exposes the transaction-related information that change tracking stores in the sys.syscommittab system table. The sys.syscommittab table provides an efficient persistent mapping from a database-specific transaction ID to the transaction’s commit log sequence number (LSN) and commit timestamp. The data that is stored in the sys.syscommittab table and exposed in this management view is subject to cleanup according to the retention period specified when change tracking was configured.

Categories: Uncategorized

Catalog Views for Change Tracking (SQL2008R2) Part 2

November 8, 2011 Leave a comment

To determine which tables and databases have change tracking enabled, you can use the following catalog views:

sys.change_tracking_databases: The view list the database’s enabled for Change Tracking.

sys.change_tracking_tables        : The view list the table’s information enabled for Change tracking.

sys.dm_tran_commit_table       Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking. The sys.dm_tran_commit_table management view, which is provided for supportability purposes and exposes the transaction-related information that change tracking stores in the sys.syscommittab system table. The sys.syscommittab table provides an efficient persistent mapping from a database-specific transaction ID to the transaction’s commit log sequence number (LSN) and commit timestamp. The data that is stored in the sys.syscommittab table and exposed in this management view is subject to cleanup according to the retention period specified when change tracking was configured.


Also, the sys.internal_tables catalog view lists the internal tables that are created when change tracking is enabled for a user table

Categories: Uncategorized

Enable Change Tracking (SQL2008R2) Part 1

November 7, 2011 Leave a comment

 

Change tracking in SQL Server 2008 enables applications to obtain only changes that have been made to the user tables, along with the information about those changes. With change tracking integrated into SQL Server, complicated custom change tracking solutions no longer have to be developed.

Change tracking is an important building block for applications that synchronize and replicate data in scenarios in which end-to-end replication solutions do not work and a custom solution is required.

For example, a scenario that requires synchronizing data with data stores that are not SQL Server databases or in which the store schemas are very different.`

 

Enable Change tracking at Database Level.

 

 

Change Tracking

Use to enable or disable change tracking for the database.

To enable change tracking, you must have permission to modify the database.

Setting the value to True sets a database option that allows change tracking to be enabled on individual tables.

You can also configure change tracking by using ALTER DATABASE.

Retention Period

Specifies the minimum period for keeping change track information in the database. Data is removed only if the Auto Clean-Up value is true.

The default value is 2.

Retention Period Units

Specifies the units for the Retention Period value. You can select Days, Hours, or Minutes. The default value is Days.

The minimum retention period is 1 minute. There is no maximum retention period.

Auto Clean-Up

Indicates whether change tracking information is automatically removed after the specified retention period.

Enabling Auto Clean-Up resets any previous custom retention period to the default retention period of 2 days.


 

ALTER DATABASE [BigMarket] SET CHANGE_TRACKING = ON;

 

 


 

Enable Change tracking at Object Level.

 

 

Change Tracking

Indicates whether change tracking is enabled for the table. The default value is False.

This option is available only when change tracking is enabled for the database. For more information, see Database Properties (ChangeTracking Page).

To enable change tracking, the table must have a primary key, and you must have permission to modify the table.

You can also configure change tracking by using ALTER TABLE.

Track Columns Updated

Indicates whether the SQL Server Database Engine tracks which columns were updated.

 

 


 

 

 


 

Will cover about internals of Change Tracking with important catalog views in next post.

 

 

 

 

 

 

 

 


 

Categories: Uncategorized