What it took to stump the optimizer

Sometime ago I was working with a Microsoft support technician on a ticket related to some database issues on SCOM. The technician wanted to run a stored procedure to clear out some of the backlog we were seeing with a batch job.

When he ran the procedure (via Query Analyser) – we encountered an error as below. Basically an error that is wee bit rare, the optimizer telling us that it is stumped and the query is too big for it to process. The technician moved on to other ways of resolving the issue as fixing this problem involved touching some procedures that came with the product and was beyond the scope of our call. But after we were done, I was intrigued to find out what caused the optimizer to give up.scomoptimiser

I went to Line 177 of the specified stored procedure and found a statement that looked pretty small on the outside:

DELETE dbo.[CategoryOverride]

FROM dbo.[CategoryOverride] CO

JOIN #BaseManagedEntitiesToPurge BMETP

ON CO.[InstanceContext] = BMETP.[BaseManagedEntityId]

The table CategoryOverride had about 300 rows..and  on checking what went into the temp table – it wasn’t all that large either, only around 100 rows. Looking at the estimated plan from the optimizer gave me nothing – a perfectly ordinary plan with an equijoin on a clustered index. So what was causing the problem?

I looked to see if the table CategoryOverride had any triggers on it. None. Then I went on to looking at keys. The table had one foreign key defined on it.

scomo1

I decided to script this key and see what it showed me.

ALTER TABLE [dbo].[CategoryOverride] WITH CHECK ADD CONSTRAINT [FK_CategoryOverride_ManagementPack] FOREIGN KEY([ManagementPackId])

REFERENCES [dbo].[ManagementPack] ([ManagementPackId])

ON DELETE CASCADE

GO

There was my culprit – an ON DELETE CASCADE. So many other tables is this touching? A quick look at the dependancies on this table showed me no less than 14. For one record to be deleted – there were dependancies on 14 tables. I picked one record on the main table and counted how many dependant records were there to clean up. It was about 6000. So the delete statement was far from a benign straightforward delete – it had a huge amount of data to clean up and the optimizer could not generate a plan across 14 tables some of them having more cascade deletes on them.

Moral of the story – never assume the simplicity of a statement based on looking at it, or what the query optimizer tells you. The truth may be much deeper. If I had the choice of fixing it I would remove the cascade delete and go on to cleaning up data step-by-step in each of the dependant tables.

Advertisements

PASS Summit 2014 – People to meet

I am late writing this…between getting ready to take one week away from work, things to pack and keep the home in a reasonably tidy shape there was no time to blog. But I have been gathering thoughts in my head as I went by doing my chores – on whom to meet and what to do. This is my summit #14 in a row, and have never ever regretted coming back here because of the friends I make and the connections that enrich me in every way.

1 Whom to meet – I am one of those many people who learnt the hard way that the community that matters the most are those in your own neck-of-the-woods – people who work at companies nearest to you, volunteer at your sql Saturdays and user groups and so on. So my very first preference is my breakfast with the team from Louisville – I know several of them already and most do not have a blogging or tweet presence. But I do look forward to meeting Dave Fackler – the user group lead after me who has been doing an awesome job keeping up the meetings, and Chris Yates – whom I met on twitter and is growing to be a good member of our local #sqlfamily. Our group is scheduled to meet on Wednesday morning for breakfast at the summit.

2 The non celebrity friends network – I like to call it that – we are people who are regular attendees of the conference – none of us are celebrities/MCMs/MVPs. Some are ardent PASS volunteers . We like to support and offer grounding to each other as we grow in our lives and careers. My long time friend from St Louis user group Julie Bloominquest, SQLSaturday champion Pam Shaw, Microsoftie friend Karthika Raman, other friends like  Meher Malakapalli, Dan Brennan and Adam Belebczuk.

3 New people to add to the network – Some people I have only known via twitter or other people’s blogs – Doug LaneNancy Daniels , Theresa Iserman, Andy Galbraith, Joey D Antoni are some of them. I think I have more just not getting names out yet.

The number of people I have known over the years and want to catch up with is simply too long for a blog post…that is what makes the summit worth returning to every single year.

Some posts you may want to read that are similar are from two awesome #sqlgals – Tamera Clark and Mickey Stuewe.

http://mickeystuewe.com/2014/10/29/who-you-gonna-meet/
http://clarkcreations.net/blog/last-minute-thoughts-for-summit-2014

 

Wishing everyone a happy conference!!

 

 

SQL Saturday 2013 Louisville – A Recap

This year was SQL Saturday #5 in a row for us at Louisville. It was by far the best event we have had and we greatly enjoyed hosting it. Below are some things that worked and did not work this time:

What worked:

1 The location – ideally situated, no parking hassles, walking distance from the discounted hotel, all rooms on one floor, a very friendly and cooperative staff – in short everything we wanted in a location came through this time with this one. Our sincere thanks to Indiana Wesleyan University for hosting us (in particular to Leigh Ann Black) and our volunteer Dave Mattingly for finding them for us. We hope to have the next event(s) at the same location.

2 An enthusiastic crowd – although our turnout was not as high as usual (190 compared to 220+) we found most of the audience interested, appreciative and many stayed until the very end.

3 Our loyal speakers – over time we have grown a crowd of  speakers who attend our every event unfailingly – these include Kevin Edward Kline, Louis Davidson, Allen White, Tim Chapman, Dave Fackler and Craig Utley. Our thanks are due to these speakers (and  everyone else) whose knowledge and willingness to share it made the event a grand success. It may also be worthy of mention that Kevin unfailingly gets voted as the best speaker, and Tim has been the first guy  who made it upto the MCM from this town.

4 Sponsors – We put the sponsors in a separate room along with snacks and sodas. Almost all of them were very appreciative of the audience they got and promised to return again.  It may also be noteworthy that other than Quest/Dell Software and Confio and our local VSoft Consulting, all the other sponsors were first timers for us. Some like Embarcadero, Imperva  and BI Tracks did their first ever SQL Saturday sponsorship with us and wanted to return again. We consider this a huge victory not just for us but for the bigger SQL Saturday  community as well.

5 Post Event party – We did not have  post event parties at older events. The main reason for this is because we are a small volunteer crowd and all of us were very tired at the end of the day. Coordinating  another party was too much of an effort to pull off. This time VSoft Consulting stepped in as sponsors and coordinators, and we were able to host the first ever post event party. While it was not hugely attended, it was a good start and we will surely consider doing this again.

What did not work:

1 Pre Cons – We started with announcing 4 pre cons and ended up cancelling all of them. Three pre cons were cancelled at the behest of the speakers, who  were busy  with other work and did not find attendance big enough to book their time in advance. The last one had to be cancelled because the count of students was too small  for us to coordinate it. This made a few attendees unhappy and we were sorry that we had to do that. But some self examination helped us understand that pre cons were too much effort in addition to the sql Saturday – we are probably not going to have them again.

2 Speaker Shirts – We ended up with some shirts that were not of desired quality. The main reason  was that our order was placed in the last minute with a vendor who did a mix and match of suppliers. Our decision was to go with the Florida based SQL Saturday supplier that was used by many events , from next time.

3 Speaker Room – We had insufficient tables and chairs in the room that resulted in many speakers relocating to the lunch/registration room for chats and discussions . More attention to be paid to this later.

4 Speed Pass – We had a printer with us  for those attendees who did not show up with their speed pass printouts. Surprisingly this time it was quite a large crowd (roughly around 30-40 %). That resulted in the printer chugging along as fast as it could and two volunteers working the registration table non stop from morning until the last attendee strolled in after lunch. We also learnt by show of hands during the raffle that most attendees were first timers. This might be one of the reasons. From the next time we plan to work around this by having something different – like perhaps a $1 fee for those who forget their printouts.

5 Lower attendance than normal – We had about 20% less people than usual. We attributed the main reason for this to be our choice of date for the event – one week away from July 4th and a time when most people take time off for summer vacations. We will work better at finding a better date.

SQL Saturdays are team organized events, they are not one person effort no matter how motivated the organizer is. The success of this event is due to the hard work of our volunteers – Brian Carter, Dave Ingram, Dave Mattingly, Karen Schuler, Deana Ritter, Kenney Snell and newbies Scott Drake and James King. We hope to remain a team going forward and bring more events to the community. Our thanks again to speakers ,sponsors,volunteers and attendees who made it a grand success.

SQL Cruise 2013

I had the opportunity to go on yet another SQL Cruise in 2013. This time our trip was to the Carribean Isles – St Maarten, US Virgin Islands and the Bahamas. In addition to some great opportunities for training and networking the cruise was a relaxing and enriching experience with great food and great opportunities for sightseeing in new land(s). Below is my summary of the experience – if you want to skip my rambling notes and get to what I got out of it please scroll at the way down to the last paragraph 🙂

Day 0- 25th January 2012: I landed at Miami late Friday evening. The cruise crowd met up with local sql server user group members and had a small get together by the pool at the hotel where we stayed.We were also given our sql cruise swag (big bag of goodies from various sponsors).It was a good opportunity to warm up to the cruise and get to know fellow cruisers.

Day 1 – The first day was marked by a breakfast get-together at the hotel we stayed in. It was a good meal and an opportunity to get to know new cruiser Mickey Stuewe and also catch up with Kevin Kline, Bruce Sacrisante and several others. Kevin was also kind to sponsor our ride to the dock in a taxi. The check in was crowded and the ship was much larger than the one I had been in Alaska. We were finally in by noon and met up again for lunch. That was followed by room check in. I took the evening off to nap and settle in – and missed the ‘search the ship’ contest which is a lot of fun. But I simply could not find energy to do it after a long day.

Day 2 – The first day at sea was marked by breakfast followed by Kevin Kline’s class on communication ‘Influence vs Authority’.  Kevin has always been one of my favorite teachers in the community particularly in areas of professional growth and this class was no exception. I learnt a lot about using different styles of communication with different people (so different from the default same cut for all that most techies are used to).I was also impressed by Kevin’s suggestion to keep a printout of various styles so that one may refer to them constantly and make it a habit. That is something I have adopted and has helped in atleast two situations so far.After a great lunch the afternoon session was Allen White’s ‘Powershell 101’. I have attended this session a couple of times before but always find it uniformly interesting and inspiring to get to learn Powershell. This session was followed by  ‘How to be an Enterprise DBA – Part I’ by Sean Mccown. Sean discussed automation strategies, communication strategies and various situations faced by DBAs who handle several servers in large shops. I found it very interesting and useful. The evening was marked by networking hour on the deck followed by formal dinner. The dinner discussion at the table I was centered around mid life challenges with finding jobs, and being a generalist versus a specialist. It provided many insights.

Day 3 – This day was again at sea and marked by a continuation of Sean Mccown’s session on being an Enterprise DBA. It was accompanied by an invigorating discussion on many challenges faced in handling DBA work in enterprise shops. After lunch the class continued with Ryan Adams teaching Active Directory terminologies and usage as applicable to SQL Server. I learnt more on many terms that I did not know in great detail about. The evening was again marked by networking – also called ‘Office Hour’. I was impressed and happy with the fact that cruisers had some time to themselves in the evening to network on their own or explore the huge ship at leisure.

 Day 4 – We docked at St Maarten today. I took a guided tour to a Butterfly Farm followed by a short exploration of the beach and some shopping. The Butterfly farm was the backyard of a canadian scientist – a sunny garden full of flowers, with only a thin mosquito net for protection and hundreds of butterflies gracing the space. It was a truly spectacular and interesting visit. The multi cultural nature of the tiny island (partly owned by the French and partly by the Dutch), and the lifestyle of the local people (very little fresh water or agriculture, tourism main industry) was interesting to observe. After a long day we met up for networking again and then retired early.

Day 5: We docked at St John’s, an island part of the US Virgin Islands today. It was a gorgeously beautiful summer day. I was torn between joining the group on their trip to a private beach or going on the tour I planned — the eco hike of the island, and finally ended up doing the latter. The hike was a short 3 mile walk through the rocky island landscape, with many gorgeous views of the beaches. We also spent two hours at Honeymoon beach, a small beach with smooth white sand and the bluest beautiful waters ever. I regretfully made my way back to the boat around 2 pm. St John’s is definitely among the most beautiful scenic places I have seen in my lifetime. We met up again for networking and dinner in the evening, most conversation centered around our sightseeing experiences that day.

Day 6: This was a day at sea. Our class started with Neil Hambly teaching Memory Management. I liked Neil’s style of teaching and learnt many tips from the presentation. It was followed by Kevin Kline’s session on SQL Server Internals. Although I have attended the session before it was a great refresher on things one needs to know. This was followed by Ryan Adams teaching Policy Based Management. In the evening we met up for a semi formal dinner at the same restaurant. My memories of the dinner center around the gluten free, sugar free peach cobbler ordered by Mickey Stuewe and shared by some of us. It was truly a memorable guilt free dessert experience 🙂

Day 7: Our last day of the cruise began with Allen White teaching ‘Powershell for Performance’. I was very impressed by scripts he used to do performance monitoring and display graphs as reports. The ship docked at the Bahamas for the afternoon. I spent the afternoon on a swimming experience with dolphins at the resort we were close to. It was truly unforgettable but left little time for anything else. The weather at the Bahamas was also chilly due to a storm front coming in. Our last evening was marked by a great get together after dinner with plenty of jokes and good humor. I took leave of the party with many warm feelings, friendships made and gratitude for a great week of learning and networking.

There are a few things I learnt from the cruise. First of them perhaps is that it has given me an opportunity to make friends easily – I am one of those people who is often perceived as a strong introvert, although am really not that much of a shy or reserved person. My personality type is more of an ‘ambivert’ or ‘centrovert’ as they call it – I take time to warm up to people though and am not an ‘instant mixer’. To add to that is all the complexities of having spent half a lifetime in another culture and the strong stereotype around asian women. I would probably not have much to contribute in a conversation that centers strongly around american food, or alcohol or music but I do like jokes of any kind and enjoy conversations where people give room for differences and have time to listen. SQL Cruise allows for that, and has helped me make many good friends among the people I have cruised with so far. I have been enriched by their stories and their experiences and hopefully they learnt some from mine too. I am also always someone up for checking out new lands and exploring new places – it is just the trip for someone interested in all that .I was able to add 3 new places to my list and that makes me greatly thrilled. Lastly is the invaluable experience of technical learning and being around people who have been successful and good at what they do, for extended periods of time. There are many conferences/sql saturdays and various events but none that give you the opportunity to do all of this in one week. 

I would highly encourage any person interested in growing their career in SQL Server to try SQL Cruise. You will want to come back for more, I promise. I want to thank the sponsors – SQL Sentry, Idera, Red Gate and Confio for making this possible for the SQL Community – in addition to Tim and Amy Ford, for their great organizing skills and also for the fun and enriching company of their entire family.

SQL Saturday 154 – St Louis

I attended SQL Saturday 154 at St Louis last weekend. I was particularly keen to attend this event since it was organized by my friends at St Louis (Sanil Mhatre,Julie Bloominquest, Kathie Kellenberger, and others) who have been regular attendees at all of our events at Louisville. It was also their first sql saturday. I arrived late on friday and checked in at Crowne Plaza, the hotel assigned to the event. It was very comfortable,reasonably priced and close to the event location – which helped me not rent a car. The next day morning I finished breakfast at the hotel and walked to the event. It was a short and pleasant walk. I had left my speedpass printout at home but the team was quick to print me a copy, and I was set with my badge and swag bag in a few minutes. I was also able to get a good cup of coffee and settled in for my first session of the day – Dan Guzman’s ‘Maximising SQL Server Insert Performance’ – I learnt a few things particularly on benchmarking and measuring performance. The next session I attended was Arie Jones’s ‘Pwned..Security,SQL Server and you’. AJ is one of those people who can keep you listening for hours with the content and stories – and he was totally in his element today. I greatly enjoyed the presentation and learnt many new things about hacking techniques and security mechanisms to prevent them. After this session I had to head back to the hotel to check out, and returned in time for lunch. There were no lines and was able to get my sandwich easily – it was recommended to sit in classrooms, eat and listen in on vendor presentations. This was a great idea but for those of us who could not attend the post event, this was also the only time to socialize. I spent this hour socializing with many people I knew, and also with vendors. The only post lunch session I was able to attend before rushing to the airport was Kevin Boles’s ‘ Common TSQL mistakes’ –  another brilliant presentation packed with tips for every day use. I thoroughly enjoyed the session and left the event very satisfied with a good day’s learning.
As an organizer of 5 sql saturdays I happen to know the many pains and huge effort to pull off an event – and the effort put in by the team was obvious. Finding a space to host such an event for free is a huge challenge, but again bigger spaces are the only way events can grow – so perhaps a bigger space would help next time. Also, as Kendal Van Dyke pointed out in the SQL Saturday news letter – the most common feedback events get are around signage in and around the building. We learnt to make investments in signage and reuse them every year – something I have never regretted doing as an organizer – this could be considered.  A boxed lunch with all sides in one box would be better – in return for $10. Other than these small issues I felt the event was very well organized and look forward to attending the next one!!

Thank You To an Awesome Sponsor

There are 3 kinds of people who keep our user groups and sql saturdays going. They are loyal speakers, sponsors and volunteers. We took extra care to reward our speakers this time – particularly those who have been returning to us every year for the past five years. For sponsors – it is difficult to offer any ‘rewards’. Sponsors are the reason behind our free lunches, our swag, our giveaways…This post is a humble thank you to one of the best sponsors there is. Idera Software has kindly sponsored our user group lunches for 4 years now, and all our 5 sql saturdays as Gold Sponsors. Without them it would be really really hard to keep both the user group meetings as well as sql saturdays going. We are proof and testimony to their commitment to community and we greatly appreciate it. This picture is our volunteer team expressing our thanks.On behalf of all volunteers and attendees of Louisville SQL Saturday – THANK YOU IDERA, YOU ROCK!

Meme Monday – February – Working with deadlines

This is in response to Thomas LaRock’s invitation for Meme Monday:

The strategy that works best for is to break the tasks involved into as much detail as possible…so that it makes the final deadline seem far less threatening. I try not to ponder too much on the final product, but work the detail diligently and try to make progress that way.  As a child my father always gave us the example of ants building an anthill – each of them does its job of carrying little pieces of mud/rock, they do not waste time contemplating the big end product. But finally it gets done. I begin with the end in mind, as the saying goes, but like to forget the end until it actually gets close and focus on the detail.

I also attempt not to commit to unrealistic deadlines or set such deadlines for myself. This is something I have learnt the hard way..being a very detail oriented type A person I have set several unrealistic deadlines for myself, and learnt the hard way that they are not doable. There are two issues with setting a realistic deadline – one is having a hard conversation on how much I can do. If my deadline is to attempt a certification, say, how many hours of study can I do? At the end of a long working day, with all home chores done, realistically, am lucky if I do an hour of focused study. If I get too optimistic and make it two I usually wind up getting stressed around finding the two hours and not doing any at all!!  (For work goals it is more around scheduling all the other tasks and explaining honestly to my boss – sometimes he takes something off my plate to help me find more time). The second thing is how much I can do in the given time. In one hour of study time I can probably read a chapter of a book really well..or a portion of the chapter, depending on complexity of the book. Some of this depends on intuitive feel, some of it is plain common sense. So with a good combination of how much i can do and what i can do i can arrive at a good estimate. After that it is just being disciplined and diligent about it. There is nothing better than the good feeling that comes with getting to one’s deadline on time, and having the task done well..that feeling itself is a great motivator.

Previous Older Entries