Bookmark : How is fill factor impacting my indexes?

David Levy writes about Fill Factor for TSQL Tuesday and provides a useful script too...

http://adventuresinsql.com/2010/09/how-is-fill-factor-impacting-my-indexes/

SSIS : OLEDB Error Codes

Some error messages I (ssis) generated today - 

-1071607116 : A rowset based on the SQL command was not returned by the OLE DB provider.

-1073450982 : component "Component Name" (1) failed the pre-execute phase and returned error code 0xC02092B4.

I had a strange sense of deja-vu and hence have to point myself back to this post to sort it!

Solace : SSIS, OLEDB and Stored Procedures

MCTS : SQL Server 2008, Business Intelligence Development and Maintenance

Just a quick note to say I passed 70-448 : Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

Not looking forward to the MCITP mind, that will be tough..

r

Bookmark : SSMS Templates

Templates are an overlooked feature of Management Studio.
I must remember to use them rather than jumping for Help / Books Online / Google !

Can't remember the syntax? Use templates!


Write your own SSMS Templates

Hyper-V : VHD size optimization #2

Time to revise an old post based on recent experiences with Hyper-V.
I posted on how to optimize the size of VHDs, but I'm revising that now to include step 2 below >

1) Defragment the VHD (Defraggler was better than the Windows tool)

2) Use Precompact.exe to a "zero out" i.e. overwrite with zeros available blank space. *

3) Shut down the VM and Shrink the VHD from Hyper-V.

* Precompact.exe is found in precompactor.ISO which in turn you get from the Virtual PC 2007 installation (in C:\Program Files (x86)\Microsoft Virtual PC\Virtual Machine Additions)

Link : Compacting Hyper-V files

Blatent Plug : SQLWorkshops.com - Free Performance Monitoring/Tuning Webcasts

There is a lot of good training material and video blogs for people wishing to further their SQL skills.
Pragmatic Works (for BI) , Cuppa Corner from SQLServerFAQ are my favourites, as well as community webcasts from Quest and Redgate.

Most of  the free stuff simply gets you going. It isn't rocket science, it just saves you an hour or so reading (another manual). Ramesh Meyyappan's SQLWorkshops site is different. 
The videos are still FREE , But they are 'Level 400' (from attending conferences I know this to mean 'the clever stuff'! ) Anyway, if you're interested in performance monitoring and tuning you can download them from http://www.sqlworkshops.com/webcast.
 
Ramesh Meyyappan attended SQLBits V where I saw him present his 'Let's make SQL fly' talk.
After presenting 'Monitoring & Tuning Parallel Query Execution' at SQLBits VI he is back for more on October 2nd to present 'Monitoring & Tuning Parallel Query Execution - Part II at SQLBits VII

* (yes I was encouraged to write this post due to a potential freebie t-shirt, but I do genuinely rate the webcasts)

rich

Windows Group Membership Checker

SQLServerCentral.com have published a script I use to look at Windows Group Membership. This is good if you use AD groups to manage security...

SSC : Windows Group Membership Checker



March 2011 Update : Putting the function here too now as SSC exclusivity period over...

/*
Script  : SQL Server - Windows Group Membership Checker
Version : 1.0 (August 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/

DECLARE @CurrentRow INT
DECLARE @TotalRows INT
SET @CurrentRow = 1

DECLARE  @SqlGroupMembership  TABLE(
    ACCOUNT_NAME      SYSNAME,
    ACCOUNT_TYPE      VARCHAR(30),
    ACCOUNT_PRIVILEGE VARCHAR(30),
    MAPPED_LOGIN_NAME SYSNAME,
    PERMISSION_PATH   SYSNAME
    )

DECLARE @WindowsGroupsOnServer TABLE(
   UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL 
 , Name  SYSNAME
 )
 
INSERT INTO @WindowsGroupsOnServer (NAME)
SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G' 

SELECT @TotalRows = MAX(UniqueRowID) FROM @WindowsGroupsOnServer

DECLARE @WindowsGroupName sysname 


-- Loop Each Windows Group present on the server
WHILE @CurrentRow <= @TotalRows 
   BEGIN 
  
  SELECT @WindowsGroupName  = [Name] 
  FROM @WindowsGroupsOnServer
  WHERE UniqueRowID = @CurrentRow 
  
    BEGIN TRY
    -- Insert found logins into table variable
    INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
       EXEC xp_logininfo @WindowsGroupName , 'members' 
    END TRY

    BEGIN CATCH
    -- No action for if xp_logininfo fails
       END CATCH
       
 SELECT @CurrentRow = @CurrentRow + 1   
 
   END 
   
-- Display final results
SELECT  @@servername AS Servername
    , [PERMISSION_PATH] AS WindowsGroup
    , Account_Name
    , Mapped_Login_Name
    , Account_Type
    , Account_Privilege
FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]