SQL Server Blog

I’ve seen a number of posts over the years asking what skills and requirements are needed to become a DBA. It’s an interesting question. From experience, I find a lot of people seem to fall into the DBA role from a development role. Not all DBA’s have gone this route, but it seems a good percentage have done so. I’m from a development background.

I started out as a DBA/Developer back in 1999. I was exceptionally lucky. I had been developing the backend for our warehouse applications and after a 4 month search for a DBA I volunteered for the role. Why did I volunteer? Well something happened one day. We had been developing our applications for about 8 months and the database had grown to a point where we were seeing performance issues. I was looking through Books on Line and also browsing the net and managed to stumble across a couple of ways to optimise performance and I had instant gains. I saw queries which ran in 10s were now sub second. I had caught the bug – how much faster could I make this thing with a few more tweaks? The answer was a lot!

The next 6 months I was reading everything I could find. Books on Line was one of my main sources. I bought a bunch of books online, subscribed to SQL Server forums etc. I then managed to get my hands on my own server. I’d spend hours after work putting what I had read into practice. I’d even go to the point of corrupting files, pulling out disks and trying to recover. I learnt so much in a short time and the fact is, I am still learning. I’ve been a DBA for over 10 years now but I still buy books, I still play about with SQL Server.

But what about you? The world of a DBA is an interesting one. I found databases an interesting subject when I first started using computers. You may have fallen into the database world like I did or you may just have an interest in the subject and wondering how to take this interest further.

What role is for you? There are a number of database roles out there. Some examples would be Production DBA, Development DBA, Developer, Business Intelligence, Data Architect to name but a few.

This post could become very long if I went into detail about what each role entails. If I’m honest, my BI knowledge is not extensive, so my thoughts would be a little raw there.

Deciding which role you want to focus on will likely come to you in time. It’s hard to stand up and say I want to be a Production DBA. You may find, with experience, that developing stored procedures is more your thing and that you get more excited about Development. You may even be in a company where you have to fill all of the database roles, much like I do in my current role.

Chances are, if you are reading this blog, you are new to the database world so I’ll give my thoughts based on being a relative newcomer as well as looking at a more one hat for all jobs role.

Books on Line and MSDN are both fantastic resources from Microsoft. Spend a lot of time reading up on various subjects. Typical subjects could include backups/restore, performance tuning, database design, security, indexes & statistics, T-SQL coding and so on.

You can also buy a number of specialised books which are well worth reading from cover to cover. You’ll probably want to buy books which cover all of the above or maybe buy books split into administrative & development areas. Either way, you’ll be reading about most areas of SQL Server.

Subscribe yourself to SQL Server forums. Read articles, post forum questions & engage in community discussions. You’ll soon gain enough confidence to write your own articles or answer questions.

Look up your local SQL Server User Group. I can’t emphasize enough about how good a resource this is. You’ll be able to network with experienced and also inexperienced DBA’s who are more than willing to share their knowledge with you.

Finally, get yourself a copy of SQL Server. You can start out with the Express edition or you may want to jump straight in with the Developer edition which contains all the Enterprise features. Either way, you’ll be able to start putting ideas into practice.

As you build up your knowledge and skills, you will likely discover for yourself which area of SQL Server you want to focus on. Maybe it’s database design which really excites you or maybe its performance tuning? Maybe it’s both?

With all that said and done, the biggest problem I find though is experience. Securing a DBA role generally requires experience. It’s a catch 22 situation. You have no production experience and yet nobody seems willing to give you that shot. This is a real tough one. Personally, I’m happy to overlook experience if I see a lot of enthusiasm and believe the candidate can do the job I ask of them. There are junior jobs out there, but you may be looking to shift your career later in life when taking a big pay cut is not possible. However, don’t give up. There are always jobs out there. You just may have to be a little more patient which naturally gives you more time to learn.

So would I recommend a role in the database world if I was asked? Definitely. It’s extremely rewarding. You do hear of great salaries for DBA’s and I do get paid pretty well. But I find the daily challenges are what keep me excited about my job. The world moves so fast that there are always new technologies and features in the latest version and it’s great when I find a piece of code online or a different strategy which improves the performance across my databases. It’s a world of discovery!

SQLBits is back! This confrence will be held at the Heslington Campus of York University from 30th September to 2nd October.

As before, you will need to pay for Thursday and Friday, but Saturday 2nd October is the community day and free to all. Unfortunately for me, I will be on vacation so I cannot attend!

http://sqlbits.com

Well 2010 has been very interesting. My company are working away on the next generation of our software. It’s been busy in the engineering team, bringing on new contractors for the duration and I have been up to my eyeballs with a number of projects as well as support.

Thankfully, I am now back fulltime on the build of our new software. In hindsight, I should have voiced stronger concerns about not having a fulltime presence from the DBA team. We have a good team of developers so the schema was decent, but we did need a few changes to types, lengths.

We’re also using MySQL as a backend for our hosted client environment. We’re putting it through the paces now. It’s an interesting product and has some cool features. We’re still developing the client database in SQL Server as well. Our central databases will remain as SQL Server.

Due to the amount of work this year, I have not actually completed any of my posts. I currently have 4 unfinished posts. I think I will be investing in a netbook so I can work on my posts when I am on the train to and from work.

It had to happen someday. It happened to me this morning. I was sat on the train and saw a whole bunch of alerts fire through about memory first of all and then thermal sensors exceeding thresholds. An email to the emergency response team from the infrastructure manager telling us that one server has over heated and has failed over. We run an active/active cluster.

A bit of fettling of memory and disabling of non critical scheduled jobs (mostly alerts) to reduce pressure and so far, everything is up and running. The day is not over, so I’m not planning my weekend just yet but this has planted a seed in my mind for a future post or possible series on high availability.

Hopefully HP will come good and resolve our overheating issues and allow me to fail back tomorrow when I have some downtime.

What is the difference between a seek and a scan? It can be quite a lot, actually!

By definition, a scan will touch on every row in the table or index and retrieve only those rows which match the query criteria. On the other hand, a seek will use available indexes to locate the data.

One thing to note is that I mentioned that a seek will use available indexes. While a table may have an index, SQL Server may still choose to perform a scan if the scan is considered less expensive – for example, a small table may be subject to a scan over a seek.

See the following examples of queries, IO statistics and the execution plan to demonstrate a seek and a scan. For these queries, I am using the AdventureWorks2008 database and SQL Server 2008. If you do not have a copy of this database, you can get it here : AdventureWorks2008 Download

Statistics Output :
Table ‘Person’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Why does this query perform a seek? Simply, there is an index (IX_Person_LastName_FirstName_MiddleName) on our search criteria. For this operation, I have also observed only 2 logical reads, which is excellent performance.

Now to demonstrate a scan.

Statistics Output :
Table ‘Person’. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If you now observe the execution plan, you will see that the query optimizer is now using an index scan, however, it is still scanning the same index that was used in the optimal search. Additionally, if you look at the statistics of this query, you will now see that there are 105 logical reads.

So why do we get a scan if, in the examples above, we exclude the surname from the where clause? The query optimiser, in this case, considers that a scan is more efficient because the index index is not selective enough. While the FirstName column is covered, with it not being the first column within the composite index, the optimiser decides the scan is a more efficient way of returning the resultset. If you change the where clause to just search on LastName with a search predicate of Williams, the optimiser will choose an index seek.

So is an index scan bad? Yes and no, really. If you have a large table, then an index scan will be resource intensive and possibly take a long time to complete. However, on a small table, such as the one in this article, performance is not really affected. However, there is a small caveat to this. If your query is executed several thousands of times an hour, an index scan will use a lot more resources over this time period than a seek, so you do need to understand the load of your queries and determine whether a scan is appropriate, even on a small table.

SQLBits VI comes to London on the 16th April 2010.

The event is being promoted as the best event yet and best of all…it’s free! So register today!

SQLBits

You may have seen the term SARGable in blogs or forums and wondered what it means. To start with, SARGable is pseudo-acronym which stands for Search ARGument. A query is considered sargable if the WHERE clause can take advantage of an index to speed up the query. A non-sargable query implies that the WHERE clause (or part of it) cannot use any available indexes which could result in a table or index scan and possibly slow down query performance.

You could possibly have a non-sargable query if you use filters like <>, !=, NOT, NOT IN, LIKE ‘%Clive’, NOT LIKE and NOT EXISTS. These filters could cause the optimiser to not use an index, although not always!

Filters such as =, >, <, <=, >=, IN, LIKE ‘Clive%’ could allow the optimiser to use available indexes to speed up query performance.

I had my appraisal this week. It was an interesting meeting with my manager with many compliments and also constructive criticism on area’s where I need to improve.

My role is also shifting for 2010 and onwards and will be moving away from the more day to day administrative duties. While I will always be a hands on guy, my boss is pushing me towards more of an data architect role. My team is growing as well, which is always a bonus!

Hopefully, this blog will also now document my new learning experiences as well as topics or problems I come across in my working day – and hopefully I will actually get round to finishing off some of the actual articles I have saved in my draft folder too!

If you are looking for some good open source offerings for SQL Server, then take a look at the CodePlex website.

Well, 2009 was a busy year for me at work. The databases are growing quite quickly and a lot of projects have come on board. Challenging, but fun.

That said, I have not blogged as much as I would have liked, so 2010 is the year for me to really get the blog off the ground. Aside from that, I do have a couple of books that I should really get round to reading and I need to get myself active again on the SQL Server Central & MSDN forums.

The final goal for 2010 is to write an article or two!