Monthly Archives: January 2014

Extended Events in SQL Server 2012 – Part 1

Extended Events, SQL Server 2012 January 31, 2014 1 Comment

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
Ee1
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.
EE2

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
go
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
go
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100
go

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.


Advertisement

Data page restore from the SSMS UI in SQL Server 2012

Backup\Restore, SQL Server 2012 January 26, 2014 Leave a comment

How about a UI that lets you restore data pages in SQL Server? Well that’s exactly what we have in SQL Server 2012. The UI allows you to select the pages from the backup to restore rather than restoring the entire database.

Here are the steps how we can restore a data page in SQL Server 2012

Step 1:dp1Step 2: Once you click on Check database pages you will have list of pages that are corrupt. Click on Add. Choose the correct backup set and click on OK. The corrupt data page\s will be restored.
dp2

Timeline – Point in time recovery with SQL Server 2012

Backup\Restore, SQL Server 2012 January 23, 2014 Comments: 3

How about a nice GUI that lets you select the time to which you want to restore all your available backups in sequence in one go. A TIMELINE of restoration points. That would be fantastic and that is what SQL server 2012 has introduced. Point in time recovery via the TIMELINE feature. Let us understand this feature with an example.

I have created a database called MyDB which I will restore to a point in time. I have kept the DB in full recovery mode. The following backups have been taken.
1) 1 full backup
2) 1 Diff backup
3) 5 t-log backups
Snapshot of the backups
tl2Now lets recover this database to a point in time from the available backup via TIMELINE option.
Step 1: Right click on the database -> Tasks-> restore -> database
tl3Step 2: select device. Give a new name to the restored DB
tl4Step 3: Click on the add button and select all the backup files
tl5Step 4: Click on ok and you will see the below screen. Observe the option restore to
tl6tl7Step 5: Lets click on TIMELINE button and this is what you will see
tl8Step 6: Select the option -> Specific date and time. Once you do that you will be able to move the scroll bar to any time highlighted in green. In tis case i have moved it to 6:02:03 PM. This is the time i would like to have my DB restored to. Click on OK.
tl9Once you click on OK, you will find the below screen with the required backup only. Also observe that a tail log backup will also be taken.
tl10Step 7: Click on options tab. We will go with restore with recovery. Click on OK
tl11tl12Once you click on OK you will find the db restored with recovery to the name given above
tl13

FIRST_VALUE – New analytical function in SQL Server 2012

SQL Server 2012 January 22, 2014 Leave a comment

In this article we will be taking a look at the new analytical function FIRST_VALUE  in SQL Server 2012. Let us understand them with examples.

This function returns the first value from an ordered set of values. The syntax for the same is

FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ]
order_by_clause [ rows_range_clause ] )

Let us create a sample table and understand this on SQL Server 2012. The following script does the same. Lets observe the output of the script below

CREATE TABLE [dbo].[MyFirstLastVaL](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
[MarksInMaths] [int] NOT NULL )
GO
insert into MyFirstLastVaL values('Alan',99)
insert into MyFirstLastVaL values('Brent',29)
insert into MyFirstLastVaL values('Charlie',25)
insert into MyFirstLastVaL values('David',37)
insert into MyFirstLastVaL values('Ello',15)
insert into MyFirstLastVaL values('Frank',59)
insert into MyFirstLastVaL values('Grant',16)
insert into MyFirstLastVaL values('Honey',18)
insert into MyFirstLastVaL values('Irene',86)
insert into MyFirstLastVaL values('Jack',76)
insert into MyFirstLastVaL values('Kalou',66)
insert into MyFirstLastVaL values('Lisa',68)
insert into MyFirstLastVaL values('Mike',98)
insert into MyFirstLastVaL values('Lisa',28)
insert into MyFirstLastVaL values('Mike',45)
insert into MyFirstLastVaL values('Alan',19)
insert into MyFirstLastVaL values('Brent',92)
insert into MyFirstLastVaL values('Charlie',35)
insert into MyFirstLastVaL values('Irene',36)
insert into MyFirstLastVaL values('Jack',67)
insert into MyFirstLastVaL values('Kalou',96)

SELECT id,name,MarksInMaths,
FIRST_VALUE(marksinmaths) OVER (ORDER BY marksinmaths) MyFirstVal
FROM MyFirstLastVaL

fvl1Now let us use the function with partition by clause and observe the output

SELECT id,name,MarksInMaths,FIRST_VALUE(MarksInMaths)
OVER (PARTITION BY Name ORDER BY MarksInMaths) MyFirstVal
FROM MyFirstLastVaL

fvl2In the first case the query simply returns the first value of the entire data set whereas in the second case the output is based on the marksinmaths column, so the FIRST_VALUE is different but the same for each partition.

%d bloggers like this: