Posted in Development

SQL Server – Extended Events in SQL Server 2012

Hi friends, today we will discuss about new performance monitoring system Extended Events which gets introduced in version SQL Server 2012.In previous versions we were using SQL Trace for capturing events and SQL Profiler for managing and creating SQL trace.  SQL Server profile, SQL Trace deprecated in future version of SQL version so it suggested you guys  to start using Extended events in near future which is more lightweight.

In this blog we will create an extended event session following below steps:

  1. Open SSMS and connect to your SQL Server instance.
  2. Select database name from available database list. I have selected TSQL2012 database for this demo.
  3. Write following query in query window :

SELECT PP.productid, PP.productname, PC.categoryname

FROM Production.Products PP

INNER JOIN Production.Categories PC ON PP.categoryid = PC.categoryid

  1. In SSMS, expand Management folder Right click on the Session folder and Select New Session Wizard.

ExtendedEvent_1

5. On Introduction page, read the information and click Next.

ExtendedEvent_2

6. On Set Session Properties page, name the session ‘TestEvent’. Click Next.

ExtendedEvent_3

7. On Choose Template page, Select Do Not Use a Template and click Next.

ExtendedEvent_4

8. On Select Events To Capture page, type string sql in Event Library textbox and select sql_statement_completed. Move it to Selected events box and click Next.

ExtendedEvent_5

9. On Capture Global Field page, select the fields which you want to capture in this session. Click Next. I have selected few fields like CPU_id, database_id, database_name.

ExtendedEvent_6

10. On Set Session Event Filter page, create a filter to limit the data you want to capture. I have created filter for database name and sql text. In Field tab select value database_name and Value to be equal to ‘TSQL2012’ with operator value ‘=’. Also, create another filter with Field value sqlserver.sql_text and Value equals to ‘SELECT PP.Productid%’ with operator value ‘like_i_sql_unicode_string’. Click Next.

ExtendedEvent_7

11. On Specify Session Data Storage page specify the way you want to collect the data for analysis.Select option Work with only the most recent data (ring_buffer_target) and click Next.

ExtendedEvent_8

12. On Summary page, verify your selections that you made for this event session. Click Finish.

ExtendedEvent_9

13. On last page of Create Event Session page, select both checkboxes: Start the event session immediately after session creation and Watch live data on screen as it is captured. Click Close.

ExtendedEvent_10

14. Extended Events Live Window gets opened in new window.

15. You can check the new event session in the session folder. Right click on the newly created session ‘TestEvent’.

16. Execute the above query in query window and see event information in Live Data Window.

ExtendedEvent_12

Extended Events is a more lightweight monitoring system in comparison to SQL Trace. So friends start making use of extended events as SQL Trace will get deprecated in future versions.

That’s all folks for the day. Hope you like this post.

Advertisements
Posted in Stored Procedure

Sysmail_help_principalprofile_sp – SQL Server Database Mail Stored Procedure – Part 10

Hi friends, in this blog of SQL Server  database mail stored procedure we will continue with another database mail stored procedure sysmail_help_principalprofile_sp.

Sysmail_help_principalprofile_sp stored procedure list the information about associations between database mail profile and database principals.

When sysmail_help_principalprofile_sp stored procedure is executed without any parameters then it returned lists all of the associations in the instance of SQL Server.

Syntax

sysmail_help_principalprofile_sp [ {   [ @principal_id = ] principal_id | [ @principal_name = ] ‘principal_name’ } ]

    [ [ , ] {   [ @profile_id = ] profile_id | [ @profile_name = ] ‘profile_name’ } ]

Arguments

@principal_Id – ID of the database user or role in msdb database.

@principal_name – Name of the database user or role in msdb database. Either @principal_id or @principal_name is necessary to specify.

@profile_id – Id of the database mail profile.

@profile_name – Name of the database mail profile. Either @profile_id or @profile_name must be specified.

Now let’s execute the below stored procedure by passing database profile name in parameters:

EXEC msdb.dbo.sysmail_help_principalprofile_sp @profile_name = ‘Database Test Mail’

sysmail_principalprofile_help

Similarly when we executed this stored procedure without passing any parameters it will return list of all association in instance of SQL Server.

EXEC msdb.dbo.sysmail_help_principalprofile_sp

That’s all friends for the day J

Posted in Stored Procedure

Sysmail_add_principalprofile_sp – SQL Server Database Mail Stored Procedure – Part 9

Hi friends, in this blog of SQL Server Database mail stored procedure we will continue with another database mail procedure sysmail_add_principalprofile_sp.

Sysmail_add_principalprofile_sp stored procedure grants permission to a database user or role to use a database mail profile.

Syntax

sysmail_add_principalprofile_sp  { [ @principal_id = ] principal_id | [ @principal_name = ] ‘principal_name’ } ,

{ [ @profile_id = ] profile_id | [ @profile_name = ] ‘profile_name’ }

[ , [ @is_default ] = ‘is_default’ ]

Arguments

@principal_Id – ID of the database user or role in msdb database. A principal_id with 0 makes it public profile which grants access to all principals in the database.

@principal_name – Name of the database user or role in msdb database. Either @principal_id or @principal_name is necessary to specify. A profile_name with ‘Public’ makes it public profile which grants access to all principals in the database.

@profile_id – Id of the database mail profile.

@profile_name – Name of the database mail profile. Either @profile_id or @profile_name must be specified.

@is_default – Specify whether the profile is default profile for the principal. A principal must have one default profile.

Now let’s execute the below code to make the profile ‘Database Test Mail’ the default public profile for all users in msdb database.

EXEC msdb.dbo.sysmail_add_principalprofile_sp

@principal_name = ‘Public’,

@profile_name = ‘Database Test Mail’,

@is_default = 1

That’s all folks for the day. In next blog we will learn how to get the information about principal profile which we created today.

Posted in Stored Procedure

Sysmail_help_profileaccount_sp – SQL Server Database Mail Stored Procedure – Part 8

Hi folks, in our previous blog of SQL Server Database Mail stored procedure we added database mail account to database mail profile. In this blog we will learn how to get information about the accounts associated with database mail profiles.

Sysmail_help_profileaccount_sp stored procedure lists the database mail accounts associated with one or more database profile. When no profile_id or profile_name is specified, stored procedure returns information for all database mail profiles.

Syntax:

sysmail_help_profileaccount_sp

{   [ @profile_id = ] profile_id

| [ @profile_name = ] ‘profile_name’ }

[ , {   [ @account_id = ] account_id

| [ @account_name = ] ‘account_name’ } ]

Arguments

@profile_id is the profile id of the profile having datatype INT.

@profile_name is the name of the database mail profile. Either profile_id or profile_name must be specified.

@account_id is the account of the account having datatype INT.

@account_name is the name of the database mail account. Either @account_id or @account_name must be specified.

Let’s execute the stored procedure passing profile name or account name:

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp @profile_name = ‘Database Test Mail’

GO

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp @account_name = ‘DB_Email_Account’

GO

sysmail_help_profileaccount

After executing the stored procedure we get following columns in result set:

Profile_Id – Profile Id of the database mail profile.

Profile_name – Profile name of the database mail profile.

Account_Id – Account Id of the database account profile.

Account name – Account name of the database account profile.

That’s all folks for the day friends 🙂

Posted in Stored Procedure

Sysmail_add_profileaccount_sp – SQL Server Database Mail Stored Procedure – Part 7

Hi friends, in last previous blogs we created database mail profile and database mail account. In this blog we will learn how to add database mail account to database mail profile using stored procedure sysmail_add_profileaccount_sp.

Sysmail_add_profileaccount_sp stored procedure is used to add database mail account to a database mail profile. Both the mail account and mail profile should already exist else stored procedure returns an error. Stored procedure sysmail_add_profileaccount_sp is present in msdb database and owned by schema dbo. If current database is not msdb then this procedure should be executed using three part name.

Syntax:

sysmail_add_profileaccount_sp { [ @profile_id = ] profile_id | [ @profile_name = ] ‘profile_name’ } ,

{ [ @account_id = ] account_id | [ @account_name = ] ‘account_name’ }

[ , [ @sequence_number = ] sequence_number ]

Arguments:

@profile_id – Database mail profile id to which mail account added.

@profile_name – Database mail profile name to which mail account added. Either profile_id or profile_name must be specified.

@account_id – Database mail account id to add to mail profile.

@account_name – Database mail account name to add to mail profile. Either account_id or account_name must be specified.

@sequence_number – Sequence number is used to determine in which sequence database mail uses account within the profile.

Now we will add database mail account to database mail profile which we created earlier in our previous blog Sysmail_add_account_sp – SQL Server Database Mail Stored Procedure – Part 5 and Sysmail_add_profile_sp – SQL Server Database Mail Stored Procedure – Part 3.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ‘Database Test Mail’,

@account_name = ‘DB_Email_Account’,

@sequence_number = 1

After executing this procedure database mail account will be added to the mail profile successfully.

That’s all for the day friends :).

Posted in Development

What is User Defined function in SQL Server?

  • User-defined functions encapsulate T-SQL statement and return a table or a scalar value to the caller.
  • UDFs can not be executed using EXEC statement like stored procedure.
  •  UDFs cannot perform any DDL (Data Definition Language) like  cannot CREATE, UPDATE tables, indexes or any permanent objects.
  • UDFs can also accept parameters like stored procedures and used by variables inside the functions.
  • There are two types of UDFs : Scalar valued function and Table valued function
  • Scalar valued function returns a single value to the caller. While table valued function returns a table to the caller.
  • When a table valued function contains a single line code then its called Inline table valued functions. While a table valued function containing multiple line T-SQL code its called Multistatement table valued function.