Tag Archives: SQL Server

SQL Server Integration Services – Packages, Datasources and Connections


creating a SSIS project…











 creating data sources..


Creating Connections



Extended Events in SQL Server 2012 – Part 1

In SQL Server 2008, Microsoft introduced a feature called Extended Events. Extended Events helped in collecting and analyzing event driven data about the SQL Server instance. Extended Events introduced lesser load than trace or profiler events on the server. But in SQL Server 2008 there was no GUI that allowed direct communication with the events. Complex T-SQL had to be written to fetch the information which was returned in xml format.

In SQL Server 2012, MS has introduced a GUI in the Management Studio (SSMS)  that allows working with Extended Events and viewing event related data a much simpler process. In this article we will understand and see step by step how we can create and manage Extended events via the GUI that is built in the SSMS.

Step 1: Connect to SQL Server 2012 instance -> Open Management Tab -> Open Extended Events -> Right Click on Sessions -> New Session
Step 2 -> In the new session dialog box type in a name of the session. In this case we will use ‘Session1’. For now we will not choose any template.

Step 3 -> Click on Events tab -> On the below window you will find many Events listed from which we need to pick an event or multiple events that we want to capture. Once selected we need to click on the button to add it. For this article we will use the Events
a) query_post_execution_showplan and b) sql_statement_completed
EE3AEE4Step 4 -> Click on Data Storage -> Here you specify how you can capture data so you can view it later. Here we will save it as Event File. Mention the path where we would save the file -> Click on OK.
EE5Step 5 -> Go to management studio -> Under sessions you will find New session listed. It would be in a stopped state as this is the default behavior. Right Click on the session and click on ‘Start Session
EE6EE7EE8Step 6 -> Now right click on the created session and select ‘Watch Live Data’. If there is no activity on the instance you will not see any data.
EE9EE10Step 7 -> Let us fire few queries on the AdventureWorks2012 database and watch the window. Following are queries that we would run on the database for testing. After running the queries go back and open the ‘Watch Live Data’ window again. Click on the Event data and observe the below.

USE AdventureWorks2012
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100

EE11EE12EE13In this article we have seen how we can start a new event trace from SSMS. In my next article we will explore more into the new GUI feature that MS has added to SQL Server 2012.

Resources in a SQL Server Failover Cluster

In this article I will discuss about the resources that are part of a SQL Server failover cluster.

A SQL Server failover cluster instance consists of 2 kinds of resources.

1)Local Resources: Local resources are those components that are installed on each possible owner node. They remain on that node and do not move over during a failover.
2)The shared resources: Those resources that fail over between the owner nodes.

Let us now go through the resources that are part of a resource group in a SQL Server failover cluster.
a) SQL Server Database Engine\Agent service : The database engine and SQL Server agent resources are always part of any SQL Server failover cluster’s resource group where the database engine has been installed. If SQL server analysis service is installed, then it will be a part of the resource group. From an availability and performance perspective it is better if both are installed on separate resource groups.

b) IP Address and Network name: The IP address and network name component ensures an interface that clients can use to connect to the SQL Server instance  irrespective of the fact that which node is currently serving the cluster. During a failover event,  the network name and IP addresses is registered to redirect to the new node that is serving the SQL Server failover cluster instance. To the clients that are connecting to the SQL Server, this is a absolutely transparent process.

c) Shared disks:A SQL Server shared disk contains all of the system and user data including databases, logs for the SQL Server failover cluster instance. When a failover occurs, the disks are mounted on the new node. When SQL Server instance is started on the new node, it goes through recovery as part of the SQL Server startup and maintains access to the same database files that existed when it was running on the previous node.The shared disks are the single point of failure for a failover cluster instance. Windows Server and SQL Server failover clustering provides redundancy for machines, operating systems, and SQL Server binaries, but requires reliable storage to assure availability of the shared storage.

How to restore a table from a Full backup using Quest Litespeed without restoring the entire database

In this article I will demonstrate how we can restore a table from a SQL Server database backup file which has been taken by Quest Litespeed tool. This feature called Object level recovery in the tool is extremely helpful as you need not restore the entire database and yet retrieve data from the backup. Follow the below mentioned steps and understand how this can be accomplished. I will be using a trial copy of Quest Litespeed along with SQL server 2012 and the database we would use is Adventureworks2012. However you can use SQL server 2008 also.

Step 1: Create a database and take a full backup of the database
Let us create a database for this purpose

USE mydb
INSERT INTO Mytable1 VALUES (1,'Sanchayan')
INSERT INTO Mytable1 VALUES (2,'Abhishek')
INSERT INTO Mytable2 VALUES (1,'Pandit')
INSERT INTO Mytable2 VALUES (2,'Agrawal')

