Conor vs. SQL

Conor vs. window functions, sequence/ranking functions order-of-operations

2012-02-03T02:15:17Z

We had a customer issue today that ended up being by-design but was confusing enough that I offered to write up a blog post so that people could reference it in the future. It boils down to the question “when should ROW_NUMBER() be computed in a query?” I’ll define some terms: Sequence/Ranking functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(N). These are all functions that use the OVER clause. They support PARTITION BY and ORDER BY clauses within OVER() and are...(read more)

Conor vs. SqlBits Spring 2012

2012-01-30T22:49:57Z

For those interested, I will be speaking at the SqlBits conference in London, UK at the end of March. I’ll be giving the Saturday keynote (on the new Batch processing engine in SQL 2012 used with Column Store indexes) and 2 other talks (one on Data Warehousing broadly and the other on Distributed Query Internals) during the conference. I hope to see some of you there! Thanks, Conor...(read more)

Conor vs. SQL Azure Resource Usage

2012-01-16T17:43:53Z

For those of you watching SQL Azure, you probably have noticed that it releases more frequently than regular SQL Server. There was a release in late November/early December (it is complicated since the installation of the new software is done largely transparently to customers and it is done over the course of several days – there are many different parts to the SQL Azure service beyond what you would call the normal “database engine” part, and we don’t install new software on them exactly...(read more)

Conor vs. ColumnStore Index Stats

2012-01-09T18:40:18Z

I haven’t posted much on the new SQL Server 2012 features yet, but I’m going to start doing so soon. I’ll also be speaking about them at upcoming conferences. We have an exciting new index type in SQL Server 2012 that is column-oriented called the ColumnStore index. It is a multi-column index that, when combined with other features in SQL Server, can make certain Data Warehouse (DW) queries run significantly faster. I did a demo of this in Serbia in October (you can...(read more)

Conor vs. Distributed Query Provider Hints

2012-01-09T14:55:19Z

(I promised I’d write more ) Today I will talk a bit about Distributed Query (DQ), a feature that lets you query across databases/servers. I’ve previously posted an academic paper we wrote on the subject some time back ( http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.59.8007 ), but today I’ll post something up there for folks considering how query plans get optimized. SQL Server’s optimizer does lots of different things before a plan is selected. It considers lots and lots of...(read more)

Conor vs. SQL Azure/SQL Server 2012

2012-01-04T14:16:04Z

I’m not one for New Year’s Resolutions, but I am going to try to blog a bit more. It’s hard to describe working at Microsoft, especially as one gets more senior. There are lots of really smart people. There’s an ability to work on problems that are very difficult for smaller companies to even try. The operative description for the recent past has been “extremely busy”. That’s good for customers, as it means we are building things for you . We have SQL Server 2012...(read more)

Conor vs. Extended Events

2011-12-16T17:46:47Z

I have been playing with the XEvent feature in SQL 2012 (RC0) and there are a few gotchas that I hit. I figured I’d post them up here so that others could learn from my experiences. I was trying to do something pretty simple – get the % Processor Used computation from the performance counters. SQL Server exposes these through an XEvent, so I thought I’d get that data through the event. 1: CREATE EVENT SESSION test_session 2: ON SERVER 3: ADD EVENT sqlserver.perfobject_processor...(read more)

Conor vs. Synergija Slide Decks

2011-10-24T19:17:06Z

I had a great time visiting Belgrade last week. I’d like to thank everyone who attended one of my talks and I wanted to post up the slide decks for those who would like to see them. One talk was on the new SQL 2012 ColumnStore index feature + basic design overview. For those who read the blog, the second deck is not a surprise as it is the standard QO architecture talk I sometimes give to new audiences. I had a wonderful time! Here is my slide deck introducting the ColumnStore index...(read more)

Conor vs. Sinergija 2011

2011-10-07T14:30:38Z

As a professional announcement, I will not be speaking at PASS this year (nor will I be at the conference). Instead, I will be in Belgrade, Serbia the following week to speak at the Sinergija 11 conference. I’ll be doing a talk on the new ColumnStore index and I believe I’ll also be doing a talk on how Optimizers work. This is my first time in Serbia, so I’m obviously looking forward to something new....(read more)

Conor vs. Max Server Memory

2011-09-30T19:00:40Z

Question (paraphrased): “Conor, does changing the max server memory settings always empty the plan cache on SQL 2005 and 2008?” Answer: yes, this is what happens and what is supposed to happen. One of the inputs to the optimizer is the max server memory, and it is used in the costing functions to determine which plan gets chosen during optimization. Changing this setting could imply that other plans are now better. As such, the plan cache is emptied and plans are compiled...(read more)

Conor vs. SQL Saturday Austin

2011-09-27T15:44:16Z

I'll be talking about one of the new things our team has been develping this Saturday at the SQL Saturday event in Austin ( http://www.sqlsaturday.com/97/eventhome.aspx) on the new ColumnStore index being added in the upcoming release of SQL Server. It will have a little something for everyone, so I hope to see some of you there. The talk is scheduled for 10:50am. I'll get the slides up when I am done. Happy Querying! Conor...(read more)

Conor vs. Parallel Execution Plan Contexts/Runtime Instances

2011-06-28T13:50:35Z

I’ve received a question about parallel execution plans from a customer. ( http://technet.microsoft.com/en-gb/library/cc966425.aspx ) Execution contexts for parallel plans are not cached. A necessary condition for SQL Server to compile a parallel query plan is that the minimum of the number of processors that have survived the processor affinity mask and the value of the "max degree of parallelism" server-wide option (possibly set using the "sp_configure" stored procedure) is...(read more)

Conor vs. SQLBits 2011 Brighton

2011-04-11T12:28:59Z

Here is the slide deck from my presentation at SQLBits Friday in Brighton file I had a great time at SQLBits, even though I had to leave early. Thank you to everyone who attended the talk and to those who asked questions and gave me feedback. It is always wonderful to talk to our customers and we very much value your input so that we can improve the product in the future. The slide deck has all the examples and you should be able to go try out all of the things I discussed in the...(read more)

Conor vs. SQLBits (United Kingdom)

2011-04-03T23:47:06Z

I will be in Brighton, England this upcoming Friday where I will be speaking at the SQLBits conference. I plan to give a talk that goes deep into how many SQL Server processes Update queries (Insert, Update, Delete). So, if you’ve ever wanted to learn about why the plan shapes look like they do, this is the talk for you. I am excited to get back to England, though I am watching the volcanoes in Iceland more warily this time :). Hope to see you there! Conor...(read more)

Conor vs. Map-Reduce vs. Databases

2011-02-11T21:06:07Z

I found this paper interesting, so you might too. It covers some of the differences between parallel database systems and map-reduce style computation: http://database.cs.brown.edu/papers/stonebraker-cacm2010.pdf Happy Querying! Conor...(read more)

Conor vs. Views/Tables

2011-01-31T16:22:16Z

I received an email from a reader this weekend that has a lot of different questions, but one of the main questions related to the difference between views and base tables in SQL Server’s plan generation process. ie if I have a view “select * from table” vs. a table “table”, does the optimizer treat these differently? Before I answer, I’ll clarify that SQL Server changes some of its internals each release and that there is no single answer that will completely satisfy here. This is a...(read more)

Conor vs. How I got into computers…

2011-01-02T02:02:52Z

I thought I’d do a small post about some of the reasons I got into computers since I’ve randomly had a few opportunities to revisit them. Tron 2 came out recently, reminding me of the original movie. Additionally, I went home for the holidays and found some of the early books I used when doing some of my early programming… (So, if you don’t care about these, you can skip this post and I’ll get back to database stuff next post :)). TRON: This was one of the first movies I saw “in the theater...(read more)

Conor vs. more SARGable predicates

2010-12-18T16:51:14Z

I got another question in the mail this week from another reader: Hi Conor Thanks for an interesting blog. Maybe you have the answer to this question: Why I SQL Server having performance problems with these types of expressions: ------------------------------------------------------------------------------------------ DECLARE TABLE dbo.Archive( LastName varchar(50), Reference INT, SocialSecurityNumer VARCHAR(10) ) ----------------- DECLARE dbo.MySearch @Reference INT, @SSN VARCHAR(10) AS BEGIN...(read more)

Conor vs. Join Search Space

2010-12-18T16:18:47Z

I received a question this week from a reader about how SQL Server determines the join order if there are more than 6 joins in a query. There are a lot of details about how SQL Server’s optimizer works here, and some of them are beyond the level of detail that I can/will share publicly. It is not really possible to search all possible join orders. If you consider a set of tables A, B, C, … that are joined together using inner joins, there are different possible orders that could come out: ABC...(read more)

Conor vs. Row Growth in Read Committed Snapshot Isolation

2010-11-16T19:23:59Z

At PASS last week, I was asked about fragmentation caused by RCSI when a row is first converted to use RCSI. I talked to some people in the team and found that one of my colleagues had already written a nice blog entry about it. http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/overhead-of-row-versioning.aspx?wa=wsignin1.0 Basically, the overhead is taken once and you can do index rebuild to complete the process if desired. RCSI is still a pretty cool feature and I think people...(read more)

Conor vs. PASS (Slide Decks)

2010-11-15T17:32:17Z

I had a great time at PASS last week and got to talk to a lot of wonderful customers- thank you! As promised, here are the decks: Update Talk Performance Troubleshooting Talk Happy Querying! Conor...(read more)

Conor vs. PASS

2010-11-08T15:35:10Z

It’s one of the most exciting weeks of the year to be a SQL Server junkie – PASS is this week in Seattle! I’m giving a talk on the many details of Update queries. It will cover the various operators and how they work, why the plan shapes look the way they do, where things like locks get taken in the plan, etc. Implicit in this is that you can get a sense for how to tell when the system is doing the right thing/what you want and how to impact it, as updates are heavily influenced by schema...(read more)

Conor vs. PASS (November in Seattle)

2010-09-29T02:08:42Z

A note to let you know that I will be at PASS this year and plan to give two talks (one in collaboration with a colleague of mine on the QP). One will be a 500 level talk on how Update queries work – lots and lots of gory details. The other will be a 400 level talk (this is the collaboration talk) on how the QP team troubleshoots issues we see. So, lots of detailed techniques we use when evaluating problematic systems (things that you can do as well). So, if you are considering going...(read more)

Conor vs. Optional Parameters Redux – LIKE %

2010-09-29T02:05:47Z

A question from a reader (thank you, Price, for sending it in). This is a follow up to my prior post on Optional parameters http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/22/conor-vs-optional-parameters.aspx . The question is whether using col LIKE @p where @p is ‘%’ in the case of no parameter is an acceptable workaround to the problems listed in the original post (basically index matching). The original formulation (@p is null or col = @p) was bad because it generally precluded...(read more)

Conor vs. Updates – Talk (for those of you in Austin)

2010-08-16T20:03:45Z

I am giving a talk tomorrow (Tuesday) night at the Austin PASS group on how Update Queries are processed/optimized in SQL Server. The talk will be at the Microsoft office in Austin (which recently moved). More details can be found at the CACTUS web page . (I don’t know if they have my talk announcement up there yet, but hopefully it will be there soon) (I believe that I will be giving a longer version of this talk at the national PASS conference in Seattle in November, for those of you...(read more)