High I/O high CPU from fragmented indexes

Interesting blog post that goes a long way to describing the mechanics involved when a CLUSTERED index is poorly designed

CLR to create a directory / folder

The reason for this is the ridiculous security settings required to use xp_cmdshell - specifically having to use sp_xp_cmdshell_proxy_account . The issue is that this requires a domain login and password, which is problematical if your account passwords change regularly.

The way around this is to use a CLR to create the folder. The code for the CLR (assuming you know how to create a CLR project) is as follows :


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]

public static void cproc_createfolders(string folderName)
{
//Check whether folder exists
if (!System.IO.Directory.Exists((folderName)))
{
System.IO.Directory.CreateDirectory(folderName);
}
}
};

Simple enough. Installing it isn't. As this is accessing external to SQL Server, you need to set up the necessary permissions. There are two ways of doing this, via a login or by setting the database to be trustworthy. I used the latter, as this is a database behind a firewall, internal use only so felt it was OK. If you do not do this, you will get an error along these lines :

Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

So :

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON

Next is to install the assembly. This needs to specify that it is for External Access:


CREATE ASSEMBLY CreateDIR
FROM 'C:\CLR\myCLR.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO


And finally a stored procedure to allow access from SQL :


CREATE PROCEDURE dbo.cproc_createfolder
(
@strPath Nvarchar(1024)
)
AS EXTERNAL NAME CreateDIR.StoredProcedures.cproc_createfolder



Access to this routine is via dbo.cproc_createfolder.

Took a lot of searching to find this, so I think it is only fair to credit Vadivel's Blog.

Save/default parameters for SSRS

The following link is a rather useful way to save parameters and recall by user in SSRS.

Handling multi value parameters from SSRS in a stored procedure

Actually quite easy, using this function :

CREATE FUNCTION [dbo].[fn_SplitIN]
/* This function is used to split up multi-value parameters */
(
@ItemList VARCHAR(4000),
@delimiter CHAR(1)
)
/* ========================================================================
fn_SplitIN

Function to return a table for joining for a multi valued parameter from SSRS

How to use :

SELECT cols
FROM mytab
WHERE cols IN (SELECT Item FROM fn_SplitIN(@multiValueParam,','))

===========================================================================
Version Date Author Comment
------- ---- ------ -------
v0.00 23/03/2009 Brian Jones Initial Version
=========================================================================== */

RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList VARCHAR(4000)

SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ',@delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END

RETURN
END

Conditional Where Clauses

Excellect article by Erland Sommarskog about the various ways of doing SQL conditional where clauses. What sets this apart is that Erland has comparative performance figures, showing what way is best. Also kept up to date.

Change server collation

Changing SQL 2005 collation does not require a total re-install. Instead the following can be done :

1. Backup all user databases
2. Drop user databases
3. Make sure security is SQL Server
4. Rebuild the master database as follows :

cd "\Program Files\Microsoft SQL Server\90\Setup Bootstrap"

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="sa-pwd" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AS

More details are here

Lock Down SQL 2005

Nice website detailing whats needed to lock down a SQL Server 2005 implementation