SQL Server – Knowing Deprecated or Discontinued Features Using Extended Events

This blog is in continuation to what I wrote couple of weeks back – SQL Server – Knowing the Use of Deprecated or Discontinued Features. The concept of using a deprecated feature is not by intensions by developers but is an accident for many. When I wrote that blog post, lesser did I know people are going to ask me for more. One of my reader mentioned using SQL Server Profiler to identify the same. Though this is a valid answer, I wanted to keep Profiler away for this and introduce him to a new method of using Extended Events for the same requirements.

With every new release of SQL Server, the investments that are going to Extended Events is amazing. So I think to bring the same in a step-by-step for folks who want to use the same for identifying Deprecated features. Let us work through the basic steps in setting this up next:

  1. Open the SQL Server Management Studio.
  2. Connect to the server next.
  3. Click the plus sign next to Management then click the plus sign next to Extended Events.
  4. Click on Sessions then right click on New Session Wizard
    deprecated xEvent 01 SQL Server   Knowing Deprecated or Discontinued Features Using Extended Events
  5. At this point the New Session Wizard screen should have launched. Once the New Session Wizard has launched, click Next.
  6. Type “Deprecated and Discontinued” in the text box after session name and click Next.
  7. Choose the radial button  Do not use a template then click Next
  8. In the Event library text box type ‘deprec’ and choose the deprecation announcement and deprecation final support events and then click the > arrow in the middle of the page to move them over as Selected events.
    deprecated xEvent 02 SQL Server   Knowing Deprecated or Discontinued Features Using Extended Events
  9. In the Capture Global Events section check the following:
  • client_app_name
  • client_connection_id
  • database_name
  • nt_username
  • sql_text
  • username
  1. Then click Next
  2. On the Set Session Event Filters screen click Next. We are not filtering this Event Session.
  3. Check the top check box next to Save data to a file for later analysis Make the Max File Size 5 MB with Rollover files as 20.
  4. Click Next.
  5. On the next screen click Finish on the Summary
  6. On the next screen click Close after the Event Session is successfully created.

The same can be scripted out using the script button in the wizard before you hit the finish button. The script for the same steps would look like:

CREATE EVENT SESSION [Deprecated and Discontinued] ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_usernamesqlserver.sql_text,
sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_usernamesqlserver.sql_text,
sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'Deprecated and Discontinued',max_file_size=(5),max_rollover_files=(20))
WITH (STARTUP_STATE=OFF)
GO

If you want to use the same. Go ahead and start the session for Extended Events and start the collection process.

Extended Events are cool in a lot of ways. As part of my learning adventures, Extended Events are slowly getting into mainstream. Having said that, I have tons to learn from you. Do let me know how you use Extended Events in your environments and let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identify Page Splits Using Extended Events in SQL Server

Digging some of my older posts reveal I have written a blog already on Page Splits with SQL Server. This was no surprise because I am in constant need to reinvent as I write the blog posts. Now you might ask, why was I searching. Well, when I was talking about a performance tuning topic at a conference, I told the harmful effects of page splits and one of the attendees asked – “How can we track Page splits inside SQL Server?”.

The easiest way to track if page splits are happening in SQL Server is to use the PerfMon Counters. For the records you can start from- “SQL Server: Access Methods -> Page Splits/sec”. This counter is quite handy to understand if this behavior happens in our SQL Server instance. Now there was an interesting counter question someone asked, “Is there a way to know each of the page splits that happen in the system?”

This question had got me thinking and I wanted to somehow find how this can be found. And to my surprise, I found this was already available with us all along with Extended Events. So here is what I did.

TSQL Script

Let us first create a database for our experiment. We will also create our Extended Event to track the PageSplits.

-- Create a dummy database
CREATE DATABASE PageSplits
GO
-- Get the DB ID, we will use it in next step
SELECT DB_ID('PageSplits')
GO
-- Create Session
CREATE EVENT SESSION [Catch_PageSplit] ON SERVER
ADD EVENT sqlserver.page_split(
WHERE ([package0].[equal_uint64]([database_id],(10))))  -- NOTE: Please make sure you change the DATABASE_ID
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF)
GO
-- Start the event session
ALTER EVENT SESSION [Catch_PageSplit] ON SERVER
STATE
= start;
GO

Before we get to the next step, let us start monitoring our Extended Events for any possible page splits.

USE PageSplits
GO
CREATE TABLE mytest (
myCompany CHAR(5) NOT NULL CONSTRAINT pk_mytest PRIMARY KEY CLUSTERED,
FillData VARCHAR(3000) NOT NULL
)
GO
INSERT mytest ( myCompany, FillData )
VALUES( '00001', REPLICATE( 'A', 3000 ) ),
(
'00002', REPLICATE( 'B', 1000 ) ),
(
'00003', REPLICATE( 'C', 3000 ) ),
(
'00004', REPLICATE( 'A', 3000 ) ),
(
'00005', REPLICATE( 'B', 1000 ) ),
(
'00006', REPLICATE( 'C', 3000 ) ),
(
'00007', REPLICATE( 'A', 3000 ) ),
(
'00008', REPLICATE( 'B', 1000 ) ),
(
'00009', REPLICATE( 'C', 3000 ) )
GO

Next step is to create a table with some values. Later we will use this to create a page split scenario.

page splits xEvent 01 SQL SERVER   Identify Page Splits Using Extended Events in SQL Server

Let us create the scenario of page split by updating a row with some extra data.

-- Update to introduce a page split
UPDATE mytest
SET FillData = REPLICATE( 'B', 3000)
WHERE myCompany = '00002'
GO

Don’t forget to look at the Live Data feed for entries. If the Page Split happen you will see something like:

page splits xEvent 02 SQL SERVER   Identify Page Splits Using Extended Events in SQL Server

As you can see, this Page Split has happened because of an Update. The two pages under question are also shown. We can also see the database ID under question that caused the page split.

I personally thought this was a wonderful concept hidden between tons of features of Extended Events. This is awesome to learn these fine prints.

Now that we learnt something new here, let us clean up the database we just created.

-- Clean up time
USE MASTER
GO
DROP DATABASE PageSplits
GO
DROP EVENT SESSION [Catch_PageSplit] ON SERVER
GO

Hope you found it interesting and do let me know how you were able to use Extended Events to learn something new inside SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – System procedures to know SQL Server Version

There are several ways to know the version of SQL Server. But did you know that there are two system procedures through which you can know the version? They are SP_SERVER_INFO and EXEC XP_MSVER

EXEC sp_SERVER_INFO

If you execute the above, you get a result set with informations about the server. The second will tell you the version number

Result is

attribute_id attribute_name attribute_value
———— ——————– —————-
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2012 – 11.0.3000.0
.
.
.
.
EXEC xp_MSVER

This is also very similar to SP_SERVER_INFO which will show you the version number (second row from the result set)

Result is

Index Name Internal_Value Character_Value
—— ——————————– ————– ——————-
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 720896 11.0.3153.0
.
.
.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Filtering CPU Bound Execution Plans with Extended Events

help pc SQL SERVER   Filtering CPU Bound Execution Plans with Extended EventsLet me take a tour to what are we talking here from a scenario point of view. Prior to SQL Server 2012, tracing the execution plan is an all or nothing approach. In profiler, if we select execution plan, we will get every occurrence of every execution plan for all the statements that are executed inside SQL Server captured by the trace. This will bloat up the trace file very quickly and unimaginable for us to do any sort of analysis. So in general performance templates used inside profiler we do not capture execution plans – and rightly so.

I have been waiting for the ability to capture execution plan only when a query executes longer than a certain amount of time. Yes, I can add a few filters to profile, but this wasn’t the most efficient way to work. In this blog we will look at the Extended Event query_post_execution_showplan (similar to Showplan XML Statistics Profile) in SQL Server 2012 which has an interesting addition of cpu_time and duration for filtering. Personally, I thought this is a great addition to the usage of Extended Events.

Why use this?

We want to use this to monitor the overall server performance and troubleshooting when things go wrong. When the server responses are slow and we have not identified any particular query, we can choose to capture all the execution plans that consumed CPU_Time or duration exceeding certain threshold. This is one of the ways this feature can be used effectively.

Extended Event UI

If you are not familiar with the Extended Events UI, here is a great start for this task. Goto SQL Server Management Studio -> Management Node -> Extended Events -> Sessions -> Right Click and select “New Session…”.

cpu xevent 01 SQL SERVER   Filtering CPU Bound Execution Plans with Extended Events

This starts a wizard which will make our configuration quite easy. In Event library, search using the keyword “showplan“. Query_post_execution_showplan will show up as a search result. Assuming you are on SQL Server 2012 and above, note the cpu_time and duration are among available event fields.

cpu xevent 02 SQL SERVER   Filtering CPU Bound Execution Plans with Extended Events

Next, select this event and move the entry to the “Selected events:” list. This enables the “Configure” button at the top of the dialog box.

cpu xevent 03 SQL SERVER   Filtering CPU Bound Execution Plans with Extended Events

Click the Filter (Predicate) tab. You can choose cpu_time, operator and value. Note that cpu_time is in microseconds. For example, if you want to capture execution plans on all queries taking 10 seconds of CPU, you will choose 10000000. This can be done for queries that are executing more than 10 seconds too by selecting the “duration” field.

Extended Event T-SQL

Instead of showing the UI version of the command, I have gone ahead and scripted out with the duration field the same extended events. The command for the same is:

CREATE EVENT SESSION [CPU_XEvent] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(    ACTION(package0.process_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text)
WHERE ([duration]>=(10000000)))
GO

In addition to the filter, I have gone ahead and added few global variables which we might be interested as part of the collection process. Use this with caution and be careful in collecting every single event using Extended Events. I was pleasantly surprised to see the cpu_time and duration in SQL Server 2014 and hence thought it is worth a mention.

Finally, if you are doing it on a Live server. Then make sure to enable the Extended Event and we can use the “watch live data” which I found very useful in SSMS.

cpu xevent 04 SQL SERVER   Filtering CPU Bound Execution Plans with Extended Events

If you have used other events of Extended Events in your environment, I would like to learn from you on how you used them and to enable what scenarios.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Idera SQL XEvent Profiler – a Free Tool for XEvent Monitoring

Download Extended Event Profile FREE

We often resist the change, we do not like change and we keep on using the same old technology which we have been using it to resolve the new challenges which we face in our daily life. The Extended Event is such a technology as well. It was added to SQL Server in SQL 2008 and expanded upon greatly in SQL 2012 is a powerful mechanism for monitoring activity in SQL Server. It has huge implications for tools like SQL Diagnostic Manager, which switched over to XEvent technology for wait monitoring, and for users of all stripes who need to see what is happening on their servers.

Though the product was well thought of it was yet not quite ready for average users due to many reasons. It offered a reduced set of events and could only be manipulated through TSQL. In SQL 2012 the number of events went up dramatically and Microsoft released an XEvent GUI as part of Management Studio. However, lots of users think that the XEvent tool is a bit overkill for a lot of purposes. Therefore, a lot of users continue to use SQL Server Profiler, which uses trace technology. It’s easy to see why – it’s familiar, it’s easy to use, and it takes only a few clicks to get a diagnostic session started.

Team Idera has put together a new free tool called SQL XE Event Profiler, which offers the simplicity of Profiler but uses the powerful and lightweight XEvent technology on the back end. The idea was to provide the simple 2 click behavior that users were looking for, along with the familiar SQL Profiler templates that users are already accustomed to using. The application has the familiar start, stop, and pause buttons, the ability to turn auto-scroll on and off, and the ability to clear the grid. While we were at it, we added some nice grouping functionality to the event grid, along with text searching and export.

XEvent Profiler Template SQL SERVER   Idera SQL XEvent Profiler   a Free Tool for XEvent Monitoring

I am downloading this tool this weekend and going to play with this tool and see how I can use it on my various servers. I encourage all of you to do the same.  Here is the link to the original blog.

Download Extended Event Profile FREE

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Tomorrow 2 Sessions on Performance Tuning at TechEd India 2011 – March 25, 2011

Tomorrow is the third day of the TechED India 2011 at Bangalore. I will be speaking on two very interesting sessions. If you are developer, database administrator or just want to learn something new and interesting, I suggest you attend my two sessions tomorrow. Here is the details of the session.

Sessions Date: March 25, 2011

Here is the abstract of the session:

Understanding SQL Server Behavioral Pattern – SQL Server Extended Events
Date and Time: March 25, 2011 12:00 PM to 01:00 PM

History repeats itself! SQL Server 2008 has introduced a very powerful, yet very minimal reoccurring feature called Extended Events. This advanced session will teach experienced administrators’ capabilities that were not possible before. From T-SQL error to CPU bottleneck, error login to deadlocks –Extended Event can detect it for you. Understanding the pattern of events can prevent future mistakes.

SQL Server Waits and Queues – Your Gateway to Perf. Troubleshooting
Date and Time: March 25, 2011 04:15 PM to 05:15 PM

Just like a horoscope, SQL Server Waits and Queues can reveal your past, explain your present and predict your future. SQL Server Performance Tuning uses the Waits and Queues as a proven method to identify the best opportunities to improve performance. A glance at Wait Types can tell where there is a bottleneck. Learn how to identify bottlenecks and potential resolutions in this fast paced, advanced performance tuning session.

Here is the video which my wife shot while I was preparing for the sessions.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – I am Presenting 2 Sessions at TechEd India

 SQLAuthority News   I am Presenting 2 Sessions at TechEd IndiaTechED is the event which I am always excited about. It is one of the largest technology in India. Microsoft Tech Ed India 2011 is the premier technical education and networking event for tech professionals interested in learning, connecting and exploring a broad set of current and soon-to-be released Microsoft technologies, tools, platforms and services.

I am going to speak at the TechED on two very interesting and advanced subjects.

Venue:
The LaLiT Ashok
Kumara Krupa High Grounds
Bangalore – 560001, Karnataka, India

Sessions Date: March 25, 2011

Understanding SQL Server Behavioral Pattern – SQL Server Extended Events

Date and Time: March 25, 2011 12:00 PM to 01:00 PM
History repeats itself! SQL Server 2008 has introduced a very powerful, yet very minimal reoccurring feature called Extended Events. This advanced session will teach experienced administrators’ capabilities that were not possible before. From T-SQL error to CPU bottleneck, error login to deadlocks –Extended Event can detect it for you. Understanding the pattern of events can prevent future mistakes.

SQL Server Waits and Queues – Your Gateway to Perf. Troubleshooting

Date and Time: March 25, 2011 04:15 PM to 05:15 PM
Just like a horoscope, SQL Server Waits and Queues can reveal your past, explain your present and predict your future. SQL Server Performance Tuning uses the Waits and Queues as a proven method to identify the best opportunities to improve performance. A glance at Wait Types can tell where there is a bottleneck. Learn how to identify bottlenecks and potential resolutions in this fast paced, advanced performance tuning session.

My session will be on the third day of the event and I am very sure that everybody will be in groove to learn new interesting subjects.

I will have few give-away during and at the end of the session. I will not tell you what I will have but it will be for sure something you will love to have. Please make a point and reserve above time slots to attend my session.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Introduction to Extended Events – Finding Long Running Queries

