This morning, I was very happy to go to my first TechEd.

Between the 10 seminars, as I said yesterday, I started with SQL Server 2008R2/2012 performance, tuning and optimization session. The thing is that we had just one session for the whole day.

IMG_0168

I will not copy the presentation of the 2 speakers (Thomas LaRock and Denny Cherry) who presented remarkably this session despite the failure of air conditioning during the morning. Fortunately, it came back for the afternoon.:-D

Introduction

For the introduction, I will just resume with this table:

What With Who
Performance ColumnsStore IndexAlwaysOn Further
Tuning Filtered IndexesExecution Plan Forward
Optimization Table PartitioningFilegroups Faster

The goal for me is to see new tools in particular for SQL 2012, to share my experience with specialists and discover other good things about these 3 themes.

How to identify Performance Bottlenecks?

In another term, what tools must I use?

The list of tools is known from all dbi services consultant in SQL Server technology but I will explain some news about the 2012 version and some tricks that I have learned.

  • Performance Monitor(perfmon): It’s a good complement to Profiler but it’s more efficient if data is collected periodically. Don’t forget that you have a good tool to analyze the output file: Performance Analysis of Logs (PAL)
  • Profiler: The thing is to choose the good template or create one
  • DMVs: Use it but be careful many reset when you restart the SQL Server Engine service
  • Activity Monitor: A good surprise for me with the 2012 version 😀 . There are 2 new features I will always use. The first is that we can enlarge the column and the second who comes with the first is a scrollbar to see all columns when you enlarge a part of them.

Act_Mon1

Another feature is that you can see what DMV you can use in a query to have a column and for this you must just place you cursor of the column title. Enjoy!

Act_Mon2

  • DTA (Database Tuning Advisor): Analyze your trace from Profiler or SQL Trace
  • SQL Trace: deprecated
  • Data Collection: Since SQL Server 2008. In the management folder, choose “Configure Management Data Warehouse” and go to the next step.

DataColl1

You can see 3 types of report but the most important is the third with Query Statistics History.

DataColl2

  • Extended Event: Like Data Collection, you find it in Management folder.

Like Profiler, you must choose a template. A good template is the “Query Wait Statistics”. You selects your events and let’s go.

ExtendedEvents

The next question is which one to use?

They have the same response that I give to my clients or during workshops: IT DEPENDS!
This is a philosophic or consultant answer but it’s a reality. Each situation has its solution.
The next chapter that we have seen is about the index optimization.

How to find the good index?

Or in other term, do you have the right index? It depends…

I give you just a few lines of reflections:

  • Uses DMVs for missing index
  • Don’t just add indexes, test it before
  • Read the book “Inside Microsoft SQL Server 2005: T-SQL Querying” Chapter 3: QueryTuning; Table 3-16

Use the Query Execution Plan:

  • Correlation with Waits Event (like table plan & CXPAQUET)
  • Examine the first operation
  • Look for Warning
  • Look for the “more costly” operator
  • Look for “fast fingers”
  • Look for the extra operators
  • Look for scan

A new feature in SQL 2012 for query execution plan is the query instruction that you have executed and you can have a warnings on it like on this picture. Very nice!

QueryPlan

Here is link to a good script posted on 11 June 2012 by Glenn Berry about SQL Server 2012 Diagnostic Information Queries to help you.

Filtered indexes
This technology creates an index on a subset of data and exists sine SQL Server 2008.
Employ it while in my experience, I haven’t seen that dba or developers use it… It’s a shame!

Columns Store Index
At our dbi services event, last year, we presented you this fabulous technology.
I don’t explain it but I will just discuss one important limitation.
The big problem is when you use a Column Store Index, your table is set to read only.

The solution is to use 2 tables:

  • One read only table with a Column Store Index
  • One write/read table without a Column Store Index

Storage optimization with table partionning

How use it?

  • Reduce Maintenance windows for large tables
  • Reduce recovery times
  • Improve query response time

One thing is to choose Right or Left partition function. You can find a good blog about that here.
I discover a few undocumented functions to help me/us for partionning:

BDCC IND(xxxxx) /*look at where the page are actually written to*/ 
DBCC TRACEON(3604,-1) /*Activate the output to the console and not in errorlog*/ 
DBCC PAGE(xxxx)/*display the content of a page*/

AlwaysOn

The last chapter was about AlwaysOn, but you can read my article here or see the presentation on slideshare.

Conclusion

I can’t resume one day session in a few words but I hope you found some interesting information in my article.
Thanks for Thomas LaRock and Denny Cherry for their presentation.

IMG_0172

See you tomorrow for the real beginning of the TechEd 2012 with more sessions! 😀