Monthly Archives: May 2014

Doing something awesome

Do you get up in the morning thinking “It’s an awesome day. I feel like changing the world. I am going to do something amazing and it’s going to change the way I see the world and the world sees me”? If yes I know exactly how you feel, if not I have only one question for you

WHY???

It’s very easy to get stuck in grind; the grind soon becomes a leash that boxes your creativity and zest. Just try this for the next 1 week, Get up in the morning, ask yourself how you feel today, then go outside and have a look at the sky and ask yourself what amazing thing I can do today. Do whatever it takes to make you feel upbeat, listen to music, workout whatever and then make a promise to not let anything dampen your spirit, make a list for the day and go after it.

If your goal oriented like me the week becomes a fast paced and extremely rewarding 7 days, if you’re more the nice and easy type you will have the luxury of seeing the world in a brand new light and it might inspire you. In the end you’re more likely to have a better day than your current routine. Try it out let me know what you think.

I used to do this for many years in my regular job and then somehow forgot but I remember now and I feel awesome.

What’s next?

Here are lists of topics we are planning to include in the upcoming blogs. If you would like to see any topics added or covered sooner, please vote for it.

Performance

  1. Resource Governor
  2. OLTP in Memory Tables
  3. Identifying IO Bottle necks
  4. Optimistic Concurrency
  5. Understanding execution plans

Security

  1. Contained Databases
  2. SQL Injection
  3. Protecting against SQL Injection
  4. Encryption and TDE

High Availability and Disaster Recovery

  1. Always ON Availability Groups
  2. Windows Failover cluster
  3. Cloud backups
  4. Piece-meal restore

 

PART 2 -Upgrading the Amazon AWS Cloud EC2 instance

The days almost over but we just managed to finish uploading Part 2 of the video on how to upgrade the instance type of your Amazon EC2 instance. In this video we upgrade the server from the micro instance to the m1.small instance type.

The link is provided below. Please ping me back if you would like more videos or need any clarifications on the video.

https://www.youtube.com/watch?v=OycjM9nkgF0

As always thank you for watching.

PS:- I am thinking of posting a poll for future topics that we plan to cover , if you want to see a video for a particular topic please vote for it and we’ll tackle those first.

Look out for the post with the list of Topics coming up

Upgrading the Amazon AWS Cloud EC2 instance and Attaching a new volume.

Today’s video explains how to implement a new volume and upgrade an existing instance of AWS EC2 to a higher hardware configuration. Sorry I couldn’t put in a more detailed description but I am off to Microsoft Ventures: ThinkNext & Demo Day and am already running late. Please enjoy the video and do ping me if you have any queries.

https://www.youtube.com/watch?v=gPE1OGzJruc

PS: this is part 1 of 2 , I will upload the second video tonight.

It’s OVER

Those who have been following the series of blogs today will notice a trend in the topics being picked up. These are all features that are launched in SQL 2012 but more importantly they all reflect the T-SQL equivalent of similar functionality available in SSAS. It makes sense considering the push MS has been making on scalable VLDB database volumes and high speed performance in OLTP systems. With the introduction of tabular model in SSAS, Columnstore indexes and the focus on power pivot and Power view it’s pretty clear that MS wants Enterprises to continue to use OLTP systems for very large data volumes without the need to shift to SSAS.

 

With the new OVER functionality we see one more feature that has long been available in MDX query language and that is the lag keyword. Coupled with the OVER clause we can now use the LAG keyword to lookup the previous primary key value to fetch the data in a manner similar to navigating a set. While this is not the equivalent of the Next member or previous member syntax available in mdx it comes close to helping address common OLAP business cases. E.g. a typical case is comparison of this week’s sales with last weeks . The below query uses the LAG and Over syntax to fetch data from the FactinternetSales table to show how this example is implemented.

 

select
d.WeekNumberOfYear ,
SUM(UnitPRICE) Weeklysales,


LAG(SUM(UnitPRICE)
)
over ( Order
by WeekNumberOfYear) PrevWeekSales,

SUM(UnitPRICE)
LAG(SUM(UnitPRICE)
)
over ( Order
by WeekNumberOfYear) Diff

from
FactInternetSales
f

inner
join
DimDate
d

on
d.DateKey = f.OrderDateKey

GROUP
BY
D.WeekNumberOfYear

ORDER
BY
WeekNumberOfYear

At long last Pagination

MS SQL Server 2012 introduced a very nice feature for developers with the PAGINATION OFFSET Syntax. It has always been a challenge to work with pagination in MS SQL server. Typically this was handled in the front end code by downloading the entire result set and then filtering as required based on client input however the problem with this approach has been that fact that the entire data needed to be fetched initially. With the new OFFSET capability we are now able to pick the starting point in the result set from which the pages need to be returned. If you remember in my previous post I had mentioned an issue with being able to identify the third highest batter in a game

 

/******************************************

Create some test data

******************************************/

declare
@table
table (

id
int
identity(1,1),

BatterName
varchar(10),

Score
int

)

 

insert
into
@table

select
‘James’
,
‘112’

union

select
‘Adam’
,
‘122’

union

select
‘Sunil’
,
‘121’

union

select
‘Rick’
,
‘121’

union

select
‘Susan’
,
‘128’

 

select
*
from
@table

order
by
score

 

 

/******************************************

Using a CTE and Ranking function to get the third highest scorer

******************************************/

 

;with
cte
as (

select
rank()
over( order
by
score
desc) as
pos
,
*

from
@table

)

select
*
from
cte
where
pos
= 3

 

 

/******************************************

using Pagination to get the third highest Batter

******************************************/

declare
@rank
int
= 3

select
*
from
@table

order
by
score

offset
@rank
1 ROWS

FETCH
NEXT 1 ROWS
ONLY

 

Comparison of the execution plans.

 

 

In this case we are first ordering the data by score then offset the number rows ( telling SQL how many rows to ignore) and then getting the result set in this case the next row. Note however this logic will fail when there are ties in the data, if you need to handle ties then the cte and rank function is still your best hope. But this is a nice feature none the less since we now only fetch the data as required so for very large result sets there sis significantly less IO and Memory usage. But I need to test the impact of the data in the buffer pool in terms of Page life expectancy.

Hope you found this Post useful. Please leave comments if you need any additional info.

Video on MS SQL Server Partitioning

 

As promised here is a short video to start of the series on SQL Server partitioning. The video covers the difference between simply placing files in Filegroups with multiple files and partitioning. It also touches upon the effects of proportional filing and its impact on IO throughput. More importantly it explains when to use what.

https://www.youtube.com/watch?v=AsXZlkklHbQ

In the next video I am going to cover how to implement partitioning, as well as multiple file groups and files.

In the last video I will cover how to convert existing tables into Partitioned tables and the sliding windows scenario.

Those who have suffered through each of the videos will have an in-depth knowledge of how partitioning works in MS SQL Server.

PS: – As always I would love to hear anything you have to say on the videos. Please be ruthless if you have to: D