Stéph@TechEd 2012: Episode 3 – SQL, SQL & SQL

Today, my TechEd day went around presentations and workshop about SQL Server 2012. SO have a look to what’s comin next…
As all day, it started with the traditional keynote. This time is was all about WIndows 8

Keynote: Windows 8

The main idea openning driving this keynote was: Designing for a changing world.

Did you alrady heard about “picture passwords”?? No more keyboard (in another word :-?), just click on precise points on the picture and then you are identified. See Steven Sinofsky blog about it!
Like any new products is design with the metro style… We must be accustomed!

Windows 8 Enterprise new features:

• Simply and easily virtualization to test for example W7 on your workstation.
• New remote desktop client with “touch language”

Launch from a USB key the OS env. Windows 8 and remove the key during running and…Waaaaaou, no crash.. You have a few seconds or minutes to reconnect the key and you can directly continue to work. Funny and impressive!

First Session: FileTables, FullTextSearch and Semantic Search

When I have seen that a session with a level of 400 was for these 3 features, I was really interested in looking this further. Here a warp up of the main points I got from it.

FileTables a great new feature

For reminder, FileTable technology is built over FileStream features.
Here some tricks about it.
You can’t add a column on the FileTable. It’s a fixed format table but all system information are present.

“SELECT INTO” command does not create a FileTable.

Delete fails on directories containing other folders or files.

You have 3 limitations:

• Memory mapped files are not supported (can’t open with Notepad, Paint… “An error message appears!”)
• 15 level subdirectories
• Windows Explorer limit 260 bytes names (NTFS allow more)

FullTextSearch(FTS)

Some information:

• You can’t revert to previous versions.
• New word breakers: Czech, Greek
• Stoplists, Thesaurus, Filters same as SQL 2008 (Office 2010 filters must be installed manually)
• New NEAR operator in the Full Text Search
• Property-based search with the query model

What’s about performance?

• Scale up to 350 million of documents
• Index-building speed (master merge parallelization, more granular build, more granular locks during build)

And what’s with Query performance?

• Worst case query response time < 3sec
• Faster search time using FTS STVF (Query parallelization, Optimized for Select TOP N)

An extended Event for Full-Text exists to visualize specific trace about this technology: Full-Text Trace.
Filter must support extracting properties of interest and troubleshooting with Trace Flag 7603.

Semantic Search

This feature allows searching the meaning of a document and not only keywords.
It implements common search pattern (search by keyword, keyphrases, finds closest document, etc.)

What’s about Semantic Index?
It uses 2 internal indexes:

• Tag index
• Document Similarity Index

Finally this session was really inetresting and interactive with presentation animated with great and simply examples of these 3 features in SQL Server 2012.

Optimizing SQL Server Performance in a Virtual Env.

So the next one was about optimizing SQL Server in virtualized environments. That’s pretty usefull as we are facing such kind of implementation more and more.

Diagnosing Performance Problems

In case of environment virtualized using Hyper-V, simply check Host and Guest performances (for IO latency, CPU numbers) with perfmon. It is the easiest way to do these checks.

In a VMWare environment, use Vcenter manager tools (menu Host first then the menu Virtual machine)

Here some essential perfmon counters:

• Read and wrtites /sec
• Seconds / read & Write
• Disk Queue
• Page Life Expectancy
• System processor Queue
• VM Disk (VMWare)
• VM Memory (VMWare)

Balloon Memory drivers
What is this balloon driver? Used by host OS to request memory back from the guest OS, it prevents host from paging physical memory to the hosts swap file.
Which status should it have? Should be enable
In addition we got a small advice from Denny: Lock pages in memory should be disabled but for a specific reason you can enabled it.
You must forces the memory to be available for the guest.
Memory Reservations is recommended that to be set to a portion of the allocated Memory.
Another Memory option is Memory Deduplication Option. It’s great for OS Memory but doesn’t work at all for SQL Server…

Storage Configuration Options
First of all, best practices are the same between virtual and physical disks
If possible, use automatic tier adjusting technology and separate disks for OS, Data, logs & Tempdb.
Another option is Storage Deduplication and can improve overall performance. By deduplicating OS virtual disks, we save much less data to the array. As less data is written to the array, less OS data is loaded into cache on th array
For conclusion: monitor, monitor and monitor all levels (SQL Server, Guest, Hypervisor, Host and Storage) and personally I add Network level.
And now go to Big Data session…

Microsoft’s Hadoop Big Data Solution

I won’tgo in details, as it is just a huge topic, but will give you some overview and the main information.

The question which all this topic is based on is : What is big data?
The answer to that question was made with the example of the Large Hadron Collider who produces 1PB/sec.

Imagine the number of disks that it would require… And more disks does mean more failures…

This is an extreme case and even if you don’t have a Large Hadron Collider at home or at your enterprise (:roll:), you still have similar principle in your environment: Sensors, clicks, logs, call centers, images, medical transcriptions…. This is the life-reason of Big Data!

What is funny is to see java code during the demo… oups, I said it!
But it’s not a mistake; we are on an Apache Infrastructure on Windows!
Advise: Go to HadoopOnAzure.com and get an eye on the information there.

Another thing is that a special ODBC connector exist for Hadoop: Hive ODBC Driver

Install it and then install the “HiveExcel add-in” and open excel. You can see in your Excel a new feature in the Data menu: Hive Pane. Create a ODBC connection with this driver…

You are ready to use it. For more information about it, see this wiki page.

Conclusion

So to conclude my day, I went to a workshop on SQL Server 2012. Even if I already knowed and practice some stuff like installation on Windows core or creating an availability group, I still played with features I didn’t before: Managing SQL Server using Powershell. In addition it always help to get ready for the dba certification for SQL server 2012!

One of new Powershell SQL Server Commandlets that I discover is “Backup-sqldatabase” cmdlet. Unfortunately we haven’t a message when the backup successful but an error message when it’s failed!

Ok, now a last funny question, does Visual Studio 2012 work with SQL Server 2012?
In the past, Visual Studio has one back version with SQL Server… It has been corrected!
In December, you will be able to work with Visual Studio 2012 for SQL Server 2012 project!
See you tomorrow for more news on SQL Server, Windows and PDW… A great journey!