Email Query Results sp_send_dbmail

As an attachment

EXEC msdb..sp_send_dbmail

    @profile_name = 'SQLMail Profile',

    @recipients = 'email@email.com',

    @subject = 'Report',

    @query = 'EXEC dbo.Sproc',

    @attach_query_result_as_file = 1,

       @query_result_separator=',',

       @execute_query_database = 'DatabaseName',

       @exclude_query_output = 1,

       @query_attachment_filename = @FileName,

       @query_result_no_padding = 1

Query Performance Difference from Application v/s SSMS

When you observe performance difference between the application layer executing a stored procedure, v/s running the same exact call via SSMS, I found there actually exist 2 distinct query plans in the procedure cache. On running this query you will find identical values for the query_hash but different values for the query_plan_hash.

SELECT

       cp.objtype AS ObjectType,

       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

       cp.usecounts AS ExecutionCount,

       st.TEXT AS QueryText,

       qp.query_plan AS QueryPlan,

       cp.plan_handle AS PlanHandle,

       execution_count,

       query_hash,

       query_plan_hash

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

join sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle

WHERE OBJECT_NAME(st.objectid,st.dbid) = 'CustomerOriginGet'

ORDER BY cp.usecounts DESC

ObjectType ObjectName ExecutionCount execution_count query_hash query_plan_hash

Proc CustomerOriginGet 10 10 0x27CBF0C0A5FFAC05 0x7FC50685222DDBF8

Proc CustomerOriginGet 8 8 0x27CBF0C0A5FFAC05 0xD1990C7B57DB8B71

I had to dig further to find the reason why 2 different plans were being generated for the same stored procedure. Using the above query to retrieve only the QueryPlan as "Saving As..." I saved the XML plan in a *.sqlplan file. Open this in SSMS for the graphical format.

On retrieving the properties of the root node select of the query plan, expand the Set Options.

Application Query Plan

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True

SSMS Query Plan

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True

The connection SET settings affect the query plan that is created.

As warned by this documentation of SET ARITHABORT

SET ARITHABORT

http://msdn.microsoft.com/en-us/library/ms190306.aspx

Caution

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting. Additional Sources that helped me gather this information.

Use this DMV to query long XML query plans:

http://msdn.microsoft.com/en-us/library/windowsazure/hh977104.aspx

Slow in the Application, Fast in SSMS?
Understanding Performance Mysteries

http://www.sommarskog.se/query-plan-mysteries.html

SET Options That Affect Results

http://technet.microsoft.com/en-us/library/ms175088%28v=sql.105%29.aspx

Determining SET Options for a Current Session in SQL Server

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

same query showing different execution plans on the same server

http://ask.sqlservercentral.com/questions/95689/same-query-showing-different-execution-plans-on-th.html

Grant permissions to functions

select 'GRANT SELECT ON [' + SCHEMA_NAME(schema_id) + '].[' + name + '] TO [public]', *

from sys.objects

WHERE type_desc in ('CLR_TABLE_VALUED_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')

order by name

select 'GRANT EXECUTE ON [' + SCHEMA_NAME(schema_id) + '].[' + name + '] TO [public]', *

from sys.objects

WHERE type_desc in ('AGGREGATE_FUNCTION', 'CLR_SCALAR_FUNCTION', 'SQL_SCALAR_FUNCTION')

order by name

Permissions granted to an object

SELECT

      OBJECT_SCHEMA_NAME(major_id) as SchemaName,

    OBJECT_NAME(major_id) as ObjectName,

    USER_NAME(grantee_principal_id) as LoginName,

    permission_name,

    'REVOKE ' + permission_name + ' ON ' + OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) + ' TO ' + USER_NAME(grantee_principal_id)

FROM

    sys.database_permissions p

    left join sys.syslogins l on USER_NAME(grantee_principal_id) = l.name

where

USER_NAME(grantee_principal_id) not in ('public', 'guest')

and permission_name <> 'CONNECT'

and OBJECT_NAME(major_id) is not null

ORDER BY USER_NAME(grantee_principal_id)

Table Rowcount

SELECT distinct

    t.NAME AS TableName,

    p.[Rows]

FROM

    sys.tables t

INNER JOIN

    sys.partitions p ON t.object_id = p.OBJECT_ID

WHERE

    t.NAME NOT LIKE 'dt%'

ORDER BY

    t.NAME

DBCC FREEPROCCACHE

DBCC FREEPROCCACHE ();

SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'%%';

Extended Events - Database Usage

-- If the Event Session Exists, drop it first

IF EXISTS (SELECT 1

                  FROM sys.server_event_sessions

                  WHERE name = 'SQL_DatabaseUsage')

      DROP EVENT SESSION [SQL_DatabaseUsage]

      ON SERVER;

-- Create the Event Session

CREATE EVENT SESSION [SQL_DatabaseUsage]

ON SERVER

ADD EVENT sqlserver.lock_acquired(

      WHERE owner_type = 4 -- SharedXactWorkspace

        AND resource_type = 2 -- Database level lock

        AND database_id > 4 -- non system database

        AND sqlserver.is_system = 0 -- must be a user process

)

ADD TARGET package0.asynchronous_bucketizer

( SET slots = 32, -- Adjust based on number of databases in instance

        filtering_event_name='sqlserver.lock_acquired', -- aggregate on the lock_acquired event

        source_type=0, -- event data and not action data

        source='database_id' -- aggregate by the database_id

)

WITH(MAX_DISPATCH_LATENCY =1SECONDS); -- dispatch immediately and don't wait for full buffers

GO

-- Start the Event Session

ALTER EVENT SESSION [SQL_DatabaseUsage]

ON SERVER

STATE = START;

GO

-- Parse the session data to determine the databases being used.