Now take a Full Backup of the database.

exec master.dbo.xp_backup_database
@database = N'Mydb',
@compressionlevel = 3,
@filename = N'D:\MSSQL\SQLBackup\Mydb.bak',
@init = 0,
@OLRMAP = 1--This is important as this marks the backup for object level recovery

Step 2: Now run the following code to find the Tables available in the backup

Use Master
exec xp_objectrecovery_viewcontents
@FileName = N'D:\MSSQL\SQLBackup\mydb.bak',
@FileNumber = 1,
@Type = 'All',
@DisableLogProcessing = 1

Step 3: Now select the table that you wish to restore. In this demo we would restore MyTable2.
We would need the below predefined:
a) The database where we would restore the table.
b) A temp directory location which should be large enough for the table to be restored initially.

Use master
exec xp_objectrecovery
@FileName = N'D:\MSSQL\SQLBackup\Mydb.bak',
@FileNumber = 1,
@ObjectName = N'dbo.Mytable2',
@DestinationTable = N'[dbo].[Mytable2]',
@DestinationDatabase = N'MyTempDB',
@DestinationServer = N'GN-A-MOOKBO-7L\sql2012',
@TempDirectory = N'D:\MSSQL\SQLBackup',
@DisableLogProcessing = 1

The output of the above code would be as below
olr2To verify if the data you can query the table that we restored on mytempdb database
olr3I hope this article was helpful in understanding how we can use ‘Object level recovery’ to fetch data from a full backup file that has been taken using Quest Litespeed for SQL Server tool.

Technical reference: http://www.quest.com/litespeed-for-sql-server/

How to increase the number of SQL Server logs


Step 2: Expand the management folder
Step 3: Right Click on SQL Server Logs
Step 4: Click on Configure
Step 6: Click on OK.
EL6We can also run the below query to attain the same:

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

How to Recycle SQL Server Agent Error Logs


To counter this situation we can do the following:

1) Run the following SP on a regular basis:

USE msdb
EXEC dbo.sp_cycle_agent_errorlog

a) Right click on the Error logs:
b) Click on OK


How to Partition a table in SQL Server

In this article we will see how we can partition a table in a database using T-SQL. We will first understand the basics of table partitioning in SQL Server and then go through the scripts that we will use to build a partitioned table. Let us start with the basics of partitioning.

What is table partitioning in SQL Server?
Partitioning is dividing a large table and its indexes into smaller fragments called partitions.

Some of the Benefits?
Its helps performing maintenance operation fragment by fragment basis rather that performing on the entire table. Apart from that SQL queries can be redirected to proper partitions directly rather than scanning the entire table.SQL queries that are properly filtered on the partition column can perform better by making use of partition elimination and parallelism. Archiving data is another major benefit of partitioning. We will not deep dive in this article on the benefits of partitioning.

Components of Partitioning:
1) Partition Function(PF): PF defines which rows goes into what partition in a partitioned table based on a range.
2) Partition Scheme(PS):The partition scheme defines how partitions will be stored on filegroups. Creating a partition scheme assumes that your database already has filegroups.

Let us now understand with an example how we can partition a table in SQL server.
1) Creating a database:

USE master
,FILENAME = N'D:\MSSQL\Data\PartitionDB.mdf'
NAME = N'PartitionDB_log'
,FILENAME = N'D:\MSSQL\Logs\PartitionDB_log.ldf'

2) Adding 4 new filegroups to the PartitionDB database.


3) Adding files to the database filegroups.

        NAME = PartitionFile1,
        FILENAME = 'D:\MSSQL\Data\PartitionFile1.ndf',
    TO FILEGROUP PartitionFG1;
        NAME = PartitionFile2,
        FILENAME = 'D:\MSSQL\Data\PartitionFile2.ndf',
    TO FILEGROUP PartitionFG2;
        NAME = PartitionFile3,
        FILENAME = 'D:\MSSQL\Data\PartitionFile3.ndf',
    TO FILEGROUP PartitionFG3;


        NAME = PartitionFile4,
        FILENAME = 'D:\MSSQL\Data\PartitionFile4.ndf',
    TO FILEGROUP PartitionFG4;

4) Creating a Partition Function(PF):

    AS RANGE LEFT FOR VALUES (10, 20, 30);

5) Creating a Partition Scheme(PS):

    AS PARTITION PartFunc1
    TO (PartitionFG1, PartitionFG2,PartitionFG3,PartitionFG4);

6) Create a Table:–

USE [PartitionDB]
CREATE TABLE PartitionTable
	MyDate datetime NULL,
	Name varchar(50) NULL
	)  ON PartScheme1(MyID)

7) Create Index on Partitioned Table

USE PartitionDB
ON PartitionTable(MyID)
ON PartScheme1 (MyID);

8) Insert Data into the Table

USE PartitionDB
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (1,GETDATE(),'Rooney');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (11,GETDATE(),'Van persie');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (22,GETDATE(),'De Gea');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (34,GETDATE(),'Moyes');

9) Verify data in the table

SELECT * FROM dbo.partitiontable

10) Verify Rows Inserted in Partitions

USE PartitionDB
SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='partitiontable';

11) Run the below code to see the details of the partitioned table

USE PartitionDB
OBJECT_NAME(idx.object_id) AS TableName ,
psh.name AS PartitionSchemeName ,
fnc.name AS PartitionFunctionName,
part.partition_number AS PartitionNumber ,
fg.name AS [Filegroup],
rows AS 'No of Records' ,
CASE boundary_value_on_right WHEN 1 THEN 'less than'
ELSE 'less than or equal to' END AS 'Condition',
value AS 'Range' ,
part.partition_id AS [Partition Id] FROM sys.partitions part
JOIN sys.indexes idx
ON part.object_id = idx.object_id
AND part.index_id = idx.index_id JOIN sys.partition_schemes psh
ON psh.data_space_id = idx.data_space_id
sys.partition_functions fnc
ON fnc.function_id = psh.function_id LEFT
JOIN sys.partition_range_values prv
ON fnc.function_id = prv.function_id
AND part.partition_number = prv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = psh.data_space_id
AND dds.destination_id = part.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
sys.allocation_units GROUP BY container_id) AS au
ON au.container_id = part.partition_id JOIN sys.tables t ON
part.object_id = t.object_id WHERE idx.index_id &lt; 2
ORDER BY TableName,part.partition_number;

P2 I hope this article was helpful in understanding how we can create table partitions via T-SQL.

Script to find the size of all Indexes in a database

Continuing with my interest with Indexes, i wrote this script that finds the size of all indexes in a database along with the table and the filegroup on which the index resides. I have used the AdventureWorks2012 database as an example. Please replace the DB name for which you want to find the information.

USE AdventureWorks2012
IF OBJECT_ID('tempdb..#Indexdata', 'U') IS NOT NULL
DROP TABLE #Indexdata

@SizeofIndex BIGINT, @IndexID INT,
@NameOfIndex nvarchar(200),@TypeOfIndex nvarchar(50),
@ObjectID INT,@IsPrimaryKey INT,
@FGroup VARCHAR(20)

create table #Indexdata (name nvarchar(50),
IndexID int, IndexName nvarchar(200),
SizeOfIndex int, IndexType nvarchar(50),
IsPrimaryKey INT,FGroup VARCHAR(20))
SELECT idx.object_id, idx.index_id, idx.name, idx.type_desc
FROM sys.indexes idx
join sys.objects so
on idx.object_id = so.object_id JOIN sys.filegroups fg
ON idx.data_space_id = fg.data_space_id
where idx.type_desc != 'Heap'
and so.type_desc not in ('INTERNAL_TABLE','SYSTEM_TABLE')

OPEN Indexloop

INTO @ObjectID, @IndexID, @NameOfIndex,

SELECT @SizeofIndex = sum(avg_record_size_in_bytes * record_count)
FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,
@IndexID, NULL, 'detailed')

insert into #Indexdata(name, IndexID, IndexName, SizeOfIndex,
SELECT TableName = OBJECT_NAME(@ObjectID),
IndexID = @IndexID,
IndexName = @NameOfIndex,
SizeOfIndex = CONVERT(DECIMAL(16,1),(@SizeofIndex/(1024.0 * 1024))),
IndexType = @TypeOfIndex,
IsPrimaryKey = @IsPrimaryKey,
FGroup = @FGroup

INTO @ObjectID, @IndexID, @NameOfIndex,
CLOSE Indexloop

select name as TableName, IndexName, IndexType,
SizeOfIndex AS [Size of index(MB)],
case when IsPrimaryKey = 1 then 'Yes' else 'No' End as [IsPrimaryKey]
,FGroup AS [File Group]
from #Indexdata order by SizeOfIndex DESC

Snapshot of the output below:



We will now see how to enable this option.

          LP2         LP3
          LP4 LP5
          1) Run the following query:

select od.node_id, os.memory_node_id, od.node_state_desc, os.locked_page_allocations_kb
from sys.dm_os_memory_nodes os
inner join sys.dm_os_nodes od on
os.memory_node_id = od.memory_node_id
where od.node_state_desc <> 'ONLINE DAC'

The locked_page_allocations_Kb will display a non-zero value.

LP10              2) DBCC MEMORYSTATUS will show the following:

Find Top 5 expensive Queries from a Read IO perspective


SELECT TOP 5 qt.text AS ‘SQL’, qstats.total_physical_reads AS ‘Total Physical Reads’, qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,    qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time/qstats.execution_count AS ‘Average Worker Time’,
qstats.total_worker_time AS ‘Total Worker Time’,
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘AgeInCache In Hours’,  db_name(qt.dbid) AS ‘Database Name’
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qstats.total_physical_reads DESC

SQL Server 2008 -How to troubleshoot error: Process 0:0:0 (0x1ebc) Worker 0x00000005393F61A0 appears to be non-yielding on Scheduler 15. Thread creation time: 13022573770856

A few days ago i encountered the below mentioned error message in SQL error logs in one of our production environments

I found the following KB Article on MSDN to throw light on this issue. As per the article there was a stack dump that we found in the server.
StackWhat caused the schedulers to be non-responsive:
As per the article the cause of the issue is:
“This issue occurs because the profiling session is not closed correctly. The profiling session is used to calculate the cost of an object to be cached, and is based on memory usage and CPU usage”.

Resolution as Provided by MS in the Kb article for SQL server 2008 is:
Apply CU6 for SP 3.

Our patch level in the environment is beyond that and still this issue happened, so literally there is no solution provided by MS. We would wait and see if this error comes back too often and open a case with MS support.


Error: 30089, Severity: 17, State: 1.



Where DatabaseID is






To show the current value:
sp_fulltext_service ‘ism_size’

To change the value to a new value:
sp_fulltext_service ‘ism_size’,@value=NewValue

Useful tech articles of Full-text search:

Buffer cache hit ratio – How useful it is?

Buffer cache hit ratio(BCHR) : Well every dba who has ever done any Performance tuning work would know about this particular counter. So what is BCHR: Percentage of pages that were found in the buffer pool without having to incur a read from disk.

And how much guidance does it give us….

Well to know that i had to share this great article from . I read this article on http://www.simple-talk.com. Read the article and challenge your understanding of BCHR once again.

Find jobs that are least run or never run

I had to find all the jobs that either least run(one in 4 months) or never executed on all our SQL environments. i wrote the below script that pulls up that information.

SELECT Jb.Name AS [Job Name], CASE WHEN Jb.[Enabled] = 1 THEN ‘Yes’ ELSE ‘No’ END AS [Enabled], Jact.LastRun AS [Last Run Date], Jact.NextRun AS [Next Run Date]
FROM    msdb.dbo.sysjobs Jb
JOIN msdb.dbo.sysjobs_view Jv
ON Jb.Job_ID = Jv.Job_ID
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
FROM    msdb.dbo.sysjobactivity
GROUP    BY Job_Id
) Jact
ON Jb.Job_ID = Jact.Job_ID
JOIN msdb.dbo.syscategories JC
ON Jb.Category_ID = JC.category_id
WHERE    DATEDIFF(day, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 120
OR NextRun < GETDATE()

Snapshot of the outcome below.

Unused Jobs

Find all unused indexes in a database

While doing an index analysis activity i had to find the indexes are hardly being used in our production DB’s. I wrote this script to acheive the same

SELECT case when os.is_published = 1 then 'Replicated' else 'Not Replicated'
end as ReplicatedorNot,
OBJECT_NAME(idx.object_id) AS TableName ,
idx.name as Index_name,
FROM sys.dm_db_index_usage_stats AS istat
JOIN sys.indexes AS idx ON idx.index_id = istat.index_id
join sys.objects os
on idx.object_id = os.object_id
AND idx.object_id = istat.object_id
istat.database_id = DB_ID('databasename') -- type the name of the Db
and object_name(idx.object_id) not like 'Ms%'
and object_name(idx.object_id) not like 'sys%'
and object_name(idx.object_id) not like 'id_%'
and object_name(idx.object_id) not like 'conflict%'
and object_name(idx.object_id) not like 'Any other tables that you like to avoid%'
AND idx.is_unique_constraint = 0 -- no unique indexes
AND idx.is_primary_key = 0
AND idx.is_disabled = 0
AND idx.type > 1 -- don't consider heaps/clustered index
AND ( ( istat.user_seeks + istat.user_scans +istat.user_lookups )  <  istat.user_updates
or (istat.user_seeks = 0 AND istat.user_scans = 0 AND istat.user_lookups = 0))
order by (istat.user_seeks + istat.user_scans +
%d bloggers like this: