Search Results for: extended event

Query Store, Forced Plans, and New Plans

I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1). Let's say you have selected a plan that you want to force. You set it up. Now, let's say the plan ages out of cache or even goes through a recompile. During the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. However, with Query Store and plan forcing, the plan that's going to be used is the plan that is being forced. But, does that other plan, the one not used, get stored in Query Store? I have no idea. Let's find out. The Setup To start with, a small stored procedure…
Read More

A View Is Not A Table

Blog post #4 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel In SQL Server, in the T-SQL you use to query it, a view looks just like a table (I'm using the AdventureWorks2014 database for all these examples): SELECT * FROM Production.vProductAndDescription AS vpad;   SELECT vpad.Name, vpad.Description, vpmi.Instructions FROM Production.vProductAndDescription AS vpad JOIN Production.Product AS p ON p.ProductID = vpad.ProductID JOIN Production.vProductModelInstructions AS vpmi ON vpmi.ProductModelID = p.ProductModelID WHERE vpad.ProductID = 891 AND vpad.CultureID = 'fr'; The above query actually combines two views and a table. This is what is commonly referred to as a "code smell". A code smell is a coding practice that works, but that can lead to problems. In this case, we're talking about performance problems. The performance problems when using views to join to…
Read More

Speaker of the Month: April 2016

THIS IS NOT AN APRIL FOOL POST! Seriously. My Speaker of the Month for April 2016 is Keith Tate (b|t) and his session at SQL Saturday Chicago called Profiler is Dead, Long Live Extended Events. I actually suspected very strongly from the start of the session that it was going to be good. The reason for this, Keith was having issues with his machine, but he started the session anyway. It was an excellent beginning. Then, he started to talk about Extended Events and use his slide deck to emphasize the points he was making, and it was wonderful. For example, as he talked about the way the number of events has grown in each version of SQL Server since 2008, he used larger and larger fonts with the bigger…
Read More

Query Store and Recompile

One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database). What happens to plan forcing when you have OPTION RECOMPILE on a query? Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing: ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; If this procedure is called with the value of 'Mentor' you get…
Read More

Why Is The Server Slow?

This is blog post #2 in support of Tim Ford's (b|t) #iwanttohelp, #entrylevel. If you haven't been working in SQL Server for very long, you may not have got this phone call yet, but you will: Hi, yeah, the server is slow. Thanks. Bye. Let's pretend for a moment that you know which server they're referring to (because just finding out that piece of information can be a challenge). Now what? The list of tools and mechanisms within SQL Server for gathering metrics is extremely long: Performance Monitor Dynamic Management Views & Functions System Views Extended Events Trace Events Activity Monitor Data Collector Execution Plans 3rd Party Tools I'm leaving out lots of stuff in that list. So where do you start when you get this phone call? Where is the server slow?…
Read More

Positivity

I'm sitting in the classroom of SQL Cruise listening to Tim Ford (b|t) explain mechanisms for monitoring indexes. It's a great class. Earlier in the week I got to hear Jes Borland (b|t) talk about extended events and do a session on wait statistics. I was also lucky enough to listen to David Klee (b|t) talking about systems monitoring, especially around VMs. Argenis Fernandez (b|t) and Jason Hall (b|t) are coming up today. In short, I've received some excellent learning while on a boat in the Caribbean. Now, one could argue (and you'd be right) that I'm thinking about positivity because of the nature of the position in which I find myself. Hang on though, I have some additional points. One of the biggest strengths of the SQL Cruise is the intimacy of the…
Read More

Removing All SQL Server Query Store Data

While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn't there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag. Jamey Johnston (t|b) came through... and it was right there in the documentation that I had been reading, over and over. In fact, it was in the documentation in two different places. Reading is clearly a problem for me today. Just so that you know, it's actually really easy:…
Read More

Trace Flags in Azure SQL Database

One of the ways that you take more direct control over your SQL Server instances is through the use of trace flags. There are a number that people recommend you enable by default. Prior to Extended Events for example, I'd say you should turn on trace flag 1222 in order to capture deadlock information on your server (now I just recommend you use the system_health session). I absolutely think you should turn on trace flag 2371 to get better behavior out of your automated statistics updates. There are others that I'll leave to all the systems experts to advise you on. What about Azure SQL Database? I doubt you'll be shocked, but if I try this: DBCC TRACEON (2371,-1); I get the following error: Msg 2571, Level 14, State 3,…
Read More

Monitoring for Timeouts

The question came up at SQL Rally, "Can you use Extended Events to monitor for query timeouts?" My immediate response was yes... and then I stood there trying to think of exactly how I'd do it. Nothing came quickly to mind. So, I promised to track down the answer and post it to the blog. My first thought is to use the Causality Tracking feature to find all the places where you have a sql_batch_starting without a sql_batch_completed (or the same thing with rpc calls). And you know what, that would work. But, before I got too deep in trying to write the query that would find all the mismatched attach_activity_id values that have a sequence of 1, but not one of 2, I did some additional reading. Seems there's…
Read More

Speaker of the Month: January 2015

I love it that my first post of the new year is going to be Speaker of the Month. I'm really enjoying doing these because I'm getting to attend a lot more sessions at the events I go to in order to get choices. But, please, don't bug me. If I can attend your session, I will. If I can't... Anyway. Speaker of the Month for January 2015 is William Wolf (b|t) and his session "Common Coding Mistakes and How to Mitigate Them" that was delivered at SQL Saturday DC. This was a good session. It was informative. I really liked how Bill (I'm going to use that because it's easier to type and despite looking like the Demon Biker of the Apocalypse, he's a bigger sweetheart than I am) kept…
Read More