Performance Tuning SQL Server Database: Extended Events

Extended Events are not new to SQL Server 2012 but they have been greatly enhanced from when they were first introduced in SQL 2008. As is my style, I am not going go into to a long protracted discussion of extended events. That is what MSDN is for and they do good job of covering all the details there.

What I am going to focus on is how Extended Events can be used in data warehousing. To begin with, Extended Events (XEvent) can be incredibly valuable for data warehouse scenarios because it can measure the throughput (MB/sec) for each query executed against the data warehouse server. Knowing the throughput requirements (MB/sec) of the date warehouse queries is absolutely critical for architecting a data warehouse server that preforms as expected! See: Data Warehouse Storage Throughput (MB/sec) for more information.

If for example you know that a data warehouse query returns 2,000 MB and that the users want the query to complete in 10 seconds, and assuming that the query takes 5 seconds to aggregate on the CPU, then you know that the minimum required throughput should be 400 MB/sec:

Example:

Query MB / seconds required

2,000 / 5 = 400 MB/sec

Note: The above does not account for concurrence (number of users and the other queries running at the same time on the server which will have to be accounted for as well).

You can use IO Statistics to measure a query’s MB as explained here: Page Compression, but that is tedious to do for a lot of queries and doesn’t give a good idea of the other queries running on the server as well. Now enter Extended Events, with extended events you create a session get all that information plus much more.

Example:

1. Create an Extended Events session with the following actions:

CREATEEVENTSESSIONQueryIOStats2012

ONSERVER

ADDEVENTsqlserver.sql_statement_completed

  (ACTION (sqlserver.database_id,

           sqlserver.sql_text,

                sqlserver.nt_username,

                sqlserver.session_id,

                sqlserver.query_hash,

                sqlserver.query_plan_hash)

    )

ADDTARGETpackage0.asynchronous_file_target(SETfilename=N’c:\temp\QueryIOStats2012.xel’)

WITH (MAX_DISPATCH_LATENCY= 5 SECONDS);

 

Note: I choose to save the results to an xml file instead of keeping them in memory because they can get very large, depending on how active your server is.

2. You then let the session run for the period of time that you want to collect query data for and with a little bit of knowledge about querying xml you have access to a wealth of knowledge.

select

       a.LogTime

       ,DATEPART(DAY,a.LogTime)asDay

       ,DATEPART(hour,a.LogTime)asHour

       ,a.Duration/1000000.00 asDurationSec

       ,a.CPUTime/1000000.00  asCPUSec

       ,a.PhysicalReads

       ,a.LogicalReads

       ,a.Writes

       ,a.PhysicalReads*8/1024.00   asPhysicalReadsMB

       ,a.LogicalReads*8/1024.00   asLogicalReadsMB

       ,a.Writes*8/1024.00  asWritesMB

from (

SELECT

    EventLog.value(‘(/event[@name=”sql_statement_completed”]/@timestamp)[1]’,

                     ‘datetime’)ASLogTime,

       EventLog.value(‘(/event/data[@name=”duration”]/value)[1]’,

                     ‘int’)ASDuration,

       EventLog.value(‘(/event/data[@name=”cpu_time”]/value)[1]’,

                     ‘int’)ASCPUTime,

       EventLog.value(‘(/event/data[@name=”physical_reads”]/value)[1]’,

                     ‘int’)ASPhysicalReads,

     EventLog.value(‘(/event/data[@name=”logical_reads”]/value)[1]’,

                     ‘int’)ASLogicalReads,

       EventLog.value(‘(/event/data[@name=”writes”]/value)[1]’,

                     ‘int’)ASWrites,

     EventLog.value(‘(/event/action[@name=”sql_text”]/value)[1]’,

                     ‘varchar(max)’)ASSQLText,

       EventLog.value(‘(/event/action[@name=”session_id”]/value)[1]’,

                     ‘varchar(max)’)ASSession_ID,

       EventLog.value(‘(/event/action[@name=”nt_username”]/value)[1]’,

                     ‘varchar(max)’)ASNTUsername,

       EventLog.value(‘(/event/action[@name=”query_hash”]/value)[1]’,

                     ‘varchar(max)’)ASQueryHash,

       EventLog.value(‘(/event/action[@name=”query_plan_hash”]/value)[1]’,

                     ‘varchar(max)’)ASQueryPlanHash             

FROM (

SELECTCAST(event_dataASXML)ASEventLog

       FROMsys.fn_xe_file_target_read_file

       (‘c:\temp\QueryIOStats2012*.xel’,null,NULL,NULL))asLogRecords

)asa

orderbya.LogTime

;

 

<rant> yeah I know querying xml makes my brain hurt too, but it is so worth it !!!</rant>

 

Results:

image

With these results you can now analyze the query patterns aggregated by a certain time interval (hours by day) and compare that to actual hardware IO limitations as measures by SQLIO. You can now answer difficult questions like the following with confidence:

How will the date warehouse server preform if I add X amount of data?

What are the largest queries and peak query times?

Will my data warehouse perform well if I add X amount of users?   

Is my ETL taking too long because of lack of server resources or slow ETL logic?

 There is so much more that can be learned from the above extended event xml query, such as query and query plan hash but I hope that this gives you an idea of what is possible.

Script

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: