SQLPASS: Are You Smarter Than an MCM? VLF Demos

Are You Smarter Than an MCM?  I had a great time on Wednesday co-presenting with some great SQL peeps! We dressed up, had a lot of fun, and shared a few technical tips along the way. My demo was on VLFs, an often forgotten and/or misunderstood part of the transaction log technology. The demo filesContinue reading “SQLPASS: Are You Smarter Than an MCM? VLF Demos”

SQL PASS: All the Magic Knobs

SQL PASS 2011 DBA-319-C #SQLPASS All the Magic Knobs – Low Effort, High Return Tuning Key points covered: Power Savings = High Performance Smart Virtualization Enough Hardware Control other apps, filter drivers Optimize for ad hoc workloads = ON Compression = ON Set LPIM + Max Server Memory Pre-size files, avoid shrink and autogrow FastContinue reading “SQL PASS: All the Magic Knobs”

24HOP: SQL Server Performance Tools

Thanks to everyone who attended my session today! I think the 740 or so of you overloaded the audio. 🙂 Sorry everything wasn’t clear on the voice delivery, hopefully the slides (below) provide enough information for you to know what to download. Then you can load your own SQLDiag data into SQL Nexus and see allContinue reading “24HOP: SQL Server Performance Tools”

Power Saving Options on SQL Server

Power Saving Options on SQL Server   Windows 2008 and Windows 2008 R2 have settings for “Power Options”. Windows 2008 R2 has additional power improvements related to “core parking” (temporarily suspending certain cores) and “tick skipping” (extended idle and sleep states). The default power setting for Windows 2008 and 2008 R2 is “Balanced Power” whichContinue reading “Power Saving Options on SQL Server”

The ins and outs of MAXDOP

Configuring MAXDOP   One of the few sp_configure options that good DBAs regularly change is “max degree of parallelism” or MAXDOP. So of course everyone is looking for the one best value to use for that setting. As with most things in the database world, the answer is “it depends.” I’m going to start withContinue reading “The ins and outs of MAXDOP”

Online learning with Kalen Delaney – SQL Server 2008 Indexes – Internals and Best Practices

Kalen always does a great job of explaining the guts of SQL Server. She has a deep, intuitive grasp of index internals. Even better, she can take that knowledge and share it so everyone can learn something, whether you’re a relative newbie or a veteran. This particular workshop is a deep-dive with advanced topics soContinue reading “Online learning with Kalen Delaney – SQL Server 2008 Indexes – Internals and Best Practices”

Windows storport enhancement to help troubleshoot IO issues

For Windows 2008 and Windows 2008 R2 you can download a Windows storport enhancement (packaged as a hotfix). This enhancement can lead to faster root cause analysis for slow IO issues. Once you apply this Windows hotfix you can use Event Tracing for Windows (ETW) via perfmon or xperf to capture more detailed IO informationContinue reading “Windows storport enhancement to help troubleshoot IO issues”

SQL Server Performance Tools – Boise Code Camp Presentation

Today I am presenting about SQL Server Performance Tools at the Boise Code Camp. You can download the slides and supporting files here on this blog (at the bottom it says Attachment(s): PerformanceTools.zip ). The basic agenda of items covered is:   ¢  Methodology ¢  SQLDiag ¢  PSSDiag ¢  SQLNexus ¢  Profiler ¢  PerfMon ¢ Continue reading “SQL Server Performance Tools – Boise Code Camp Presentation”

How and Why to Enable Instant File Initialization

See my new blog post (written with Denzil Ribeiro) about “How and Why to Enable Instant File Initialization” on our PFE blog. Keep an eye on the PFE blog for more posts from my team in the near future.

SQL Server and Fragmentation

There are several types of fragmentation that affect SQL Server, all of which have the potential to cause performance problems.   1)      At the disk/OS level individual files can become fragmented due to growth over time. This can be addressed by a defragmentation tool at the SAN or Windows level. Ask your storage team aboutContinue reading “SQL Server and Fragmentation”