ANUG Talk: Optimizing SQL Server 2005

Tonight I held my SQL Server Optimization talk at ANUG. There was an impressive turnout of almost 50 people, and based on the feedback I've received so far, I think it went alright :)

You can download the slides, code and sample database (with sample data) below. Note that slides are in Danish.
Slides + Code
DB Backup (12mb)

I'm afraid the query scripts are not in text format, they're only included as images in the slides. I'll probably be presenting some of the topics on my blog over time, where the scripts will also be included.

When I held a TechTalk on the same topic in January, Daniel taped it. Unfortunately the video quality could be better, but if you combine it with the slides, it's alright. Even more unfortunate however, the last 30 minutes or so are missing - guess I should start scheduling my talks for whatever space is available on the cam hard drive :)

You can download/vide the video here:
MarkSqlOpti.wmv

kick it on DotNetKicks.com

Mar 11th, 2009, 11:59 PM | Posted in .NET, SQL Server

Comments

Robert Klujeff | Mar 12th, 2009, 10:45 AM

Thank you for your talk yesterday, it contained a lot of wonderfull details we can use in our daily work, it was great.

I asked you a question regarding extending the database, but didn't remember the exact terminology, wherefore you refused this had any performance costs. For your information I include the details I didn't remember yesterday:

I thought about extending the logfile, which uses something called VLF – Virtual Log Files. According to this link: http://www.sqlskills.com/resources/SQLServerHAChapter9.pdf we should care about the number of VLF, and take care to shring the logfile at each backup (and of course not accept the default increase of 1 MB a time, as you told us).

Mark S. Rasmussen | Mar 12th, 2009, 1:49 PM

Indeed a too large amount of VLF's may decrease performance. To avoid it from start, we have to plan our log & database auto growth values according to the growth expectations - as they also mention in the article you linked.

If you have set your autogrowth size properly - or even better, created a log file with a large enough initial size (so no auto growths will be needed), you should have no problem in regards to VLF's, and will thus not need to shrink your log file after log backups. You would only need to shrink the log file (once) after a log backup in the case you had VLF issues.

Add comment

After you have posted a comment, an email will be sent to the provided email address. Before your comment is activated, you will have to click the confirmation link within the email.

Name:

Email (only used for validation):

Website (optional):

Message:

Notify me when new comments are added:

Please type the following letters into the box below:  

Post!