August 31, 2008

SSRS data sources


SQL Server Reporting Services
I recently needed to quickly produce a list of Reporting Services data sources. 
Since Reporting Services uses SQL Server to store report metadata, I wrote the following T-SQL code to retrieve the information I needed.


SELECT Distinct C.Name as 'Data Sources'
 FROM [REPORTSERVER].[dbo].[DataSource] AS DS
 LEFT OUTER JOIN ([REPORTSERVER].[dbo].[DataSource] AS DSL
 INNER JOIN [REPORTSERVER].[dbo].[Catalog] AS C
 ON DSL.[ItemID] = C.[ItemID]
 LEFT OUTER JOIN [REPORTSERVER].[dbo].[SecData] AS SD
 ON C.[PolicyID] = SD.[PolicyID]
 AND SD.AuthType = 3)
 ON DS.[Link] = DSL.[ItemID]
 WHERE c.name IS NOT NULL
 ORDER BY c.name

August 22, 2008

Replace Cursor with While Loop


The dreaded cursor . . .

Experiencing performance degradation due to cursor processing? Use a WHILE loop instead. Hint: remember to do benchmark testing to verify you'll see a performance gain!!! Here's some T-SQL code to get you started:

DECLARE
@RowCnt int,
@MaxRows int
SET @rowcnt = 1

CREATE TABLE #whileTable
   (RowNum            int IDENTITY (1,1) Primary key,
    DemographicID     int,
    DemographicTypeID int,
    Title             varchar(50),
    DataType          char(1)   )

insert into #whileTable
(DemographicID,Title, DataType, DemographicTypeID)
Select DemographicID,Title=Cast(DemographicID as varchar(12))
       , DataType, DemographicTypeID
From dbo.Demographic
Order by DemographicID
  
/* DELETE your cursor declaration */
/*
Declare democursor cursor for
    Select DemographicID, Cast(DemographicID as varchar(12))
            , DataType, DemographicTypeID
          From Demographic
    Order by DemographicID
    End
*/


/* REPLACE it with WHILE loop syntax and get the total number of rows to process */

select  @MaxRows=count(*) from #whileTable
WHILE exists    (Select Rownum, DemographicID, DemographicID
                               , DataType, DemographicTypeID
                   from #whileTable
                  where Rownum <= @MaxRows
                    and RowNum = @RowCnt
)

/* DELETE your open/fetch CURSOR & variable assignments */
/*
open democursor
Fetch democursor into @DemographicID, @Title, @DataType, @DemoType
While (@@Fetch_Status = 0)
Begin
*/


/* REPLACE it with the WHILE LOOP */

BEGIN – Begin WHILE loop
                 select @DemographicID=DemographicID, @Title=DemographicID
                        , @DataType=DataType, @DemoType=DemographicTypeID
                   from #whileTable
                  where Rownum <= @MaxRows
                    and RowNum = @RowCnt
  
– Additional query logic can go here
  
/* DELETE your last Cursor Fetch  */
/*
Fetch democursor into @DemographicID, @Title, @DataType, @DemoType
End
*/


/* REPLACE it - increment the WHILE loop counter */

 select @RowCnt = @RowCnt + 1 – increment counter for WHILE loop

 /* DELETE your close/deallocate cursor statements */
/*
End — of While (@@Fetch_Status = 0) Begin
Close democursor
Deallocate democursor
*/


 /* REPLACE it by ending your WHILE loop */

END – END WHILE loop

DROP TABLE #whileTable

August 20, 2008

Index fragmentation


Indexes become fragmented during insert, update, and delete transactions - and fragmented indexes can cause performance degradation.
  Internal fragmentation
If the index pages are not filled to the current fill factor level, you're dealing with internal fragmentation.
  External fragmentation
When the physical order of the the index pages doesn't match the logical order, you're dealing with external fragmentation.
  T-SQL code to check fragmentation and the size of the indexes on a table
 use dotnetnuke
go
SELECT * FROM sys.dm_db_index_physical_stats     (db_id(), object_id('tabs'), 1, NULL, 'DETAILED'go

OR use this code:
DBCC SHOWCONTIG('tabs', 1) WITH ALL_LEVELS, TABLERESULTS
go
  Correcting Internal fragmentation
To correct index fragmentation, execute the ALTER INDEX T-SQL statement.
Use ALTER INDEX...

Reorganize when external fragmentation levels are between 10 percent and 15 percent.

ALTER INDEX ALL ON DotNetNuke.dbo.tabs
REORGANIZE
GO

 If external fragmentation is greater than 15 percent, use the ALTER INDEX...Rebuild T-SQL statement.
 ALTER INDEX ALL ON DotNetNuke.dbo.tabs REBUILD WITH (FILLFACTOR = 90, ONLINE = ON) GO

SSIS in a clustered environment


If you're configuring SQL Server Integration Services (SSIS) in a clustered server environment, remember to change the value for the <ServerName> element in the SSIS configuration file to the virtual server name on each node of the cluster.
If you don't, SQL Server will probably throw this error:
SSIS MsDtsSrvr connection error Login timeout expired. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Named Pipes Provider: Could not open a connection to SQL Server (MsDtsSrvr)
Solution:
Run this query on the machine where you have SSIS installed:
select srvname from master.dbo.sysservers where srvid = 0
Navigate to the nodes of the machine where SSIS is installed and modify the configuration file: %Program Files%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml

You will have to replace the value of the <ServerName> element from a dot to the Virtual Server Name that was returned by the query because the dot resolves to the default SQL Server instance on the local server.
With SQL Server Named instances, change the value for the <ServerName> element in the MsDtsSrvr.ini.xml configuraton file.
Fire up SQL Server Configuration Manager, navigate to SQL Server 2005 Services and then restart SQL Server Integration Services.
Note: You may also have to configure remote access for Dcom Config for the MsDtsServer Properties (Security tab) in Component Services if running with a non-admin account. Kirk Haselden on how to make SSIS a clustered service:http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx