Category Archives: DBA

5 Things SQL Server Should Get Rid Of

 

I’ve been wanting to toss my hat into the latest Meme by Paul Randal( Blog | Twitter).  So here’s my list. 

1.  Auto Shrink.  I agree with Paul on this.  It should never be turned on and it’s not needed.  I don’t like to have to shrink files but allowing them to Auto shrink is even worse.  Just asking for performance problems. 

2.  Defaults for files.  I promise I’m not trying to copying Paul verbatim here.  This one effects me all the time.  I have a client that makes lots  of databases then throws millions of rows into them.  They never change this setting!  Until I come in to look at the server and find thousands of autogrows.  Why not let me set the model to a specific setting and everything uses that as the default?  Or perhaps you could have a server property to set it?  I’m planning some work on PBM in the future to see if I can get this to do it for me. 

3.  Enterprise Edition.  Now before you get the tomatoes ready here me out.  I don’t want MS to kill the flagship product with all the features I want them to move many of those features down to Standard.  I work with lots of small customers that have sometimes hundreds of db’s and can’t afford Enterprise edition but could really use something like Resource governor or Data Compression.  The Big customers and big enterprises I work for rarely use these technologies because they’ve got 20 developers to write there own resource management or ways around compression.  They have the benefit of resources to find another way.  Give these features to the little guys so they really utilize the new features.  I typical see the smaller companies are the ones trying to blaze new trails with the new features. 

4.  ONE TEMPDB!  I would love to see the ability to create multiple TEMPDB resources and assign groups of DB’s to it.  Think of those out there with 100’s or thousands of Db’s you have a tempdb bottleneck always.  Now yes you can split the files and put it on fast disks but it’s always going to be a single point of contention. 

5.  Last but not Least.  SSMS.  I know others have mentioned this one as well.  This tool tried to be a dev studio and a management studio and failed.  It’s ok at both.  Why not just leave VS as the dev studio(which rocks) and create a rocking Management Studio.  Something like Quest Fog light/Spotlight.  Give us some of the cool tools out there to really MANAGE our servers not just Design against them. 

Since I crashed the party I won’t bother Tagging anyone.  :) 

Photowalking at the 2009 PASS Summit

It’s that time of year again for the PASS Summit.  Which means it’s time for a PASS Photowalk.  Tim Ford(@sqlagentman)  and myself wanted to get together last year and take some pictures around Seattle.  Many friends joined us and the PASS Photowalk was born.  We will be leading a Photowalk once again this year  Here are all the details. 

Where: Sheraton Lobby
When:  Monday Nov 2nd 8:00 a.m.
Who:  Anyone with a camera.  The only requirement to Photowalking  is a camera of some sort.  If it’s a camera on your phone that’s fine. The goal is to socialize and meet other PASS/SQL folks and to take pictures while doing it. 

Myself and Tim Ford will be leading the Photowalk.  We will start off at the Sheraton and hopefully do a quick group shot and then work our way down to Pikes Place Market and then to Sculpture Park near Pikes place.  From there will be anyone’s guess.  We will most likely stop at Pikes Place for some breakfast of some sort.   Many of us will have things to attend later in the day on Monday so the walk most likely will not go past lunch time.   Hopefully everyone is attending Don Gabor’s session later in the day as well. 

We will use #sqlpassphotowalk as the hash tag for the event in case you want to follow where we are on twitter.  I’m sure a few other people on the walk will be using twitter.  If you know your going to be late email me at pat.wright@sqlpass.org and I’ll pass on my cell phone number so you can call me and find out where we are. 

I’ve created a Flickr group for us to post photo’s from the Photowalk.  You can visit it here.  http://www.flickr.com/groups/passphotowalk/ please Tag your photos with SQLPASS. For ease of searching.  If you don’t have a flickr account it’s free to setup.  You can see my pictures from last years Photowalk as well to get an idea of the walk.  

Here are some suggestions to make this a good photowalk for you.

1. Be sociable.  This is about learning and networking.

2. Be prepared.  Last year we were lucky with excellent weather.  We will see if we are that lucky again.  Dress in layers and either carry an umbrella or be prepared to get wet.  If your brining a Digital SLR like myself it’s best to have something to cover it up with such as a bag to keep the water out. 

3. Walking.  While we may stop at a location to take pictures there will be much walking.  Wear shoes that are appropriate to do so. 

4. Have fun.  As with all things at PASS have a good time.

If there’s any questions comments feel free to add them here.  I’ve also posted this in the Hotels.sqlpass.org  forums .

Hope to see you all there! 

Pat

When a Second just isn’t fast enough

So I find myself performance tuning queries and statements all the time for SQL Server.  Many times I run into easy statements that are missing an index or calling a function 20 million times and causing problems.  These queries typically move from 20 seconds to 1-2 seconds very easily with some quick changes.  My problem this week was a query that took  1.5 seconds.  I needed to get this to run under 1 second consistently. I knew could get a little more out of the query by looking at the execution plan but I also knew I would have to consider the Architecture and the application itself to really get it the way I wanted it. 

I was surprised to find something in the execution plan that helped me out more than I expected.  The Key Lookup (In 2000 they are called Bookmark Lookups).  These show up in the execution plan and look like this. 

image

You will usually find these in your execution plans along with an Index seek/scan.  But what really do they mean?  SQL Server has found your data in the index but some of the values you have asked for in the query are not located in the index pages and it must go and lookup the data.  This will take a little more time as SQL Server goes and fetches your data. The funny thing about this query is that for the Execution plan it only put cost at 10% on this particular lookup.  Which seemed odd to me because when I looked at the IO STATS ( Set STATISTICS IO ON) it seemed like many of my reads were to pull back this data.  I decided I would ignore the Cost %  and place a covering index on the fields that are getting pulled back. I changed the index that it was using in the seek to add columns in my select statement as INCLUDE columns in my index.  This made a larger improvement than just the 10% that it had estimated.  It removed about 500ms of time from the query.  Which is a lot when your trying to go from 1.5 seconds to <1 second. 

Of course you do have a trade off here. By adding the columns to the index I’ve made my index larger and take up more space so don’t go around just adding all your fields into the INCLUDE of a index.  You need to evaluate and find out if the trade off is worth it.  In this case it is.  I’m also making changes to the application so that it doesn’t request columns it doesn’t need then I can remove them from the INCLUDE and save myself some space. 

If there is one thing I’ve learned from performance tuning and reading execution plans is never take them at face value.  They are a tool that was created to help you make better decisions.  They are not always right so make sure to do your homework and really test your procedures and break them down to find the real bottlenecks. 

Happy Performance Tuning

Path to DBA

Recently I needed to make a list of what tasks I thought would be needed to qualify someone from a Jr. DBA to a DBA.  One of the people I was managing had started as a Jr. DBA that we were training and after a year of training and tasks we felt it was time to move him up in rank to a full time DBA.  Of course we wanted a checklist of items to prove this so below is the list I used to qualify the person. 

1. Fixing corruption issues

2. Determining optimal Disk layout based on application needs. 

3. Index suggestions based on Slow running Stored Procedure.

4. Index/performance suggestions based on profiler running against application. 

5. Defragmentation, Stats, reindex schedule suggestions.

6. Understanding of Sql Server configuration best practices.

7. Ability to place the db in Single user mode, Rebuild master, Attaching/Detaching Db’s.

8. Understanding Security differences in Role/user security

9. Understanding Isolation levels, difference suggestions for using each one. 

10. When is a good Use of (nolock) and for what?

11. What are memory configurations and settings for SQL server.

12. Backup and Restore Db through t-sql and to a point in time.

13. Setting up Db mail.

Along with this list was some specific goals and suggestions for the specific company and application we worked with.  A good DBA not only understands the Database system they are working with but they also know the system and how it is used. 

I’m sure there are more things people could think of feel free to add more in the comments if you have suggestions for additional items.