Combine Extended Events and TagWith to Monitor Entity Framework

SQL Server 2016, SQL Server 2017, T-SQL
I'm going to start with a sentence that makes a lot of people crazy; As a DBA and database developer, I love Entity Framework. That's right. Entity Framework is the bomb. It's amazing. It does wonderful stuff. Entity Framework makes the developers lives and jobs easier. It makes my life and job easier. Yes, Entity Framework will improve your job quality and reduce stress in your life. With one caveat, it gets used correctly. That's the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if used correctly. Yet, all of these, used incorrectly, can make your life a hell. One nit that I've always had with Entity Framework is that it's very…
Read More

Which SELECT * Is Better?

SQL Server, T-SQL
The short answer is, of course, none of them, but testing is the only way to be sure. I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different). Let’s start with the simplest: SELECT    * FROM    Production.ProductListPriceHistory AS plph; This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index: CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate); When I…
Read More

SQL Rally: Performance Tuning Abstract

PASS
I get the call, you get the call, everyone gets the call. "Hey, my app/procedure/query/report is running slow." Now what do you do? You go to my full day session at SQL Rally, that's what. Assuming you vote for it. I didn't post the abstract I submitted for the SQL Rally before because I thought that it would be redudant. However, since it's not right off the voting page (unless they updated it since I voted), if you're interested, here's what I thought I would do for a day. If it sounds good to you, please go here and vote for it. One of the most common problems encountered in SQL Server is the slow running query. Once a query is identified as performing poorly, DBAs and developers frequently don’t understand how…
Read More

24 Hours of PASS: Summit Preview

PASS, T-SQL
Registration is open for the second 24 Hours of PASS this year. This one is going to be a preview of the Summit itself. So all the sessions are tied, in some manner, to sessions being given at the summit.Here's a link to go and register. I'm very excited to be able to say that I'll be presenting in this 24HOP. One of my presentations at the Summit this year is Identifying and Fixing Performance Problems Using Execution Plans. It covers pretty much what it says, methods for fixing performance problems by exploring the information available within execution plans. But, how do you know you have a performance problem? That's where my preview session comes in. Identifying Costly Queries will show you several ways to gather metrics on your system so that you…
Read More

When did this statement start?

SQL Server, T-SQL
UPDATE: This post is incorrect. Adam nailed it in the comments. I explain my mistake here. A question came up over at SQL Server Central where someone was wondering if it was possible to know when a given statement within a batch started. Immediately I thought, oh yeah, that's easy, use the sys.dm_exec_requests dynamic management view (DMV). Done. Wrong. The original poster pointed out that I had assumed that the values present in the DMV represented statement level values, but they show the batch. While the DMV shows a start_time, that time is the start of the current batch, not the statement within the batch. Now the question was, where else might I get this data? I next tried sys.dm_exec_sessions because it has the last_request_start_time value. Sure enough this worked. Don't…
Read More

More Refinements on the Missing Indexes Query

SQL Server, T-SQL
Greg Larson posted a really nice query to find worst performing stored procedures in your system. He put in all kinds of attributes to make it customizable, changing the definition of "worst" to different measures,etc. Great query in general. In it he linked to sys.dm_exec_plan_attributes and got the db_id attribute. Duh! So instead of spelunking through the XML to retrieve the database name, I can pull the db_id and use the DB_NAME function. Cleans things up considerably. Thanks Greg. Here's the cleaned up code: WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp) SELECT DB_NAME(CAST(pa.value AS INT)) ,s.sql_handle ,s.total_elapsed_time ,s.last_execution_time ,s.execution_count ,s.total_logical_writes ,s.total_logical_reads ,s.min_elapsed_time ,s.max_elapsed_time --,s.query_hash ,p.query_plan ,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact ,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage ,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName FROM (SELECT TOP 20 s.sql_handle ,s.plan_handle ,s.total_elapsed_time…
Read More

Refining the Missing Index Data

SQL Server, T-SQL
In my previous post I showed how you could determine if a query had missing index information, but it was difficult to get meaningful data out because I didn't know XQuery well enough. I spent some more time refining the query and here are the results. This new query will show some basic performance information gathered from the aggregate data in sys.dm_exec_query_stats. It combines this with the full data pulled from the Missing Indexes element in the XML of the execution plan. I've got it listing all the recommended columns and grouping. So this means that the performance data is repeated in order to allow for the full listing of groups & columns. It seems to work well. A couple of interesting points. My purpose is to provide a short-cut…
Read More