Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 112 | 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    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones