A practical example on missing a filtered index

Hello all, Last week I did a brief presentation on SQLPort about hinting the database engine. What’s related here is that during the presentation, when I was discussing index hints, an attendee asked me about the usefulness of Filtered Indexes in certain scenarios, and how the database engine would behave differently in SQL Server 2008R2…


The SQL Swiss Army Knife #3 – View I/O per file – Updated

Hello all, Here is another one focusing on SQL scripts that may help on everyday DBA tasks, following the series “SQL Swiss Army Knife”. This script will return, very promptly, the overall I/O statistics for all databases in your server, ordered by stalled I/O, and is based on the sys.dm_io_virtual_file_stats DMV. This DMV returns I/O…


Can log files growth affect DML? Post-CU Update

Hello all, A while back I blogged on how log files can affect DML performance, depending on their structure and growth ratio. It recently came to my attention (thanks Paul Randal) that a fix was published late last February, in KB2455009, aimed at scenarios where one might experience slow performance when recovering a database, if…


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…

8

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 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  …