SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Just Enough know-how

By Louis Davidson (@drsql),

SQL Server offers very simple interfaces to many of its features. Most people could open up SSMS, connect to a server, write a simple query and see the results. Even several of the core DBA tasks are deceptively straightforward. It doesn't take a rocket scientist to perform a basic database backup or run a trace.

However, appearances can be deceptive, and oftentimes it is really important that a DBA understands not just the basic of how to perform a task, but why we do a task, and how that task works. As an analogy, consider a child walking into a darkened room. Most would know that they need to turn on the light, and how to do it, so they flick the switch. But what happens if light fails to shine forth? Most would tell you immediately that you need to consider changing the light bulb. So you hop in the car and take them to the local home store and instruct them to buy a replacement. Confronted with a 40 foot display of light bulbs, how will they decide which of the hundreds of types of bulbs, of different types, fittings, shapes, colors, power and efficiency ratings, is the right choice? Most would resort to guesswork, and resolve to use their cell phone as a flashlight next time, so they don't have to ask for Dad's help.

Likewise, when the metaphorical toddlers who use our database server have issues, they will instinctively know something is wrong, and may even have some idea what caused it, but will have no depth of knowledge to figure out the right solution. That is where the DBA comes in and attempts to save the day. However, when one looks beneath the shiny UI, SQL Server has its own "40 foot display of light bulbs", in the form of the tremendous number of tools and the often-bewildering amount of information they can present to the DBA, to help us find issues. Unfortunately, we can't resort to guesswork, to trying different "bulbs" over and over, hoping to stumble on the answer. This is where the right depth of knowledge goes a long way.

If we need to write a SELECT statement, then knowing the syntax and where to find the data is not enough. Knowledge of indexes and query plans is essential. Without it, we might hit on a query that "works", but we are basically still a user, not a programmer, because we have no real control over our platform. Is that level of knowledge deep enough? Probably not, since knowledge of the underlying metadata and structures would be very useful in helping us make sense of any query plan. Understanding the structure of an index makes the "key lookup" operator sound less like what happens when someone taped your car key to the ceiling.

So is even this level of understanding deep enough? Do we need to understand the memory architecture used to process the query? It might be a comforting level of knowledge, and will doubtless come in handy at some point, but is not strictly necessary in most cases. Beyond that lies (more or less) full knowledge of SQL language and the intricacies of every step the SQL Server engine takes to process our query.

My personal theory is that, as a professional, our knowledge of a given task should extend, at a minimum, one level deeper than is strictly necessary to perform the task. Anything deeper can be left to the ridiculously smart, or obsessive, or both. For example, tasked with storing an integer value between 0 and 99999999, it's essential that I know that choosing an Integer over Decimal(8,0) will likely offer performance benefits. It is then useful that I also understand the value of adding a CHECK constraint, to make sure the values are in the desired range, and comforting that I know a little about the underlying processors, registers and computer math. Anything further, I leave to the likes of Joe Chang, whose recent blog post on the topic offers depth by the bucketful!

Louis Davidson (Guest Editor)

Total article views: 113 | Views in the last 30 days: 1
Related Articles

Understanding Execution Plans Part 1

The purpose of this article is to give you a working knowledge of how to view and understand query e...


Share Database Knowledge with Office and SQL Server

Longtime SQL Server expert Bill Wunder has written a very interesting article on how to share knowle...


Knowledge sharing

This article talks about the sharing of knowledge in forums such as SQLServerCentral.


Understanding SQL Server Query Optimization Statistics

SQL Server query optimizer uses statistics to create query plans that improve query performance. For...


Understanding SQL Server Query Optimization – Part 2

An execution plan is the sequence of operations SQL Server query optimizer performs to run the state...

database weekly