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;

Colours are not displayed on SSRS subscriptions sent in mhtml format

August 26, 2015

Problem: Colours are not displayed on SSRS subscriptions sent in mhtml format

Solution: Rather than selecting the colour White for example from the color selection drop down list, instead select expression and enter the value =”White” This approach will also work when specifing hex codes eg enter =”#996699″ as an expression rather than #996699

user/login impersonation extending beyond the current database

August 3, 2015

A stored procedure utilizing execute as owner (database owner = sa) is unable to call an procedure within another database whose owner is also sa.

The reason for this is that security impersonation is only valid within the database that the impersonation is performed. Any calls external to the database will result in an error.

Consider the following scenario: 2 databases, 2 different owners in the same instance. db1 owner creates db2 owner as a user within db1. He then issues an EXCUTE AS USER = ‘db2_owner’ command. If external database calls were allowed he would have full admin to db2, clearly not desirable.

To extend impersonation beyond the database you need to let the instance know that teh dataabse is trustworthy.

You can do this: ALTER DATABASE xxx SET TRUSTWORTHY ON

see https://msdn.microsoft.com/en-us/library/ms188304(v=sql.105).aspx for further information


Follow

Get every new post delivered to your Inbox.