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)

About these ads

SQLAuthority News – I am Presenting 2 Sessions at TechEd India

TechED 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 Bugger
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.

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)