I'm actually having problems identifying the utility of execution plans when working with natively compiled procedures. Or, put another way, why bother? I've posted a couple of times on natively compiled procedures and SQL Server execution plans. I've found the differences interesting and enlightening, but I'm seriously questioning why I should bother, at least currently. I'm sure there will be many changes to the behaviors of the natively compiled procedures and their relationship with execution plans. But right now, well, let's look at an example. I have three simple tables stored in-memory. Here's the definition of one: CREATE TABLE dbo.Address ( AddressID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 50000), AddressLine1 NVARCHAR(60) NOT NULL, AddressLine2 NVARCHAR(60) NULL, City NVARCHAR(30) COLLATE Latin1_General_100_BIN2 NOT NULL, StateProvinceID INT…
All the wonderful functionality that in-memory tables and natively compiled procedures provide in SQL Server 2014 is pretty cool. But, changes to core of the engine results in changes in things that we may have developed a level of comfort with. In my post last week I pointed out that you can't see an actual execution plan for natively compiled procedures. There are more changes than just the type of execution plan available. There are also changes to the information available within the plans themselves. For example, I have a couple of stored procedures, one running in AdventureWorks2012 and one in an in-memory enabled database with a few copies of AdventureWorks tables: --natively compiled CREATE PROC dbo.AddressDetails @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL…
Ever had that moment where you start getting errors from code that you've tested a million times? I had that one recently. I had this little bit of code for pulling information directly from query plans in cache: WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), QueryPlans AS ( SELECT RelOp.pln.value(N'@PhysicalOp', N'varchar(50)') AS OperatorName, RelOp.pln.value(N'@NodeId',N'integer') AS NodeId, RelOp.pln.value(N'@EstimateCPU', N'decimal(10,9)') AS CPUCost, RelOp.pln.value(N'@EstimateIO', N'decimal(10,9)') AS IOCost, dest.text FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY deqp.query_plan.nodes(N'//RelOp') RelOp (pln) ) SELECT qp.OperatorName, qp.NodeId, qp.CPUCost, qp.IOCost, qp.CPUCost + qp.IOCost AS EstimatedCost FROM QueryPlans AS qp WHERE qp.text = 'some query or other in cache' ORDER BY EstimatedCost DESC; I've probably run this... I don't know how many times. But... I'm suddenly getting an error: Msg 8114, Level 16, State 5,…
No, I don't mean the use of sp_updatestats is not smart. It's a fine, quick mechanism for getting statistics updated in your system. But the procedure itself is not smart. I keep seeing stuff like "sp_updatestats knows which statistics need to be updated" and similar statements. Nope. Not true. Wanna know how I know? It's tricky. Ready? I looked at the query. It's there, in full, at the bottom of the article (2014 CTP2 version, just in case yours is slightly different, like, for example, no Hekaton logic). Let's focus on just this bit: if ((@ind_rowmodctr <> 0) or ((@is_ver_current is not null) and (@is_ver_current = 0))) The most interesting part is right at the front, @ind_rowmodctr <> 0. That value is loaded with the cursor and comes from sys.sysindexes and the rowmodctr column…
Earlier this week I introduced the concept of Managed Backups (and caused less of a turmoil than I thought I would). Now I want to show you how it works. It's really simple and quite well documented. Before you get to the, insanely simple, task of actually enabling Managed Backup, you will need to go through the prerequisites. First, and this should be obvious, but I'll state it, just in case, you need to set up an Azure storage account. That's so insanely straight forward that I'm not going to say more. Then, you have to set up encryption on your system. I used these commands to prep it: CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$qlserver2012queryperformancetuning'; CREATE CERTIFICATE CloudDojoCert WITH SUBJECT = 'Backup Encryption Certificate'; Again, shouldn't have to…
It's kind of fun to see Azure development artifacts on display. I've posted about them before, a couple of times. I'm starting to finally get systematized about the whole thing, just so I can see stuff as it changes rather than discover them by accident or get told about them by someone else. Here's a little query I'm running to see when system views were last modified: SELECT av.name, av.create_date, av.modify_date FROM sys.all_views AS av ORDER BY av.modify_date DESC; The most recent stack of changes are here: I'll keep an eye on them to see what I can spot about interesting new functionality. I also compared the listing of all views in Azure to those on a SQL Server 2012 instance and came up with a list of differences. These…
KILT DAY! Today we have to eat our vegetables and then get lots and lots of sweet desert. Or. Today we hear about PASS Finances as a part of the official annual meeting and then we get to hear Dr. David Dewitt speak (completely and utterly getting our nerd on and squeeing like teenage girls at a Bieber concert). I will be live-blogging this event, so watch this space. 8:20: Douglas McDowell kicks off the key note today. Â the vast majority of the money that runs PASS comes from the Summit. That's right, by attending the Summit you're also supporting the organization. The Business Analytics Conference, which kicked off this year also provides quite a bit more money to the organization. 8:25: PASS has changed its budgeting process. At this…
I am liveblogging the keynote from the bloggers table at the PASS Summit again this year. Just keep scrolling. Watching the introduction video as people trickle in. All the other bloggers are setting up. I get in early. I didn't rearrange the seats this year. I see others doing it now. 8:11: Watching the videos of all the attendees registering and meeting people at the start of the event and last night's welcome reception is awesome and fun. 8:21: The lights go down and the videos of what everyone is looking forward to at the Summit. In keeping with our location, right next to the NASCAR Hall of Fame, we've got a bit of a race theme going on. We're seeing current PASS President, Bill Graziano having a dream about…
First thing, there are no bad operators, just bad parents, uh, I mean query writers, or database designers, or ORM tools. Why do I say this? Because all the operators within a query execution plan serve a purpose. They are there to fulfill a task. Depending on where and when you see them, they're doing exactly what you ask of them. The issues come up because you're asking them to do a task that they may not be well suited for. This comes from inappropriate structures and inappropriate code. Lazy spools are not really bad (that was just link bait). In fact, depending on the query, what's being done, how you're retrieving data, what data is being retrieved, the lazy spool is actually awesome. But, it's good to know what…
I'm honestly not crazy about dynamic T-SQL within stored procedures. There are just a few too many opportunities to mess it up with dire circumstances to your server and your data. However, I absolutely recognize that dynamic T-SQL may be needed, and, in some situations, the best way to solve a problem. If you must use dynamic T-SQL, there are ways that are much more efficient than others. The very best thing you can do if you need to build dynamic strings to execute T-SQL in your stored procedures is use sp_executesql. The main reason I advocate for sp_executesql is because you can build out completely dynamic strings of T-SQL, but, you can still take advantage of parameters. Parameters help you avoid a chat with the parents of Bobby Tables…