Populate table from Stored Procedure resultset in SQL Server

I was answering post in MSDN website today and found one question which was asking “How to populate table from stored procedure in SQL Server?”. This is quite a common question and I have answered it in Experts-Exchange.com forum so many times but today I felt to share it with my blog reader.

Actually there is no direct way to populate result of stored procedure into SQL Server table but yes, you can use OPENROWSET to achieve this task. Let us look at it practically.

[sourcecode language=”sql”]
USE AdventureWOrks2012
GO

CREATE PROC uspGetSaleOrderHeader
AS
SELECT
SalesOrderNumber,
RevisionNumber,
OrderDate,
ShipDate,
AccountNumber,
CustomerID,
TotalDue
FROM
Sales.SalesOrderHeader
GO

IF OBJECT_ID(‘tempdb..#TempSalesTable’) IS NOT NULL
DROP TABLE #TempSalesTable

SELECT * INTO #TempSalesTable FROM OPENROWSET(‘SQLNCLI’, ‘Server=WIN-9H6QATRKY81\SQL2K12DEV;UID=sa;PWD=upgrade1;’,’EXEC AdventureWOrks2012..uspGetSaleOrderHeader’)

SELECT * FROM #TempSalesTable
[/sourcecode]

If “Ad Hoc Distributed Queries” is disabled in your SQL Server, you will not be able to use OPENROWSET. You can enable “Ad Hoc Distributed Queries” with small TSQL Script, click here, for more details about this.

BTW, I have few more article where I have used OPENROWSET, click on the link to see more examples of OPENROWSET.

http://blog.extreme-advice.com/2011/07/05/openrowset-and-openquery-in-sql-server-20052008/

http://blog.extreme-advice.com/2011/07/15/filestream-in-sql-server-2008/

http://blog.extreme-advice.com/2011/07/06/msg-7301-cannot-obtain-the-required-interface-iid_idbschemarowset-from-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-linktoaceess/

http://blog.extreme-advice.com/2009/08/10/insert-and-update-image-field-in-sql-server-20082005/

http://blog.extreme-advice.com/2009/04/25/load-relational-xml-data-in-sql-server-2005/

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

What is Cloud Computing ?

What is Cloud Computing  ?

Well, I have received the question “What is SQL Azure?” many times in an email and in IT events I go. I generally write blog about SQL Server but I have not written anything about SQL Azure so far. Before I actually answer What the SQL Azure is, I have to explain a small bit about “Cloud” first.

“CLOUD” is one of the booming words in IT industry since last 5 years. Cloud computing is still growing very fast, even beyond our imagination.

Basically to setup IT infrastructure, any company would need:

  • Computers with Operating system (Server and/or desktop)
  • Software to install on top of Operating system, to cater the need of users, such as Exchange Server, MS Office, Share Point, SQL Server, IIS etc.
  • Highly scalable Network/Internet

Provided list of software is just to give an understanding to the readers otherwise this list can easily grows up long & long.

To setup this IT environment, company has to pay a lot for hardware and software licensing. Even after establishing IT infrastructure or website, whenever company grows, it needs to keep changing the infrastructure based on the growing needs of business and again company has to pay up-front for all these requirement whereas, in cloud you don’t need to worry about, you can increase or decrease capacity of whatever infrastructure / software you are using and have to pay for only what you are really using.  Apart from this, you don’t need to employ staff to maintain this infrastructure even you will get 99.9% up time monthly SLA from almost all the major cloud service provider.

Now, question comes to your mind that how they provide Operating system along with software we need? This is really very valid question and the answer will laid down in the following paragraph.

Cloud computing comes in several different categories and you have to choose what exactly suits your need, some of the popular categories of cloud are defined here:

  • Platform as a Service (PaaS)
  • Infrastructure as a Service (IaaS)
  • Software as a Service (SaaS)

These are three very popular services, out of few more, that cloud service provider provides.

Cloud Architecture
Cloud Architecture

Platform as a Service (PaaS): As name suggest, this service provides you a platform where you can host your application without worrying about Hardware, Operating System, Performance of Operating system, update SPs and other patches to Operating system. Microsoft provides “Microsoft Windows Azure” as a PaaS.

Infrastructure as a Service(IaaS): Iaas basically cater your needs related to networking, load balancing, security, firewalls and much more as long as Infrastructure concerns.

Software as a Sercvice (SaaS): As name describes, this model provides a complete leverage of cloud where you can get SaaS software enabled in your cloud space along with utilization of IaaS and PaaS. You may have CRM, E-Commerce application, Exchange Server, Lync Server, Share Point, Office Professional and many more. In SaaS model, you can even get support of programming framework like .NET, JAVA, PHP to cater the need of your own customized application. You can also ensure that you get relational database support, if needed, in form of SQL Azure.

Hope this makes your concept clear about Cloud Computing.

Images used in this article were taken from “Wikipedia

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Packt Publishing reaches 1000 IT titles and celebrates with an open invitation

My regular blog reader knew that I have recently on SQL Server book for PACKT Publication, named “SQL Server 2012 Performance Tuning Cookbook“.

Birmingham-based IT publisher Packt Publishing is about to publish its 1000th title. Packt books are renowned among developers for being uniquely practical and focused.  Packt books cover highly specific tools and technologies which IT professionals might not expect to see a high quality book on.

Packt would like you to join them in celebrating this milestone with a surprise gift – to get involved you just need to have already registered, or sign up for a free Packt account before 30th September 2012.

Packt published their first book in April 2004. One of the most prolific and fastest growing tech book publishers in the world, they now have books on everything from web development to web graphics, e-learning to e-commerce, IT architecture to games, and app development.

So what are you waiting for, register with PACKT and book your surprised gift which will be disclosed soon. click here to register.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find Blocking in SQL Server and use it to send an alert

Find Blocking in SQL Server and use it to send an alert:

As a DBA, I am always interested to find whether blocking is going on, in my environment or not? How long blocking exists? Which are the queries/SPs creating blocking etc.

Blocking generally occurs when one SQL Server connection (user process or application process) places a lock on a table or a number of rows and a second connection attempts to read or modify the data under the lock by first connection. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

Sometime It may happens that blocking gets converted into deadlock situation where neither of both connection able to finish its job and one of the connection identified as deadlock victim by SQL Server. In short, Locking, Blocking and deadlocking is not good to keep. This is the reason I always eager to know the details about blocking happening in my system.

Few days back, I have created one SQL Server View “WhatIsGoingOn” which was using sys.dm_exec_requests and sys.dm_exec_sessions DMVs to find information about currently running processes in SQL Server instance along with sessionID and blocking Session ID, if any. I am going to use “WhatIsGoingOn” SQL Server View in one SP to find only blocked session and insert its detail into following SQL Server table.

[sourcecode language=”sql”]
CREATE TABLE tblBlockingDetails

(

ID INT IDENTITY(1,1),

ObjectName SYSNAME NULL,

BlockingObjectName SYSNAME NULL,

StatementText VARCHAR(MAX),

BlockingStatementText VARCHAR(MAX),

SessionID INT,

BlockingSessionID INT,

Status VARCHAR(50),

BlockingStatus VARCHAR(50),

DateCreated DATETIME

)[/sourcecode]

After having above table in my database, I will create following Stored Procedure which will capture details of any session being locked and insert that detail in “tblBlockingDetails” table.

[sourcecode language=”sql”]
CREATE PROC uspInsertBlockingRecords

AS

INSERT INTO tblBlockingDetails

SELECT

W1.ObjectName AS ObjectName,

W2.ObjectName AS BlockingObjectName,

W1.Statement_Text AS StatementText,

W2.Statement_Text AS BlockingStatementText,

W1.sessionid AS SessionID,

W2.sessionid AS BlockingSessionID,

W1.status AS Status,

W2.status AS BlockingStatus,

GETDATE() AS DateCreated

FROM

WhatIsGoingOn W1 INNER JOIN WhatIsGoingOn W2

ON

W1.BlockingWith > 0

AND W1.BlockingWith = W2.sessionid

[/sourcecode]

Now, I can schedule SP “uspInsertBlockingRecords” every few minutes for business hours in SQL Server Job Agent so that I can see details of blocking anytime I wish from table “tblBlockingDetails”. I can even set email schedule either to get information about blocking, if I wish.

If you want to create DeadLock situation forcefully to check its behavior, you can use the script given in one of my past article here.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Write process in TRASACTION LOG (.LDF) file in SQL Server

I have written one article about Transaction Log File (.LDF) yesterday, today I am presenting one more article on the same subject.

There is a myth going on in the mind of many developers that only DML statement like INSERT/ UPDATE & DELETE are being written in the log file but the truth is that SQL Server writes DDL operation like CREATE TABLE or CREATE INDEX etc. in the log file too.

Let us see this fact by small demonstration.

  • Open SQL Server Management Studio
  • Keep two query window open
  • In First Query window, execute following TSQL Code which will begin one transaction & create one SQL Server table but wouldn’t COMMIT or ROLLBACK

[sourcecode language=”sql”]
Begin Tran
Create Table TestTable
(
Col1 INT
)[/sourcecode]

  • Since our CREATE TABLE script ran successfully in first query windows, we will execute simple SELECT statement on this table in second Query Window

[sourcecode language=”sql”]SELECT * FROM testtable[/sourcecode]

  • Your SELECT statement won’t finish execution until you either COMMIT or ROLLBACK. So let us now go to first query window and execute following statement

[sourcecode language=”sql”]
ROLLBACK[/sourcecode]

  • If you go to your second query window, you will observe that your SELECT statement would have finished execution with error something like “Invalid Object name ‘Test Table”
  • Now again go to First query window and execute following command once again.

[sourcecode language=”sql”]
Begin Tran
Create Table TestTable
(
Col1 INT
)[/sourcecode]

  • Since our CREATE TABLE script ran successfully in first query windows, we will execute simple SELECT statement on this table in second Query Window

[sourcecode language=”sql”]SELECT * FROM testtable[/sourcecode]

  • Your SELECT statement won’t finish execution until you either COMMIT or ROLLBACK. So let us now go to first query window and execute following statement

[sourcecode language=”sql”]
COMMIT[/sourcecode]

Now look at your second query window and you will see your SELECT query has successfully finished execution.

Isn’t this proves that DDL commands are written in LOG file too?

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn


Note: Microsoft Books online is a default reference of all articles but examples and explanations

Transaction Log File (.LDF) in SQL Server along with ACID property and recover mode of database

SQL Server databases created with two types of file:

Data File (File with extension .MDF/.NDF)

Log File (File with extension .LDF)

Data File can be one or more than one but physical log file will always remain one for each database. You have control over the size of log file but you can’t have more than one physical log file.

There are so many mysteries and myth roaming around log file in developer’s mind so I thought to take an initiative to break this ice.

What is the need of Log File?

Log file plays an important role for SQL Server databases. Any DML or schema change TSQL you apply, it first goes to log file and later on it will be save to data file. By having this kind of architecture, SQL Server ensures your data integrity. If anything wrong happens to your TSQL command and it doesn’t get finished then SQL Server can ROLLBACK it or even user can do it too if transaction was used.  Because of LOG (Transaction Log) file, SQL Server guarantees ACID (Atomicity, Consistency, Isolation, and Durability).

Before we move further, it is worth to look at the definition of ACID.

Atomicity ensures that either all operation in one transaction apply or none. It means that either 100% COMMIT or 100% ROLLBACK.

Consistency ensures that a transaction either creates a new and valid state of data, or returns all data to its state before the transaction was started. It means that you have guarantee that a transaction never leaves your database in a half-finished state.

Isolation ensures transaction separation until they are finished.

Durability ensures that the data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

Since we can have more than one database data file, we can either keep it on one drive or may distribute it over multiple disks as per our requirement of performance as well as disk space but we can have only one transaction log file (.LDF) so it is necessary to have proper size of .LDF file based on database requirement.

Note: Though we have only one transaction log file, we can have multiple Virtual Log File (VLF) inside our one transaction log file. This is different topic and out of the scope of this article so I will come up with separate article on this topic.

There is no rocket science which suggests you exact size of your database transaction log file. It is completely up to the requirement of business which vary from case to case. All I can suggest is neither keep log file too big then your requirement nor keep it too small.

Bigger transaction log file takes time to recover and somehow it affects the overall performance of database too at the same time, smaller log file size resulted into frequent trigger to auto grow transaction log file size.

Prevent transaction log file from frequent auto grow; keep its size reasonably big to accommodate your business transaction. No matter how big transaction log file you have, it used to be filled completely sooner or later and at that time, you have no option other than either to increase transaction log file size or truncation log file.

I personally against both the way I have mentioned in above paragraph. We shouldn’t truncate log file, not at least if we have other alternative because truncating the log file increase the risk of break up in log backup chain.

I would suggest taking transaction log backup frequently which helps you in two ways:

1.)    You can restore your database in point-in time whenever needed

2.)    It free-up unused part of log file so that new transaction can use that free space inside transaction log file and you don’t need to grow it much

Last but not the least, we have three recovery mode (Simple, Bulk-Logged and Full) for database which affect our transaction log file very much.

Simple: This recovery mode don’t use transaction log file too much. It writes the data in transaction log file and as soon as checkpoint runs and data transfers to data file, it truncate transaction log automatically so there is no possibility to take transaction log backup and hence in time of crisis, we can only restore the last full backup of data base available.

Bulk-Logged: This recovery mode uses transaction log file and log all DML operation except BULK Insert, BCP, CREATE INDEX,  SELECT INTO and text & image operation. We can take transaction log file backup in this mode. In time of crisis, we can have full database backup and all transaction log backup we have.

Full: This is one of my favorite recovery modes for mission-critical production database which logs each and every transaction. It helps to achieve Point-In time recovery.

Reference: Ritesh Shah

http://Extreme-Advice.com
http://www.sqlhub.com

Quick Learn


Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find locked table name with duration and count in SQL Server

Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.

I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.

[sourcecode language=”sql”]
CREATE VIEW WhatIsLocked
AS
SELECT
Locks.request_session_id AS SessionID,
Obj.Name AS LockedObjectName,
DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
ActTra.Transaction_begin_time,
COUNT(*) AS Locks
FROM    sys.dm_tran_locks Locks
JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
JOIN sys.objects Obj ON Obj.object_id = Parti.object_id

JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
WHERE   resource_database_id = db_id() AND Obj.Type = ‘U’
GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name
[/sourcecode]

You can use this view to keep automated watch. you can query this view every few minutes in database in business hours and if find lock bigger then certain duration, send email with detail. You can even keep watch by putting this query in web/windows application which keeps refreshing every few seconds and show details of locking.

I have written one more article in past which displays lock table, table with  ‘SCH-M’ lock and which command causing the lock. read article in detail, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

use sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server Instance

Many time we need to find who are logged in to SQL Server instance with which tool/program/software. SQL Server gives this very useful information to us with following two DMVs.

sys.dm_exec_requests
sys.dm_exec_sessions

I have written VIEW in my SQL Server Instance to capture all these necessary information.

here is the TSQL script for the same:

[sourcecode language=”sql”]
CREATE VIEW WhoisLoggedIn
AS
SELECT
ISNULL(sess.host_name, ”) as host_name,
ISNULL(DB_NAME(req.database_id) , ”)  as database_Name,
ISNULL(sess.program_name, ”)   as ProgramName,
ISNULL(sess.login_name, ”)  as login_name,
ISNULL(CAST(req.start_time AS VARCHAR), ”)  as start_time,
DATEDIFF(MINUTE,ISNULL(req.start_time,GETDATE()),GETDATE())  as RunningMinutes,
req.Session_id

