The Viking DBA - Making Strong Bodies and Databases

  Parameters & Presses I was in the gym on Tuesday and had this idea whilst executing some shoulder presses. About how picking a weight is similar to a common issue in stored procedures: Parameter Sniffing. Before I can make the comparison, let's define Parameter Sniffing. Oftentime, stored procedures in SQL are driven by a parameter being passed to define the criteria for a query. These can be incredibly simple to ultra complex. For the purposes of my example today, I have an extremely simple procedure. See below. I've got a procedure called ParamSniff which accepts one parameter called Location. Then I have a very simple SELECT query that uses that parameter in the where clause. When you execute the procedure it passes the required parameter value through and searches for that. Now, if this is the very first time the procedure has been run or the server and/or SQL services have been restarted recently, there is no cached execution plan for this procedure. So the SQL Engin...

 Reading is fun! I'm an odd duck in the world of IT. Yes, I love innovation and I enjoy learning new things. However reading and books are something I tend to stick to the old school on. I much prefer having a physical product in hand to read. I've tried Kindles, had the now long defunct Barnes & Noble e-reader, the Nook, and for some reason I could just never get on board.  When I was a wee lad, I can remember carrying around stacks of Choose Your Own Adventure books. Or hitting our local library and coming home with 2 or 3 large novels (typically getting through at least one of them). So I feel like this harkens back to that time. When I enjoyed carrying around a book or 3 and flipping through a few pages when I had time. Now I know that this is an antiquated method. Yes I know how many books I can keep on an e-reader. Yes I know how much space books can take up. That doesn't phase me though. If given the choice, I'd take the physical copy any day of the week. The...

 Parallelism - Databases and Isometric Movements The other day I was doing single leg hamstring curls and came up with the analogy of comparing this movement with parallelism in a database. So let's cover a couple of things up front. For the purposes of this topic, consider my legs. They are representing my processors (CPU or core) inside a computer today. In this machine, we start with legs fully extended and then using the hamstring muscle, flex and pull the bar toward you lifting the weight stack. Obviously, utilizing both legs we can lift way more weight than with a single leg.  Similarly, the more processors you have in a system, the more work it can accomplish. In terms of databases, we call this Parallelism. If a query is big (heavy) enough, the engine will spread the workload across multiple processors, divvying up the work. Within SQL Server, you can configure a couple of components around Parallelism.   First you have to determine how many processors you ha...

Uncommitted Data: the messy gym floor of databases One of, if not the largest, pet peeves of mine when it comes to the gym is people who can't put away equipment when they're done. A plate is taken from rack near the benchpress all the way across the gym to the turf and then just left there for some other user to put away. The worst is what I encounter nearly daily. Kettlebells and dumbbells that are pulled from the rack and then left out in front of it! Why? Why are you unable to extend the tiniest bit of effort to return the item to the rack? So this got me thinking this morning about how left out weights, bars, straps, etc. are much like transactions in our database that don't get "put away".  If you read one of my previous posts on indexing, we're going to stick with our weight rack/table structure example. Today we have our kettlebell rack where each shelf can represent an 8k data page within our table. Shelf = 8k data page in table The kettlebells here a...

  Welcome to the exciting world of Indexes! I truly do love indexes! I started intentionally learning and diving into index creation and maintenance a few years back taking Brent Ozar's Fundamentals course. Recently I completed his full suite of Fundamentals and Mastering courses. Fun tip, he's got a Black Friday sale coming up and I strongly recommend getting that training if you have the budget for it. Onwards though! Today, I'm writing about tables and their indexes (or lack thereof). To illustrate my point and keep within my weightlifting motif, we're going to be using this weight rack as an example. Using the above graphic for illustration, imagine this weight rack is our table (dbo.dumbbells). Tables in SQL Server store data in 8K pages, but in the gym today, our data is stored in 8K shelfs. Each dumbbell here is a record in our table. Heaps The data in our table is unstructured; weight sizes are all mixed together, there's no rhyme or reason to their order. J...

  Welcome to something new Let me first introduce you to, well, me. My name is Brain McMeans and I am a 49 year old weightlifting and database enthusiast. I have lived in the Woodlands for the last 17 years with my wife and two children, a few dogs and a mess of cats. I've owned a game store, worked for several great companies, tried my hand at personal training and now am fully settled and looking towards the future. I have nearly 20 years experience in SQL Server ranging from versions 2000-2022. My path has been an interesting one. I began working with SQL Server 2005 when we were implementing a new HR system for the company I was working at. Fumbling my way through it and writing some truly horrible queries to get data imported. In 2006, a co-worker had a free certification exam voucher that she didn't need. I was able to snag it, picked up the MSCP exam book for SQL 2005 and began studying. Figured if I failed the exam, hey all I'm out is time and the cost of the book. ...