The Viking's Pet Peeves - Part 2

 

Pet Peeves - Part 2 - In the Database

Last week I rattled off my top 5 biggest Pet Peeves when it comes to working out in the gym. If you missed it, give it a read here. Today though, being the Viking DBA, we're taking a dip into a short list of my biggest pet peeves when it comes to working with databases. It was difficult narrowing this list down to just five, I didn't want to prattle on incessantly. There's so many more I could've done. If you don't have a background in the database realm, I'll do my best to make it relatable. Ready? Here we go in no particular order.

1. SELECT * FROM tablename - Let's get this elephant right out of the way. We as DBAs all do this. Admit it to yourself. You need to get a quick sampling of the data from a table to see what's there; what kind of data you might be working with, column names, values and so on. Often times, it's just to pull a reference list of codes or keys for other tables you need to scan through later. It's habit. Heck I think I probably did it a handful of times just today. 
   This isn't an issue when your table is small, maybe a hundred rows or less. But what about the manager who wants to see some order history or transaction related data? Millions and millions of rows, perhaps billions. That simple SELECT statement turns into a big bottleneck. When you read from a table, unless you add the NOLOCK query hint (another topic for another day), your query is going to start holding up other operations, specifically updates or inserts. Not a great idea if you have customers attempting to place orders on your website. These also can create memory and TempDB pressure as the engine is processing those millions of rows and values. If the datatypes are VARCHAR or good heavens maybe BLOB, this is going to get you in trouble.
   Have you noticed if you right mouse click on a table inside of SQL Server Management Studio (henceforth referred to as SSMS), there's an option to Select top 1000 rows? That's a good alternative. It even returns in a lovely grid format. To alleviate extra mouse clicks, if you prefer to simply type, it's an easy SELECT TOP 1000 * FROM tablename. Don't be the problem, be the solution.
   There's more we could do with this; adding a WHERE clause to filter data or an ORDER BY to get more accurate data. However, we're then starting to drift into more proper query design. Just putting a WHERE clause in could dramatically slow down that simple SELECT query if the right index isn't there. Not wanting to go that far in this post. Just know, if I see a SELECT * in a query coming from an application (and I do in my environment) my blood begins to boil.
2. Using the Clustered Index Field as the Leading Key Field in other Non-Clustered Indexes - Let's consider an example Orders table with an ID (Primary Key), OrderNumber, OrderDate, CustName, OrderAmt, IsProcessed fields. This is an extremely simplified example, but hopefully drives home my point with this particular peeve.
   In this example, we'll say that the ID field is an identity. Therefore by nature of it being the primary key and an identity we should already have a clustered index on here. The records are sorted by ID field automatically and stored in numerical order. There are also Non-Clustered indexes on this table where that same ID field is being used as the leading field. So one with ID, OrderNumber. Another with ID, OrderDate. You get the picture. This garners the user almost no benefit. By nature, the primary key is included in your non-clustered indexes as a reference pointer, so why is it included again?! And as the first field no less. This will cause the analyzer to work a bit harder and the statistics will mean almost nothing if you pass in a meaningful query.
   To illustrate, I setup the example above and ran some queries. The table has only 10,000 rows. The first query is SELECT ID, OrderNumber, OrderDate FROM Orders WHERE OrderNumber = 'ORD005021'. Look at the execution plan, what do you see?
   
   
   
   

   It did use the index containing the OrderNumber, but what is it doing with it? A scan. That means it's starting at the top of the table and reading row by row until it finds the corresponding order number and then will mercifully stop. So even though we have an index with that OrderNumber field, it garners us almost nothing in terms of performance since we're still doing a scan until it's found. Check out the helpful green text, it's even telling you that an index with OrderNumber as the first field is the better choice.
   
   

   
   

   One more to really drive this home. This time I'll do a range of Order Dates. SELECT ID, OrderNumber, OrderDate FROM Orders WHERE OrderDate BETWEEN '2026-07-18' and '2026-07-19'.  Examine this query plan.
   
   
   

   
   

   Ah yes, the ever helpful Clustered Index scan. It's not even touching that index with OrderDate in it. The analyzer said "No thanks, I'll just look at this clustered index row by row until I've found everything I need. The helpful green text appears again, it knows what's up.
   This is an incredibly simple example with minimal rows. Imagine this Orders table after 5 years of constant activity. Millions and millions of rows, these scans will only get worse. I bring this one up because I have this in my work environment. A multi billion (yes billion with a b) row table has three indexes where the PK is the leading field. These indexes are worthless! They came from the vendor that way! I could address, however downtime on this system is not an option and the last index I built for this table with the Online=On option took......ready for this?   Four days! Oh and crushed the TempDB in the process. So please, if you're a developer and think you're doing a good thing with indexes like this, please speak with a professional DBA and test.
   
   
3. Mismatched Datatypes Between Procedure Parameters and Tables - Here is another scenario I have encountered in my daily job. The table has two varchar() fields of varying length. Not a big deal there. The table isn't too wide, just 8 columns. The query coming in is SELECT * FROM tablename where OperationID = 'xxxxxxxx'. On the surface, not a huge deal. I have an index on that column. Yet when I get alerts regularly on long running queries, big waits and when I examine the execution plans I saw massive Clustered Index scans. Well that's odd. Why is it not using my non-clustered index with the OperationID field? 
   Dig a little more and discover that with Entity Framework (the web code that builds the SQL Query), they're actually building a dynamic SQL statement, passing the value for OperationID in a parameter which is perfectly good practice. The issue is that in coding proper dynamic SQL, they're converting the parameter value to NVARCHAR(). Even though to our human eyes, the data value is the same, to the SQL Engine, those are different datatypes. Because of this mismatch, the analyzer is not seeing my index as viable since it's using VARCHAR(). Thus, it gets a full table scan. This query executes hundreds of times in an hour. Imagine that workload
   Change the dataype on the table? That's a no go. It's another vendor provided system. Who know what other code lurks out there? If I change that, how much else do I break? Changing the code isn't an option either as we don't have access to do so. Also of note, the dynamic SQL isn't calling a stored procedure, just writing it's own straight SQL Statement.
   I did tinker with and indexed view as that might have been able to fake it out, however due to some other complications in that setup beyond the scope of this post, that was also a no go. The end result was a long piece of documentation to the vendor outlining all observations, attempts to work around, screenshots and recommendations. Still waiting on a resolution. The bottom line, just ensure that your called procedures are using the same data type of the columns in your table. 
4. Ultra Wide Tables - At some point your tables simply bloat too wide and lose effectiveness. There's no good recommendation for how many columns is the right amount, it's whatever your data and structure need. However, speaking personally, once you get past 50, 75 columns it might be time to consider breaking some of that data up into another table. If start getting really wide in your columns, ask yourself how valuable it is to have all of that data in a single row. 
   Additionally, indexing gets tricky. If you DO need all of those columns and you need to query a bunch of fields, you'll need indexes to support them. Lest you end up with big table scans as we've seen already. All I ask is that we be design conscious. If you're building from scratch you have the opportunity to prevent a canyon's width of fields. If you find yourself adding a column here and there, watch out! Next thing you know it's 200 fields wide. Though I did just read about a table with 700+ columns. Just....why?
   Caveat to this, I understand the data warehouse type systems are an entirely different animal. I'm speaking solely from a standpoint of an Online Transactional Processing (OLTP) system where you have user or customer interactions that demand speed.
5. Improper Use of BEGIN TRAN -  This command can be a great safety net or a major system killer. Putting BEGIN TRAN before your insert, update or delete statement will place a lock on the table thus ensuring that whatever it is you're about to modify is done so isolated. No other user can step on your transaction and the data you're working with won't be modified until you're done. This sounds fantastic right? 
   The safety component of this is that you can rollback that transaction. Begin Tran keeps it in memory, uncommitted. You've just run a massive update statement expecting to affect 10 records, but instead it updated 10,000! Uh oh, something went awry. Fear not, you've used BEGIN TRAN. Issue a ROLLBACK command and your change is reversed. One thing to note though about doing a rollback, it will go single threaded. So if you ran an absolutely massive update or insert and decide to roll it back, whereas the original query may have gone parallel (spread work across multiple processors) the roll back will only utilize one processor. 
   Here's the dangerous component and I'm sure any DBA has seen this occur at minimum one time in their career. A user (perhaps even yourself) has issued a BEGIN TRAN with their query and then forgotten about it. They keep working with the results or worse still walk away for coffee or lunch. Recall what I mentioned just a few lines up? This puts a lock on the table so nothing can be done. No reads, no writes, zip! The table used in the transaction is all locked up until you either run a COMMIT or ROLLBACK command. 
   I am a massive advocate for using BEGIN TRAN, however please be careful with it. If can't recall if the COMMIT or ROLLBACK has been done, you can run a SELECT @@Trancount to verify. If it's anything other than zero, you got a transaction open out there. 

That's a wrap on today's post on Database Pet Peeves. Don't forget to go back and read part 1. Sound off in the comments, what are some peeves in the database world that drive YOU crazy?