Most Costly Statement in a Stored Procedure

SQL Server
A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don't have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital. Happily, Extended Events are here to help. Finding a Costly Statement Query tuning is initially an act of discovery. Which queries, batches, procedures are inflicting the most pain on us. That pain could be measured a bunch of ways. The three most common, in particular order, are: Frequency with with a given query/batch/procedure is called.Resources used by that query.Length of time that it takes…
Read More

Capture Execution Plans Only For Long Running Queries

SQL Server
I love questions. Most of all, I love questions I can answer. I spotted this question recently: How can I use Profiler to capture execution plans for queries over a certain duration? Oh, that's easy. You don't use Profiler. You use Extended Events. Query_post_execution_showplan Extended events are just better than Profiler. Period. One of many things that is superior is the way in which the events are configured. Take for example query_post_execution_showplan. Here are the fields it captures: This event will capture execution plans plus runtime metrics. It can easily be filtered on any of the fields listed, and you can even add the database_name field if you want. So, to filter by duration is pretty simple: CREATE EVENT SESSION ExecPlansDuration ON SERVER ADD EVENT sqlserver.query_post_execution_showplan (WHERE ([duration] > (1000000)))…
Read More

Extended Events: Filter on Stored Procedure Name

SQL Server, You Can't Do That In Profiler
I just received a question about Extended Events: What about filtering on the stored procedure name. You know I love writing and talking about Extended Events. The answer is, well, sure, we can do that. However, as with all things, there may be wrinkles worth being aware of. Let's examine this. Filter on Stored Procedure Name Let's create an Extended Event session that captures rpc_starting and rpc_completed: CREATE EVENT SESSION StoredProcedureName ON SERVER ADD EVENT sqlserver.rpc_completed (ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ), ADD EVENT sqlserver.rpc_starting (SET collect_statement = (1) ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ); I've added a WHERE clause to the Extended Event to capture only those procedures that have an object_name equal to 'AddressByCity'. If we look at the output from these…
Read More

Extended Events Capturing the T-SQL of Prepared Statements

SQL Server, T-SQL, Tools
I asked this question myself: Is there a way to use Extended Events to capture the T-SQL of a prepared statement? Why would I be concerned with prepared statements? Wouldn't sql_batch_completed and rpc_completed cover us? Well, no. What happens when you use sp_prepare? What happens when you're using an ORM tool that's using prepared statements? You may see queries that look like this: EXEC sp_execute 5, 48766; What the heck code is that executing? Let's find out. sp_statement_completed Here's a set of sample code that I swiped from Microsoft (they don't mind, but, full attribution like a good citizen, you'll find it here): DECLARE @P1 int; EXEC sp_prepare @P1 output, N'@Param int', N'SELECT * FROM Sales.SalesOrderDetail AS sod INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID WHERE SalesOrderID =…
Read More

Fun Fact: AWS RDS and system_health

AWS, RDS
Did you know that the system_health Extended Event session was running in your RDS instances? Well, it is. HOWEVER. This query, which works perfectly fine on my on premises instance of SQL Server, will fail: SELECT @path = dosdlc.path FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc; SELECT @path = @path + N'system_health_*'; WITH fxd AS (SELECT CAST(fx.event_data AS XML) AS Event_Data FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL) AS fx ) SELECT dl.deadlockgraph FROM ( SELECT dl.query('.') AS deadlockgraph FROM fxd CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl) ) AS dl; Whereas, thanks to Aaron Bertrand, this query will work just fine: WITH fxd AS (SELECT CAST(fx.event_data AS XML) AS Event_Data FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx ) SELECT dl.deadlockgraph FROM ( SELECT dl.query('.') AS deadlockgraph FROM fxd CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl) ) AS dl;…
Read More

Can We Get Row Counts After Execution?

SQL Server, T-SQL
The general idea for this question came from dba.stackexchange.com: could we, and if we can, how, get row counts after execution. I was intrigued with the idea, so I ran some tests and did a little digging. I boiled it all down in the answer at the link, but I figured I could share a little here as well. Properly Retrieve Row Counts After Execution The right way to do this is obvious and simple. Before you need it, set up an Extended Events session. Done. The only question is what goes into the Session. First blush, sql_batch_completed and/or rpc_completed. Both will return a rows affected value. Although, interestingly, the row_count value is documented as rows returned. However, it's both. But, if you really want to get picky, batches and…
Read More

Extended Events: Queries and Waits

SQL Server, You Can't Do That In Profiler
Wouldn't it be great to be able to put together queries and waits at the same time? You all capture query metrics using some method. Most of us query sys.dm_os_wait_stats or sys.dm_db_wait_stats. Combining them is hard. You could query the wait stats. Store the results in a table variable. Run the query in question. Then query the wait stats again into a different table variable. Join the two table variables together to find the differences. Ta-da, you have query waits. Well. Probably. If you're the only one running queries on the system. Also, you're not seeing system waits or other noise caused by activity on the system. Or, we could put Extended Events to work. Queries and Waits Just like Profiler/Trace, you can capture stored procedures, batches, and individual statements…
Read More

Extended Events: Live Data Explorer, Grouping

SQL Server, You Can't Do That In Profiler
Of all the things that Extended Events does, I've found the ability to quickly and easily gather a little bit of data and then use the Data Explorer window Live Data grouping to aggregate it to be one of the greatest. Sure, if we're talking about using Extended Events on a busy production server, this method probably isn't going to work well. There, you are going to be better off querying the XML (I know, I know, but I have ways to help there too). But in development, when doing testing and query tuning, the Live Data window is a gift of the gods on par with fire or beer (it's not as good as whiskey). Live Data Grouping Let's imagine a scenario like this. You're working on some query…
Read More
Extended Events: Live Data Explorer, Getting Started

Extended Events: Live Data Explorer, Getting Started

SQL Server, You Can't Do That In Profiler
One reason a lot of people don't like Extended Events is because the output is in XML. Let's face it, XML is a pain in the bottom. However, there are a bunch of ways around dealing with the XML data. The first, and easiest, is to ignore it completely and use the Live Data window built into SQL Server Management Studio. I've written about the Live Data window before, and I've been using it throughout this series of posts on Extended Events. There's a lot more to this tool than is immediately apparent. Today, we're going to explore the basics around this tool Live Data There are two easy ways to get the Live Data window open. The first, for any Extended Event session that's running, you can right click…
Read More

Extended Events: system_health and a Long Running Query

SQL Server, You Can't Do That In Profiler
Wouldn't it be great to just quickly and easily take a look at your system to see if you had any queries that ran for a long time, but, without actually doing any work to capture query metrics? Oh, yeah, I can do that right now, and so can you. All we need is something that is built into every single server you currently have under management (sorry, not Azure SQL Database) that is SQL Server 2008 or better: system_health system_health Extended Event Session The system_health Extended Events session is very well documented by Microsoft here. I've talked about it before, in relation to Azure SQL Database, and, on the same topic we're going over today, long running queries and waits (cause if you have a long running query, you…
Read More