Expert Performance Indexing for SQL server 2012

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012

One more book review 🙂 (It’s really nice having a long holiday to read books and relax).

This is also a “Must have book”; simply because Indexing is so important for good performance. Indexing is always a trade off between better read access to data versus storage and degrade in write performance, and I think this book gives you the tools to support your decisions when you are working with the part of performance that are related to indexing.

No doubt that my favorite chapter is the chapter about Myth’s when it comes to indexes. All persons working with SQL servers should read that chapter.

With this book you first get an insight into what indexes are, what types of indexes there is and what statistics are (statistics is critical to know in relation to performance optimization).

The book also describes some of the tools that are important to know when you want to work with indexes (including the most important DMO’s).

Finally I also like that the book gives you some good advices (they call it best practice and strategies) on how to write queries and how to use indexes.

Overall a very competent book (I think all books from APress is competent).

On the negative side, then it is easy to see that this book is an upgrade from the same book on SQL Server 2008, and one of the places where that is seen is when they discuss Column store indexes. It is few pages and it is absolutely NOT Expert knowledge what they give you.

I am not sure it is a negative thing, but the book is called “Expert performance….”. I think this book can be read and should be read by anyone that has been working with the Microsoft SQL server 2012 for more than 6 month to 1 year. It could be a good starting point for going deeper into the Microsoft SQL server.

I think this book is worth buying and I give it 3 out of 5 stars.

 

Microsoft SQL Server 2012 Internals

Microsoft SQL server 2012 Internals

Microsoft SQL server 2012 Internals

This book is a “must have book”, if you want to dig into the SQL server. Not only because it is written by some of the most Genius SQL persons (Kalen Delaney, Jonathan Kehayias, Benjamin Nevarez and Paul S. Randal), but because with this book, you get deep dive knowledge about how the SQL Server 2012 works.

The book is split into 14 chapters, starting with a very good introduction about the SQL server architecture, the SQLOS and databases.

Some of the new features are described in chapter 4, where an introduction to Contained databases are given.

As a MCT, then I really like when a book is focused and understandable, and one good example of this is on page 23, where it is stated that “SQL Server Books Only lists only 17 trace flags that are fully supported”. That means: If you want to pass an exam for MCSE on SQL server, then you must know 17 trace flags!!! (of course now the MCSE is changed so that it also covers Microsoft SQL Server 2014).

Chapter 6-9 is about indexes and storage, and that is of course important knowledge to have if you e.g. want to optimize performance.

Chapter 10-13 is a deep dive into how the SQL Server executes, how the Query optimizer works, and this is among my favorites. You can’t get enough information about how the Execution is done, how SQL server works with concurrency and how the SQL server is working with execution plans.

 

Knowing that the last chapter about DBCC is written by Paul Randal, then it is a “must read chapter”. Paul Randal has been writing a lot of the DBCC functionality, so here we get information about the DBCC command directly from one of the architects. And he knows what he is talking about 🙂

On the downside, then the book is not for beginners. I think that the reader should have at least 3-5 years of experience with Microsoft SQL Server, and he / she must have a good understanding about tables, indexes, queries, maintenance, Backup and so on.

Another downside is that in some places of the books, then we have output and examples that span multiple pages. It can be a little bit difficult to read.

I think this book deserves 4 out of 5 stars. I was a little bit in doubt about if it should be 4 or 5 stars, but on the other hand, then I must also say that this book is an update of the same book for SQL server 2008, and even though it describes new things in Microsoft SQL Server 2012 very well, then I can’t give 5 stars, when a lot of the material has been published before (to be fair. Everything has been rewritten and changed to SQL 2012).

Practical Troubleshooting The database Engine

This book is an oldie, but a goodie, and I finally managed to get through all the pages. I hate my self for not having done that for many years ago.

 

This book is a technical book, and it covers a lot of serious aspects related to troubleshooting; from DBCC to Memory preassure; from IO problems to CPU problems.

 

The best thing is that it mentions a lot of Dynamic management views that are still very relevant.

 

So. If you a skilled SQL person, and you want to learn more about Troubleshooting, then this is a must have book.

Buy it on Amazone.com.

MicrosoftÂź SQL ServerÂź 2012 Pocket Consultant

This is the first time I would make a book review, where I am 100% negative about a book. I know that if you have nothing good to say, then you should not say it, but I must warn everybody about buying this book.

Especially if you also have the 2008 version of the book.

There is a lot of good knowledge in the book, but I can’t see the purpose of it, and I hate when I buy a book, then I just feel that the FrontPage has been changed from the previous version.

I must admit that there is a lot of nice information in the book, but what does that help when the physical layout of the book is very bad?

There are simply to many pages for the small format, and if I really should use it as a pocket consultant, then I would not be able to bring it onboard a plane, because my total weight would exceed the allowed weight.

I would give this book 1 out of 5 stars, and I would recommend you to use your Money buying other books instead.

I want to pass 70-461 – Querying Microsoft SQL Server 2012

Hi,

I just want to share some of the thoughts that I have about how you in the most easy way can Pass exam. 70-461.

Basically, the easiest way is to learn SQL, but it is my opinion that you should have been using TSQL and the SQL server for at least 3-5 years, if you want to be sure to pass the exam.

The Exam is split into the following topics.

  1. Create Database Objects (24%)
  2. Work with Data (27%)
  3. Modify Data (24%)
  4. Troubleshoot & Optimize (25%)

At present (on 2012-10-22) there is no trainingmaterial from Microsoft, so you must learn a lot of stuff your self. That stuff includes (but is not 100% perfect):

  1. What is the syntax to
    1. Create/drop/alter a table
    2. Create/alter/drop  a filetable
    3. Create /alter/drop a view
    4. Create an indexed view.
    5. What types of functions are there.
    6. How to extract data to XML format (What is the difference between e.g. FOR XML AUTO, RAW, PATH)
    7. How do you insert, update, delete records.
    8. Understand different types of constraint (Unique, Foreign key e.g.).
    9. How SELECT works, and how to use Joins (at least inner, Outer and Cross joins), APPLY.
    10. How to Insert, Update and Delete data.
    11. How MERGE is working.
  2. Dynamic management views you must underStand:
    1. Views to tell you about missing indexes.
    2. Views to tell you about index usage, including physical info.
  3. Other things that you might need to know:
    1. How collation works.
    2. New features in SQL 2012 in relation to querying.
    3. General understanding of different types of objects (e.g. views, tables, Stored Procedures, Functions) and when to use what.
    4. Data types (especially the new ones).
    5. How Transactions works (also what the difference between implicit and explict transactions are).
    6. How Executionplans works
    7. Table hints
    8. Query hints

And there are much more. I would recommend also to read the studyplan at microsoft.

So how would you prepare. Well, I would start downloading the Adventureworks databases from CODEPLEX.COM.
When they are installed (at least just choose one of them, then I would do the following:

  1. Search Books online to read about the topics.
  2. Open SQL Server Management Studio (or other tools if they are required) and then start to code.
  3. If any doubts then try searching www.google.com (but be carefull. there is a lot of crap out there).

When you have learned a new topic, then you can move on to the next topic. However!!!!! What about the level of knowledge?

Well, that is always the question. Often Microsoft has this “Free shot” campaign, and if that is the situation (or if you have to much money), then try to take the exam. If you pass then you are fantastic. If you fail, you are still fantastic, but then you have learned something about the level of knowledge.

AND!!!!! At some point www.measureup.com and www.selftestsoftware.com will have some preparation questions ready, and Microsoft will send out the training material, and then you can use that for your preparations.

Good Luck (and please call me if you need any specific advises).

SĂžren

 

1) Print out a checklist (that could e.g. be mine, eventhough there are more topics to be covered)

 

SQL Server MVP – Deep Dive Volume 1

This is a crappy book, but you should buy it anyway…. It sounds strange, but please look at the review and feel free to disagree with me.

I bought this book since like the idea of having a lot of small but interesting articles gathered in one book, and some of the articles are very good and you can learn a lot from them, and some of the articles are really bad.

“Deep dives” means, as I see it, not an introduction but a Deep dive, and if we look at the chapter about full text search, then the big question is: What does it have to do with a Deep Dive? It is a good introduction, but it is not something that I would expect from a book that calls it self a Deep dive.

On the other hand, then there is an interesting article about Gaps and Islands, and this is really a deep dive. It is expected that you know a lot about SQL Server and programming, and now you want to be introduced to a problem that has caused a lot of troubles to a lot of developers over time.

So why do I think it is crappy? Because the level of the content is in average not a deep dive. It is in some cases a beginners guide to functionalities. When that is said, then the quality of the articles are high, and that is why I recommend you to buy the book.

 

SĂžren

 

 

Professional SQL Server 2008 Internals and Troubleshooting

It is very easy to give a description of this book. It is a must, if you want to work with SQL Server as an administrator. Of course there are things that are not really necessary to know, but in general the book describes quite well, some of the mainissues when it comes to performanceproblems in a SQL Server: Memory and Locks.

Besides that there is a good deal of information about the storage engine and how it works, and we also look at the CPU.

On the downside, I think the book uses to much time talking about other approaches like SQL Nexus, and therefore it places it self between a good technical book and a book for administrators that does not want to be to technical.

It can be bought at amazon.com 

 

 

Pro SQL Server 2008 Mirroring

This book review is about the book “Pro SQL Server 2008 Mirroring“, and it is a nice little book that can be bought on Amazon or other online Book stores. It is 237 pages, and it is an expert book in Database mirroring on Microsoft SQL Server platform.

I recommend that you buy this book if you have setup a couple of installations (mirroring setups) and now you want to learn more about how mirroring works. There are 10 chapters, and each chapter cover an important topic when it comes to mirroring and as all other books, the first chapters are a general introduction, including the most common words that you need to learn.

One chapter I like in particular is chapter 3, that is called Planning and Best Practices. This is not a technical chapter. this is a chapter to understand that everything in a SQL server is not about using wizards and clicking Next- Next and Next. This chapter introduce you to the fact that Planning is everything!!!

It also have a list of Best Practices, and this is a nice list, BUT this is also an example of one of the downsides of the book. The layout is not very good. Why do you make a list that is difficult to read and prioritize, when you could create a checklist in prioritized order? I  would have done that.

Chapter 10, Planning for Failure, is also very interesting to read, and it sums up why you should use databasemirroring. You should use it because you are a good DBA, that plans for failure in a cost efficient way.

So… conclusion is, that if you want a good book on Database Mirroring, and you have some experience with that area, then the book is absolute worth buying. I think the price is around 50 USD.