SQL Server with Mr. Denny


November 7, 2014  3:10 PM

Recommended reading from mrdenny for November 07, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: sqlpass also known as PASS

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny

November 4, 2014  7:00 PM

SQL Karaoke Is TONIGHT! – Or it would have been, if this post went out on time.

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

I just want to remind everyone that SQL Karaoke is Tonight at Cow Girls, Inc.

Be sure to get signed up and show up for a great time with an awesome Rockaraoke Band.

A great time will be had by all. I can’t wait to see you there.

Denny


October 31, 2014  5:10 PM

Recommended reading from mrdenny for October 31, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

Happy Halloween!

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: MartyCatherall also known as Martin Catherall

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


October 29, 2014  7:00 PM

A Seek Isn’t Always Good Enough

Denny Cherry Denny Cherry Profile: Denny Cherry
Execution Plan, Query tuning, SQL, SQL Server

Index seeks are good, right? Normally they are, unless the index that you are seeking against isn’t the correct index. If we look at Figure 1 everything in this plan looks good, right?

Blog 1 Figure 1

The cost of the index seek against Table1.Index4 is 0%, the IO cost is very low as is the CPU cost. So where’s the problem? The prblem is that the index seek is being executed over 20k times. We can see this by looking at the tool text information about the specific operator which we can see in Figure 2.

Blog 1 Figure 2

Because of this the other operators are executed ones for each row which is returned, causing each of them to then be executed 20k times once for each row from the parent side of the join. We can see this a little better if we look at the exeuction plan in SQL Sentry Plan Explorer which we can see in Figure 3.

Blog 1 Figure 3

Counting the operators and the number of executions we can estimate the amount of IO that this query is going to generate when the query is run. Just looking at the operators on the right hand side of the plan, and ignoring everything else we can see ~320k seeks and/or lookups. If we assume that each seek and/or lookup requires 3 IOPs that’s about ~960k IOPs. That’s about 7.3GB just for this portion of the plan. That doesn’t include work tables, spills, etc.

This specific query is executed thousands of times per hour for 8-10 hours per day. Assuming that it’s just 1000 executions per hour for 8 hours a day, that’s ~570TB of logical IO per day, for one query.

I don’t care how many CPUs the system has, or how fast the RAM is on the server, that’s going to cause some massive delays when running the query. In this perticular case this query is run every time a user clicks a button within the application. This button click would take 6-8 seconds to complete.

So how do we fix this problem. We need to look at the query that is using this plan. We see that the index which is being used is filtering based on a status column, which means that there are 20k rows which have the status set to active. When we add an index on the table, which matches the other predicates of the query (the where clause). When we did this to this specific system, the first index seek which was executed dropped from ~20k rows being returned to 5. The number of executions for the other operators also dropped to a small number (we also fixed the indexes so that they would address the key lookup problems). When all was said and done we reduced the when to running in just a few milliseconds and we went from almost 1M IOPs to several hundred IOPs.

Because of all the extra logical IO that the SQL Server was doing for these queries the CPU was running quite high as well. There were 24 vCPUs allocated to the Virtual Machine, which were running about 45% CPU workload. After adding in the indexes for this query, the CPU workload dropped to an average of 4% workload with spikes to 7%. A massive improvement.

It just goes to show, that just because your query is seeking on your indexes that doesn’t mean that you are done tuning your query.

Denny


October 24, 2014  4:54 PM

Recommended reading from mrdenny for October 24, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database Mail, SQL Server, SQL Server 2014

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: SQLSWAGG also known as Dan King

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


October 22, 2014  7:00 PM

And your SQL PASS Speaker Idol Judges Are…

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

If you are attending the SQL PASS Summit in Seattle, WA in November 2014 you’ve probably heard that the SQL PASS Summit is going to be having it’s first speaker idol with the winner of the competition winning a speaking slot at the SQL PASS Summit in 2015.

In order to have a contest we need judges to tell the contestants what they’ve done right and what they’ve done wrong.

So without further ado, I present the judges for the 2014 PASS Summit Speaker Idol, in no particular order.

Tim Ford
Grant Fritchey
Karen Lopez
Jason Strate
Allen White
Stacia Misner
Joey D’Antoni

These judges will be reviewing the 5 minute session that our lucky 12 contestants (to be announced shortly) will be presenting during the PASS Summit. After each person’s presentation the judges will have a few minutes to provide feedback to the contestant. After all the contestants have given their presentation the judges will select the winner from that round.

I look forward to seeing what our contestants bring to their sessions, and I look forward to seeing everyone at the PASS Summit.

Denny


October 17, 2014  3:42 PM

Recommended reading from mrdenny for October 17, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database Administrator, Git, Meeting, SQL, SQL Server, Tor

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: Ike_Chuks also known as Ike C

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


October 13, 2014  4:50 PM

Your SQL PASS 2014 Speaker Idol Contestants Are…

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

At the PASS Summit we will for the first time ever have a speaker idol contest, where the winner will receive a speaking spot at the next years PASS Summit. There was a great response to the application form to be considered for Speaker Idol, with 40 people applying. Of those 40 I had to choose 12 to make the cut. In no specific order those 12 people are:

Sergey Olontsev
Reeves Smith
Rob Volk
Ed Leighton-Dick
Pieter Vanhove
John Flannery
Luan Moreno Medeiros Maciel
Mike Byrd
Mitchell Bottel
Tim Costello
Jason Carter
Wagdy Ishac

These 12 folks will be presenting at the three opening rounds of the speaker idol sessions. There’s an opening round session on each day of the summit (Wednesday, Thursday and Friday), with four people presenting each day. The contestants are presenting on the following days.

Wednesday
Reeves Smith
Pieter Vanhove
Mike Byrd
Wagdy Ishac

Thursday
Sergey Olontsev
Ed Leighton-Dick
Rob Volk
Tim Costello

Friday
John Flannery
Luan Moreno Medeiros Maciel
Mitchell Bottel
Jason Carter

I wish all 12 of them luck, and I’ll see everyone at the PASS Summit.

Denny


October 10, 2014  5:33 PM

Recommended reading from mrdenny for October 10, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server 2014, Windows Azure

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: TulsaSQL also known as Tulsa SQL User Group

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


October 8, 2014  4:00 PM

Still Time To Sign Up To Be In SQL PASS Speaker Idol

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences, SQL, SQL Server

Speaker Idol 2014 BadgeEver wanted to be a speaker at the PASS Summit? Now is your chance to impress some top notch judges and sign up to be one of the contestants in the PASS Summit Speaker Idol competition. Entering is easy, simply fill out the registration form and if you are selected to be one of the 12 contestants you’ll have a chance to win a speaking spot at the PASS summit.  The registration form is very basic, no abstracts just your contact info and why you want to speak at the PASS summit.  And the price is worth your time, the winner of the competition will has a guaranteed session at the SQL PASS Summit 2015.

You can read more about the speaker idol on the Community Session Room page on the PASS Summit site, and all the official rules are available on this blog post.

I hope to see you on stage in the Community Session Room, at the PASS Summit during Speaker Idol, and then hopefully on stage giving your own presentation during the SQL PASS 2015 Summit.

Denny

 


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: