Executing the same SQL statements against multiple servers and databases using SQL Server Management Studio, and Merging the Results

Validated against: SQL Server: 2008 R2.

If you need to apply the same SQL statements against multiple servers and databases, this can be accomplished using SQL Server Management Studio (version 2008 onwards). Simply with the Registered Servers window, select the group of servers and perform a “New Query”. When the query is executed it will be applied against every server in the server group; concurrently.

So in the real world what are the extra steps required to make the latter actually work. Just recently I applied the steps below again over 100 databases on 15 servers. What’s more this was really easy.

Let’s say you have query that must be applied against various SQL server instances and their associated databases, however not all the associated databases. The results should be merged (union) together in one list. To do the latter I suggest the following:

1) Using SQL Server Management Studio and the Registered Server window (shortcut Ctrl+Alt+G) create a server group that contains all the servers (new server registration) you want to query against.

New Server GroupNew Server Group

New Server GroupNew Server Group


2) Against the Registered Server group, perform a right mouse click for the shortcut menu and select “New Query”:
New Query

New Query


3) Create the SQL script, one approach could be as follows:

USE [tempdb]
SET NOCOUNT ON;
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('tempdb..#Results') IS NOT NULL 
    DROP TABLE #Results;
GO	

CREATE TABLE #Results
( [Local DateTime] [DATETIME] NOT NULL ,
  [UTC DateTime] [DATETIME] NOT NULL ,
  [ServerName] [NVARCHAR](128) NOT NULL ,
  [Database] [NVARCHAR](128) NOT NULL ,
  [Result] NVARCHAR(MAX)
);
GO

EXEC sp_msforeachdb '
Use [?];
IF DB_NAME() LIKE ''%???%''
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Names]'') AND type in (N''U''))
	INSERT INTO #Results
	([Local DateTime],[UTC DateTime],[ServerName],[Database]
	,[Result])
		SELECT  GETDATE(), GETUTCDATE(), @@SERVERNAME, DB_NAME()
		, n.[Name]
		FROM [dbo].[Names] n;
';

SELECT  [ServerName], [Database], [Local DateTime], [UTC DateTime], [Result]
FROM    #Results
ORDER BY 1, 2, 3, 4, 5;

4) As can be seen in the latter example, the SQL script tests each database in turn to see if the script is allowable on the database. This is done by testing the database name and also seeing if a table with a name of [Names] exists in the database. When the query is applied against the database, the results are placed in a temporary table. By placing the results in a temporary table, allows the results of each database within the server to be merged together.

5) The last step is to return the results in the temporary table (#Results).

6) In order to merge all the server results together, use Query Options (menu Tool | Options) and select Multiserver Results and set Merge results to true.
Merge Results

7) When the script is executed, SQL Server Management studio applies the script concurrently to each server in the server group. The results from each server are merged together which result in one result set being displayed.

Posted in SQL 2008, SQL 2008 R2 | Tagged | Comments Off

SSIS Package Upgrade then error “Cannot use the OUTPUT option when passing a constant to a stored procedure”

I have been recently upgrading SQL Server 2005 SSIS packages to 2008 R2 using the automated wizard. The wizard performed the upgrades without error, however on executing the packages I would get the following error in a data flow OLE DB command component:

Error: 2012-08-29 14:53:02.20 Code: 0xC0202009 Source: DFT – Process Hits OLE DB getBrowserHash [89428] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0″ Hresult: 0×80004005 Description: “Cannot use the OUTPUT option when passing a constant to a stored procedure.”. End Error

I did much investigation into this (basically Google and various hacking (i.e. turning MARS off then on) but was unable to resolve the issue.

To resolve the issue I created a new OLE DB connection manager and changed the data flow OLE DB command to use the new connection manager, and the error no longer occurred.

Posted in SQL 2005, SQL 2008 R2 | Tagged | Comments Off

Blocked SQL Sessions and Blockers

Validated against: SQL Server 2005, 2008, 2008 R2.

Below is a SQL script that returns SQL sessions waiting (blocked) for one second or more, and also the associated SQL sessions that are doing the actual blocking. The script returns pretty much all the information you will ever need. If any suggestions to improve it, please be sure to let me know.

;WITH request_mode ( request_mode, [description] )
AS (
SELECT 'NULL', 'No access s granted to the resource. Serves as a placeholder.'
UNION ALL
SELECT 'Sch-S',
'Schema stability. Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.'
UNION ALL
SELECT 'Sch-M',
'Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.'
UNION ALL
SELECT 'S', 'Shared. The holding session is granted shared access to the resource.'
UNION ALL
SELECT 'U',
'Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.'
UNION ALL
SELECT 'X', 'Exclusive. The holding session is granted exclusive access to the resource.'
UNION ALL
SELECT 'IS',
'Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.'
UNION ALL
SELECT 'IU',
'Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.'
UNION ALL
SELECT 'IX',
'Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.'
UNION ALL
SELECT 'SIU',
'Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.'
UNION ALL
SELECT 'SIX',
'Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.'
UNION ALL
SELECT 'UIX',
'Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.'
UNION ALL
SELECT 'BU', 'Bulk Update. Used by bulk operations.'
UNION ALL
SELECT 'RangeS_S', 'Shared Key-Range and Shared Resource lock. Indicates serializable range scan.'
UNION ALL
SELECT 'RangeS_U', 'Shared Key-Range and Update Resource lock. Indicates serializable update scan.'
UNION ALL
SELECT 'RangeI_N',
'Insert Key-Range and Null Resource lock. Used to test ranges before inserting a new key into an index.'
UNION ALL
SELECT 'RangeI_S', 'Key-Range Conversion lock. Created by an overlap of RangeI_N and S locks.'
UNION ALL
SELECT 'RangeI_U', 'Key-Range Conversion lock created by an overlap of RangeI_N and U locks.'
UNION ALL
SELECT 'RangeI_X', 'Key-Range Conversion lock created by an overlap of RangeI_N and X locks.'
UNION ALL
SELECT 'RangeX_S', 'Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_S. locks.'
UNION ALL
SELECT 'RangeX_U', 'Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_U locks.'
UNION ALL
SELECT 'RangeX_X',
'Exclusive Key-Range and Exclusive Resource lock. This is a conversion lock used when updating a key in a range.'
)
SELECT d.[name] AS [DatabaseName], wt.[session_id] AS [Waiting SessionId],
CAST(wt.[wait_duration_ms] / 1000. AS DECIMAL(38, 1)) AS [Waiting Duration (Secs)],
CAST((wt.[wait_duration_ms] / 1000.)/60. AS DECIMAL(38, 1)) AS [Waiting Duration (Mins)],
wt.[wait_type] AS [Waiting Type], wt.[resource_description] AS [Waiting ResourceDescription],
tl.[request_mode] AS [Waiting RequestMode], rm.[description] AS [Waiting RequestModeDescription],
tl.[request_type] AS [Waiting RequestType], tl.[request_status] AS [Waiting RequestType],
(
SELECT SUBSTRING(ST.text, ( ER.statement_start_offset / 2 ) + 1,
( ( CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset ) / 2 ) + 1)
FROM sys.dm_exec_requests AS ER WITH ( NOLOCK )
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE ER.session_id = tl.request_session_id
) AS [Waiting SQL], wt.[blocking_session_id] AS [Blocking SessionId],
CASE WHEN wt.blocking_session_id > 0 THEN (
SELECT ST2.text
FROM sys.sysprocesses AS SP WITH ( NOLOCK )
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
WHERE SP.spid = wt.blocking_session_id
)
ELSE NULL
END AS [Blocking SQL],
CASE WHEN UPPER(tl.[resource_type]) = 'OBJECT'
THEN (
SELECT [name]
FROM [sys].[objects] o1 WITH ( NOLOCK )
WHERE [o1].object_id = tl.[resource_associated_entity_id]
)
ELSE (
SELECT [name]
FROM [sys].[objects] o1 WITH ( NOLOCK )
WHERE [o1].object_id = p.[OBJECT_ID]
)
END AS [Object Name], p.[index_id], i.[name] AS index_name
FROM sys.dm_os_waiting_tasks AS wt WITH ( NOLOCK )
JOIN sys.dm_tran_locks AS tl WITH ( NOLOCK )
ON wt.resource_address = tl.lock_owner_address
JOIN sys.databases d WITH ( NOLOCK)
ON tl.[resource_database_id] = d.[database_id]
LEFT JOIN [sys].[partitions] AS p WITH ( NOLOCK )
ON tl.[resource_associated_entity_id] = p.[hobt_id]
LEFT JOIN [sys].[indexes] AS i WITH ( NOLOCK )
ON p.[OBJECT_ID] = i.[OBJECT_ID]
AND p.[index_id] = i.[index_id]
LEFT JOIN request_mode rm WITH (NOLOCK)
ON tl.[request_mode] = rm.[request_mode]
WHERE wt.[blocking_session_id] IS NOT NULL
AND wt.[wait_duration_ms] >= 5000/*5 seconds or more*/;
Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off

Tracking Down a Deadlock Issue.

Validated against: SQL Server 2008 R2.

Tracking down the reason for a SQL Server dead lock issue is straight forward enough, simply use the SQL Server Profiler and place a ‘Locks’ trace against the event ‘Deadlock graph’. Start the profiler and wait for a deadlock to occur.

If you required more information also apply a ‘Locks’ trace against the events ‘Lock:Deadlock’ and ‘Lock:Deadlock Chain’. For a richer picture of what was happening apply a ‘Stored Procedures’ trace against ‘RPC:Starting’ and ‘SP:StmtStarting’, and also a ‘TSQL’ trace against the event ‘SQL:StmtStarting’.

When a dead lock does occur a graphical representation of the lock is presented within the trace, which provides sufficient detail about the lock. The graphical representation shall most likely include a narrative stating ‘associated objid: <object id>’. To find out what the lock was against, query the sys.objects table (i.e SELECT * FROM sys.objects WHERE object_id=2121058592).

Another option if you wish to avoid use of the SQL Server Profiler is to have the deadlock event logged in the SQL Server Log. To do the latter, suggest a quick google search for ‘DBCC TRACEON(3604)’ and ‘DBCC TRACEON(1204’).


							
Posted in SQL 2008 R2 | Tagged | Comments Off

Very Quick Accurate Row Counts

Validated against: SQL Server 2005, 2008, 2008 R2.

;WITH cte([TableName],[IndexName],[RowCount])AS
(SELECT o.[name],i.[name],SUM([p].[row_count])
FROM sys.objects AS o WITH(NOLOCK)
JOIN sys.indexes AS i WITH(NOLOCK)
ON o.[object_id]=i.[object_id]
JOIN sys.dm_db_partition_stats  AS p WITH(NOLOCK)
ON i.[object_id]=p.[object_id] AND i.[index_id]=p.[index_id]
WHERE o.[type]='U'/*Table (user-defined)*/
GROUP BY o.[name],i.[name]
)
SELECT c.[TableName],MAX(c.[RowCount])AS[RowCount]
FROM cte c
GROUP BY c.[TableName]
ORDER BY c.[TableName];
Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off

Current Database Update All Table Statistics with Fullscan

Validated against: SQL Server 2005.

Updating statistics against each table in the current database by using the undocumented stored procedure [sp_MSforeachtable].

exec sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off

Efficient Numbers Table

Validated against: SQL Server 2005.

Just performed a quick Google of the various methods of acquiring a list of sequential numbers and recommend the approach by Itzik Ben-Gan. The approach uses a small footprint of code and allows numbers up to 4,294,967,296. For details see URL:

CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (@n) n FROM Nums ORDER BY n;
GO
SELECT n FROM dbo.GetNums(1000)
Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off

Disk Subsystem Benchmarking

When benchmarking disk subsystems, I generally benchmark against the most challenging task; which is random writes, with no buffering! The tool I use is the Microsoft SQLIO tool that measures IO performance.

The SQLIO tool can be acquired from:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163

Once installed the default location to find it is:  C:\Program Files\SQLIO

It is a command line tool which means when required you can easily create batch files for repeated testing.

For the changeling tasks of random writes I suggest:

  • sqlio -kW -t2 -s300 -frandom -o2 -b8 -LS -BN C:\Testfile.dat
  • sqlio -kW -t2 -s600 -frandom -o2 -b8 -LS -BN Z:\Testfile.dat

The latter parameters indicate:

  • -kW =  Kind of IO. W = Write.
  • -t2 =  Threads 2. Recommend you set this number of the number of CPU cores you have.
  • -s300 = Seconds. 300 seconds is 5 minutes.
  • -o2 = Outstanding queued I/Os on a single thread.
  • -frandom = Random testing.
  • -b8 = Block size in KB. Note SQL Server writes in pages, and a page is 8K, hence 8K is a good writing benchmark size. Also note that SQL Server reads in extents, and a extend is 64K.
  • -LS = Latency System timing.
  • -BN = Buffering none.
  • - Z:\Testfile.dat = Drive to test. Note manually delete file at end of test.

When executed the SQLIO process will create the Testfile.dat file against the given disk, however it does not delete it! So please manually delete when finished.

The output of SQLIO is pretty much self explanatory.

Against my cheap IDE drive with the following parameters:

  • sqlio -kW -t2 -s60 -frandom -o2 -b8 -LS -BN Z:\Testfile.dat

the output was:

  • IOs/sec: 268.34
  • MBs/sec: 2.09
  • Avg_Latency(ms): 14
  • Max_Latency(ms): 70
Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off

Random String

Validated against:

  • SQL Server 2005.
CREATE VIEW [RandomCharacter]
AS
SELECT SUBSTRING
('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
,CAST(RAND()*62 AS INT)+1
,1
) AS [Character];
GO
CREATE FUNCTION [RandomString]
(@Length SMALLINT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @return VARCHAR(8000);
SET @return='';
WHILE LEN(@return)<>@Length
SELECT  @return=@return+[Character] FROM [RandomCharacter];
RETURN @return;
END;
GO
SELECT [dbo].[RandomString](10);
Posted in SQL 2005 | Tagged | Comments Off

Random Character

Validated against:

  • SQL Server 2005.
CREATE VIEW [RandomCharacter]
AS
SELECT SUBSTRING
		('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
		,CAST(RAND()*62 AS INT)+1
		,1
		) AS [Character];
GO
SELECT [Character]
FROM    RandomCharacter;
Posted in SQL 2005 | Tagged | Comments Off