Category Archives: SQL Server

Today is Thursday, October 27th already. For some of us the summit begins monday – with precons and PASS Volunteering related meetings on Tuesday. For most other attendees the first day was Wednesday.

I arrived in the afternoon on Sunday with six other friends from Louisville,including my good friend Chris Yates. I have been travelling to the summit 11 years now – this is the first year that I had so many co passengers from my town heading there. I plan to write a blog post entirely on that subject. But was proud and happy to see attendance and interest growing from our small town. Following arrival I met with one of my favorite friends in #SQLfamily, Arlene Rose – we went shopping at Pike Place market. I have been going to Pike place for many years now – was a bit sad to see a few of my favorite stores gone. They included a tibetan buddhist store selling masks, a consignment store selling gently used scarves and jackets, and a herbal store.It is a way of life and i hope they are well somewhere.

Monday, Day 1: I went in to attend Itzik Ben Gan’s pre con on Advanced TSQL. I have not been attending precons after they stopped recording the sessions – since it was too pricey and was easier to find cheaper equivalents at sql saturdays and other places. But Ben-Gan was someone you really want to learn T-SQL from, and he did not teach at too many other places. The class was worth every dime. The session was packed with tips and tricks and presented in elegant, simple, easy-to-understand ways. I greatly enjoyed it and would highly recommend it for anyone considering it next year. In the evening I had dinner with Chana Cohn, one of my old friends from my days at Kindred Healthcare. We had a great evening catching up.

Tuesday, Day 2: Tuesdays are usually reserved for PASS Volunteer related meetings – my meeting started with rehearsing for the keynote for wednesday. (More on that below). It was fascinating for me to witness the amount of work that goes on behind-the-scenes for the keynote – from staff at PASS and volunteers including directors and several others. We as attendees and even as volunteers at other levels do not normally see this – we owe them a  thank you if we enjoy a keynote, and not just for the content. It is a ton of hard work to pull off. Following this we had the yearly meet-up of SQL Saturday organizers. Many items were discussed including funding from PASS, anti = harassment policy, website changes, sponsorship changes and so on.Overall it was a productive and informative meeting. In the afternoon we had a meeting of Regional Mentors. Could not attend the meeting for Chapter leaders as i had some work to take care of. But all meetings were useful and was great to meet with volunteer friends you don’t get to see otherwise. Attended the opening ceremony in the evening – which had some really good food options for vegans and vegetarians. I got my fill of dinner here and decided to pass up on the volunteer party – given the weather and the distance, a good six miles from where I was. Retired early since i knew next day was a long one.

Wednesday, Day 3: I normally do not dress up for any PASS event days. Just wear one of my many SQL Saturday shirts – and jeans to go with it. But today was special – today was my day as I had won the PASSion award – this news was communicated to me a month ago but was asked to keep it secret as NDA information.The PAssion award is a true honor – it is the highest award in the PASS community for service to the community. It is via nomination from fellow community members and approval by the board. I was humbled and honored to receive it. After a bit of make up and a tiara – I made my way to the convention center. I was given a special seat in the front row – which in itself was an honor – among so many outstanding volunteers, microsoft customers and VIPs. My good friends and directors – Grant Fritchey, Allen White and Argenis Fernandez were around to help with nerves.  The ceremony was over quickly and social media started to near blow-up the phone with tweets and facebook messages of congratulations. It was a unique, once-in-a lifetime experience and one that I shall greatly treasure and remember. Sadly, there is no video recording available yet – I am told that it will be there on summit recordings. I will be happy to share a clip when I find one.

The rest of my day was taken with hugs and thanking many people – many of whom I did not know at all. I want to say THANK YOU again to the awesome sql community who made all this possible. Am very humbled and honored by your love and regard, and hope to continue to live up to it.

TSQL Tuesday is a monthly blog part hosted by a different blogger every month – it was started by Adam Machanic. This week’s TSQL Tuesday is hosted by Andy Mallon – the topic is ‘We’re dealing with the same problem’. I have chosen to write about a common problem I have encountered at many places I have worked at. The problem has to do with queries or stored procedures that suddenly start to perform poorly – when no changes have been made to the code or to the server.

The common perception/misunderstanding I have encountered for this is that it is only an issue with statistics updates and updating statistics with full scan should take care of it. In many cases this is the real reason. In some cases it really isn’t. It could be an issue with parameter sniffing, and a plan being reused that was generated for a set of parameters and appropriate for that set of parameters. But, most people jump to fixing statistics. This is especially true when they don’t really have the runtime plan it used, can’t find it in the cache and are just going with some sort of past experience.

At one place I was at people would update statistics every 10 minutes or so in a frantic attempt to ‘fix the slow query’, which would at many times not respond at all. At another place they actually had an automated check for when the stored procedure finished running and if it was still running beyond its normal duration a full scan statistics update would fire off. None of these are wrong – but repeatedly doing the same thing when the query does not improve, and assuming that is the only reason for the problem is wrong.

What I do is to recompile the plan with different parameters – test it to see the performance. If you are able to get a difference in performance with a different set of parameters then it is probably a parameter sniffing issue. And, if the stats updates do not fix it it is probably that too. Statistics updates are never really a bad thing to do, but they may not fix every slow query there is. Check if the issue is with parameter sniffing also. Also make sure there are no changes that went out – to the code and to the environment, that may be contributing to it.

Make sure you have a working version of SQL Server 2016.

USE [master]
GO

/****** Object: Database [WorldHealth] ******/
CREATE DATABASE [WorldHealth]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’WorldHealth’, FILENAME = N’D:\Microsoft SQL Server\DATA\WorldHealth.mdf’ , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N’WorldHealth_log’, FILENAME = N’D:\Microsoft SQL Server\\WorldHealth_log.ldf’ , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

CREATE TABLE [dbo].[smokers](
[Smokingstatus] [char](1) NULL,
[AgeRange] [varchar](50) NULL,
[HealthyorSick] [char](1) NULL,
[Numberofpeople] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 1′, N’1′, 20132)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 2′, N’1′, 21671)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 3′, N’1′, 19790)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 4′, N’1′, 16499)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 1′, N’1′, 39990)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 2′, N’1′, 32894)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 3′, N’1′, 20739)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 4′, N’1′, 11197)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 1′, N’0′, 204)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 2′, N’0′, 394)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 3′, N’0′, 488)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’0′, N’ 4′, N’0′, 766)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 1′, N’0′, 647)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 2′, N’1′, 857)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 3′, N’1′, 855)
GO
INSERT [dbo].[smokers] ([Smokingstatus], [AgeRange], [HealthyorSick], [Numberofpeople]) VALUES (N’1′, N’ 4′, N’0′, 643)
GO

This month’s TSQL Tuesday post is from one of my favorite people in the community – Jorge Segarra a.k.a. SQL Chicken. It is an invite to play with a new and shiny toy – Power BI. Now – like most people, my initial  reaction was like ‘naaah, am not a BI person!’. I dread TSQL Tuesdays that leave me staring at a wall wondering what to write, and there have been a few of them. Certain features are not easy to try in a test lab environment – and work environments do not always lend themselves to that flexibility. But this seemed like something very easy to install and play with, on one’s own desktop.

After some hiccups with email and such which Jorge helped me resolve – (details on how to download the tool are in his post) I got the tool installed very easily. I considered various test data to play  with it – felt it would be fun to use data accumulated over our sql saturdays at Louisville, which I have been the lead organizer for. We have had 8 events to date, but  only 5 are available as history on website. But this was information enough. I pulled the reports on attendee registration going back five years from the sql saturday  website, saved them as excel files, removed attendee personal information, and consolidated all of it into one CSV file. I learned later that you can actually do some data scrubbing within PowerBI itself but this  was something I did outside and was ready with.

I launched the PowerBI desktop –  pointed to my csv file as data source, and bingo, I was ready to go. The reports I needed  were not raw data from the file – needed some grouping and consolidation – so  I clicked on new query – which by default will bring up your raw data for you. In my case I needed the data to be grouped in several ways for my reports.Details of grouping are as below.

I created  4 queries – one each for each of my reports.
1 Query 1 – really simple, year wise grouping of attendee count.
2 Query 2 – two levels of grouping – year, meal preferences, count of people with those preferences.
3 Query 3 – two levels of grouping- year, opt in choice for emails, count for opt in choices.
4 Query 4 – two levels of grouping – year, state attendee is from, count of attendees. Here I had to filter out some junk data for name of state – and consolidate some verbage, like KY=Kentucky and so on.

When I was done the list of my data sources looked like below.

Now with my data ready, I launched into getting my reports up. I used each data source from above list for each graph (you can also use multiple but wanted to keep this simple).

The interactive graph can be viewed with better fonts here

https://app.powerbi.com/view?r=eyJrIjoiY2EzMWI1ZWQtYTQxNC00YjJhLTgxZTQtYjEyZjBkNzU5YTllIiwidCI6IjU2MzQ0OTU1LTJiZWYtNGFhZi1iYzg2LTQ4YWEzNzMzNzI0MSIsImMiOjZ9

Based on above graphs I was able to draw following conclusions:

1 First bar graph on the top left: Attendee by year: Our highest turnout was in 2011 (301) in the past five years. Hope to beat that record in 2016! Last year was 263 people.

2 Second horizontal bar on the right: Food payment preferences by year: A lot more people opted to bring their own lunch last year – this may be because we hiked the lunch  fee to 10$ after several years (yeah Kentucky:)) . There are more people being comped as well due to higher number of sponsors and volunteers, which is not a bad thing. It would be interesting to see how this graph changes this year, because we provided a fantastic BBQ lunch last year. We have also hiked lunch fee from 10-12$ so it could go either way.

3 First bar graph on bottom left: Optin attendees over years: People who have opted out from vendor emails has gone up and was highest last year.We need to work harder on getting more people to opt-in as this means lesser reach for vendors.

4 Last graph bottom right: Attendee count by state: Looks like we did not have significant people from TN until 2013…(I filtered out #s less than 10 for this graph as we are not interested in 1 or 2 random attendees from other states). Our largest # of people are from KY, which is no surprise at all..but what was  surprising to me for sure was that IN and OH both had around similar number of people.We did have several speakers from OH – but not 23, and there are certainly more people coming in  from there. This shows that we have to promote our event harder in OH, as well as work on getting more people from TN, another border state.

All said and done, I had an absolute ball doing this. Not only is it easy and convenient to use but comes in handy for a variety of different things, including analysing personal finances and such. I highly recommend it!