Search results matching tags 'Performance' and 'DMVs'

A Warning to Those Using sys.dm_exec_query_stats

Adam Machanic — Fri, 23 Apr 2010 02:15:00 GMT

The sys.dm_exec_query_stats view is one of my favorite DMVs. It has replaced a large chunk of what I used to use SQL Trace for--pulling metrics about what queries are running and how often--and it makes this kind of data collection painless and automatic. What's not to love? But use cases for the view are a topic for another post. Today I want to quickly point out an inconsistency.

If you're using this view heavily, as I am, you should know that in some cases your queries will not get a row. One such case, as it turns out, is any time an ALTER TABLE appears in your batch. "No big deal," you might be thinking. "I don't regularly alter tables in stored procedures." But think again. Do you ever create a temporary table using SELECT INTO? Do you ever create indexes on the resultant table? I know I do--and quite often those indexes are primary keys, or unique constraints.

The interesting thing is that CREATE INDEX does not suffer from this problem. You can create unique indexes all day long and still get entries in the query stats DMV. And thus the inconsistency: Using ALTER TABLE to create a primary key or unique constraint on a temporary table is, for all intents and purposes exactly the same thing as creating a unique index using CREATE INDEX. But one works with the query_stats DMV and the other doesn't.

It's not difficult to imagine how this inconsistency arose; just rather annoying in practice. I went through a bunch of code today and changed several instances of ALTER TABLE to CREATE INDEX, and my monitoring scripts are already working better as a result. I recommend that you take the time to do the same, if you use this view.

To conclude this post, a quick repro so that you can see the issue:

SELECT
    'abc' AS a
INTO #x

ALTER TABLE #x
ADD UNIQUE (a)

SELECT *
FROM sys.dm_exec_query_stats
WHERE
    plan_handle IN
    (
        SELECT
            plan_handle
        FROM sys.dm_exec_requests
        WHERE
            session_id = @@SPID
    )

DROP TABLE #x
GO


SELECT
    'abc' AS a
INTO #x

CREATE UNIQUE INDEX whatever
ON #x (a)

SELECT *
FROM sys.dm_exec_query_stats
WHERE
    plan_handle IN
    (
        SELECT
            plan_handle
        FROM sys.dm_exec_requests
        WHERE
            session_id = @@SPID
    )

DROP TABLE #x
GO

Enjoy, and best of luck in all of your monitoring endeavors.

Who is Active? v9.57: Fast, Comprehensive DMV Collection - What's Really Happening on Your Server?

Adam Machanic — Thu, 03 Dec 2009 19:59:00 GMT

UPDATE, April 28 2011: Who is Active v9.57 is outdated. Please use v11.00 instead.

Happy December, SQLblog readers! My gift to you, just in time for the holidays: The newest "official" release of your favorite SQL Server activity monitoring stored procedure.

Click here to download Who is Active? v9.57

Since the last release--v8.82, from August of this year--I've made a number of modifications to the script, resulting in six interim "beta" releases. The uniting theme of all of these changes is more, better quality data, faster.

More data.

Two new core options were added at the behest of users: @show_sleeping_spids and @show_system_spids. These options cause the procedure to return information that wasn't previously available via Who is Active, about--you guessed it--sleeping and system sessions, respectively.
Another major change was adding a feature so that the script now shows blocking sessions whether or not they're included in the default filter criteria. This is not something you turn on or off--it just happens--and will ensure that if you're debugging a blocking scenario you'll automatically have all the information you need.
And for you query plan geeks out there, the full wait stats collection mode (see the "Faster data" section below) now returns node identifier information with CXPACKET waits--helping you to track progress of tasks as a plan is executed.
Finally, a small modification. The online help system (@help=1) now returns information about both the available input parameters as well as all of the available output columns.

Better data.

A few minor bugs were fixed, mostly having to do with the evils of MARS and the fact that the DMVs don't properly deal with MARS sessions in many cases.
Workarounds were also added for inconsistencies in how the DMVs report SQL handles, even without MARS.
Two features were added to help you get only the data you need when you need it, and not the data you don't:

Dynamic sort ordering, via a parameter called @sort_order, lets you pass in a list of columns and column directions by which to sort the output.
And "not" filters, implemented using parameters @not_filter and @not_filter_type, work exactly the opposite of regular filters. These are useful in those cases where you have a bunch of sessions that aren't of interest, and you don't want them cluttering your output.

Finally, I've changed the default output column order to something I think is a bit more useful. Don't like my selections? No problem--override me using the @output_column_list parameter.

Faster data.

This is the area in which I made the most modifications. A monitoring tool borders on useless when it takes a minute or more to return key metrics when your server is on fire, and alas, previous versions of Who is Active were doing just that for some users.
I've made major changes to the core queries in this version of Who is Active, bringing down query times from minutes to a few seconds in many cases.
In conjunction with these changes I added a new lightweight wait stats collection mode, which is the new default. This mode collects only the top non-CXPACKET wait, giving preference to blocked waits, so that you can see the worst problems without having to sort through a lot of output that may or may not apply.

If you miss the complete stats collection mode, it's still there--simply set @get_task_info=2 when calling the procedure.

I'm quite happy with this release, and I hope that it will help people quickly solve a number of tricky SQL Server problems. As always, your feedback is very much appreciated! Leave me a comment here, e-mail me (my address is in the script), or track me down at a conference. Most of the features in the past few versions are a direct result of requests I've gotten from users.

A huge thank you to those who tested and gave me feedback since the last version! Aaron Bertrand, Rajiv Jain, Michelle Ufford, Uri Dimant, and everyone else, I really am thankful for your efforts. To these people and all of my readers, I wish you a happy December and a prosperous 2010.

Until next time, enjoy!

Click here to download Who is Active? v9.57

Please ignore the text below. Putting in it for search purposes.

sp_whoisactive whoisactive sp_who sp_who2 sp_who3 sp_who4 sp_who5

The Hidden Costs of INSERT EXEC

Adam Machanic — Thu, 25 Jun 2009 20:19:00 GMT

INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision--seemingly purely aesthetic--can bring to the fore.

To illustrate the first of these examples I will be using the following table:

USE tempdb
GO

CREATE TABLE dbo.MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
GO

Consider the following queries, disregarding the fact that this isn't a good dynamic SQL example (I'm trying to keep it simple), and take a moment to think about the differences between them from a behavioral point of view:

EXEC
('
    INSERT dbo.MyTable
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')
GO

INSERT dbo.MyTable
EXEC 
('
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')
GO

It's quite common to insert some data into a table from dynamic SQL, and for as long as I can remember, I've believed the choice between these two forms of the query to be primarily an issue of style. My preference has been for the latter style, as I feel that it's a bit more readable. It also has the benefit of better supporting ownership chains, but that's something that, in most cases, we don't have to worry about any longer in SQL Server 2005 or 2008. But let's move beyond style and potential security issues and get to the substance. What actually happens when we run these two queries?

In the first case--insert done inside of the EXEC--the first step is that the outer statement--the EXEC itself--is parsed and validated. Now we jump down one level of context, into the string that was passed to EXEC, and that string is parsed and validated. The referenced objects are resolved, and the plan cache is checked. If there is no plan, the query is compiled. And then it's executed. Data is streamed from SalesOrderDetail into MyTable and when the query inside of the EXEC is done control returns to the calling context--EXEC--and assuming there were no errors, the EXEC is now completed.

In the second case, something quite a bit different occurs. The insert is parsed and validated, and an INSERT EXEC plan is generated. This plan does not involve SalesOrderDetail, as the query optimizer doesn't know at this point in the process where the data will be inserted from, since that step is dynamic. So the plan references something called the "Parameter Table" (plan truncated for simplicity):

|--Table Insert(OBJECT:([tempdb].[dbo].[MyTable]))
   |--Top(ROWCOUNT est 0)
        |--Parameter Table Scan

Once this plan has been generated, we again jump down one level of context, and the inner string is parsed and validated, the referenced object resolved, the cache checked, and the compiled plan executed. But what is this Parameter Table thing?

Here's where things start really diverging. Data is not, at this point, streamed from SalesOrderDetail directly into MyTable. Rather, it is streamed from SalesOrderDetail into the Parameter Table. And the Parameter Table, as it turns out, is in actuality a hidden temporary table. And not until all of the data has streamed into that hidden temporary table is control resumed by the EXEC context, and only then does the data start moving into its final home.

The natural question you might be asking yourself at this point is, just how much overhead does this Parameter Table introduce into the equation? The answer might just send you racing to check your stored procedures: the additional cost for the Parameter Table is well over 100% as compared with doing the insert inside of the EXEC. The fact that all of the data must be spooled to the Parameter Table before the insert can even begin tells us that this must be true, and it can be verified using a simple check against the sys.dm_exec_requests DMV, as in the following example:

EXEC
('
    INSERT dbo.MyTable
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')

SELECT
    r.writes AS [INSERT inside EXEC writes]
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID
GO

INSERT dbo.MyTable
EXEC
('
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail
')

SELECT
    r.writes AS [INSERT EXEC writes]
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID
GO

So that's that. We should avoid INSERT EXEC and try to do our inserts in the same context in which the SELECT is running--right?

Well, yes and no. There is another element at play here which I haven't yet mentioned. What if we were only inserting a few rows, and the table we were inserting into looked something like the following:

CREATE TABLE #MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
GO

The only difference between this table and the previous one is that this is a temporary table and the other is not. But temporary tables have their own interesting little twists, especially when it comes down to one of the key enemies in a highly-transactional system: recompilation. As it turns out, doing the insert inside the EXEC will cause the internal statement to recompile every time a new temp table is encountered. This means that if you have a stored procedure that creates a temp table, puts together a bit of dynamic SQL, and does an insert inside of that dynamic SQL, you'll now have yourself a recompilation problem.

To see this illustrated, try the following script. Here only a single row is inserted as a result of the dynamic query, but it's complex enough that the compile time more than overshadows the overhead of the Parameter Table:

USE tempdb
GO

DBCC FREEPROCCACHE
GO

CREATE TABLE #AvgTimes
(
    CPU_time DECIMAL(19,4) NOT NULL,
    insert_type VARCHAR(25) NOT NULL
)
GO

CREATE TABLE #MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

EXEC
('
    INSERT #MyTable
    SELECT TOP(1)
        sod.*
    FROM AdventureWorks.Sales.SalesOrderDetail sod
    WHERE 
        sod.UnitPrice > 10
        AND sod.LineTotal > 100
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Person.Contact c ON 
                c.ContactID = soh.CustomerID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND c.LastName LIKE ''Ab%''
        )
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Production.Product p
            WHERE
                p.ProductID = sod.ProductID
                AND p.Color IS NULL
        )
        AND NOT EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Sales.SalesPerson sp ON
                soh.SalesPersonID = sp.SalesPersonID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND sp.CommissionPct > 50
        )
')

INSERT #AvgTimes
SELECT
    r.cpu_time,
    'INSERT inside EXEC'
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID

DROP TABLE #MyTable
GO 5

CREATE TABLE #MyTable
(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [rowguid] [uniqueidentifier]  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

INSERT #MyTable
EXEC
('
    SELECT TOP(1)
        sod.*
    FROM AdventureWorks.Sales.SalesOrderDetail sod
    WHERE 
        sod.UnitPrice > 10
        AND sod.LineTotal > 100
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Person.Contact c ON 
                c.ContactID = soh.CustomerID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND c.LastName LIKE ''Ab%''
        )
        AND EXISTS
        (
            SELECT *
            FROM AdventureWorks.Production.Product p
            WHERE
                p.ProductID = sod.ProductID
                AND p.Color IS NULL
        )
        AND NOT EXISTS
        (
            SELECT *
            FROM AdventureWorks.Sales.SalesOrderHeader soh
            JOIN AdventureWorks.Sales.SalesPerson sp ON
                soh.SalesPersonID = sp.SalesPersonID
            WHERE
                soh.SalesOrderID = sod.SalesOrderID
                AND sp.CommissionPct > 50
        )
')

INSERT #AvgTimes
SELECT
    r.cpu_time,
    'INSERT EXEC'
FROM sys.dm_exec_requests r
WHERE 
    r.session_id = @@SPID

DROP TABLE #MyTable
GO 5

SELECT 
    AVG
    (
        CASE a.insert_type 
            WHEN 'INSERT inside EXEC' THEN a.CPU_time
            ELSE NULL
        END
    ) AS [CPU time - INSERT inside EXEC],
    AVG
    (
        CASE a.insert_type 
            WHEN 'INSERT EXEC' THEN a.CPU_time
            ELSE NULL
        END
    ) AS [CPU time - INSERT EXEC]
FROM #AvgTimes a
GO

DROP TABLE #AvgTimes
GO

So what have we learned today? The choice between INSERT EXEC and INSERT inside of EXEC is not purely stylistic and has definite performance implications. Here are the rules I'll be following from now on:

When working with permanent tables, always avoid INSERT EXEC if possible. There are some cases where it won't be possible to avoid. I only showed dynamic SQL in this post, but INSERT EXEC also applies to stored procedures. Can you safely rip apart all of the stored procedures in your system in order to avoid this issue? Maybe not quite as easily as you can rip apart the dynamic SQL within stored procedures.
When working with temporary tables, evaluate the complexity of the operations, the amount of data to be inserted, and most importantly, test every solution! The more rows that are inserted as a result of the INSERT EXEC, the more the overhead you'll get from the Parameter Table. On the flip side, the more complex the dynamic statement, the more overhead you'll get from recompilations. Every scenario is bound to be different and you may just learn something about your processes by doing this extra bit of analysis.

Enjoy!

A Gift of Script for 2009: Who is Active, Redux

Adam Machanic — Wed, 31 Dec 2008 14:49:00 GMT

Last year on December 31 I posted part of a larger monitoring script that I had been working on for a few months. I received lots of great feedback on the little script in both the comments and from people I was working with, and over the course of the last year I estimate that I have invested at least a couple of hundred hours in the script. The result of all of this time is attached, and I hope that this script is as useful for others as it has been for me; I've been using it pretty much nonstop while modifying it and it has become an indispensable part of my toolkit.

Alas, while I had planned to write a comprehensive blog post on the script for publication today, I simply don't have time to do it correctly at the moment--but I wanted to get the script out on the 31st in order to create a kind of tradition. So instead of telling you everything it can do, I will ask that you instead take a look inside the script, where I've done a fairly good job documenting both the parameters and the outputs. I'll do a followup post soon (I hope) and do a better job of telling you about the various options. In the meantime, I'll leave you with a list of the script's major features:

Wait stats collection, if you enable the GET_WAITS switch. This mode aggregates the various resources that the tasks associated with the session are waiting on, and lets you know how long the tasks have been waiting.
Collection of lock information, if you enable the GET_LOCKS switch. A lot of effort went into this particular feature--all of the locks for each session are aggregated in an XML format, and work is done to map the various identifiers associated with each lock to actual object names so that you don't have to do the footwork yourself.
Information about sleeping SPIDs holding open transactions, if you enable the GET_TRANSACTION_INFO switch. If you're being blocked by a SPID that is not active, you should be able to find out about it. This option lets you do it. As a bonus, it also pulls information about transaction log writes in any database in which the transaction has done a write.
Collection of the "outer" command/batch, if you enable the GET_OUTER_COMMAND switch. One of the comments on the script I posted last year asked why, if you issue SQL like "ALTER TABLE dbo.xx ALTER COLUMN yyy FLOAT NULL", you'll end up with statement text that looks like "UPDATE [zzz].[dbo].[xxx] SET [yyy] = [yyy]". This is an internal representation of what's actually happening, but in many cases is not esepecially useful for end-users. So if you want to see what was actually run, now you can.
Every expensive or potentially expensive option is now parameterized. I spent a lot of time tweaking performance and with no options running the thing is pretty fast. Enable some options--especially lock collection--and it will slow down considerably, so use these advanced features with care.

Enjoy, and please keep sending feedback! Let me know what else you would like to see, if you find bugs, etc. I'll post updated versions as major changes are made.

To finish I would like to thank a few of the people who had an impact on the evolution of this script over the past 12 months: Michael Condon, Erland Sommarskog, Aaron Bertrand, Louis Davidson, and Jonathan Kehayias.

Have a great 2009, everyone!