Press "Enter" to skip to content

Category: T-SQL Tuesday

Don’t Store Files in the Database

Published 2022-07-19 by Kevin Feasel

Josh Darnell provides timeless advice:

As Deborah’s invite post suggests, this is a “that one time at that client” story. I was working at a consulting firm, and we had written an app for a particular client. Part of this application’s workflow involved users uploading images alongside some other information. These were not particularly large images in the grand scheme of things – they were taken by a microscope, and were a few kilobytes each, maybe.

However, this app had been in use for a long time. And as you might have guessed from the title of this post, each of these images was stored in a single table in the database that backed this application

Yeah, that’ll be a problem… Read on for some recommendations on how to avoid the issue. One thing I would add is FileTable, which came out in SQL Server 2012. In that case, the files are actually stored on disk but are queryable via T-SQL. It introduces its own set of problems but I do have some fond feelings about having used FileTable in the past.

Comments closed

The Siren Song of Platform-Portable SQL

Published 2022-07-18 by Kevin Feasel

Shane O’Neill gets on the soapbox:

Part of why I’m ranting about database-ubiquitousness SQL instead of ORMs is that I’ve also witnessed SQL code that has to work everywhere

I’m going to call this SQL “DUh SQL” from now on since I only have so many vowels on my keyboard and shouldn’t waste them re-writing the word “ubiquitousness”

Them: “Nope, we can’t use DATEFROMPARTS; it’s not available in other databases! Yeah, we use GETDATE() everywhere; what’s your point? WHAT!?!”

I strongly agree with this. Lowest common denominator SQL just means your code is likely to perform poorly in all environments. Even if you know you need to support multiple platforms, it’s better to get the most out of each and have whichever data access layer implementation you inject figure out the details of how to do it.

Comments closed

Proofs of Concept and Pilots

Published 2022-07-15 by Kevin Feasel

Kenneth Fisher strikes a chord:

If your POC does not follow your companies best practices and standards then it is not a valid POC.

There are way to many settings that will change it’s performance, cause security issues, etc. On top of that, almost every POC I’ve ever seen ends up becoming the test environment if not the actual production environment. So all of those little compromises end up in your actual, non POC environment because it’s way too much work to fix them now. You should have said something when we set this up.

To use one of my favorite lines, “Short answer: yes with an if; long answer: no with a but.”

Before I get to the answer, I do want to differentiate between a proof of concept and a pilot. The idea of a proof of concept is to see if I can make this thing work. Can I get these two processes to talk to each other? Can I build a website which accepts user input and displays something? Can I get this idea from my head into code? Can I process 500,000 records per second using our existing hardware? One important thing about a proof of concept is that it always has the possibility of failure. “No” is a valid answer here based on the conditions. By contrast, a pilot is a starter for the full project. You might work with one business unit instead of all of them, migrate a small amount of traffic to the new system, or only handle data from a single branch office. Also, you want that answer as fast as is reasonable so that your business decision-makers can make business decisions on that information. By contrast, when we do a pilot, we already know the answer is yes; we just need to build it out and answer the technical details along the way.

Returning to the line above: Yes, I agree with Kenneth if your company lacks the discipline to differentiate between proofs of concept and pilots (and that’s not as denigrating a remark as it sounds…though it’s somewhat denigrating). No, do not follow the same practices for a proof of concept that you would for a full product, but you need to ensure that code gets destroyed and you start over with new code which does follow those practices.

2 Comments

Have One Data Model per Business Area

Published 2022-07-15 by Kevin Feasel

James McGillivray offers us an important piece of advice:

I cannot stress this enough. If people are consuming your data in multiple places, the data needs to come from the same data model. That can be an Enterprise Data Warehouse, a Data Mart, a Power BI Model, or any other data source, but at some point you need to be able to track the data back to a single place. If you don’t do this, you will spend THE REST OF YOUR DAYS explaining the differences between the data models to business and customers, and reconciling the differences over and over again.

Read on to learn why this is so important.

Comments closed

Giving Managers Access to Servers

Published 2022-07-14 by Kevin Feasel

Tracy Boggiano opens a can of worms:

So, what to rant about?  Should your managers have access to your SQL Server instances? It depends.  You say on what.  Well, just one thing.  What type of manager do you have?

Read on for Tracy’s thoughts on the matter. Speaking as a manager, I tend to agree. If you don’t know what you’re doing, better not to have the ability to mess things up. I can think of oddball scenarios where you might still want the manager to have (at least theoretical) access to a system, primarily as a backstop in case the line staff get locked out or someone gets hit by the lottery bus and you suddenly need to bring in a new employee. That’s more of “break glass in case of emergency” access, though.

Comments closed

Hardware and Finger-Pointing

Published 2022-07-14 by Kevin Feasel

Glenn Berry gives us two rants for the price of one:

This is rant #1. Even though I have an unusual fascination with computer hardware, I am still somewhat taken aback when I encounter DBAs who have absolutely no idea what type of hardware they are using. I’ll sometimes ask a DBA “What processor does your most important database server have?”, and I often get a “deer in headlights” look in response. Then a mumbled response, “I’m not sure, maybe a Xeon?”.

Read on for this rant, as well as the origin story of Glenn’s outstanding SQL Server Diagnostic Information Queries.

Comments closed

Indexes: Gotta Add ‘Em All

Published 2022-07-14 by Kevin Feasel

Andy Yun tells a tale of woe:

I once worked for an Entity Framework SaaS company that was having horrific T-SQL performance issues. We put out a major release one weekend and spent the subsequent week buried in horrible performance hell. Because all T-SQL code was generated from Entity Framework (stored procedures were banned by a development exec prior to me joining the company), there were no easy fixes that could be implemented (and a Production release typically took 12 – 36 hours, I wish I was joking).

The manager of infrastructure had heard about SQL Server’s missing index recommendations:

Yep, it ends about as poorly as you’d expect.

Comments closed

Sources of Data Structure Truth

Published 2022-07-13 by Kevin Feasel

Deb Melkin performs database epistomology:

The “source of truth” is my newly made up phrase for whatever you are using to say this is my database schema and initial data needed to start up the application. This can be your script directory; this can be a dacpac or bacpac; this can be your data model; this can be a combination of these things. My go-to “source of truth” right now is my source control repository. I’ve got both the schema and the default data needed in the same location. In the past, I would have probably included the data model as way to help me make sure whatever database table changes I have in my source control are there, especially for that one database which only had tables and views. (A different rant for a different time.) Whatever you use, it absolutely CANNOT be an actual database. There are two main reasons for this:

Read on for those reasons.

Comments closed

SQL Login of the Week Club

Published 2022-07-13 by Kevin Feasel

Mala Mahadevan recounts a horror story:

Its been a few years since I left consulting. But the last gig I was at – we encountered something like this. We had a big client who had outsourced all their database development and manual update work (no not to us, to some third-party contracting company). These were contractors paid by the hour, and the turnover was really high. Our client did not want to issue windows based authenticated logins to these people for some reason (do not recall what). So every week, when the week started, the contractor working on a particular server would get a SQL Server authenticated login they could use. This was valid just for that week and would expire the next week. And, every weekend , it was our job, as the remote DBA company, to set up those logins.

But wait, it gets worse.

Comments closed

The Risks of “Unused” Databases

Published 2022-07-13 by Kevin Feasel

Chad Callihan reminds us about things in use:

You may have been updating data someone needed for validating a fix. The “_OLD” table that you assumed could be dropped may still have been useful to the person who created it. There might be a database covered in cobwebs that should have been dropped years ago but it could also be a database that’s used for some type of reporting every few months.

Yeah, that’s a pretty common problem. A couple of things which help mitigate this issue:

  • Check wherever you can to see if the database (or database object) is in use: cached plans, stored procedure calls, application calling code, SQL Agent jobs, SSIS packages, etc.
  • Take (and test!) backups of databases before you drop tables or get rid of them.
  • Keep those database backups around for quite a while.
  • Take databases offline for a while before dropping them. That way, if somebody really does use it on occasion, it’s easy to bring back online rather than needing to restore from a backup.

At the end of the day, however, you shouldn’t be afraid to drop things. Do the appropriate amount of diligence and make it a controlled demolition.

Comments closed