Linked Server Connectivity Test Procedure

My latest submission has been published on SQLServerCentral.com 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     : http://sqlsolace.blogspot.com
*/
CREATE SCHEMA [Utils] AUTHORIZATION [dbo]
GO

CREATE PROCEDURE [Utils].[LinkedServerTest] 
 @ServerName SYSNAME
,@emailProfile VARCHAR(255) = ''
,@emailTo VARCHAR(255) = ''
AS 
BEGIN
DECLARE @Test BIT

BEGIN TRY
EXEC @Test= sys.sp_testlinkedserver @servername 

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

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

IF (@emailProfile <> '') AND (@emailTo <> '')
BEGIN
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'
END

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

END CATCH

END

GO

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? >

SELECT 
 SCHEMA_NAME(schema_id) AS [SchemaName]
 , o.name  AS [TableName]
 , i.name 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 [' + i.name + '] ON [' + SCHEMA_NAME(schema_id)  + '].[' + o.name + '] ' 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

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

Technet SQL Scripts

These seem to have appeared on Technet last year. Linking here and now as a memory jogger (because I intend to look at them further)....

Systems Scripts >

• Buffer Counts by Object and Index
  
• Determine CPU Resources Required for Optimization
  
• List Indexes With the Most Contention
  
• List Statements By Input/Output Usage
  
• Calculate Average Stalls
  
• List SQLOS Execution Model Information
  
• List Real-Time Tempdb Statements

Locking Scripts >

Analyze Index Statistics

- these are demos, how to reproduce locking behaviour >

• List Locks and Reads
  
• List Lock and Serializable Information
  
• Compare Locking and Repeatable Reads

Index Usage Query

" How are my indexes being used? "

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

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
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 
WHERE    OBJECT_NAME(S.[OBJECT_ID]) = 'mytable'

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

SELECT   
 OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
  I.[NAME] AS [INDEX NAME], 
 SUM(USER_SEEKS) AS TOTAL_SEEKS, 
 SUM(USER_SCANS) AS TOTAL_SCANS, 
 SUM(USER_LOOKUPS) AS TOTAL_LOOKUPS, 
 SUM(USER_UPDATES) AS TOTAL_UPDATES 
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 
--WHERE    OBJECT_NAME(S.[OBJECT_ID]) = 'tablename'
GROUP BY 
 OBJECT_NAME(S.[OBJECT_ID])
    ,I.[NAME] 
ORDER BY
 OBJECT_NAME(S.[OBJECT_ID])
    ,I.[NAME]  

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 >

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."

LEFT OUTER JOIN vs NOT EXISTS

"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."

Bookmark : Change Data Capture (CDC) in Data Warehousing

http://dwbi1.wordpress.com/2010/03/11/change-data-capture/

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

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...
http://sqlinthewild.co.za/index.php/2009/01/27/a-bookmark-lookup-by-any-other-name/

Pinal Dave tells us how to get round them...
http://blog.sqlauthority.com/2009/11/22/sql-server-remove-bookmark-key-lookup-4-different-ideas/

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

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)

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.*
FROM Table1 WITH(INDEX(IndexName),NOLOCK)
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

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.


http://www.sqlservercentral.com/articles/T-SQL/69481/

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

SQL Trends - SQL Server Read/Write Ratio

SQL Server Read/Write Ratio :

SELECT   
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?

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
GO

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
GO

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.

r

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).

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

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   sys_schemas.name AS SchemaName
,sys_objects.name AS TableName
,sys_indexes.name 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_schemas.name <> 'SYS'
INNER JOIN sys.partitions partitions
  ON partitions.object_id = partition_stats.object_id 
--WHERE partition_stats.[object_id] = object_id('dbo.tablename')
GROUP BY
sys_schemas.name
,sys_objects.name
,sys_indexes.name
,sys_indexes.type_desc
ORDER BY 6 desc

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.

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)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@inputdate)+1,0))
END
GO

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)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@inputdate)+1,0))
END

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

NEWID() vs RAND()

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....
http://www.sqlservercentral.com/articles/T-SQL/69111/