Optimization Techniques

Question

Optimization Techniques

karthik M

One Orange Chip

Points: 29139
More actions
November 26, 2012 at 3:19 am

#266606

All,
what are all the new "Optimization Techniques" available in SQL 2012 which is not available in SQL 2008?
karthik

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

There are lots and lots of new things in SQL Server 2012. Here's a fairly complete listing. What specifically are you after?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

karthik M (11/26/2012)
All,
what are all the new "Optimization Techniques" available in SQL 2012 which is not available in SQL 2008?

On the new T-SQL optimization I highly recomend Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions by Itzek Ben-Gan. There are some powerful new Windows functions in the 2012 version of T-SQL; this book details them quite nicely with some awesome examples.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

karthik M One Orange Chip Points: 29139 More actions · Answer 3

say for example,

I have a procedure in SQL 2008 & 2012.

If I fine tune the procedure in SQL 2012, I would know what are all the new "Optimiztion Techniques introduced in Sql 2012 which is not available in sql 2008".

karthik

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

karthik M (11/26/2012)
say for example,
I have a procedure in SQL 2008 & 2012.
If I fine tune the procedure in SQL 2012, I would know what are all the new "Optimiztion Techniques introduced in Sql 2012 which is not available in sql 2008".

But you would't necessarily find out that some of the new functions are slower than some of the tricks people had to pull to get the same job done in 2005 and 2008. For example, the 2012 SUM widowing function for doing running totals is still quite a bit slower than the Quirky Update (an admittedly unsupported feature).

Because of that, I'm not sure I'd refer to such functions as "optimizations". "Feature additions", sure. But not "optimizations". The new "Selective XML Indexing" could also be perceived as an "optimization" because it allows for faster indexing and takes less space than standard XML indexing. Of course, if you really want to save some space, normalize the data to get rid of the tag bloat and index it properly. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 5

But you would't necessarily find out that some of the new functions are slower than some of the tricks people had to pull to get the same job done in 2005 and 2008

Thats True. I agree your point 🙂

For example, the 2012 SUM widowing function for doing running totals is still quite a bit slower than the Quirky Update (an admittedly unsupported feature).

oh..Really !!!

"Quirky Updates" still Win & Run in the market. I think it is a horse with 6 legs 🙂

karthik