Backup all ssis packages to disk

April 21, 2016

ssis package backup – rename as dtsx

See attachment, change extension to dtsx

Querying the plan cache to discover which queries are using an index

March 8, 2016

DECLARE @IndexName AS NVARCHAR(128) = ‘name of index here’;

IF (LEFT(@IndexName, 1) <> ‘[‘ AND RIGHT(@IndexName, 1) <> ‘]’) SET @IndexName = QUOTENAME(@IndexName);

IF LEFT(@IndexName, 1) <> ‘[‘ SET @IndexName = ‘[‘+@IndexName;
IF RIGHT(@IndexName, 1) <> ‘]’ SET @IndexName = @IndexName + ‘]’;
;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan&#8217;)
SELECT
stmt.value(‘(@StatementText)[1]’, ‘varchar(max)’) AS SQL_Text,
obj.value(‘(@Database)[1]’, ‘varchar(128)’) AS DatabaseName,
obj.value(‘(@Schema)[1]’, ‘varchar(128)’) AS SchemaName,
obj.value(‘(@Table)[1]’, ‘varchar(128)’) AS TableName,
obj.value(‘(@Index)[1]’, ‘varchar(128)’) AS IndexName,
obj.value(‘(@IndexKind)[1]’, ‘varchar(128)’) AS IndexKind,
cp.plan_handle,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple’) AS batch(stmt)
CROSS APPLY stmt.nodes(‘.//IndexScan/Object[@Index=sql:variable(“@IndexName”)]’) AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);

performance tuning note

December 8, 2015

dont forget pivots and recursive cte’s are great on small datasets but they dont scale well

  • pivots can be replaced with joins and case statements
  • recursive cte’s can be replaced with while loops and insert statements

Check if you have a disconnected remote desktop session

November 16, 2015

1. open up powershell
2. QUERY USER username /server:servername

“No user exists for username”

will displayed if you do not have a disconnected rdp

Error enumerating session names, the rpc server is unavailable will be displayed if the servername you entered doesnt exist

create partition scheme strings

November 13, 2015

SELECT
i.object_id,OBJECT_NAME(i.object_id) table_name,
QUOTENAME(s.name) + ‘(‘ + z1.name + ‘)’ partition_scheme
from sys.indexes i
join sys.partition_schemes s on i.data_space_id = s.data_space_id
JOIN sys.index_columns z ON z.index_id = i.index_id AND z.object_id = i.object_id
JOIN sys.columns z1 ON z1.object_id = z.object_id AND z1.column_id = z.column_id

Another example of importing extended event data

November 4, 2015

SET QUOTED_IDENTIFIER ON

IF (OBJECT_ID(‘tempdb..#staging’)) IS NOT NULL DROP TABLE #staging

SELECT q.statement_text,q.client_hostname,q.client_app_name,timestamp
INTO #staging
FROM (
SELECT statement_text=e.event_data_XML.value(‘(//data[@name=”statement”]/value)[1]’,’varchar(max)’)
, client_hostname=e.event_data_XML.value(‘(//action[@name=”client_hostname”]/value)[1]’,’varchar(max)’)
, client_app_name=e.event_data_XML.value(‘(//action[@name=”client_app_name”]/value)[1]’,’varchar(max)’)
, TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]’,’datetime2(7)’)
, name=e.event_data_XML.value(‘(//@name)[1]’,’varchar(max)’)
, *
FROM (
SELECT CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘Z:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\trace*.xel’, NULL, NULL, NULL)

)e
)q

IF OBJECT_ID(‘database_name.dbo.readonly_trace’) IS NULL

SELECT TOP 0 *
INTO database_name.dbo.readonly_trace
FROM #staging

INSERT INTO database_name.dbo.readonly_trace
SELECT b.* FROM database_name.dbo.readonly_trace a
RIGHT JOIN #staging b ON a.timestamp = b.TIMESTAMP
WHERE a.timestamp IS NULL

create index creation scrips

October 15, 2015

This will create a view which you can use to get your create index scripts

 

CREATE VIEW dbo.vw_create_index_scripts

AS
WITH col_list AS (
SELECT DISTINCT object_id,index_id,

STUFF((SELECT ‘, ‘ +QUOTENAME(B.name) + CASE WHEN a.is_descending_key = 1 THEN ‘ DESC ‘ ELSE ” END AS [text()]
FROM SYS.index_columns A
INNER JOIN sys.columns B ON B.column_id = A.column_id AND B.object_id = A.object_id
WHERE A.is_included_column=0 AND a.index_id=z.index_id AND a.object_id=z.object_id
For XML PATH (”)),1,1,”
) col_list
FROM sys.index_columns z ),
include_list AS (
SELECT DISTINCT object_id,index_id,

STUFF((SELECT ‘, ‘ +QUOTENAME(B.name) + CASE WHEN a.is_descending_key = 1 THEN ‘ DESC ‘ ELSE ” END AS [text()]
FROM SYS.index_columns A
INNER JOIN sys.columns B ON B.column_id = A.column_id AND B.object_id = A.object_id
WHERE A.is_included_column=1 AND a.index_id=z.index_id AND a.object_id=z.object_id
For XML PATH (”)),1,1,”
) col_list
FROM sys.index_columns z )

SELECT ‘CREATE ‘ + CASE WHEN a.is_unique =1 THEN ‘ UNIQUE ‘ ELSE ” END +a.type_desc COLLATE DATABASE_DEFAULT+’ INDEX ‘+ a.name + ‘ ON ‘+ QUOTENAME(c.name) + ‘.’+QUOTENAME(b.name)
+ ‘(‘
+ CASE WHEN a.type_desc != ‘NONCLUSTERED COLUMNSTORE’ THEN ISNULL(e.col_list,”) ELSE f.col_list END
+ ‘) ‘
+ CASE WHEN f.col_list IS NOT NULL AND a.type_desc != ‘NONCLUSTERED COLUMNSTORE’ THEN ‘ INCLUDE (‘ + f.col_list + ‘)’ ELSE ” END
+ CASE WHEN filter_definition IS NOT NULL THEN ‘ WHERE ‘ + a.filter_definition ELSE ” END
+’ ON ‘ + QUOTENAME(d.name) script,c.name AS name_schema,b.name name_table,a.name name_index,a.is_unique,a.is_unique_constraint,a.is_primary_key
FROM sys.indexes a
INNER JOIN sys.tables b ON a.object_id = b.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
INNER JOIN sys.data_spaces d ON d.data_space_id = a.data_space_id
LEFT JOIN col_list e ON e.object_id = a.object_id AND e.index_id = a.index_id
LEFT JOIN include_list f ON f.object_id = a.object_id AND f.index_id = a.index_id
WHERE a.type !=0 AND a.is_primary_key =0 AND a.is_unique = 0

UNION ALL

SELECT ‘ALTER TABLE ‘+ QUOTENAME(c.name) + ‘.’+QUOTENAME(b.name )
+ ‘ ADD CONSTRAINT ‘ + QUOTENAME(a.name) + ‘ ‘+ CASE WHEN a.is_primary_key =1 THEN ‘PRIMARY KEY’ WHEN a.is_unique =1 THEN ‘ UNIQUE’ END +’ ‘+a.type_desc COLLATE DATABASE_DEFAULT
+ ‘(‘
+ CASE WHEN a.type_desc != ‘NONCLUSTERED COLUMNSTORE’ THEN ISNULL(e.col_list,”) ELSE f.col_list END
+ ‘) ‘
+ CASE WHEN filter_definition IS NOT NULL THEN ‘ WHERE ‘ + a.filter_definition ELSE ” END
+’ ON ‘ + QUOTENAME(d.name),c.name AS name_schema,b.name name_table,a.name name_index,a.is_unique,a.is_unique_constraint,a.is_primary_key
FROM sys.indexes a
INNER JOIN sys.tables b ON a.object_id = b.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
INNER JOIN sys.data_spaces d ON d.data_space_id = a.data_space_id
LEFT JOIN col_list e ON e.object_id = a.object_id AND e.index_id = a.index_id
LEFT JOIN include_list f ON f.object_id = a.object_id AND f.index_id = a.index_id
WHERE a.type !=0 AND (a.is_primary_key =1 OR a.is_unique =1)

which session is using up space in tempdb?

October 2, 2015

SELECT * FROM FU_Catalog.sys.dm_db_session_space_usage

view security settings on an SSRS report

September 17, 2015

select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
inner join dbo.Policies B on A.PolicyID = B.PolicyID
inner join dbo.Users C on A.UserID = C.UserID
inner join dbo.Roles D on A.RoleID = D.RoleID
inner join dbo.Catalog E on A.PolicyID = E.PolicyID
WHERE path = ‘XXX’
order by C.UserName

Run a SSRS subscription manually

August 27, 2015

Use the following query to locate the report on which your subscription is based and execute the string in the execute_subscription field.

SELECT a.[Path] ,
a.Name ,
CONCAT(‘exec [ReportServer].dbo.AddEvent @EventType=”TimedSubscription”, @EventData=”’,b.SubscriptionID,””) execute_subscription ,
c.ExtensionSettings
FROM ReportServer.dbo.Catalog a
INNER JOIN ReportSchedule b ON b.ReportID = a.ItemID
INNER JOIN dbo.Subscriptions c ON c.SubscriptionID = b.SubscriptionID
ORDER BY a.Path;


Follow

Get every new post delivered to your Inbox.