The job of an SQL Consultant is very interesting as always. The month before, I was busy doing query optimization and performance tuning projects for our clients, and this month, I am busy delivering my performance in Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning Course. I recently read white paper about Extended Event by SQL Server MVP Jonathan Kehayias. You can read the white paper here: Using SQL Server 2008 Extended Events. I also read another appealing chapter by Jonathan in the book, SQLAuthority Book Review – Professional SQL Server 2008 Internals and Troubleshooting. After reading these excellent notes by Jonathan, I decided to upgrade my course and include Extended Event as one of the modules.

This week, I have delivered Extended Events session two times and attendees really liked the said course. They really think Extended Events is one of the most powerful tools available. Extended Events can do many things. I suggest that you read the white paper I mentioned to learn more about this tool. Instead of writing a long theory, I am going to write a very quick script for Extended Events. This event session captures all the longest running queries ever since the event session was started. One of the many advantages of the Extended Events is that it can be configured very easily and it is a robust method to collect necessary information in terms of troubleshooting. There are many targets where you can store the information, which include XML file target, which I really like.

In the following Events, we are writing the details of the event at two locations: 1) Ringer Buffer; and 2) XML file. It is not necessary to write at both places, either of the two will do.

-- Extended Event for finding *long running query*
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')
DROP EVENT SESSION LongRunningQuery ON SERVER
GO
-- Create Event
CREATE EVENT SESSION LongRunningQuery
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.sql_statement_completed
(
-- Add action - event property
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
-- Predicate - time 1000 milisecond
WHERE sqlserver.sql_statement_completed.duration > 1000
)
-- Add target for capturing the data - XML File
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\LongRunningQuery.xet', metadatafile='c:\LongRunningQuery.xem'),
-- Add target for capturing the data - Ring Buffer
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 seconds)
GO
-- Enable Event
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE
=START
GO
-- Run long query (longer than 1000 ms)
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY UnitPriceDiscount DESC
GO
-- Stop the event
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE
=STOP
GO
-- Read the data from Ring Buffer
SELECT CAST(dt.target_data AS XML) AS xmlLockData
FROM sys.dm_xe_session_targets dt
JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
WHERE dt.target_name = 'ring_buffer'
AND ds.Name = 'LongRunningQuery'
GO
-- Read the data from XML File
SELECT event_data_XML.value('(event/data[1])[1]','VARCHAR(100)') AS Database_ID,
event_data_XML.value('(event/data[2])[1]','INT') AS OBJECT_ID,
event_data_XML.value('(event/data[3])[1]','INT') AS object_type,
event_data_XML.value('(event/data[4])[1]','INT') AS cpu,
event_data_XML.value('(event/data[5])[1]','INT') AS duration,
event_data_XML.value('(event/data[6])[1]','INT') AS reads,
event_data_XML.value('(event/data[7])[1]','INT') AS writes,
event_data_XML.value('(event/action[1])[1]','VARCHAR(512)') AS sql_text,
event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') AS tsql_stack,
CAST(event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') AS XML).value('(frame/@handle)[1]','VARCHAR(50)') AS handle
FROM
(
SELECT CAST(event_data AS XML) event_data_XML, *
FROM sys.fn_xe_file_target_read_file
('c:\LongRunningQuery*.xet',
'c:\LongRunningQuery*.xem',
NULL, NULL))
T
GO
-- Clean up. Drop the event
DROP EVENT SESSION LongRunningQuery
ON SERVER
GO

Just run the above query, afterwards you will find following result set.

longrunningquery SQL SERVER   Introduction to Extended Events   Finding Long Running Queries

This result set contains the query that was running over 1000 ms. In our example, I used the XML file, and it does not reset when SQL services or computers restarts (if you are using DMV, it will reset when SQL services restarts).

This event session can be very helpful for troubleshooting. Let me know if you want me to write more about Extended Events. I am totally fascinated with this feature, so I’m planning to acquire more knowledge about it so I can determine its other usages.

Reference : Pinal Dave (http://blog.SQLAuthority.com)