Wednesday, March 31, 2010

Linked Server Connectivity Test Procedure

My latest submission has been published on as script of the day today :)

SSC : Linked Server Connectivity Test

March 2011 Update : Putting code here after exclusivity period elapsed -
Script  : Linked Server Connectivity Checker
Version : 1.0 (March 2010)
Author  : Richard Doering
Web     :

CREATE PROCEDURE [Utils].[LinkedServerTest] 
 @ServerName SYSNAME
,@emailProfile VARCHAR(255) = ''
,@emailTo VARCHAR(255) = ''

EXEC @Test= sys.sp_testlinkedserver @servername 

PRINT 'Sucessfully connected to ' + CAST(@servername as VARCHAR(30))

PRINT 'Failed to connect to ' + CAST(@servername as VARCHAR(30))

IF (@emailProfile <> '') AND (@emailTo <> '')
DECLARE @emailSubject VARCHAR(255)
DECLARE @emailBody VARCHAR(8000)

SET @emailSubject = 'Linked Server Connnection Failure : ' + @servername + ' cannot be accessed from ' + @@SERVERNAME
SET @emailBody = @emailSubject

EXEC msdb.dbo.sp_send_dbmail 
 @profile_name= @emailProfile
, @recipients=@emailTo
, @body=@emailBody
, @subject=@emailSubject
, @importance='High'

RAISERROR ('Linked Server Failure', 16, 1, @emailSubject) WITH LOG




Monday, March 29, 2010

TSQL : Which indexes have I disabled?

Have disabled several reporting indexes to load data faster.
(Yes, some ad-hoc reporting is being done on my processing system but I'm working on that...)
So, how to determine which indexes I have disabled again? >
 SCHEMA_NAME(schema_id) AS [SchemaName]
 ,  AS [TableName]
 , AS [IndexName]
 , i.type_desc  AS [IndexType]
 , CASE i.is_disabled  WHEN 0 THEN 'Enabled' ELSE 'Disabled' END AS [IndexStatus]
 , i.fill_factor AS [FillFactor] 
 , 'DROP INDEX [' + + '] ON [' + SCHEMA_NAME(schema_id)  + '].[' + + '] ' AS [DropCommand]
FROM sys.indexes i
INNER JOIN sys.objects o
 ON i.object_id = o.object_id 
 AND o.is_ms_shipped = 0
WHERE is_disabled = 1 
ORDER BY 1,2,3

Disabling an index - ALTER INDEX IX_myindex ON schema1.table1 DISABLE
Rebuilding an index - ALTER INDEX IX_myindex ON schema1.table1 REBUILD

Solace : Indexes 101
MSSQLTips : Disabling Indexes in SQL 2005 and SQL Server 2008

Friday, March 26, 2010

Index Usage Query

" How are my indexes being used? "

The following TSQL (from MSSQL Tips) shows us...

         I.[NAME] AS [INDEX NAME], 
FROM     sys.dm_db_index_usage_stats AS S 
         INNER JOIN sys.indexes AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 

It can easily be rolled up like this to produce 1 line per index >

FROM  sys.dm_db_index_usage_stats AS S 
INNER JOIN sys.indexes AS I 
--WHERE    OBJECT_NAME(S.[OBJECT_ID]) = 'tablename'

SSIS Contol Flow - For Loop with Breakout

Some screenshots on how to set up a For Loop that repeats until a variable is set.
The Variable is called Breakout in this example >

Thursday, March 25, 2010

Gail Shaw's JOIN Performance Testing (of March 2010)

SQL MVP Gail Shaw does some performance testing on comparing join types, with surprising results.
A brief summary of her findings with her final quotes for each page >

IN vs INNER JOIN (IN wins by a nose)
"If all you need is to check for matching rows in the other table but don’t need any columns from that table, use IN. If you do need columns from the second table, use Inner Join."

EXISTS vs IN (little difference)
"IN and EXISTS appear to perform identically both when there are no indexes on the matching columns and when there are, and this is true regardless of whether of not there are nulls in either the subquery or in the outer table."

NOT EXISTS vs NOT IN   (no difference)

"On non-nullable columns, the behaviour and performance of NOT IN and NOT EXISTS are the same, so use whichever one works better for the specific situation."

"If you need to find rows that don’t have a match in a second table, and the columns are nullable, use NOT EXISTS. If you need to find rows that don’t have a match in a second table, and the columns are not nullable, use NOT EXISTS or NOT IN."

Tuesday, March 23, 2010

WSUS : Force Updates

Use this command to get all updates from the WSUS server.

wuauclt.exe /resetauthorization /detectnow

Then go to Control Panel > Windows Update

Sunday, March 21, 2010

Exploring Bookmark/Key Lookups

A Bookmark Lookup is a Key Lookup i.e. lookup of other columns by using the key in the join.

SQL Server's execution plan has changed the representation of these operations over the years...

SQL 2000 : Bookmark Lookup
SQL 2005 : Nested Loop Join + Clustered Index Seek (confusing)
SQL 2005 SP2+ :
Key Lookup (Clustered) - When table has a clustered index
RID Lookup - when table has NO clustered index

Key lookups
Definition according to MSDN >
"Introduced in SQL Server 2005 Service Pack 2, the Key Lookup operator is a bookmark lookup on a table with a clustered index "

And according to SQL MVP Gail Shaw

" the one that fetches extra columns from the clustered index when the nonclustered index that’s used to retrieve the rows doesn’t have all of the columns required. "

Gail explains them well here...

Pinal Dave tells us how to get round them...

One method is to add 'Included columns'

Pinal Dave lists the advantages of included columns as >

" 1) Index can exceed the 900-byte limitation of the index key.

2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.

3) Size of the key index can be reduced, which improves the overall performance of the index operation. "

Links >
SSC : Covering Index using Included Columns
SQL Solace : Index Sizes using Included Columns

Friday, March 19, 2010

Bookmark : SSIS Tuning Tips

A timely post considering my workload right now, Jamie Thomson's SSIS Lookup Component Tuning Tips
Especially the ‘thinking outside the box’ section regarding dividing up lookup tasks.

(useful, will me trying out asap)

Wednesday, March 17, 2010

SSIS Annoyance ; Derived Column Data Types

SSIS wants to to use a Unicode String when you know a non-unicode will do.

Found this today, and it is covered perfectly here >

BI Monkey : SSIS Derived Column forcing data type changes

Force SQL to use a named index....

Should SQL chose a less than optimal plan, you can influence index choice.I say influence, i mean FORCE :)

A SQL Query (I know it uses * !)
SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN Table2 ON Table1.Id = Table2.Id
Add an index hint e.g.
SELECT Table1.*, Table2.*
FROM Table1 WITH(INDEX(IndexName))
LEFT JOIN Table2 ON Table1.Id = Table2.Id
You can still add NOLOCK to that hint too...
SELECT Table1.*, Table2.*
LEFT JOIN Table2 ON Table1.Id = Table2.Id
A hint could equally have been applied to the joined table or both.

Links :
Brent Ozar has some good reasons for NOT using index hints though so it's only for use as a last resort.
Andrew Chen's post on Index Hints
You can also force SQL's choice of Query Plan

Tuesday, March 16, 2010

Bookmark : It Depends

A brilliant SQL Server Central article published this morning, comparing MAX() , TOP(1) and ROWNUMBER()  as methods for getting ID values back from a table.

Sunday, March 14, 2010

Bookmark : SQL Server Best Practices

Doh! , there is a SQL Server Best Practices page on Technet.

You'd of thought I'd have found that before really...

Technet : SQL Server Best Practices

Saturday, March 13, 2010

SQL Trends - SQL Server Read/Write Ratio

SQL Server Read/Write Ratio :

CAST(SUM(user_seeks+user_scans+user_lookups) AS decimal)/CAST(SUM(user_updates)+SUM(user_seeks+user_scans+user_lookups) AS decimal) 
AS ReadPercent 
, CAST(SUM(user_updates) AS decimal)/CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal)  
AS WriteRatio 
FROM sys.dm_db_index_usage_stats

Ref : Is 80/20 a nineties estimate?

Friday, March 12, 2010

Management Studio - Scripting Agent Jobs Annoyance :/

I've noticed this a few times, but thought I'd finally note it because I still cannot think of any sensible reason for it. It's an annoyance / feature when you script sql agents jobs in Management Studio via the 'Generate Scripts' option.

To reproduce -
1) Right click an agent job
2) Select 'Script Job as'
3) Select either 'DROP To' or 'DROP And CREATE To' ...

My annoyance is at the top of the generated script in the delete step (sp_delete_job). You'll note below that the job to be deleted is referenced by job_id which is server specific (meaning the script cannot be reused without modification...
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'my agent job')
EXEC msdb.dbo.sp_delete_job @job_id=N'c3776ebf-d7f4-4b97-bc74-c2d3aa745054', @delete_unused_schedule=1

The modification is a simple one, and is demonstrated below. Pass the name of the job via the @job_name parameter rather than the @job_id. Maybe there is some security / safety reason for this behaviour but it just seems a pain to me...
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'my agent job')
EXEC msdb.dbo.sp_delete_job @job_name= N'my agent job', @delete_unused_schedule=1

Thursday, March 11, 2010

Included Columns - Index Sizes

When viewing an execution plan in Managment Studio it sometimes suggests an index you could add. From what I've seen, these suggestions frequently include adding adding every column as an 'included column'.

Doing this has the storage affect of duplicating the table. Not exactly ideal for storage, even if you do eliminate your Bookmark Lookups.

In the screenshot above >
ix_1 - this is the clustered index , i.e. the table itself.
ix_3 - is a non clustered index on 2 columns (bigint, datetime2)
ix_4 - is another non clustered index on the same 2 columns (with all 10 included columns as per Management Studio's suggestion)

Obviously this is an extreme example (on a schema screaming for some normalization), but I thought it noteworthy anyway.


Tuesday, March 9, 2010

SSIS : Package Execution - Start without Debugging

As easy as it is to debug, running a package through BIDs prior to deployment is SLOW.

Hence the option many seem to miss, 'Start without debugging' >
Select it from the Debug window or use CTRL+F5 to do this.
The package then runs in a command window a lot faster (shown below).

Sunday, March 7, 2010

Bookmark : Why you can't work at work

A brilliant video by Jason Fried that was doing the email rounds last week.
Sadly I think the irony was lost on most of it's recipients though...

Link : Why you can't work at work

Friday, March 5, 2010

TSQL : Total Size of Partioned Indexes

If you run my Index size script you get a row for each partition.

This version rolls up the results by grouping on the common columns and summing the index size information. Far more helpful for a quick glance...

SELECT AS SchemaName
, AS TableName
, AS IndexName
,sys_indexes.type_desc AS IndexType
,MAX(partitions.rows) AS TableRows
,SUM(partition_stats.used_page_count) * 8 AS IndexSizeKB
,CAST(SUM(partition_stats.used_page_count) * 8 / 1024.00 AS Decimal(10,3))AS IndexSizeMB
,CAST(SUM(partition_stats.used_page_count) * 8 / 1048576.00 AS Decimal(10,3)) AS IndexSizeGB
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
ON partition_stats.[object_id] = sys_indexes.[object_id]
AND partition_stats.index_id = sys_indexes.index_id
AND sys_indexes.type_desc <> 'HEAP'
INNER JOIN sys.objects sys_objects
ON sys_objects.[object_id] = partition_stats.[object_id]
INNER JOIN sys.schemas sys_schemas  
ON sys_objects.[schema_id] = sys_schemas.[schema_id]
AND <> 'SYS'
INNER JOIN sys.partitions partitions
  ON partitions.object_id = partition_stats.object_id 
--WHERE partition_stats.[object_id] = object_id('dbo.tablename')
ORDER BY 6 desc

Thursday, March 4, 2010

VAMT : "SKU not Supported"

In VAMT (Volume Activation Management Tool) i'm getting the message "SKU not Supported" for new Windows 2008 R2 VMs.

If you get this, you need to upgrade VAMT to version 2.0 (I was using 1.1) >

Download : Volume Activation Management Tool 2.0

VAMT 2.0 has a new look and is now a MMC snap-in too.

Tuesday, March 2, 2010

TSQL : Custom date functions

Really simple these, but posting them incase someone googles for them / i forget them...

I'm using DATETIME and rounding to the last second of the period in question , but you could quickly change that.

CREATE FUNCTION dbo.LastDayOfMonth (@inputdate DATETIME)
    RETURN DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@inputdate)+1,0))

The results -
SELECT dbo.LastDayOfMonth (GETDATE())
2010-03-31 23:59:59.000

SELECT dbo.LastDayOfMonth ('2010-02-03')
2010-02-28 23:59:59.000

CREATE FUNCTION dbo.LastDayOfWeek (@inputdate DATETIME)
    RETURN DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@inputdate)+1,0))

The results -
SELECT dbo.LastDayOfWeek ('2010-02-03')
2010-02-07 23:59:59.000

As default, my week starts Monday and ends on Sunday.
To change this, use SET DATEFIRST

Monday, March 1, 2010


Run singularly, both appear to generate a different result each time.
Always returns the same result with a query however...

Was featured last week on SSC....