Category Archives: Thank you

by

Hit me with them good vibes, CTfP is set nice. Everything is so fire, little bit of sunshine!

Cost Threshold for Parallelism (CTfP) is one of my favorite server level settings in SQL Server. I remember the first time I heard this setting mentioned by Grant Fritchey. I quickly hopped on my servers and found them all set at the default (5) and adjusted them to 50 for the non SSRS servers and 30 for the SSRS ones. That was many years ago, but I had kept those numbers in my head because I didn’t know a better way.

Peter Shore gave an awesome presentation on Waits to our user group last week and reminded me of how much this setting can impact tuning. He also pointed us to a fantastic blog by Jonathan Kehayias about how to know the correct setting for your CTfP.

Peter explained that as I ran Jonathan’s awesome query, I would start to see a point in the StatementSubTreeCost column to help me identify the best CTfP for my environment.

My first thought after looking at this query, “I am so glad Jonathan wrote it because with that much XML, I wouldn’t know if it were safe to run without that trust.”

Today, I gave it a go. I kicked off the query and held my breath. Then I started to turn blue and realized this would probably take a minute. It took about 15 minutes and I was happy I didn’t panic at the wrong disco. It runs in a read uncommitted state which prevents blocking (thank you so much!) and I ran sp_whoisactive over and over to be safe.

This is Jonathan’s query, but I recommend you read his article too because there was so much good information.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
WITH XMLNAMESPACES   
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')  
SELECT  
     query_plan AS CompleteQueryPlan, 
     n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText, 
     n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel, 
     n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost, 
     n.query('.') AS ParallelSubTreeXML,  
     ecp.usecounts, 
     ecp.size_in_bytes 
FROM sys.dm_exec_cached_plans AS ecp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n) 
WHERE  n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

After running it, I got back 43 records. I felt that was low until I remembered that our CTfP is set higher than my brain standard at 150. After looking over the results, I felt that 150 was about right for this environment. I didn’t stop there.

Jonathan had mentioned how he uses this query to identify what needs to be tuned, and since tuning is my favorite, I started to play with the queries to get them running better.

Huge THANK YOU to the awesome SQL Server Community that is always willing to share and teach! I love being able to find what I need from people that I trust to make my job easier and I couldn’t do it without all of you!

Hugs and please stay safe!

The song for this post is Sunshine by OneRepublic.

by

I take it all back and want an “Undo”

“You made a mistake, now let it go and move one and don’t make it again”.  It is a super easy thing to say to someone that has just had a bad day. But when you are the one that made the mistake, it becomes a lot harder to move on. You think about it, and assume everyone else is thinking about it. “Look at that loser over there, they brought the whole system down.”  You know they are thinking it.

Last week, I was in the situation of being the friend of someone that made a mistake.  I honestly had forgotten about it within an hour, but he kept bringing it up, over and over again.  All day long I was reminded of what he had done wrong because he wouldn’t let it go.  At the end of the second day, I told him that if he kept telling me he was terrible at his job, I would start to believe him.  I told him to let it go and if nothing else, to stop talking about it because we all had forgotten.  We all had forgiven him, he just couldn’t forgive himself.

This week, I was the one that made the mistake. It was big and everyone saw it.  I really wanted a time machine or an “Undo” button.  Instead, I went home, cried, ate some chocolate and watched cartoons to feel better.  My husband, and biggest cheerleader, asked me, “Are you perfect?” I have been tricked by this one before so I replied that I wasn’t.  He looked at me and said, “Then stop beating yourself up for being human”.  This made me think about how I handle things everyday.  I also realized I had take all the words I had said to my friend and apply them to myself.  I made a goal to not bring up the mistake to anyone after one day.  If they mention it, we can talk about it or I can answer questions about the incident if needed.  Also, at the end of the day, I have to forgive myself and move on.  My entire team was AMAZING and kind when they learned it was my fault.  They knew how bad it hurt to make a mistake and knew I would be my harshest critic. This is not to say that I am taking it lightly, it will be something I don’t do again and am disappointed in myself.   I am really lucky to have an awesome boss that doesn’t hold mistakes over my head.

Every Technology person I have met, has a great story of how they messed up big.  If they don’t have one, they are either lying or don’t push themselves to grow.  I have a whole bag of them, but each one is something I learned and grew from, and thankfully,  made me better.

So here goes, end of the day, time to forgive myself and move forward.

*Hugs*

 

The song today is: Take it all Back by Judah & the Lion

by

Don’t want to look back and say “Could’ve been me!”

Greetings friends! This is a feelings post (you have been warned). Tomorrow I start my new job. Those that see me on a regular basis were shocked to hear I had accepted a new position. It was kind of a shock to me too. I have loved my time with my now previous employer. I was happy, challenged and most importantly appreciated. But then I got a call, an opportunity to work for one of the companies on my list of “Dream Places to Work”. I talked to my boss, he is a coach at heart and told me that I had to at least meet the team and know what I would be turning down or accepting so that I wouldn’t look back and wonder what might have been. I will forever be grateful for the lessons he taught me and the encouragement that he so kindly provided daily.
I did what he said, I met the team, toured the facility, asked questions and finally accepted the offer. It was such a hard choice to leave, if I could work at two places at once, I would have done that. I have been asked a bunch what made me choose to take the offer, so here goes:
1. Opportunity to work with advanced systems, HA and DR that is mature and the chance to learn from it. I won’t lie, I am nervous about learning new stuff, but I also love it, so I am sure it will be good.
2. Opportunity to learn the advanced features of SQL Server that only Enterprise level systems can provide.
3. An awesome team. I have become a bit of a feral DBA, so we will see how this goes. I very much hope that they are understanding of my crazy ideas, weird habits and kind in teaching me how to work with a team again. I also am so excited to learn new things, and be a part of something awesome. I have a lot of information in my brain, but I don’t always trust it, so it is good to have people to bounce ideas off and to gut check my ideas.
4. An awesome boss, his team spoke so highly of him, I knew I would be in good hands. This was also key. I had such an amazing boss already, there is a lot for him to live up to, but seeing the way the team admired and listened to him, plus tease him, helped me see that this could work for me.
5. A chance to learn ASL and make a difference. I have always wanted to learn American Sign Language, now I have a chance. I am super nervous about signing the wrong thing or offending someone (classic Andrea) but I know the only way to learn and get better is to try. Also, I get a sign name, which was kind of the seal on the deal. I am excited to learn what it is tomorrow. Helping people is something that has always been important to me. I make the joke that I am like Mary Poppins in that I only stay as long as I am needed. But making a difference is something that I need. I need to know that I am helping people live better lives, and this is definitely something that helps people.
My princess dresses are also a way to help people. I volunteer work for children’s charities and my personality is a big part of that work. I recently had a discussion with someone about not being just a character. He asked if I should stop wearing the princess dresses because it hides who I am and makes me a character instead of a person. I thought about this question a lot for a few days. I even talked to people about it and asked if they thought that I was more of a joke doing it. But it always came back to me and what I wanted. I feel that me dressing as a princess shows how I feel on the inside but am too shy to show. It is me trying to bring joy to other people. It is a reminder that we all have something special that we can share with the world to make the world better. We can help build up each other and also make the difference in the life of a child. I give a lot of reasons why I do what I do, but at the end of the day, it is because it builds me up so I can keep building other people up. I hope you have felt that love and support and if not, send me a tweet so I can tell you what I see in you.
I spent a lot of time trying to decide what to do. I think a specific song by The Struts made me feel that I had to do it:

I wanna taste love and pain
I wanna feel pride and shame
I don’t wanna take my time
I don’t wanna waste one line
I wanna live better days
Never look back and say
It could have been me
It could have been me

So, tomorrow, it’s me and I am hoping for the best.

by

We could be immortals, just not for long when using a duplicate delete!

Sometimes awesome things just happen.  Today Rob Farley (@Rob_Farley) was helping me with a previous post about my dates table and just as a side note he said, “Oh, let me show you something else cool.” It was really cool and so I asked if I could add it to my blog since I never know when this problem will strike.

I have a table about Super Heroes and their cape colors.  I made a mistake and put Batman in there twice.  But since there is no primary key, how do I tell it which one to delete?  Hero Table

Since they are exactly the same, I can let SQL sort it out. This uses both a CTE (I hadn’t ever used it without some kind of join before today) and OVER which I am learning about.  So cool!

WITH SuperHeroDuplicates
AS
(SELECT *, ROW_NUMBER() over (partition by HeroName,CapeColor order by HeroName,CapeColor) as rownum
FROM dbo.HeroCapeColor)
DELETE
FROM SuperHeroDuplicates
where rownum > 1;



Thanks Rob!


—————UPDATE—————


Kenneth Fisher (@sqlstudent144) also wrote a blog post about another way you can accomplish this task if you only have a few of them to delete.


He has been a super big help to me.  He taught me how to display my code better in my blog so it is easier to read and copy. He also encourages me, builds my confidence and even included a link to one of my posts in an article. It made me feel special and like what I have to say matters to other people.  I love how he comments on my posts and gives me ideas on how to make them better. I feel so lucky to be a part of such a great community of people that are so thoughtful, selfless and giving.  Thanks Kenneth for being such a great example to me!
by

I can’t live without you, I can’t live without you SQL

By now you know I love Microsoft SQL Server. I get a lot of questions about how I got into database and why I got into database. One of the new people I am working with suggested I add the story to my blog. So here goes…

Life should be fun and money is a big part of life. I wanted to be able to work hard so I could play hard, but I also wanted to love what I did. I use the phrase all the time “When it stops being fun, it is time to be done.” Basically, when I don’t love what I do, I am miserable.

My first real job was as an Administrative assistant. I was terrible at it. I didn’t type well, hated writing memos, and dictation was a nightmare for me. The only think I really loved was when I got to do some mail merges (it was tables, I just didn’t know it yet) and changing the copier toner. You should have seen the day I opened the toner wrong and it went all over the mail room. It was such a mess. I would come home crying everyday because I knew I wasn’t good at it. I didn’t have fun with it. I finally decided that if I couldn’t love my job, I would get a dumb job to so I could go play more and call in sick when I needed a break.

I went to work at a call center selling long distance. I was good at it, and it was fun, but still not perfect. The fun part was the computer program we got to use. I learned it super fast and would help the IT guys test new upgrades. They were super nice to me, and one day suggested that I help train new people on the software we had been testing for roll out. After the training, working on the phone was so boring! The IT guys suggested I come work with them as a Business Analyst. I had no idea what that was or what they did, but it was a new adventure so I went for it. They had me learn Access and when I started pointing Access as the production database (because no one had heard of a snapshot reporting databases back then) it would slow the databases down. The awesome IT guys asked me to learn T-SQL. They gave me new tools and showed me a few things, pointed me to a few websites and it was like I had found the best chocolate ever! I continued to learn working as an Operations Admin and really loved learning SQL Server. I even started to dream in T-SQL. You can imagine my disappointment when I would wake up and my house hadn’t been cleaned by a T-SQL Query. Truncate Table dbo.Dust anyone?

Life changes and so did my opportunities, I worked as an Information Manager, Reporting Manager, and finally I got a gig as a DBA. It was one of the happiest days of my life. I felt like I had finally arrived. Little did I know that just having the title, wouldn’t give me all the answers. There have been a lot of moments of joy and may times my sweet husband has found me crying in my closet because I felt like I wasn’t good enough. I can be loved by 99 out of 100 people in a room and will see that 1 person that doesn’t like me or thinks I am not good enough. I will focus on them and try to change their opinion. When that happens, I go back to what my dear friend Tara said when I was first starting out: “Andrea if they don’t like you, it is because they don’t know you.” I think that is true for so many people.

Today as I watched Brent Ozar, Kendra Little and Doug Lane answer questions about presenting, I realized that many of us don’t feel like we are good enough to teach someone else. We don’t have every perfect answer, but that is part of the fun of SQL Server. There is always more to learn, a different way to do something and someone you can help. There will be people who are mean along the way, but if we focus on our passion and forget the rest we can find joy in the work we do. As Pat Wright says: “When you do what you love, you won’t work a day in your life.” My hope for you is that you find the thing that you love and make it work for you, so you never have to work a day again.

May you feel joy in the work you do today!

by

Let’s get these teen hearts beating faster and send me your PASS 2013 pics!

Hi All,
I love the PASS Summit. One of my favorite parts is posting all of the pictures after so I can remember all of the happy memories. I would love if you sent me some of your pictures that I can include on my Magic Mirror Page. I have posted anything that was put out on twitter, but would be happy to credit you if you were the photographer. Thank you to everyone who shared and big hugs to you all!

Here are my pictures and you are welcome to share. You can also see the ones I have collection on the Magic mirrors page.   Thank you to everyone who has let me post their pictures and if I missed a credit on them, please let me know and I will add it.

http://www.flickr.com/photos/29241535@N02/

Enjoy!

Andrea

by

Like some kind of day dream I’ll never forget…I made the “Who would be your top speaker choices?” for PASS Summit 2013!!!!

This is just a quick post to say thank you for whomever put me on this list.  I can’t believe it, and am totally bouncing off the walls right now.  The survey results are here:

http://www.sqlpass.org/LinkClick.aspx?fileticket=u_budRInYfA%3d&tabid=1476

I will submit my abstracts now.  I was debating on if anyone would want to listen to me, thank you for making me feel important!  Keep your fingers crossed that I get accepted to speak. I have a different dress for each day of the PASS Summit, I just have to figure out how to pack them.  I hope to see you all there!

Have a magical day, I totally am!

THANK YOU!!!!

by

SQL Heroes – Thank you for the inspiration

The plan is for this to be a technical blog, but before that can happen there are people that I need to thank for inspiring me to get this far.  There are so many people that I would love to include here, but I had to pick a select few.  To everyone else, *big hug* and thank you for being there for me.  I love how we really do become a big family as we help the community to grow.

Pat Wright – Thank you for pushing me to present at the SLC Users Group #slcsql.  His passion for SQL Server and helping others be better is evidenced in the amount of time he dedicates to our group.  He is always willing to give good and useful feedback and helps everyone to feel welcome at the group.  He also took the main picture on my blog and is an incredible photographer.  Thank you Pat for sharing your passion and helping me feel included in this great community.

Buck Woody – From the first time I walked into one of Buck Woody’s classes he has been one of my favorite people.  He is friendly and so funny.  I know that I probably threw his whole class off by having the “noisy blonde in the front” but he was still kind.  I am always thankful for the classes he posts and his constant commitment to teaching others.  Buck makes people feel like they are the most important person in the room which is a talent that I am striving to learn. Thank you Buck for always being so kind.

Brent Ozar – He may not remember this, but a few years ago I cornered Brent and asked about how to network and present and how to get my name out.  He was really helpful and spent time introducing me to people.  Because of his ability to talk to anyone, I pretended that I could talk to anyone at this most recent PASS Summit and I met so many people!  Brent is so much fun with costumes and that is one of my favorite things so I love that I can now merge my love of costumes with my love of SQL.  He is willing to answer my silly questions and help me better understand what I can do to be better.  Thank you Brent for your confidence and letting me borrow it this last summit.

Matt DeWitt – This list wouldn’t be complete with out my very first mentor. Matt believed in me when no one else thought I could be a DBA.  He would take time to point me in the right direction and help me correct the small and very large mistakes I made along the way.  He has been my emergency DBA contact for when things blew up and I didn’t know what to do.  His patience with me has been limitless and I am so thankful that he didn’t give up.  Thank you Matt for believing in me.

Now I come to you, kind reader.  Thank you for being here and please help me continue to improve.

Have a magical day!

Andrea