Performance Problems

Whitepaper on why database maintenance is important

Today I’m publishing a white paper which I wrote for our Customer Service department to give to our customers. It explains why database maintenance is so important in keeping your database happy and healthy. Continued »

Auto Close is almost as bad as auto grow

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

Auto Grow is the bane of my existence

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

Getting rid of Instant File Initialization (or enabling it if that strikes your fancy)

SQL Server 2005 introduced us to Instant File Initialization. This allows SQL Server to create files of any size without sitting there for minutes or hours (depending on the size of the files).

While this is great when creating your database, or extending your database files there is a cost to doing so. Before each data page is written the SQL Server will write all zeros to the page. It also has the potential of a security issue as any data fragments which are in the space which the file took up are going to be included in the backup and could then be read if the backup was lost.

Continued »

Does your SQL Server database improve SharePoint performance?

This morning I posted by first article up on SearchWinIT.com called “Does your SQL Server database improve SharePoint performance?“. It is all about getting the most out of your SharePoint environment by getting the most out of your SQL Server.

Denny

Don’t Consolidate Yourself Into Performance Problems - Archive

The webcast which I did for Quest Software called Don’t Consolidate Yourself Into Performance Problems is now available for viewing after the fact.

This is the webcast which I did last week for Quest Software.  It you had signed up for the session Quest should have sent you an email with this URL already.

 Thanks,

Denny

Don’t Consolidate Yourself Into Performance Problems

Consolidated too many databases or too many instances onto a single server?  Got users screaming for their own servers back?

Join myself and David Swanson from Quest Software (and frequent poster on SQLServerPedia.com) as we show you how to identify problematic applications so that they don’t take down all of the other applications on the server at the webcast “Don’t Consolidate Yourself Into Performance Problems“. Attend this webcast to save your consolidation project and to keep your users happy.

The webcast will be on Thursday, August 21, 2008 at 8 a.m. Pacific / 11 a.m. Eastern / 4 p.m. United Kingdom / 5 p.m. Central Europe.  After the presentation there will be a Q&A session where David and I will answer as many questions as possible.

Denny