FROM
sys.dm_exec_requests req
INNER JOIN
sys.dm_exec_sessions sess
ON
req.session_id=sess.session_id
WHERE
–will eliminate all system processes
req.session_id >= 50
AND
sess.host_name IS NOT NULL
[/sourcecode]

Once you create this VIEW in your SQL Server, you don’t need to remember complex query but to use only user friendly name “WhoisLoggedIn” with standard SELECT statement like this:

[sourcecode language=”sql”]
SELECT * FROM WhoisLoggedIn
[/sourcecode]

Note: I have written one blog post in past which was finding currently running processes in SQL Server with the help of above given two DMVs.  click here to read it.

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions

As a DBA, it is mandatory to keep an eye on your database and server too, especially during the business hour. SQL Server providing very helpful Dynamic Management View to help in this situation. Dynamic Management View (DMV) are as follow:

sys.dm_exec_requests
sys.dm_exec_sessions

I have created one SQL Server view with the help of following script which I used to use, often, during business hours to check whether everything is going smooth or not.

 

[sourcecode language=”sql”]
CREATE VIEW WhatIsGoingOn
AS
SELECT
OBJECT_NAME(objectid) as ObjectName
,SUBSTRING(stateText.text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(stateText.text)
ELSE statement_end_offset
END – statement_start_offset)/2) + 1) AS statement_text
,DB_Name(database_id) as DatabaseName
,req.cpu_time AS CPU_Time
,DATEDIFF(minute, last_request_start_time, getdate()) AS RunningMinutes
,req.Percent_Complete
,sess.HOST_NAME as RunningFrom
,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy
,sess.session_id AS SessionID
,req.blocking_session_id AS BlockingWith
,req.reads
,req.writes
,sess.[program_name]
,sess.login_name
,sess.status
,sess.last_request_start_time
,req.logical_reads

FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id
AND sess.is_user_process = 1
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText
[/sourcecode]

Now, whenever I want to see the status whether anything is blocking to my query/queries or which query/SP is being executed from which time. How many reads/writes happening by each objects. if there is a task like backup/restore, I can even see, how many percent complete by just executing one simple SELECT statement:

 

[sourcecode language=”sql”]
SELECT * FROM WhatIsGoingOn
[/sourcecode]

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

SQLHub News: Interview of Ritesh Shah is published on PACKT website


There is a big difference between writing a book and writing a blog. I understand this when I have started writing a book “SQL Server 2012 Performance Tuning Cookbook”.
SQL Server 2012 Performance Tuning Cookbook
SQL Server 2012 Performance Tuning Cookbook
As soon as PACKT proposed me to write the book on this subject, I have started deciding the topics to cover in this book with my co-author Bihag Thaker. It was a big challenge to decide the content of this book because performance tuning is a big topic and covering all aspects under the one book is little difficult but finally we have managed the book in three following parts.
1.)  Performance Monitoring
2.) Performance Tuning
3.) Performance Management
Each topic covered the practical recipe for hands-on experience of the readers. PACKThas conducted my interview regarding my journey of this book writing process. Further reading the interview, please click here.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah