Words: 675

Time to read: ~ 3.5 minutes

T-SQL Tuesday is back around again and this time it’s the first one of 2018!

This illustrious prize, founded and given by Adam Machanic ( Blog | Twitter ), is awarded to Arun Sirpal ( Blog | Twitter ); an MVP wise in the ways of Azure.

Arun asks us to…

write about and share with the world a time when you faced a technical challenge that you overcame and you can go really technical with both the issue and solution if you like.

My problem…

… is that I’ve written of technical challenges that I’ve overcome before. I’ve had problems with creating indexes from jobs, I’ve had issues where PowerShell wouldn’t STOP doing what I asked it to, and I’ve dealt with a login which seemed to defy all I knew about permissions. In fact it’s only a bad gripe because at the moment I cannot think of a topic to write about. Which is not something I’m enjoying; I have massive respect for Arun and I’d love to take part in his T-SQL Tuesday.

So I’ve trawled as far back into the past as my caffeine-addicted, peanut M&M craving mind can go and dredged up something from the deep.

I hope you’re not disappointed.

The “I don’t know, it works” case

When I first started the SQL path, I worked as a SQL Support Engineer on a bespoke application. We had this recurring issue where a certain client could not open the application; eventually they would just timeout.

Now the way that the application was developed, each client was supposed to have between 20 and 30 sites per client, and when the application opens it loads a list of their sites.

Checking out the client in question, they had over 5,000 sites; definitely a difference! (You probably already know what’s happening…I didn’t)

The only way that I knew to fix it was to work with the customer to mark the sites that were inactive then I would update all of them to inactive. A simple switch of a IsActive column from 0 to 1 of those sites. (Validating your assumption huh?)

This seemed to work and the clients were able to access again…for a while.

Now we didn’t have a company DBA at the time, and when I moved on, that situation was still an issue.

Let’s compare states

Old Me

• A user has a problem – Okay…
• They can’t open the site page – Okay…
• They have around 250x the amount of sites – Okay…
• The only fix is to mark sites as InActive – Okay…
• That’s it. – Right…um…not a lot to start with…I’ll see what I can figure out…maybe it’s a SQL Server limitation?

What follows is figuring out what stored procedure calls that screen, checking for any obvious bugs and then days of Google-Fu.

Some days I think it was luck that we found that IsActive flag…

Me Now

• A user has a problem – Okay…
• They can’t open the site page – Okay…
• They have around 250x the amount of sites – Sounds like parameter sniffing problem
• The only fix is to mark sites as InActive – Definitely sounds like a parameter sniffing problem
• That’s it. – That should be enough! Bet you a fiver it’s a parameter sniffing problem?

Technical Solutions?

I got none.

Like I said, this was a while ago and was still an issue when I left.

Damn how I’d love to go back and see if I’m right though! Compare plans between the parameters. Do some testing to see if I can improve the performance.

Basically just validate my assumption!

You may argue…

…that this isn’t a technical post and in some way, I agree with you.

It’s not a technical problem now…but it was for me back then. Something I wrestled with and something that I failed to overcome.

I do love the fact that it is a baseline that I can look back on and think…

wow…2 years ago I had no clue…

At least now I know that I have no clue now but at least that changes in the future!