ANUG Talk: Optimizing SQL Server 2005

Written on March 12, 2009 by Mark S. Rasmussen in Development: .NET, SQL Server: Optimization, Presenting

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

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 :)

Click here to download the video (.wmv)

Feedback

Robert Klujeff wrote on 3/12/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: www.sqlskills.com/.../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 wrote on 3/12/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.

Post Comment

Name
Email
Url
Comment
	Remember Me?
	Please add 1 and 6 and type the answer here: Enter the code shown above: