Wednesday, September 24, 2014

PASS Elections 2014: my thoughts

I voted.

I think you should vote also. Go vote.

See ya at summit!

Monday, June 30, 2014

On Speaking

Back in 2004, I attended my first PASS Summit conference. I was not as integrated into the community back then, and only knew a few folks. I met some great folks on that adventure, some i still know today and some that have become quite celebrities in the community. I attended because a local user group leader encouraged me to attend, as he encouraged me to do a great many things. One of which was to speak at the local User Group. I was frightened, and exhilarated.

Having grown up in a religion that encourages its members to speak, I have had various opportunities throughout my life to stand in front of an audience and speak. I did so as a youth many times, also as a missionary, and as an adult. One in particular will always remain fresh in my mind. Usually as a youth, after speaking to the congregation, you get the 'good job' and 'atta boys' doled out by members of your congregation. They do it to make you feel better, since you probably looked terrified while in front of them. I received some of these while at church. But after church, members called me at home to congratulate me. This was when phones were not as easily accessible as today, so it seemed like an extra effort to call a child and buoy them up for a job well done. I was elated. And terrified. It meant that I had a knack for speaking, that I didnt appear to be as terrified as I felt, and that meant that I oughta continue practicing the talent, or it would become latent. So I did. I never really shied away from opportunities to speak.

Fast forward to the User Group leader who asks me to speak. I say yes. I give it a shot. I am thrilled at being selected to speak, and humbled at the task, as well as terrified. But I pull it off and get compliments. Not overflowing, but not under. A sweet spot. So I repeat at the next chance.

As my career marches on, more opportunities have presented themselves. I once spoke at a Microsoft event to a large room full of attendees, following the lesson notes they had outlined, teaching about Analysis Services. I had never used it before for real, but here I was teaching it to a crowd. I did OK. Not great, but not bad enough to recognize in myself the lack of this talent, or the subsiding of talent such that I should quit. I did OK.

I remember being asked to fly to Arizona to record some sessions for a training company. I was thrilled again. Humbled. Worried. Scared. Thrilled. Me, they picked me. I did it, and it was OK. I did good enough to be asked back on multiple occasions.

When it came time for our own community events here in my home state, I volunteered to not only speak, but to help out organize and do tasks and whatnot to help the event be accomplished. I have done this every chance I get, and have even volunteered at regional events. At this same time, I volunteer in various capacities in PASS. Each time I get to do a task, a part of me is thrilled that I was picked. I was allowed to give some of me to something bigger than me.

After a while, I notice that I may be expecting to be picked, and when this happens, I need to remind myself of the beginnings, and those feelings of humble come back. The scared and thrilled are ever present. But remembering to be humble at being picked is key. Then the level of thrill can remain high when you are picked.

I have never spoken at the PASS summit, though I have heard some members respond in surprise to this knowledge, as they coulda swore that I had. Nope. I have submitted a couple times, but have not been selected. I have been selected to SQL Saturdays and Code Camps. Once I have been asked to not speak at an event I had submitted to. Besides PASS Summit, I have been accepted to every even I have submitted. Does this make me proud? Yes. But does it mean I deserve it next time? No. I try to temper the pride with the humility of being asked to speak, because remember, that allows the thrilled feelings to be elated.

So, in a word, get over yourself. If you are not selected, you will get over it. There will be another opportunity. If you are selected to speak, awesome. Accept it with humility. Be proud you were selected. Prepare to do as good a job as you can do. Do a better job than you did last time you were given this singular honor. Be scared and thrilled. Realize that no matter how much 'celebrity' you think you are, you are just a person. Treat each opportunity like your first and react accordingly. Don't ever loose that excitement that the first infused into your being. And be scared. And be thrilled.

Monday, June 23, 2014

Can that report run faster?

We have a scenario where a report is causing us some grief. Let's call this report 'Revenue'. Let's assume that this report is shown in a 'Leadership' meeting where revenue is an important topic. Let's assume that the presenter in this meeting shows the 'Leadership' team this report by launching it in Report Manager, along with several other reports. All these reports will be used to discuss points of interest in said meeting.
So put yourself in the seat of one of the observers of said report. It is kicked off, and you watch the little spinny do its thing, while you wait for this report to materialize the data and render it visually for you to consume. You wait. And wait.

How long do you wait before you open up your device and kick off the same report to 'see if you can run it faster' than the presenter? I'm betting a few minutes, maybe 4-5. And if you do that, who else in the room is doing the same thing? Let's assume more than one individual does this, which can cause the other executions to slow, or at least causes your execution to slow.

This scenario played out a few weeks ago. Presenter kicked off report, which timed out after 8 minutes or so, but at the same time, 4 other individuals also kicked off the report. It was in the middle of these executions that I was contacted to look at said report, and see if I could make it faster. Later that day, as everyone else stopped executing it, the speed of execution dropped to a couple minutes, under 2 minutes. But during the peak of multiple executions, it was taking 8 minutes. (also, that morning, 4 times the normal quantity of reports were being generated. 4 times. as in 4000 reports, instead of 1000 reports)

Over the past few weeks, I have been digging at this report and looking for solutions to its speed issue. It seems that when normal circumstances prevail, this report can retrieve and render its data in under a minute. That seems acceptable. But under load, it takes time, and causes frustration in the team that needs to consume it.

This report is written in SSRS and hits our production reporting system that is a SQL Server 2008 R2 system that utilizes several FusionIO drives to speed things along. It is a nice machine and is much faster than our old reporting system. The data seems to be properly indexed. The queries seem to function as expected. So I jumped into caching and snapshotting to investigate alternatives to 'speeding up the report'.

Let's look at the past execution of this report to see what we are dealing with. Looking into the ExecutionLog view allows us a view into what has transpired with this particular report. I format the time to perform the tasks, and convert it to minutes, so i don't have to think too hard. I have to join to the Catalog to limit the results to the actual report that I want to see. And for sorting, let's order it by the time the report started in descending order to see the latest executions to the older ones.

  2.     c.[Name],
  3.     (TimeDataRetrieval + TimeProcessing + TimeRendering)/60000.00 AS [Time],
  4.     [RequestType],
  5.     [TimeStart]
  6.   from ReportServer.dbo.ExecutionLog el
  7.     JOIN ReportServer.dbo.Catalog c ON c.ItemID = el.ReportID  
  8.   WHERE c.[Name] = 'revenue'
  9.   ORDER BY [TimeStart] desc;
This query shows me a lot of good information. I can see how many times the report was executed, how long it took, and so on. When I did this for the time frame of issues, I see exactly what I expected. A ton of executions taking a long time, and many many within the same time frames. I can expand this query to add other fields, like UserName to see who executed the report, status to see success or otherwise, among other. Use the fields you want to tell the story of the data. My story showed me that a few individuals had executed the report repeatedly and upon failure, attempted again, and again. As I mentioned, later in the day, it sped up, once everyone stopped hitting it incessantly.

I went to SSRS Report Manager to drill down to the properties of this report. On the 'Snapshot Options' tab, I enabled 'Allow report history to be created manually' and 'Store all report snapshots in history' checkbox items. In the 'Report History' tab, I saw nothing, as expected. I planned on revisiting this tab to see the progress of adding caching and snapshotting. On the 'Processing Options' tab I chose 'Cache a temporary copy of the report. Expire copy of report after a number of minutes:' and chose 240 as the minutes. I also took this opportunity to select the 'Do not timeout report' option, hoping to allow the slow report to simply continue, instead of eventually dying, which prompts others to try the same thing. And fail.
The last option that I did was to setup a cache plan on the 'Cache Refresh Options' tab. I chose 2 plans, after careful review of the executions seen from the query above. This query helped me see the times that were executed, and when a cache would be most profitable to this report.

With these 2 cache plans created, and the rest of the options selected, I was happy that the report would run better next week. As Monday rolled around, I was interested to see the results. So I grabbed the above query and executed it mid day Monday, only to encounter report executions. Meaning I could only see individuals that had fired off this report, and the time it took them to render. (which was fast). I was unable to see cache executions. I would have thought that there would be an execution at the times indicated in my cache plan, and I could see how long those took. But this query failed me. To the twitterverse!!!

After receiving some help from my #SQLFamily via @markvsql, @jasonhorner, @RateControl, and @tameraclark, I was able to create this query.

  1. SELECT 
  2.     ItemPath,
  3.     (TimeDataRetrieval + TimeProcessing + TimeRendering)/60000 AS [Time],
  4.     [RequestType], 
  5.     [TimeStart]
  6.   from ReportServer.dbo.ExecutionLog3 el
  7.   WHERE [ItemPath] like '%revenue%'
  8.   ORDER BY [TimeStart] desc

                  This uses ExecutionLog3, which has different fields in its view. Happily, some of these fields are exactly what I need. I can add into the where clause options to show 'Interactive' (ran by a human) or 'Refresh Cache' (ran by the caching mechanism) and see how long each took.

                  Looking at these results showed that the cache took longer than the interactives, as expected. But when you compare the 'Interactive' times to pre cache times, the results are astounding. The caching takes a chunk of time, and then subsequent executions are tremendously smaller in execution time. Meaning, let the system create the cache on its cache plan schedule, and then the humans can simply run the report, and get the cached results quickly. No more multiple executions. We went down from 28 executions that never seemed to complete, to 4-5 that are a lot faster.

                  After a few Mondays, and reviews of the resultant data, I believe I have narrowed it down to the appropriate options. I kick off the cache about a half hour before the meeting on Monday morning, and then again at noon. This allows for caching of the report data twice on a Monday, allowing the 'Leadership' meeting to have quick report executions, and cached data in the morning, and the noon cache allows for other teams to utilizes a fresher cache in their afternoon meetings. But as soon as the cache reaches 240 minutes, it expires, which reverts users back to non cached data retrieval. The rest of the week the report should run quickly in the random times it needs to run.

                  Point of this story is that it is important to use data to make data faster. And not a single solution is better than the other, and the appropriate solution may be something you are unfamiliar with. I did not know or utilize caching in reports much prior to this. I would have normally added indexes and tweeked the queries used to perform the data retrieval, trying to squeeze all the juice out of them that I could. But a simpler solution was to understand the use of this report, compare to other non peak time of use, and adjust appropriately.

                  I believe that this solution is valuable, and suits my needs, and satisfies the users of this report. Thus I share it with you in the hopes you too can learn as I did.

                  Thursday, April 17, 2014

                  SQL Saturday #295 Las Vegas wrapup

                  (written while disconnected in the southern desert of Utah on April 8th)

                  This last weekend found me travelling to Las Vegas to help celebrate its first SQL Saturday ever. The team of Stacia Misner, Jason Brimhall, as well as a slew of others, were able to stand up a great event that drew a descent opening round as well as great support from local, regional and remote speakers.

                  As it is only a few hours (6) from my home, we decided to go as a family, spend some time in Vegas, then start a Spring Break week vacation afterwards. Various options were discussed in my family, and it was decided that we would go camping and dirt biking after an extra bit of time in Vegas. This requires us to load up anb haul our camper somewhere. We chose to camp and bike in St. George, which is partway to Las Vegas. We found a location where we could drop off our trailer and leave it for a few days while we spent time in Las Vegas.

                  The fateful day came when we left to make our way south. With the trailer loaded and ready we took off around 10:30 am, with 2 planned stops that would consume an hour or so, extending the trip from 6 hours to about 7 hours.

                  The first stop occurred as planned, and I even saved $20 I wasn't planning on. Yeah, its always nice to save money. Around 11:30 am we left that stop and headed south. We passed a town called Nephi that is an hour plus from our home. As we pass this town, there is a large gap of towns that usually means you are really on your way. The trip underway, we get excited to be going. Its about this time that I hear a small explosion from the left side of our vehicle, and am alerted to flying debris from the mirror. Having never experienced a blow out on a tire, I was surprised at how I simply knew what had happened. Blowout. Yeah. less than 200 miles from our home and at the beginning of the tirp, boom. I pull over semi gracefully and get stopped to  inspect the damage. As I exit the vehicle, I grab my nexus 5 cell phone so I can take pictures, if needed. As I exit, the phone slips from my hand and lands screen down on the pavement. It looks fine, but I can tell that it just added to the cost we had just had shoved down our throats with the blowout. Sure enough, the screen was cracked before I could even go see the damage behind us. It turns out that only 1 of the 4 trailer tires blew, which explains why it was easy to pull over after I saw the tire pieces in my mirror escaping their previously contained existence.

                  After several phone calls, we found a tire dealer that could send out help. When Ben arrived, he was able to pull the borked tire off our trailer. While he was there, I inquired as to his expert opinion concerning the other tires. The prognosis was rather negative, and while he returned to his shop to fix the 1 tire, we deliberated what we should do. Several options lay in front of us. Replace the single tire, replace the other partner tire on that side, replace all 4, take the trailer back home, continue on with some or all tires replaced, and so on. Ugghh. When Ben returned with a brand new tire, we were able to get it mounted easily. We had decided to return to Nephi and get all 4 tires replaced. The tires were from 2007, and though they didn't have a ton of wear and were actually had plenty of tread left, there was evidence of age and cracking visually. The time had come, we just hadn't planned on it. The tire store was amazing and got us fixed up really quickly and on the road again. 2.5 hours delayed, but on our way. Let SQL Saturday trip begin, again.

                  Back on the road again, with stress levels high, we got to experience them even higher as the winds blew us all over the road for the next 4 hours. At some points during the trip I had to remove my hands from the wheel and shake them out because of holding on so hard to counteract the pull of the wind gusts. Stress was a constant companion. Eventually we made it to St. George and were successfull in dropping off the trailer at storage. Phase one of the trip was complete. On to Vegas, just hours and hours delayed.

                  We arrived in Vegas about 20 minutes after the speaker dinner started. I went in to the dinner to grab some keys to our hotel (SQL Solutions Group had a suite of rooms rented for us and had checked in already). I intended to grab and go, but ended up hanging out a bit to talk and say hi to #SQLFAMILY members before getting my family to the hotel. After dropping the kiddos off at the hotel, my wife and I returned to the dinner and had a great time catching up with folks. It is always fun to introduce my wife to the members of my other family. The dinner that was thrown for the speakers was delicious. The gifts given to speakers were spot on for the locale. A deck of cards with the SQL Saturday Las Vegas logo, a handful of similarly logoed poker chips and a usb jump drive shaped like a poker chip. Turns out my cards have 2 5's of clubs and no 4 of clubs. But #firstworldproblems aside, I love the cards and enjoyed playing card games with my family and with them already.

                  At the dinner, I was able to meet and talk with several of the volunteers that would be helping out in the morning. Questions were answered, tasks discussed, and times set to meet in the morning to get it going.
                  The next morning I awoke late, but was at the facility a little after 7am. I grabbed the signs and went back out to the various roads to put up the directional signs to help attendees find the facility. The morning in the desert was glorious, warm, wind blowing slightly, and bright blue skies. ?I ran around with my truck and the signs and the radio blaring, pounding the sign holders into the bedrock around the area. Once complete, I returned to the facility and said more "Hi's" to folks as well as started doing various volunteer tasks. The morning got going with a couple of hiccups, mainly the printing of SpeedPasses had some issues. The rooms were up on the third floor and we found attendees had an issue initially getting around and finding the rooms and getting around. Once the layout was understood, folks had less of an issue with it. It turned out that the printed map had a typo that also lent to this confusion. It was a problem, but not a large one, and once communicated out, it was overcome. The location of the vendors was not ideal, and lent to light foot traffic to visit the vendors. This should be remedied next time if this facility is reused. The location for lunch was ideal as it was the largest room and also served as one of the session rooms. So once lunch arrived, attendees and speakers knew where it was and easily found their way there. Lunch had been catered and the catering folks did a great job keeping the supply available as the attendees flowed thru to get food. The food was good and probably one of the best lunch selections I have had at an event like this. There was a sponsor for lunch, which may have helped it be such good food as well as catered. I likey.

                  The facility was a bit odd, and one of the rooms had almost lounge chairs to sit in instead of the normal desk and office type chairs. The speaker stations were really high tech and had an ipad controller to select all the options for display. Initially I thought that this may prove to be a problem as each new speaker took the station, but it turned out to not be an issue, as the previous speaker had already selected the options and left it ready to go. The speaker ready room was large enough for a few speakers to get ready in, and small enough that all the speakers were unable to hang out the entire day there. This is a good thing, as it forced speakers to get out and around the event, instead of holing up in the speaker room. It was also centrally located and lent to a good flow. It's nice when the speaker room is not off in a far off location of the facility.

                  When the last session was completed, we all adjourned to the lunch room and got to listen to a sponsor session from Microsoft. This was a bit odd, and luckily was not the entire half hour as previously mentioned. Since it was a large room, folks gathered in the back of the room and started talking. These conversations became loud enough that the attendees actually trying to listen started turning around and giving 'that look'. Truth be told, i was one talking and received 'that look'. Suffice it to say, the location in the big room and the gathering of us all there, was not ideal for an after session. Maybe it would have been better to let folks know to convene in the big room at 5:30, after the sponsor session was over, instead of 5:00 and talking. But its never a perfect science.

                  After the sponsor session, the attendees were able to receive the large prizes from the sponsors. Each sponsor was given a moment to talk and give out their prizes. A lot of folks were able to receive some great prizes and they all looked happy. Once completed with the giveaways, Stacia and Jason were able to sell the event for next time, as well as PASS and the local user group. Hopefully this activity will have instilled in the local populace the excitement that we have all felt, and propel them to continue building these communities, networking with locals as well as others in the community, and help themselves and their careers.

                  There was an after party and though not everyone attends these, enough folks did that we were able to hang out  a bit and ultimately say our good byes to the #SQLFAMILY members we did get to see. Hugs, shakes and networking completed, we went our separate ways. Some returning to hotels, others going to the airport, and still others heading out to be entertained by the Vegas nightlife. Thus bringing to and end, the event, the learning, and the networking. Another one in the bag.

                  Thanks to all organizers, volunteers, speakers and attendees. Let's hope that this is the start of many more to come.

                  Wednesday, April 02, 2014

                  SQL Saturday #295 in Las Vegas – April 5, 2014

                  SQL Saturday #295 in Las Vegas – April 5, 2014

                  Tuesday, April 01, 2014

                  SQL Saturday #279 Phoenix wrapup

                  This last Saturday I had the opportunity to go from 29 degree mornings to the Arizona desert and 70 degrees during the day. Looking at it that way, it wasn't much of a choice. The weather was nice and very enjoyable. Thanks for having it. If whoever was in charge of that weather, please send some my way.

                  But back to the event. I took off from Utah around lunchtime and flew down to Phoenix with Randy Knight and Jason Brimhall of SQL Solutions Group fame. We had an uneventful flight down, with rough weather on both takeoff and landing. Landing in Phoenix with clothing on the bottom halves of my legs seemed like a bad idea and was remediated quickly. The evening found us at a baseball game with the rest of the speakers. Amy Lewis and crew were able to acquire tickets to the D-Backs vs Cubs game. We also got vouchers to get food in the local offerings. After standing in line for 15 minutes and waiting for a my food for a half hour I had a descent burger with some incredible garlic fries. They tasted amazing. And hours, even days later, they still tasted great. Just kidding, but they did hang around for quite a while. I enjoyed them for most of the time that they were with me. The game was disappointing if you were a DBack fan, but it was still enjoyable, with some great plays and tense moments, as well as some great time to mingle and talk with all the folks attending the event. It is always like the first day of summer camp when you get to see all your friends and catch up with them, telling stories, comparing, sharing and simply enjoying each others company. Mixing and talking to everyone, watching the game, repeating, I think that we all had a blast. With the game over, folks broke up into groups and disappeared, the evening almost over and the next day looming.

                  Luckily I was able to return to the hotel and not have to dig into and demos or slides, as my presentations were ready to go. I know that some were not so fortunate and went back to do more work on session prep or even more real work. Saturday was rapidly approaching and i am sure we all made it there in different way. But collect again we did, the morning of Saturday at the event. More mixing and mingling and reuniting with old friends not seen the night before occurred. The morning started with some great bfast snacks and a keynote in a large windowed room with garage door openings and massive fans. One can imagine the doors opened and the fans pushing the air around. However, since the weather was just nice and not insanely hot like Phoenix can be, the room was nice, with the blue skies glowing softly outside. 

                  After the keynote, folks traveled to 3 different buildings on campus to attend from 11 distinct sessions. I was up for the first session hour and had the opportunity to speak on Release Management. The session time came and I only had 2 attendees and the room monitor that was forced to attend. We waited for a bit past the start time and a few more folks came in, bringing the total up to 8. So an intimate session it was, with discussion and sharing, and me blathering on about my experiences and ideas on how to keep change from impacting us negatively in our production environments. I believe that the session was well received and I enjoyed giving it and interacting with the attendees. 

                  More sessions came until the lunch hour. We reunited back in the large room with the glass and glass garage doors and massive fans. Pizza and pasta and salad was on the menu as well as networking and story telling. Food was consumed and connections made. SQL was discussed and plans were made. 

                  Sessions resumed in the 3 locations and more learning was presumably acquired by the attendees and speakers. I got to listen to parts of 2 sessions in the afternoon and learned a few things, and need to make follow up to learn more. I love being able to have access to such great knowledge and experience. Keying into the right people and making those contacts to forge even stronger relationships through learning is what these events are about for me. Sharing and learning.

                  The last hour of the event found me in a room with nearly 20 other folks discussing the gloriously exciting topic of Documentation. One would not think that I could get even my closest friends tricked into coming to my session of documentation, let alone 20 strangers, but alas, this is what happened. After a lively discussion that strangely started out with an explanation of my name and ended with folks wanting to get copies of my documentation, I finished the session. 

                  With the session ended and the final group session beginning, the attendees that were left were energized at the chance to receive presents. We went to the keynote room and swag was given out to a ton of folks. Books, licenses and more was given away. 

                  Cleanup started, goodbyes were hugged out, and eventually attendees, speakers and volunteers finished and parted ways. Some to the airport, some to hotels, some back to their homes and some went to the after party at a local watering hole. More networking and stories occurred, which eventually led to more hugging and departing friends. Some of us were talked into going to a Packers Bar to witness the lovely Tim Mitchel and Kathy Kellenberger sing karaoke. After some descent performances and some, well, other performances, we were treated to our favorites sing 'Babe' to the packed bar. After that, more hugs and goodbyes. And it was over.

                  I enjoyed the venue. The keynote room was large and spacious, with views to the outside making it feel larger than it was. The speaker room was spacious and allowed folks to spread out and practice their craft. There was also a speaker lounge just outside the serious room where seats enough for a few more speakers to kick back and chat. One could wander in here and always find someone to talk with and scheme. The rooms were not all together, which i heard a few 'complaints' about. In quotes because they were not hearty complaints, more a discussion. But having them grouped together as they were was helpful. one could go from 1 to the next easily. Once inside the rooms, with the doors even open, it provided enough space and quiet to perform the session well without outside interruptions. Having the sponsors on the way to the sessions outside one of the buildings was a great idea, letting folks chat up the sponsors without impacting any of the rooms or hallways. And being outside was glorious. The parking was plentiful and close to the venue. The venue was close to the freeway and easily accessible. I really have no complaints about this event that would even help it be better next year. It was well done, the volunteers kept the machine running along well, and you could tell that they had had some practice with previous years, and this year it just coasted along smoothly, giving everyone a great experience.

                  My thanks to the sponsors and volunteers that put the event on. Thanks to the speakers that came from near and far to help with the event. And a very special thanks to the attendees for giving up their Saturday to come out and get some SQL learning on.

                  Cya next year!!

                  Wednesday, March 26, 2014

                  SQL Saturday #279 in Phoenix – March 29, 2014

                  SQL Saturday #279 in Phoenix – March 29, 2014