More SQL Server 2008 performance tricks

1. Optimize for ad hoc workloads option

http://msdn.microsoft.com/en-us/library/cc645587.aspx

and http://sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx

2. New compression features in SQL Server 2008

http://www.sqlskills.com/blogs/kimberly/post/SQL-Server-2005-and-2008-Compression.aspx

Also see this article for deciding on when to apply database compression and whether to use ROW or PAGE level compression...

http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

3. Filtered indexes

http://technet.microsoft.com/en-us/library/cc280372.aspx

SQL 2008 Change data capture

Fed up of using triggers to track changes on your data tables? Try using change data capture instead.

"Change data capture provides information about DML changes on a table and a database. By using change data capture, you eliminate expensive techniques such as user triggers, timestamp columns, and join queries."

http://msdn.microsoft.com/en-us/library/cc627397(v=SQL.105).aspx

SQL Server 2008 Filtered Index and xEvents

Just came across these bad boys, that can drastically increase your query performance as well as reduce the over head in the maintenance and storage costs of the index

 

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate     ON Production.BillOfMaterials (ComponentID, StartDate)     WHERE EndDate IS NOT NULL ;

For more information on these see here

http://technet.microsoft.com/en-us/library/cc280372.aspx

or here

http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/

Also just had my eyes opened to this new concept in SQL Server 2008 called xEvents

http://sqlserverpedia.com/blog/sql-server-bloggers/are-my-connections-to-sql-server-using-connection-pooling-correctly/

which can be helpful for performance reasons...

http://msmvps.com/blogs/eladio_rincon/archive/2008/12/07/using-xevents-extended-events-in-sql-server-2008-to-detect-which-queries-are-causing-page-splits.aspx

VBA Excel Macro for Hyperlinking first column

Use this macro in an excel 2010 spreadsheet to turn the first column of a table into hyperlinks, using the text of the table as a hyperlink.

Sub CreateLinks()
'
' CreateLinks Macro
'
' Keyboard Shortcut: Ctrl+R
'
   
    Dim i As Integer

    'Starting on row 2 because I have a header.
    Range("A2").Select
   
    'move to the final cell in the first column
    ActiveCell.End(xlDown).Select
       
    Do Until ActiveCell.Row = 1
   
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
           ActiveCell.Text, TextToDisplay:= _
           ActiveCell.Text, ScreenTip:= _
           "Click here to open Person record in Beacon"
               
        Range("A" & ActiveCell.Row - 1).Select
       
    Loop
   
   
End Sub