SELECT  slot.value('./@count', 'int') AS [Count] ,

        DB_NAME(slot.query('./value').value('.', 'int')) AS [Database]

FROM

(

      SELECT CAST(target_data AS XML) AS target_data

      FROM sys.dm_xe_session_targets AS t

    INNER JOIN sys.dm_xe_sessions AS s

            ON t.event_session_address = s.address

      WHERE   s.name = 'SQL_DatabaseUsage'

        AND t.target_name = 'asynchronous_bucketizer') AS tgt(target_data)

CROSS APPLY target_data.nodes('/BucketizerTarget/Slot') AS bucket(slot)

ORDER BY slot.value('./@count', 'int') DESC

GO

Ring Buffer

;WITH connectivity_ring_buffer as

(SELECT

record.value('(Record/@id)[1]', 'int') as id,

record.value('(Record/@type)[1]', 'varchar(50)') as type,

record.value('(Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(50)') as RecordType,

record.value('(Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(50)') as RecordSource,

record.value('(Record/ConnectivityTraceRecord/Spid)[1]', 'int') as Spid,

record.value('(Record/ConnectivityTraceRecord/SniConnectionId)[1]', 'uniqueidentifier') as SniConnectionId,

record.value('(Record/ConnectivityTraceRecord/SniProvider)[1]', 'int') as SniProvider,

record.value('(Record/ConnectivityTraceRecord/OSError)[1]', 'int') as OSError,

record.value('(Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') as SniConsumerError,

record.value('(Record/ConnectivityTraceRecord/State)[1]', 'int') as State,

record.value('(Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(50)') as RemoteHost,

record.value('(Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(50)') as RemotePort,

record.value('(Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(50)') as LocalHost,

record.value('(Record/ConnectivityTraceRecord/LocalPort)[1]', 'varchar(50)') as LocalPort,

record.value('(Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') as RecordTime,

record.value('(Record/ConnectivityTraceRecord/LoginTimers/TotalLoginTimeInMilliseconds)[1]', 'bigint') as TotalLoginTimeInMilliseconds,

record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTaskEnqueuedInMilliseconds)[1]', 'bigint') as LoginTaskEnqueuedInMilliseconds,

record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkWritesInMilliseconds)[1]', 'bigint') as NetworkWritesInMilliseconds,

record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkReadsInMilliseconds)[1]', 'bigint') as NetworkReadsInMilliseconds,

record.value('(Record/ConnectivityTraceRecord/LoginTimers/SslProcessingInMilliseconds)[1]', 'bigint') as SslProcessingInMilliseconds,

record.value('(Record/ConnectivityTraceRecord/LoginTimers/SspiProcessingInMilliseconds)[1]', 'bigint') as SspiProcessingInMilliseconds,

record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTriggerAndResourceGovernorProcessingInMilliseconds)[1]', 'bigint') as LoginTriggerAndResourceGovernorProcessingInMilliseconds,

record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') as TdsInputBufferError,

record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') as TdsOutputBufferError,

record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') as TdsInputBufferBytes,

record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]', 'int') as PhysicalConnectionIsKilled,

record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'int') as DisconnectDueToReadError,

record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]', 'int') as NetworkErrorFoundInInputStream,

record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'int') as ErrorFoundBeforeLogin,

record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'int') as SessionIsKilled,

record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'int') as NormalDisconnect

--record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]', 'int') as NormalLogout

FROM

( SELECT CAST(record as xml) as record

FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY') as tab

)

SELECT c.RecordTime,m.[text],*

FROM connectivity_ring_buffer c

LEFT JOIN sys.messages m ON c.SniConsumerError = m.message_id AND m.language_id = 1033

ORDER BY c.RecordTime DESC

Unused Index Script

-- Unused Index Script

-- Original Author: Pinal Dave (C) 2011

SELECT TOP 25

o.name AS ObjectName

, i.name AS IndexName

, i.index_id AS IndexID

, dm_ius.user_seeks AS UserSeek

, dm_ius.user_scans AS UserScans

, dm_ius.user_lookups AS UserLookups

, dm_ius.user_updates AS UserUpdates

, p.TableRows

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'

FROM sys.dm_db_index_usage_stats dm_ius

INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID

INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID

FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p

ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID

WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1

AND dm_ius.database_id = DB_ID()

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

SQL Agent Job Failure Notification Setup

select

name,

'/*'

+ name

+'*/      '

+'EXEC msdb.dbo.sp_update_job @job_id=N'

+ '''' +

cast(job_id as varchar(255))

+ '''' +

',

              @notify_level_email=2,

              @notify_level_netsend=2,

              @notify_level_page=2,

              @notify_email_operator_name=N''EC-DBAs'''

from msdb.dbo.sysjobs

where enabled = 1

and notify_level_email = 0

--and name like 'basic%'

order by name

Query Plan Handle & Usage

SELECT

       cp.objtype AS ObjectType,

       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

       cp.usecounts AS ExecutionCount,

       st.TEXT AS QueryText,

       qp.query_plan AS QueryPlan,

       cp.plan_handle AS PlanHandle,

       execution_count,

       query_hash,

       query_plan_hash

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

join sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle

WHERE OBJECT_NAME(st.objectid,st.dbid) = ''

ORDER BY cp.usecounts DESC

Static Pivot

select *

from

(

      select a.ObjectId, f.Name, a.Address

      from V6Addresses a

      join V6ObjectTypeFields f on a.V6ObjectTypeFieldId = f.Id

      join V6ObjectTypes t on f.V6ObjectTypeId = t.Id

      where t.Name = 'Servers'

) v

PIVOT

(

      MAX(Address)

      FOR v.Name in (ServersPrimary, ServersSecondary, ServersGateway, ServersManagement, ServersManagementGateway)

) as p