DMO Roulette #1/11 – Introduction

Let’s have fun!!!!

All my life, I have dreamed about russian Roulette, being the last mand standing, winning the big price and ride of in the sunset with the big breasted woman.

Unfortunately I don’t dare to play russian Roulette, and since I am happily married to my wife, then I don’t need a new woman.

But I want to play Roulette, so I have come up with my own version of it. Over the next 10 days, I will pic a random Dynamic management Object, and then I will give you a short introduction to it.

To make it a little bit more interesting, then I will only do it within the main areas of DMO’s, and therefore the query I will execute every day, will looks like this:

select top 1 name, type_desc
from sys.system_objects
where name like ‘DM_%’
and
(
name like ‘dm_OS%’
or               name like ‘dm_Exec%’
or               name like ‘dm_Tran%’
or               name like ‘dm_db%’
or               name like ‘dm_io%’
)
order by newid()

Rules are very Simple: Every day I run the query and then I have 45 minutes to write what I think is important about the selected Dynamic Management Object.

So… See you all tomorrow.

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).

Executionplan with crazy percentages

I got the following executionplan from a customer (IT IS AN ESTIMATED EXECUTIONPLAN)

2013-12-09 00_40_56-10.5.1.51 - Remote Desktop Connection

and as you can see, then the sum of the percentages is more than 100%. That should be impossible, but when I started to look into it, the reason was quite obviously:

The shown executionplan shows the complete SQL statement, but only a part of it is executed, and the sum of the executed part is 100%. So lets look into the SQL statement (you must have the Adventureworks2008R2 installed)

if (select count(*)
from Person.Person)=1234
begin
	select *
	from person.person
	cross join 
	sales.SalesOrderDetail
end

There are 19972 rows in the Person.Person table, so we will never execute the cross join between the begin and end. Therefore the 100% in the executionplan will only be calculated on the Select count(*) from Person.Person.

Now we know how much CPU+IO the executed parts takes (hover over the Cond With Query Cost operator). On my server with my databsae it will take 0.0787.

If I hover over the Nested Loops with a cost of 12866355% that wil NEVER be executed, then the operator cost is approx. 10127,94. Now the formula is 100*10127,94 /0.0787 which approximately equals 12866355%.

So that is the reason for the strange percentages: 100% equals the executed part and then we divide the operator cost for each “Non Executed” operators with the total cost for the executed parts.

What happens if the IF was true so that the cross join will be executed?

Well, then we get the same result.

If I execute the query then I get the following:

 

2013-12-09 00_52_50-10.5.1.51 - Remote Desktop Connection

Strange. But basically I think I understand why the estimated executionplan acts as it does.

Imagine a SQL Statement like this:

if (select count(*) from Person.Person)=19972
begin
	select *
	from person.person
	cross join 
		sales.SalesOrderDetail
end
else
begin
	select *
	from person.person
	cross join 
		sales.SalesOrderDetail
end

Now the Query got more complicated, but if the sum should get 100% no matter what, then it would also be wrong because we will never execute all code, so I like to conclude:

  1. The code that the Optimizer is 100% sure to execute will calculate 100%.
  2. All other path’s of the code will related to the “100% sure exetued 100%” so that you have an idea about how long time it takes.

Please challenge me on this.