I've had Benjamin Nevarez's Inside the SQL Server Query Optimizer sitting on my bookshelf for a couple of years. I recently pulled it off the shelf to read it cover-to-cover, to see what new things I could learn. <p> This book is supposed to take the "magic black box" feel out of the query optimizer. Chapters range from an introduction to optimization, to index selection, to hints. </p> <p> I remember my first introduction to the parts of the query optimizer – I was at SQL Saturday Chicago and <a href="http://scarydba.com">Grant Fritchey</a> was giving a presentation to a packed room. He mentioned terms like "parsing" and "binding". I thought it was interesting, but most of my work with queries was more practical – how do I make them suck less? (Fortunately, in the past four years, I've gotten a lot better at making queries run better.) Now, I'm interested in more details around this process. </p> <p> The book starts with a discussion of what a query optimizer is, and what it does. For those of us without computer science degrees, it's a great introduction to the history of optimization, core fundamentals, and challenges. It sets a good foundation for the information that is covered in the rest of the book. </p> <p> What's the difference between a scan and a seek? What is a lookup? What are the differences between the nested loops, hash, and merge joins? How can you give SQL Server the information it needs to make the most efficient decision on which to use? The second chapter covers some of the most-frequently seen operators in execution plans. </p> <p style="text-align: center;"> <img src="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/users/grrlgeek/index scan.JPG?mtime=1387830757" alt="" /><img src="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/users/grrlgeek/index seek.JPG?mtime=1387830757" alt="" /><img src="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/users/grrlgeek/Key lookup.JPG?mtime=1387830757" alt="" /> </p> <p> How does the optimizer make its decisions? It's based on query cost estimates. How does it get information about costs? It looks at the statistics on indexes. The next chapter covers what statistics are, how they are calculated, and how to keep them up to date. I have a few sections highlighted here for future reference. </p> <p> Then, he dives into indexes – how your queries are written and how your indexes are created can make or break a determination by the optimizer. Knowing how it will search for indexes to use will help you create better indexes. Two tools for discovering better indexes – the DTA and DMVs are covered. (Secret? I don't like the DTA. I'd rather you know how to use the index DMVs.) </p> <p> In chapter five, Ben starts digging into the inside of the optimizer. I learned some new things about the sys.dm_exec_query_optimizer_info and sys.dm_exec_query_transformation_stats DMVs. Transformation rules are covered, which reminded me of some high school math classes. To me, it's interesting to know how the optimizer handles this, but not something I would touch very often. </p> <p> Different phases of optimization are covered as well. It's good to know the difference between trivial and full – it's helped me understand why SQL Server picked a specific plan time sometimes. It's also good to understand the phases of full. </p> <p> The next couple of chapters cover a hodge-podge of topics. How are updates (which can be an insert, update, or delete) different from a select? How does SQL Server prevent rows from being updated multiple times? What is parameter sniffing, and how can you work with it? What are query, join, and table hints, and what do you need to know before using them? </p> <p> If you are used to reading execution plans and are looking for a bit more understanding of how things work, this is a good book to pick up. Keep in mind that as things change with SQL Server 2014 coming out, you might not see some of the same behaviors on newer versions, but the information is solid nonetheless. </p> <p> Thank you, Ben, for distilling what could be a complicated technical discussion into easily-understandable pieces! </p>

This weekend I decided to take advantage of the Windows Phone Preview Program for Developers and update my Lumia 920 to Update 3 for WP8. While Update 3 was downloading and installing on my phone, I decided to install the Windows Phone SDK 8.0 which includes Visual Studio Express for Windows Phone on a Win 8.1 VM that I use for development purposes. Installation was quick and painless until the very end when I received a message stating that the installation was unable to enable Hyper-V. When I tried to enable it manually by going into Control Panel -> Programs & Features -> Turn Windows Features On or Off, the Hyper-V Platform checkbox was grayed out. Upon hovering over the check box I received a tool tip stating “Hyper-V cannot be installed: A Hypervisor is Already Running”.

Prior to the release of supporting listeners for availability groups in Azure running Windows Server virtual machines, availability groups were supported but more so in a mirroring configuration. This means, applications, services, or users would connect to the instance name and in the event of a failover, the name would have to change. Availability groups power lies in the concept that there is a complete protection level thanks to clustering and single entry points. Alleviating the need for custom or addition tasks to be performed in the case data, hardware or operating system failures occur.

I've been notified I am a finalist for the category Best New Voice at the Tribal Awards. These awards are not for some tattoos on your lower back or on your biceps, but rather a collaboration of Simple-Talk and SQLServerCentral to give outstanding community contributors the chance to receive a round of applause by their fellow peers. That's right, nomination is entirely done by the tech community itself (and I somehow seemed to miss that survey).

Introduction In my quest to learn a bit more about Elasticsearch ([post 1][1] en [post 2][2]) I will now use a .Net framework to connect to the server. I have found that [NEST][3] is the most popular out there and featurecomplete. Of course there is a [blogpost by Joel Abrahamsson][4] out there but it is a bit dated. The code Let me begin by making a model. Class Fiber Public Property Id As Integer Public Property FiberColor As FiberColorEnum Public Property Remarks As String Public Property Msps As IList(Of Msp) Public Enum FiberColorEnum Orange Red Brown Blue Green Black NoColor End Enum End Class Class Msp Public Property Id As Integer Public Property A As Decimal Public Property WaveLength As Decimal End Class``` And now that I have that out of the way I can make a connection to the server. ```vbnet Dim setting = New ConnectionSettings(New Uri("http://192.168.73.128:9200")).SetDefaultIndex("fibers") Dim client = New ElasticClient(setting) Apparently the setdefaultindex is important because without it you get all kinds of errors all telling you to set a default index. Don’t know why that isn’t in the constructor as a parameter.

Introduction I tried out ElasticSearch this weekend and I needed a GUI to make it speak to me a little more, because real men use GUI’s. And I must admit that the tooling around Elasticsearch is amazing. I started with ElasticHQ. Installation Installing it can’t be much easier. Got to the getting started page and click start download. Then unzip the zipfile where you can unzip it. And then just double click the index.html file.

Introduction I heard of [Elasticsearch][1] before but while at NDC-London I saw it put to work and I immediately fell i love with it. This is how software is supposed to work. Install and let it figure out itself. Elasticsearch is a distributed restful search and analytics server based on Lucene. So if you feel the need to add fulltext index searching to your application this is the software to go for.

Recently Packt Publishing released the book SQL Server 2012 Reporting Services Blueprints authored by Marlon Ribunal (blog|twitter) and Mickey Stuewe (blog|twitter). My former colleague Valentino was the technical reviewer for this piece – he wrote about it here – so I was very interested in getting my hands on this book. Luckily Packt was kind enough to provide me with a copy so I could write a review.

I’m asked this question quite often while setting up availability groups and setting a preferred secondary for transaction log backups – can you recover from the full backup taken on the secondary with COPY_ONLY and also rely on a full backup taken on the primary or does the log chain become broken and prevent it? To answer the question directly, yes, you can recovery form the COPY_ONLY full as well as the full taken on the primary and still apply logs to both. Instead of simply answering the question, let’s show it on our lab setup with availability groups running the following configuration.

It’s the monthly blog challenge known as T-SQL Tuesday! Edition 49 is being hosted by Robert Davis, and he wants us to wait around. No, that’s not right – he wants us to write about waiting. Yeah, that’s it. You’ve read a lot about wait statistics. You know your servers have wait statistics on them. You know your applications and queries are waiting on something. Before you panic, before you go changing maxdop or rewriting queries, remember one thing: every system is going to have waits recorded. It’s the nature of computing. The chance that every resource a query needs is available at the exact millisecond it needs it is nonexistent. It takes time to read data from storage to memory. It takes time to write data to storage. It takes time to send results across the network to the client.