SQL Server with Mr. Denny


August 13, 2009  11:21 AM

Auto Close is almost as bad as auto grow

Denny Cherry Denny Cherry Profile: Denny Cherry

In theory the auto close setting is a great idea. Free up resources for other databases to use when all the users are finished with the database.

In practice, its not so great.  If a single user connects and disconnects over and over (like say a single user using your website or even a few users using your website) every time a user connects the database has to be spun up and the data loaded into memory.  When the user disconnects the data is removed from cache, and the connection to the file is closed.  Then when the next user runs a query the file is opened again, and the data is loaded into cache.

This causes the users queries to run slower as the data must be pulled from disk each time instead of pulled from memory.  Plus there’s the time spent spinning up the IO thread, and opening the file.  And as I said last time, more time is bad.  Plane and simple.

Denny

August 10, 2009  11:08 AM

Auto Grow is the bane of my existence

Denny Cherry Denny Cherry Profile: Denny Cherry

OK, that’s not entirely true, but it’s pretty damn close.  The auto grow feature of SQL Server shouldn’t be counted on.  It should be disabled, or at least used on as an emergency basis only.

Having the auto grow setting enabled will cause your database to grow when ever it needs to, not when you want it to.  It will also cause fragmentation on the disk, as the physical database files will end up becoming fragmented as your various database all grow as they need fragmenting the files across the disks.

Look at your databases, and figure out how much they are growing.  You’ll want to preallocate the space to the database so that the database space is allocated all in a single chunk on the disk.  This will allow the disk to more easily load data from the disk into the buffer cache as all the data from a single database will be contiguous on the disks.  If the databases and fragmented because of auto grow then as you are trying to load data from the disk, the disk will need to keep moving from place to place sporadically reading data from the disk which takes more time; and more time is bad.

Denny


August 7, 2009  5:22 PM

Slide Decks from OC SQL Users Group

Denny Cherry Denny Cherry Profile: Denny Cherry

Last night I presented two presentations for the Orange County SQL Server Users Group.  I started with my Virtualization presentation and my Storage for the DBA presentations.  The slide decks can be downloaded from those links.

Denny


August 6, 2009  11:02 AM

What do you mean I can’t access my own database when trying to attach it?

Denny Cherry Denny Cherry Profile: Denny Cherry

When you detach a database from Microsoft SQL (I’m talking SQL Server 2005 and up here) the SQL Server automatically changes the NTFS permissions on the file so that only the user who told the SQL Server to detach the file has access to them.  SQL Server does this to ensure that an unauthorized person isn’t able to access the data files. Continued »


August 4, 2009  11:44 PM

Speaking at the Orange County SQL Users Group This Thursday

Denny Cherry Denny Cherry Profile: Denny Cherry

This Thursday I’ll be speaking at the Orange County SQL Server Users Group.  The group meets at the New Horizons Computer Learning Center in Anaheim, CA which is at 1900 S. State College Blvd Suite 100.

It’s right behind Angles stadium, you can’t miss it.

We haven’t decided on a topic yet, I’ll probably bring a few presentations with me, and let the group pick the topic from the list.  After the presentation I’ll post what presentation we did, and the slide deck, etc for it.

The meeting starts at 7, but people show up as early as 6:30.  There’s usually some pretty good eating, so drop on by.  I’m pretty sure that they charge $5 at the door to cover food, etc.

See you there.

Denny


August 4, 2009  9:11 PM

SoCal Code Camp – LA #2 has been announced

Denny Cherry Denny Cherry Profile: Denny Cherry

The SoCal Code Camp website has been reset and is ready for the next Code Camp.  This code camp will be the second camp camp that we are doing up in LA.  This time it is November 21st and 22nd and is being hosted at USC’s campus again.  (There’s a second site setup for the LA Code Camp specifically so be sure to check that site out as well.) Continued »


August 3, 2009  11:00 AM

Single Instance vs Multiple Instances

Denny Cherry Denny Cherry Profile: Denny Cherry

I see this question quite a bit, should I use a single instance or one instance per application database on my server. Continued »


July 29, 2009  7:50 PM

Windows 2008, where oh where has all my drive space gone?

Denny Cherry Denny Cherry Profile: Denny Cherry

If you are using Windows 2008, you may be like me.  Wondering where the hell all this drive space is disapearing to. Continued »


July 27, 2009  11:58 AM

Push vs. pull: Configuring SQL Server replication

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve published a new artcile over on SearchSQLServer.com.  This article is titled “Push vs. pull: Configuring SQL Server replication“.  In this article I talk about the differences between the two, and when each should be used, as well as how to combine them to get the best of both.

Denny


July 23, 2009  11:12 PM

Yesterday’s Webcast Recording posted

Denny Cherry Denny Cherry Profile: Denny Cherry

The folks at Quest and Live Meeting worked overtime to get yesterday’s webcast recording posted already.  This way if you missed the great webcast that Trent Mera and I did you can at least catch it afterwords.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: