Learn More About SQL Server IO and Query Tuning in These Webcasts

I’m doing two new webcasts next week on Wednesday, December 19th, one in the morning and the other after lunch.

 

SSDs are a Game Changer for SQL Server Storage

No, don’t let the title mislead you.  This session is not exclusively about SSDs.  But this is my first session on IO and storage tuning that emphasizes SSDs over hard disks.  As Bob Dylan said “Times, they are a’changin'”.  This session on Wednesday, December 19th at 11:30 AM EST, sponsored by Astute Networks, takes you through all of the basics of storage and IO tuning, regardless of the underlying storage technology.  I’ll show you how SQL Server handles storage structures, how to identify IO activity on Windows and SQL Server, and best practices for minimizing IO bottlenecks.  Register now for: Storage IO Best Practices for SQL Server and a New Approach to Solving Application Performance Issues.

 

Write Better SQL Queries

The next webcast on Wednesday, December 19th at 2 PM EST, is with me, Aaron Bertrand  (Twitter Blog) and SQLCruise Impresario & Microsoft MVP Tim Ford  (Twitter | Blog) as we take you through the query tuning process, discussing important DMVs to use during query tuning, as well as demonstrating several essential query tuning techniques that every SQL developer should know.  Not only are we presenting an hour of top quality technical content, we’ll also be giving away some cool prizes, including the grand prize of a paid registration for the upcoming SQLCruise Miami, a $1,395 value!  Register now for: SQL Server Query Tuning Best Practices, Hosted by Kevin Kline and Aaron Bertrand with special guest Tim Ford

 

I hope to see you at both of these sessions next week!  Best regards,

-Kev

-Follow me on Twitter!

Comments

  1. Kevin, as a IT Pro I have a client with a SBS ’08 server with 9 workstations running a combo of XP Pro and Win 7 Pro. All the latest updates have been applied as of 12/18/12. The Server is running MS-SQ L SharePoint which having ran several disk defrags and see that a couple of files which have grown very large, one is 14Gb compressed down to 3.6Gb. This is causing the defrag process to take over 4 hours. The file name is SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6_log.LDF which is 14,539,584Kb AND a ShareWebDb_log.LDF file which is 2,876,352Kb. So a couple of obvious questions are how to better manage the size? Is the size of these files causing significant degradation. As we know performance is relative to what you’re used too. Any assistance is much appreciated.

  2. Hi Scott,

    A couple quick notes. First, NTSF file compression is very bad for SQL Server performance. It’ll slow down SQL Server enormously. Otoh, your client probably doesn’t care about performance as much as saving disk space (and money), as many SMBs are. I strongly encourage you to exclude all SQL Server data and log files from NTFS compression and, for that matter, anti-virus scans for this and other customers using SQL Server.

    Because these are LDF files, we can tell that these are SQL Server log files. First thing you should do is go into the SQL Server Management Studio and make sure that the database is set to SIMPLE Recover Mode. The Books On Line (BOL) can take you through the step-by-step process for doing this.

    Here’s what’s happening: SQL Server is designed to make your data recoverable, should there be an outage, a power failure or whatever. So that means SQL Server is, by default, set to FULL recovery mode. Full recovery mode tells SQL Server that you’re going to perform manual data and log file backups according to your own design. A log file backup is the only manual process in SQL Server which truncates the log and keeps it small. That’s because it backs up all those transactions and gives the signal to SQL Server that it’s safe to truncate. There is another way, though. When you set the database to Simple recovery model, you tell SQL Server “I ain’t got time for that biz” and it automatically truncates the log every minute or so. Although I can’t be sure from a distance, but chances are that the 14.5GB file and 2.8GB file are mostly air and empty space in the first place. So with the databases set to Simple recovery mode, your LDF files will become much smaller.

    That doesn’t mean the files will suddenly become small. You have to run a special process for that too. There a great blog post from my buddy, Brent Ozar, here (http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/) that will take you through the whole process that you need to follow. You’ll have lots of space after going through the process and, very likely, some billable hours for your client.

    Let me know what other questions you have. Best regards,

    -Kev

  3. I wasn’t able to attend the first webcast. Do you know whether it will become available online or whether the slides are available somewhere?

    Thanks in advance!

  4. I believe that everyone who registered should receive an email with links to the archived sessions. Let me know if you can’t get that info.

  5. Hi Kevin,

    You mentioned on your December 29th blog that NTFS compression is very bad for SQL files and performance. Since the entire drive is partitioned using NTFS is it worth turning OFF compression and if so, how is that accomplished? This SQL program has been running for many years, so it appears that the files are only going to grow larger.

    On the subject of your friend Brent Ozar’s blog I will review it and discuss the idea of shrinking these large files with my client. Hopefully I’ll be able to express to him the importance of managing and reducing the file size as it relates to performance.

    Thanks again for your insite.

    Regards,

    Scott

  6. I have been exploring for a bit for any high-quality articles or weblog posts on
    this sort of area . Exploring in Yahoo I ultimately
    stumbled upon this web site. Studying this info So i’m happy to express that I’ve a very excellent
    uncanny feeling I found out exactly what I needed. I such a lot without
    a doubt will make sure to do not disregard this web site and give it
    a glance on a continuing basis.

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.