About Maintenance Plans – grooming SQL Server

Latest update: 10/24/2016 (Check change log) Download scripts on GitHub. Hello all, I admit I’m not a big fan of Maintenance Plans (the feature that goes by that name) mainly because of its “do or die” approach. So, it was not by chance that I authored and keep updating the AdaptiveIndexDefrag procedure. Good maintenance (or lack…


A case of seeks and scans

Hello all, A couple weeks ago while I was onsite, I was looking at some queries that had performance issues. To the customer, one in particular had become a conundrum that could not be easily answered with the usual approach of good indexing, simply because there was already a good covering index for that highly…

2

Auto Update Statistics Asynch - Are you really, really using it?

Hello all, It’s been awhile since I’ve updated the blog from my part. It’s been a rough month, and I’ve been on vacation prior to that, but I’m back. What I want to get your attention in this post is to the “Auto Update Statistics Asynchronously” database option. Not to discuss in which scenarios to…

0

MOSS, BizTalk, SCOM and SQL Server Collations

Hello all, The topic today is database and server collations. As part of pre-installation tasks for SQL Server, we should always take into account what is the collation required by the user databases. Why is that? Collation is a subject many times overlooked when installing SQL Server, and many just use the default when installing…


Best Practices on FILESTREAM implementations

Hello all, Recently I was asked to advise a customer on a FILESTREAM implementation he was devising, and part of that ended up becoming this blog post that I hope can be of some help for you. When to use FILESTREAM? In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in…

6

The SQL Swiss Army Knife #6 - Evaluating compression gains

Download code here: view_CompressionGains.sql Hello all, Here is another one focusing on SQL scripts that may help DBAs, following the series “SQL Swiss Army Knife”. This time we are evaluating table and index compression, specifically an approach on how to select the best compression method for a database object. There is a great deal of…


SQL Best Practices for Biztalk

As promised in my last post, here is the post for SQL Best practices for a Biztalk installation. Althought the majority of best practices for a SQL Server instance are still valid for a Biztalk implementation we should consider some points that are different. In a Biztalk instance: Auto create statistics must be disabled Auto…

1

SQL Best Practices for MOSS

Hello all,  One question that arises often is how can we correctly configure SQL Server to work with MOSS?. Although the majority of best practices for SQL does apply, there are some diferences that it is important to be aware of. 1 – Architecture recommendation 1.1 – Limit content database size to enhance manageability Plan…

1

SQL Server & Disk Partition Alignment

Jimmy May is sharing a very good slide-deck about the importance of Disk Partition Alignment.   http://blogs.msdn.com/ace_team/archive/2008/11/04/disk-partition-alignment-sector-alignment-part-i-slide-deck.aspx   You can find some other useful information about this topic on the Microsoft web site:   ·         Predeployment I/O Best Practices – SQL Server Best Practices Articlehttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx   ·         Disk Subsystem Performance Analysis for Windows subsys_perf.doc http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx  …