Adam Machanic
SQL Down Under Show 60 - Guest: Adam Machanic - Published: 28 Jun 2013
SDU Show 60 features SQL Server MVP Adam Machanic discussing SQL Server parallelism and futures including SQL Server 2014.
Details About Our Guest
Adam Machanic is a database-focused software engineer, writer, speaker and SQL Server MVP based in Boston, Massachusetts.
Show Notes And Links is here: (
Show Transcript
Greg Low: Introducing Show 60 with guest Adam Machanic.
Welcome, our guest today is Adam Machanic. Adam is a SQL Server MVP based in Boston in the USA, welcome Adam!
Adam Mechanic: Thanks Greg.
Greg Low: Listen we have had Adam on the show and I will put in the show notes a link to the earlier show where we had Adam there so it had details of his background and how he came to be involved with the product but what I will get you to do Adam is just tell us what things you have been primarily working on over the last couple of years?
Adam Mechanic: So I have started kind of specialising a bit in financial services. I have been doing data warehousing almost to the exclusion of all else well to the exclusion of OLTP I guess, for the past seven years or so. I don’t really touch OLTP anymore. Basically I have been working on data warehouses and started really focusing on financial services data warehouses. I decided that maybe be interesting to be not just a SQL Server expert but also have a little bit of domain expertise.
Greg Low: So get a bit of domain knowledge as well. What has made you end up doing more data warehouse work rather than OLTP work?
Adam Mechanic: I have always really enjoyed performance tuning and there is nothing to me more dissatisfying than to take a 12 hour query and making it run in 30 seconds. I get to do that all day long with data warehouses, on that OLTP side or the performance tuning is kind of micro tuning and concurrency management and things like that, there is not really any big wins. You don’t get to take a huge lumbering query and fight with that. And that’s what I really, really love to do on the data warehouse side.
Greg Low: I find in the OLTP side in fact the biggest gains I tend to get is more working with getting people to change the application or looking at the bigger picture. In fact I was at a site yesterday, I see a lot of people doing performance tuning and they are sort of like how do I get this little query to run 10% or 50% faster but the issue is more like why you are doing that whole process in the first place. Those sorts of discussions require a higher level discussion but for me that is usually where the biggest benefits come from.
Adam Mechanic: Right, right, and it’s fun to put a big hardware, big sets of data, really kind push systems to their limits that’s what I really enjoy doing. So it’s been fun!
Greg Low: So it’s interesting, I notice that a lot of the materials or sessions you have been doing over the last year or so you have mentioned have been around parallelism. I suppose in data warehouses, you have more opportunity to take advantage of that. I am thinking in most OLTP systems I work on, while the queries do tend to be parallelised fairly well. Basically there is a natural parallelism with large number of users using the system them anyway.
Adam Mechanic: On OLTP systems?
Greg Low: Yes.
Adam Mechanic: You broke a little bit. I guess I wouldn’t refer to that is parallelism.
Greg Low: No I suppose, I am just getting it from the point of view of the machine, it is not like you have got thread sitting there idle and so on. There is always something else for them to be doing all the time but in a lot of the data warehouse we do tend to get these sorts of larger queries run for long periods of time.
Adam Mechanic: Right, right so I think the whole point parallelism to take a large set of data, break it up into lots of smaller chunks and make it run faster via lots and lots of cores. It is a pretty natural fit because most data warehouse servers are fairly beefy. They have big disk systems that can handle lots and lots of concurrent requests, current I/O requests, they have lots of CPU power, lots of memory and they don’t have a lot of queries running at a given time. Each query can afford to use a decent amount the resources available.
Greg Low: Yes exactly, yes I think that’s a point I was getting at, in most of the OLTP systems I see there is just so many things going on at once. That it is just not like the system is sitting there idle and just using one thread or something to execute. From a point of view of one user it might be but overall the system is kind busy. In the data warehouse one invariably there are not huge number of queries going on but they are often very, very heavy queries.
Adam Mechanic: Yes absolutely.
Greg Low: What does that mean in terms of disk subsystems because again a lot of these systems tend to be I/O bound and it is great to break it up into a whole lot of things going on at once but I suppose the first starting point is the I/O subsystem has to be able to deal with it that?
Adam Mechanic: I don’t really agree with you.
Greg Low: Ah okay good.
Adam Mechanic: When a lot of the systems are still I/O bound, that is not what I am seeing.
Greg Low: Interesting.
Adam Mechanic: I think that disk systems in general have come a long, long way in the past 5 maybe 10 years, I would say more like 5 years than 10 years. Disks have gotten a lot faster, disks have gotten a lot cheaper, disk systems including high-end SANS have gotten a lot cheaper and have a lot more cache than that have before. We have these on-board SSD products now that we never had before, those have now started finally to come down in price a bit toward fairly realistic. Memory has gotten a lot cheaper, we have a lot more of it. It is now a lot more common to see boxes, with up to a terabyte of RAM. Even 7-8 years ago a terabyte of RAM would of been unimaginable, today it is still a lot of RAM but you don’t really raise an eyebrow when you hear about it.
Greg Low: No that is really interesting too because one of the white papers I was a reviewer on a little while ago, there was a hardware sizing guide for the tabular data model in Analysis Services. In fact it was interesting that one of the questions that they raised, was would it be unreasonable to suggest a terabyte of memory as a starting point?
Adam Mechanic: As a starting point, see there you go.
Greg Low: So I thought that was kind of interesting.
Adam Mechanic: I know a lot of people don’t have a terabyte, but I don’t think it is a starting point but I don’t think it is nearly out of reach any longer.
Greg Low: Yes.
Adam Mechanic: I recently priced machines which were fairly large, I won’t give it the manufacture. They had 80 cores, 160 threads and a terabyte of RAM and they were less than $100,000 to us which is not a lot for that kind of asset.
Greg Low: Yes for that level performance, yes I must admit most of the machines I still see tend to still be I/O bound, so it is interesting that you are getting to work with those.
Adam Mechanic: That you were working more in OLTP right?
Greg Low: Yes indeed, yes that the case but even so it is interesting that the number of memory sockets for a start is increasing, so it even the common servers I am seeing around the place tend to at least now have 24 memory sockets and so on. Again that is getting up over time, so I think obviously increasingly the direction of all of these products is towards heavily use of memory anyway.
Adam Mechanic: Absolutely, absolutely, so that is kind of where things are headed anyway. I mean they just announced that I am allowed to talk about it now, in the next version of SQL Server, it is kind of like Oracle actually. They have this feature called flash cache, do you remember that one?
Greg Low: Yes indeed.
Adam Mechanic: What you can do is take a on-board SSD and tell Oracle, I want you to use this on the server as kind of a local cache for the buffer pool. A lot of people have been clamouring for that in SQL Server for quite a while and they are finally introducing it. I do know what they will are going to call this feature but it is kind of the same idea, in SQL Server 2014.
Greg Low: Yes.
Adam Mechanic: That should be a really good way for people to also take advantage all of these new technologies and un IO bind their servers.
Greg Low: Yes actually, today is the day that product guides and things have finally been published for CTP 1 for SQL server 2014. I am sure those sorts of names and things will appear in that, if people haven’t had a look at that, it would probably be good to pop up to the site and have a look at the product guides for those. Again I will put the links to the show notes for the product guides.
Adam Mechanic: But this CTP is not out yet right?
Greg Low: Not quite yet, but I think people should expect to see that mighty soon.
Adam Mechanic: All right I will look forward to that myself. I got to play with that a little bit at the Tech Ed show. Tech Ed North America that was 2 weeks ago and got to play with this CTP there on a VM, very very slow VM but it was kind of call to try out some of the new features. The clustered column stores is something I am fairly interested in.
Greg Low: I suppose maybe given the fact that this is announced now we should just momentarily divert, what are the things that you are most looking forward to in this version?
Adam Mechanic: Again the clustered column store for me is the thing that I think has the most potential to be a game changer.
Greg Low: Yes I tend to agree, yes because we have the column store index introduced in the previous version and for many people would not have used that as yet. The key thing was that it added a non-clustered index in a highly compressed form but the biggest issue was the table became read-only. I find that while the column store index is in place, I find that loading that is still possible but it requires a fair bit of partitioning, switching in an outer partition and so on. It is kind and messy, where it sounds like the upcoming version should just simply eliminate that plus the fact that we don’t have to store the original copy of the data in the uncompressed form either.
Adam Mechanic: While yes, the non-clustered column store that is in SQL Server 2012 for me the biggest limitation wasn’t the fact that it wasn’t writable but more the fact it was very hard to get used in the query plan and this thing could batch mode.
Greg Low: Yes.
Adam Mechanic: And all the performance you were going to get out of it, performance gains come from this batch mode working and getting it to use that batch mode in SQL Server 2012 was very, very difficult, especially if you had some uncompressed data alongside. Supposedly in 2014 it will become much easier.
Greg Low: To do that. Actually that is another one I will put a link to in the show notes, is there I think from the SQL Cat team but there is one White Paper that sort of describes when using the existing column store indexes that the query patterns that lend themselves to batch mode and not.
Adam Mechanic: Right so I played with it for about 2 hours at Tech Ed on this VM and it was interesting, it compressed really well, so that was cool.
Greg Low: Yes.
Adam Mechanic: I did speed up certain queries that I ran but I was very disappointed in a few things I have to be honest. Table for example can have no foreign keys.
Greg Low: Yes.
Adam Mechanic: If it has the clustered column store index, it can no triggers. I am not sure about check constraints but I don’t believe you can have those either. Basically data integrity forget about it.
Greg Low: Yes.
Adam Mechanic: No primary key either because the only index you can have on the table is the clustered column store index. So it is basically like you can have a fast and bad data but potentially. I like to tell people that, if you don’t have constraints you will violate the constraints you are missing.
Greg Low: Yes indeed, I couldn’t agree more in fact in the case of foreign key constraints it is an interesting discussion as to whether data warehouses should have the more not. Again even the site I went to yesterday, they just said we don’t have them and I said why? They said well, performance. I always say have you tried it is the first question. What I find most sites, they might in really bigger situations, there might be one sort of scenario where you can’t actually have 1 or 2 but you don’t need to have them across the board. A lot of the things that people argue, they say I hear the app does that alright hear you know all these sorts of words all the system it already came from had integrity and so on but yet they never think about what if there is ETL process has a bug. Every site I go into when they say, it will be right and it doesn’t need to be there and when I check the data it is usually wrong.
Adam Mechanic: Yes every single time I’ve seen a missing constraint, every single time of my entire career there is data in there that is not supposed to be there, every single time. It is like, it finds a way in and so my role is trust no one and always put constraints in. It doesn’t hurt performance that much, I would much rather have correct data and take a small performance hit. If there is even with foreign keys, they can actually benefit performance too.
Greg Low: Yes, exactly.
Adam Mechanic: It can help the query optimiser do a better job, so it is really nonsensical for these people to not include those.
Greg Low: Yes every time I then uncover bad data, I was here the story about, that’s right we had that bug a couple weeks ago. There is always a story but invariably it is wrong.
Adam Mechanic: Yes or I’ll will see a lot of code every time there is not enough constraints, kind of code smells, or are a sniff I guess. Will be a lot of TOP 1s in sub queries and a lot of SELECT DISTINCT used all over the code.
Greg Low: Yes, yes.
Adam Mechanic: Yes that’s how you can kind of tell you have a problem.
Greg Low: Yes no that’s good, so yes they are expecting a value to be there but occasionally there are multiple is that having to do a SELECT DISTINCT or something.
Adam Mechanic: Or a TOP 1
Greg Low: Yes or a TOP 1, yes let me find the first one that matches this thing that should only be only one indeed. No that’s good so the clustered column store index you are looking forward to, have you played around with them at all? Have you got a feeling for the level of compression you are seeing?
Adam Mechanic: Again I have only played around with it for 2 hours at Tech Ed on a really slow VM, so I didn’t get to do a whole variety of things but I will just say it look pretty good. I don’t know the number but I was pleased. I can say that I have messed with the non-clustered column store and I think I was seeing like 80% on some datasets.
Greg Low: Yes, that is pretty much my thinking. It is very very much the same engine as in the tabular data model and pretty much all the time we tend to see somewhere between 7 and 10 times compression is quite routine but I do see scenarios where it literally ends up being one hundredth or four hundredth of the size which is bizarre. It certainly is an overwhelmingly interesting story when it does do the right thing.
Adam Mechanic: So that would DI mine you is it as well.
Greg Low: Oh yes, absolutely so.
Adam Mechanic: I don’t know whether it will work for your OLTP scenarios very well but in a datawarehouse it will definitely IO bind you and if you are in batch mode, it will reduce your CPU time as well so it is pretty cool technology. The only problem is batch mode doesn’t cover, even in 2014 it is not going to cover fairly large surface area such as windowing functions won’t be covered in batch mode.
Greg Low: Yes.
Adam Mechanic: Which is one of the main things that I test when I was at Tech Ed and had a couple of hours play and I found in that case, the normal indexes still worked better.
Greg Low: One of the things I look at is whether there are any data type restrictions because I am guessing there would be as well.
Adam Mechanic: I believe that I read that XML, CLR, UDTs, I assume that includes Geography, Geometry and Hierarchy ID and BLOB types aren’t supported.
Greg Low: Yes that will be another thing that is that would need to be checked to see whether those data types are something that you can work with or not as well. I know that certainly in the regional Power Pivot and Tabular models it wasn’t just only the data types, it was certain precisions of certain data types as well. For example they didn’t support initially the entire range of decimals for example, again I don’t know if that has changed as yet or not.
Adam Mechanic: Yes that has actually, that is the case I should say in SQL Server 2012 and all those restrictions have been lifted in 2014.
Greg Low: That’s great!
Adam Mechanic: Although I’m using float most of the time of these days. I am pretty happy with just taking a little tiny precision hit and save me a lot of space as is with the data types. I’m not too concerned with decimals these days.
Greg Low: Interesting.
Adam Mechanic: I used to be a big decimal fan and over time I kind of became a float convert.
Greg Low: Ah that’s something I’d like to hear your thoughts on because I must admit I am still very decimal fan, mostly because like columns of numbers to add up properly and so on and so on. So tell me why the move to float, is it just size?
Adam Mechanic: It has to do with more than just data size. First of all I found that float was in as bad as a lot of people make out to be. I don’t know whether I would use it in an accounting system, where everything has to add up to the exact penny every single time.
Greg Low: That’s my point nearly every system I work on tends to be accounting and people with floats are the ones with columns of numbers that don’t add up.
Adam Mechanic: Even if you have float, I find that most of the rounding errors are maybe 6, 7, 8 digit’s after the decimal.
Greg Low: Oh yes but people just don’t do the rounding, that’s the problem. I mean a simple example 10 cents can’t be represented exactly so you end up having to round every single thing all morph to a different data type in the other app. In most of the systems I work in actually float is a bit scary. What I do see in a lot of the large data warehouses, is quite a lot of people using the money data type which again is not one I typically use but they are doing that simply because they want the exact value and they don’t want the size of the decimal.
Adam Mechanic: I always thought that money was just a wrapper over decimal.
Greg Low: Yes no they were saying the storage for that, I think if you look at up is actually sort of smaller and so some of the data warehousing guys were recommending using money instead.
Adam Mechanic: That is interesting, the other issue I found the decimal is if you get very high scale, well very high precision over high scale and you do operations and this is something I have just learnt a few years ago. Let’s say if you multiply to decimals and they are each precision of 9, the output will have a precision of 18.
Greg Low: Yes indeed.
Adam Mechanic: I was working with the system where we were storing very exact exchange rates and U.S.-Japan exchange rates at some point went down to a really large number, it was like 0.0000000000 something and we were getting a problem where we were doing some simple conversions and winding up with a 0 where we should of had a real number. As soon as we switch to float, that problem went away and it is because of that decimal issue.
Greg Low: It reminds me, one of the things I love showing people in classes is I have a whole lot of UI things where things have gone wrong with numeric values. One of my favourites was a competition where they had X box as a surprise, and is said is you had a 0% chance of winning. Obviously they tested it with a small number of people in the thing and it said 10 or something and once they had more than a couple hundred people they had shown the nearest integer and it was 0.
Adam Mechanic: Oh okay this is I just thought they wrote the UI for me.
Greg Low: Oh it was lovely, great competition, thanks! It is true, but yes is not really appealing. Look also you are mentioning the hecaton story all the in memory table storage story and so your early take on that at this point?
Adam Mechanic: I haven’t had a chance to play with it extensively yet. I know it’s really for OLTP, for me my biggest interests in that, once I get my hands on that CTP what I am going to be doing with this is first and foremost is seeing whether I can use it to replace Temp DB. You know Temp DB is minimally logged in most cases but it is not non-logged so the idea of a staging area in the data warehouse being completely unlocked is very interesting to me. Being able to completely eliminate that overhead, so that is my hope for it but I really don’t know if it’s going to work or not.
Greg Low: Yes, that is fascinating thought that’s good. But so anyway, back towards parallelism. What are the main thing she have been learning in terms of parallelism?
Adam Mechanic: Actually I teach a whole day seminar on this but
Greg Low: Yes and in fact a quick plug yes you are doing that at the SQL Rally events in Europe couple of those, is it later in the year? Or is that a different session?
Adam Mechanic: I am not actually doing that seminar, I am doing another seminar there.
Greg Low: Okay
Adam Mechanic: On SQL server monitoring, come to the SQL Rally in Amsterdam and Stockholm in November.
Greg Low: So people will see you there that is great. So anyway so maybe the Reader’s Digest version of what you have been finding with parallelism?
Adam Mechanic: Basically you can make your queries many times faster but the query optimiser will not do for you and is a matter of fact the query optimiser more often than not on complex queries at least. For simple query your fine as soon as you get into a little bit of logic, a little aggregation, a little bit of windowing, you are using row number maybe you are using lag and lead or something like that in SQL Server 2012. As soon as you get into that stuff, the optimiser comes up with some very questionable plans and if you just allow the optimiser to manage your parallelism for you without taking control. I discovered you are really doing yourself a huge disservice. As it turns out unfortunately, the design of some of the parallelism. I think most of these components were designed all the way back to SQL Server 7. Unfortunately the design is not the best in the world, so parallelism is all based around these iterators called exchanges.
If you look at parallel query plan, you will see all these iterators show up in your query plan and are called parallelism. There is like gather, distribute and repartition, internally those actually called exchanges and the way they work is basically data flows in to one side of exchange and the exchange collects the data into data structures called packets. The packets are pushed across the exchange to the other side where threads on the other side, one or more threads depending on the type of exchange will pick up the packets and continue processing. Unfortunately I don’t know the complete internals having never seen the code of the exchanges I do know what they do, I don’t know whether inefficiencies lie. Unfortunately these are very, very inefficient doing their job and some parallel plans.
If you run SELECT * FROM sys.dm_exec_requests or sys.processes, remember back in SQL Server 2000 when we only had sys.processes you would run it. SELECT * FROM sys.processes WHERE SPID = 65 and 150 rows come up. What is happening there, is you have 150 tasks allocated to your plan and it is not because your plan is using 150 active threads, it is because you have basically each exchange iterator in your plan represents a parallel zone and each parallel zone has its own sets of threads. So you would get as many threads as zones x degree of parallelism. That is how that maths works and each time you have one of those exchange iterators, the data has to flow through that iterator into the packet structure and out of that packets structure again and it gets incredibly inefficient. What I have found that if you want to improve the performance of parallelism you have to learn how and why the query optimiser places those exchange iterators and get it to put few of them into the plan.
Greg Low: Excellent! So otherwise this is basically, sounds to me like you are describing, one of the old discussions about the optimal number of threads in any application used to 1 but that is not very good for concurrency. Otherwise the more you start doing, the more time you spend switching around between threads and managing threads and so on. Is it somehow related to that, you just simply so the overhead has become too high?
Adam Mechanic: I think you are referring to context switching which is when the operating system all SQL Server itself.
Greg Low: Sure I am just thinking in terms of the same thing. If there are scenario where the degree of management of it starts to overwhelm the benefits from it that’s all.
Adam Mechanic: Yes they’re probably could be, but that’s not really the key point for me. I think that when you get to that point the answer is run fewer queries or by more CPUs.
Greg Low: Okay.
Adam Mechanic: Versus this is like, if you have an empty system and you run this query it is going to go, 4, 8, 10 times slower than insured. Because all the data is going in and out of this inefficient data structures, this really has nothing to do with thread management and everything to do with data flow management.
Greg Low: Yes, yes so it is still a management overhead though in what you are describing.
Adam Mechanic: Somewhat.
Greg Low: Interesting.
Adam Mechanic: But what it comes down to is really I found certain query patterns that you can use that will cause the query optimiser to change the shape of the plan into something that will perform much much better. It is quite interesting, these are transformations that the query optimiser could do itself it just doesn’t know how. Which is kind of sad be that as it may.
Greg Low: Interesting, so what types of things are you having to doing your query to achieve that?
Adam Mechanic: Effectively, any time that you have a hash or sort or aggregation which is kind of grouped globally across the query or TOP any of these things can cause the query to either require what is called a repartition where all the rows need to be realigned on whatever threads they are on, or it can cause a serial zone to become necessary. Any time those things are necessary, that is kind we have this problem.
Greg Low: So a serial zone being an area of the plan where things have to happen sequentially you suggest.
Adam Mechanic: Well they have to happen on one thread.
Greg Low: So on is single threaded.
Adam Mechanic: Yes on a single thread, an example of that would be SELECT TOP 1000 * FROM table. The query processor can go back to the table, back to the index and can do a parallel scan, let’s say you have DOB of 8 and each thread starts reading rows off the table. While the question becomes which thread has the top 1000 rows? The answer is I don’t know, the solution is the only way to solve the problem is to have each thread read maybe 1000 rows, the top 1000 rows that on that thread then put them all together to a collection of 8000 rows on a single thread and have a single thread take the top 1000 off the 8000. That is an example of where a serial zone is needed.
If you think of an aggregation, SELECT sum(UnitsSold) FROM table, same thing each thread can start reading and do something called partial aggregation where each thread kind of aggregates what data you has and merges it altogether and the data is re-aggregated into one final answer. Anyway once you understand why those things occur, you can start to write your plans in such a way that they don’t have to occur. For example consider SELECT ProductID, sum(UnitsSold) FROM table GROUP BY ProductId.
Greg Low: Yes.
Adam Mechanic: Now we have one group are product, and by default the way this will be solved by query optimiser is depending on how many rows you have on indexes, so let’s just kind pretend. The default way this can be solved, is that the data can be read out of the table and the query processor users this thing in parallel plans called scan and is parallel scan reads data page at a time. It is fed out to each thread a full page at a time and depending on your index, each page might have data for you know the same product ID or different product ID it might be all mixed up across the threads. So each thread will have some product IDs, maybe some products will be spread across different threads and so on. Each thread will start doing this partial aggregation thread, and would take all units sold, they have locally and produce maybe one group are product that each thread has. Then all the threads will have to be merged together and the data kind of re-sorted and may be some more threads will come along and each thread now that the data has been re-sorted will have the partially aggregated versions. Maybe the 1st thread will have the data from 1, 2, 3 and the 2nd thread will have all the data 4,5,6 and so on. That way everything will come together and you will get the right answer. The problem with that is the sorting are moving of data and what I found is a solution to solve this problem. Is to introduce another operator into the plan which is an outer reference in this case it would be the products table itself.
Greg Low: Yes.
Adam Mechanic: What I call that is I call that driver table, so you start your query from the products table and then you make use of the APPLY operator which was added in SQL Server 2005.
Greg Low: 2005, we had OUTER APPLY and CROSS APPLY.
Adam Mechanic: What apply let you do is create a correlated table expression, so you SELECT FROM Product APPLY, and in your APPLY you SELECT FROM your Sales table WHERE Sales.ProductId = Product.ProductId. Now logically the sales table will be hit once per outer product ID, now the query optimiser can optimise that into a hash match or nested loop or a merge but what we want is a nested loop every time. The reason we want that is because a nested loop each thread will get one product at a time it would take the product and it will go seek into the in a table and he would do the work just for that product and then the data would then just have to be merged at the end for output. That doesn’t have to be.
Greg Low: Once has to totals.
Adam Mechanic: That is kind of how I’ve been doing this work. It really flies in the face of everything that most books say you should do with regard to query writing. What I am creating is instead of a single scan and people who have been optimising SQL for years, to optimise for a single sequential big scans of tables. This goes completely against that and instead of optimising for big scans, it is optimising for lots and lots of little seeks but it works quite well and has a lot of interesting other characteristics when you get into maths.
The maths of query processing as it were, for example sort, you know the big O, kind of time for sort. Big O notation, big O is the way that computer scientists describe the scalability of algorithms and big O so you can describe the algorithms big O which is kind of, it’s algorithmic scale in terms of time. Big O represents basically given a number of elements which is generally referred to as N, how well worth algorithms scale mathematically. So for example a table scan is a big O of N, which means as you take N, you double it, the algorithm will take twice as long.
Greg Low: Yes.
Adam Mechanic: Yes that make sense because a table scan if you read 100 rows it would take N times, if you read 200 rows it would be N x 2. If you read 300 rows it will be N x 3 and so on. So is sort there were sort algorithms, bubble sort take big O of N squared time. So if you go from 1000 rows to a million rows you are now taking a very long coffee break. The best sort algorithms which SQL uses a couple of take big O of N x log N which means if you go from 1000 rows to a million rows is still is going to be slower naturally it is not going to be.
Greg Low: It is not going to be exponentially.
Adam Mechanic: Not going to be exponentially but going to be extra linearly slower. If you go from 1000 to 10,000, it is actually more than 10 times slower. So mathematically that if we can reduce our sorts into smaller chunks rather than doing them in big monolithic pieces, we can actually take advantage of maths and makes our sorts faster. So if you are doing a lot of windowing functions and things like that where you are doing sorts all over your query plan and you break it up using some sort of nested loops like I am describing. You can hash you get much faster and reduced their about CPU will reduce the amount of overhead, reduce the amount of temp DB spills due to the fact that big monolithic sorts often won’t fit in memory.
Greg Low: Yes they usually have to spill out into temp DB.
Adam Mechanic: Not if you break them up into lots of small pieces.
Greg Low: Indeed.
Adam Mechanic: Now they don’t overspill so it is really really interesting once you get into this technique how well it works and how you really can start optimising queries wary was impossible before.
Greg Low: Fascinating, listen it does lead me to wonder due you almost think that the engine itself should have 2 basic tunings for optimisation for particular types of service. You would be able to say look I want to this style sort of big level controls in terms of optimise for this type a work, or optimise for this type a work or do you think many systems end up doing both?
Adam Mechanic: I think that in general there never is enough knobs, so give me more knobs and I will be a very happy man. That said if there are too many knobs, I wouldn’t have a job.
Greg Low: Indeed, indeed.
Adam Mechanic: Someone needs to tune those queries but in general seriously I think SQL Server again we will see how the clustered column store does and the kind of things that are coming but in general I think they have not paid enough attention in the pass SQL releases to query optimisation and kind of keeping it up to date with the latest advances in technology. We have a lot more cores than we had even in SQL server 2005, that wasn’t too long ago right? A few years ago, but that is not really a long time and if you think about that timeframe I think I was working around the time that came out. I was working with a company and we had 16 or 32 cores or something like that and that was considered to be a really big server.
Greg Low: Yes up I think some of the larger mega dome system I saw at the time, where 32, 64 bit processors and I think 2 terabytes of memory at the time, that was the absolute top end of the.
Adam Mechanic: That would have been like $1 million.
Greg Low: Yes, that is incredibly high but I mean that was like the absolute top end of any thing you can pretty much by at the time.
Adam Mechanic: Okay that’s not even really realistic, the vast majority of people are running on 8 cores.
Greg Low: Absolutely, in fact again most sites I go into probably for in 2005.
Adam Mechanic: Right now you get 4 to 8 cores on a single chip in your desktop. My desktop machine that I’m setting up right now is 24 cores. I mean that is ridiculous, but the point is SQL server is self is a great optimizer, hasn’t changed is logic in any way to take advantage of all these new cores, the memory I was talking about earlier been cheaper. The disk systems being faster, nothing has changed in the query optimizer, it actually still uses the same constants, so the way the query optimiser works is everything is cost based. Those costs is all based on constants, and is basically some constant x estimated number of rows x estimated row size based on which operation kind of has taken place. So a scan of certain constants, seek of certain constants and so on and those constants haven’t really changed since SQL Server 7. SQL Server 7 was probably developed in 96, 97 because a real was released in 98.
Greg Low: That is exactly the timeframe.
Adam Mechanic: Say it if you think about it we are like 17 years of the same exact constants, the same exact math being used to do query optimisation. It is getting a little bit long in the tooth.
Greg Low: Actually one of the other fundamental design things is also the storage engine seems to always have had a clean separation from the query optimisation engine but that also means that when query optimisation is being performed it doesn’t understand for example whether data is compressed or not.
Adam Mechanic: Yes, that is true with the compression that is available in the SQL Server 2008.
Greg Low: 2008 yes.
Adam Mechanic: But that’s not true any more with the column store.
Greg Low: Column store is a different story but I just think it is another challenge if some of those fundamental changes could potentially occur and I think just as a design if you have got one thing laid above the other you just sort of wonder how much knowledge you have to have leaking between the two. Is that separation even sensible?
Adam Mechanic: Right, have you got it a lot good luck with compression, in general, in your projects?
Greg Low: Compression yes in fact if I think back to 2008 I thought the 2 best things in the box were compression and spatial. I would say almost without question. Number 1 compression by long way, whenever I’m talking to clients, they always seem to start with this assumption that compression means smaller and slower but that is just so not the case. Given the fact they have change the row format and potentially the page format. The idea that because again a lot of the sites I am working with tend to be I/O bound something that cuts that to half or a third is huge but even not just that the page formats change it means that these same buffers now the 2 or 3 times as many rows in memory. The argument used to be it was a few percent CPU per page, but the thing we found impracticality is that the number of pages has also gone down to half or third the number of pages. In fact what we are seeing in larger ones, just the CPU actually drops as well. So given the fact, the whole thing is smaller, it cuts the I/O down, buffers feet 2 to 3 times as many rows, in many cases the CPU goes down as well. To me that is just a no-brainer.
Adam Mechanic: Yes that’s what I’ve seen in some cases, I have actually seen the reverse happen as well so.
Greg Low: Interesting so what sorts of areas?
Adam Mechanic: Well you know data warehouses that aren’t I/O bound. I actually found a lot of the queries it works actually pretty well I have got up to 70% compression ratios especially on fact tables where there are lots and lots of repetition.
Greg Low: Are you predominantly using row or page compression?
Adam Mechanic: This was with page compression that I am referring to.
Greg Low: I was going to say the thing I found in general I haven’t found a system me yet that hasn’t benefited from row compression. I have got some tables are some sites where there is page compression and that is a different story.
Adam Mechanic: Right, so the page compression obviously gives much better compression ratios but in that case I saw several cases where we weren’t I/O bound and the queries ended up being twice as slow. We do get a considerable amount of CPU overhead, I agree with you on the Page compression especially if you have the right data types to really make it worthwhile. I think that the fact it works on nvarchar and compressed it down to the UCF8, I think that is really cool.
Greg Low: Yes, indeed so look overall, I find most of the sites I go in and we enable that the reaction we have is like wow why didn’t we do that before. That is almost invariably the reaction, the other thing that people don’t get is that when it is enabled and it doesn’t do anything immediately it is only when it thinks that it is going to avoid page splits that it goes off and compresses the page and so they do need to do something to cause it to rewrite the all the data in the table to really have any effect. Of course a lot of people had heaps so there was no option to rebuild until 2008 where you had the alter table rebuild at the same time.
Adam Mechanic: Right, don’t you have to rebuild the index to enable it?
Greg Low: Yes, yes you do that is the thing that you need to do something that rewrites all the data so that it goes back and reassess is all those pages. Rebuilding those indexes you absolutely had to do but prior to 2008 there wasn’t a way to rebuild a table. What I used to see is some people would go and add a clustered index to it and drop it again and that was one of the things they would do. It is not necessarily a great idea but one of the other reasons they wanted to do that was to remove forwarding pointers out of the table. Often if you have heaps that have been you in use for some time, when there are lots of updates operations going on, it always ends up with a bunch of forwarding pointers eventually. At least again alter table with rebuild removes all of those as well.
Adam Mechanic: Okay I haven’t seen too many heaps in the real world, so you seem to be getting some interesting projects.
Greg Low: Yes I think it is just a pretty wide variety of things that tend to.
Adam Mechanic: I am working with a vendor system right now. What they have done is created a non-clustered primary key on every single table.
Greg Low: Yes I see that all the time actually. It is a very common one I see.
Adam Mechanic: Really so what you think that comes from?
Greg Low: Sometimes it is simply the libraries that they are using to generate that, other times it will be something like a GUID primary key and they want to keep it separate somewhere else. But even there though, again the argument being it is much quicker to rebuild the index that just has the GUID than some other identifier than it is to rebuild the entire thing.
Adam Mechanic: Who cares how quick it is to rebuild the index when every single query is either scan or has rid lookups.
Greg Low: Yes, yes indeed, very much the case but the other thing that I find a lot of the sites where they have got that, they end up with tables that are messes in terms of the GUIDs but what I find is a lot of the time people still have this idea that the logical and physical models have to be the same. My personal preference on a lot of those is I end up with a single separate table that matches up the GUIDs to int and literally everything else is joined and done via int instead.
Adam Mechanic: To that one table though?
Greg Low: Oh yes, while it may be the one all you maybe not but the thing is that becomes a very small thing to look after and maintain but the idea that the ID externally has to be the same ID internally is just something I don’t necessarily buy into, if you have some layer of the extraction into the database.
Adam Mechanic: Yes I would agree with that as well. I think people may be concentrate a little too do much on the GUID thing.
Greg Low: But again that is changing story as time goes on.
Adam Mechanic: For me personally, in cases where I’ve seen a lot of GUIDs actually use that was in the past, more OLTP style applications. In those cases a lot of times fragmentation doesn’t matter, it matters somewhat but it doesn’t really matter a tremendous amount.
Greg Low: Yes I suppose it depends on the sort of the thing, right. If you talking about the site I was at yesterday, I mean the tables averaged be internal fragmentation was 50% but that means you are reading twice as many pages to get the same amount of data.
Adam Mechanic: Yes but if you only read in 1 or 2 pages per query as you are.
Greg Low: Sure, it depends on what they are doing on the system. In fact it is one of the things I often look at, the things like the index maintenance. At least the sorts of things that Ola Hallogren has in place are better than the ones from the box but one of the things none of these things never look at is how the table was actually ever used all the indexes used. I look at a lot of these things, as you say if all you ever doing is particular seeks or something on an index almost who cares.
Adam Mechanic: Right, right, so it is always interesting as I talk to a lot of DBAs who the first thing that they do when things slowdown is they go in start defragmenting ting everything and very rarely does it make much of a difference. Sometimes it does make a difference because it brings the pages into the buffer cache so they rerun the query.
Greg Low: And they think it has made a big difference.
Adam Mechanic: Kind of do that, I always remind them of DBCC drop clean buffers.
Greg Low: Or any of those when you are doing a lot of testing, Freeproccache, some of these things till they start getting plans in place.
Adam Mechanic: Recently I have got to the point where I am working with fairly large systems with a lot of memory. I have gotten to the point where realistically the working set is always in the buffer cache and one of these things that is kind of changing.
Greg Low: Yes.
Adam Mechanic: When I first started out, I would never test anything without dropping the buffer cache. I wanted to test everything against the cold read and I would assume everything is going to be a cold read. Now that is shifting at least for me, I am testing everything against a warm read. And that is really, my assumption now is that the cold read will be much less frequent and again this is due the fact that we have a lot of memory, a lot of SAN cache and the cold reads are just. It is still bad but it doesn’t really matter as much.
Greg Low: Agreed, as that amount of memory is increasing. That’s right it completely changes the thing you are trying to optimize.
Adam Mechanic: That is getting back to 2014 I think that is called flash cache at the risk of completely confusing anyone. The flash cache style thing is really going to change the way I think a lot of people do query tuning.
Greg Low: Yes it is interesting, again I think one of the common things that are probably look at obviously local SSD’s but more likely local fusion I/O boards, you know the boards that are sitting in the server itself but is using that is an extension of the cache.
Adam Mechanic: Right or violin memory.
Greg Low: Yes violin memory is another one again. What I noticed with most of those boards to from what I gather in most cases is the bus is the bottleneck in the case of most of those. I know they are certainly experimenting with versions where basically they take power off the motherboard but basically the boards plug into memory sockets instead. I think it is going to be very interesting when you get that style of memory sort of masquerading as real memory in the system in the away. I think that just gives you large amounts of something that is something that is slightly slower but persistent memory. Again that would lead to a different style of optimisation and possible outcomes.
Adam Mechanic: There are a lot of new memory technologies just over the horizon. Supposedly this magnetic RAM is going to be coming someday right? I do know if you have read about that much.
Greg Low: A few actually thing locally just about that the other day.
Adam Mechanic: They have talked about that for 10 years now, and I am kind of still waiting for that. Eventually one of these persistent RAM technologies is going to come along, assuming that it is cost-effective it will completely change everything. This discussion will not even make sense any more.
Greg Low: Yes exactly.
Adam Mechanic: That would be interesting when that day comes.
Greg Low: All good, in fact what is your overall thought on just SQL just being philosophical just as a whole in terms of direction?
Adam Mechanic: SQL or SQL Server, first of all?
Greg Low: I suppose both, let’s hear your thoughts on both.
Adam Mechanic: So I laugh a lot and the reason I say that SQL Server is I have been really kind of laughing at these no SQL products. These products all came out with a big flourish, for 4 or 5 years now I guess or even 3 years that they have been in the mainstream. We are going to do away with this terrible SQL thing and reintroduce this new methodology, Java-based way of doing everything. Low and behold we look at it now, every single one of these products is adding and SQL layer.
Greg Low: Yes.
Adam Mechanic: Someone learn somewhere SQL is a language and it is pretty decent language for what it is. Retrieving data, retrieving it in a nice declarative manner, a lot of people know it a lot of people good with it.
Greg Low: Yes.
Adam Mechanic: Business users can use that easily, business users can’t go code, some complex thing very easily. So looking at these no SQL products and thinking about does SQL have a future I think it definitely does. I don’t think it is going to go away, I will probably spend the rest my career working with some form of SQL or maybe it won’t be in SQL Server.
Getting into the SQL Server side of things, the optimiser is getting a little bit long in the tooth and I think there is a lot of external competition coming from these no SQL products as well. I think that the age of the monolithic DBMS is slowly coming to an end. It is not next year, it is not 5 years now, but it is definitely decreasing and it doesn’t make sense any more to pay huge, huge upfront licensing fees when there is really fantastic free products available. Or maybe they are not in free but pretty close to free, you can pay a little bit for support on top of that. All of these companies have consulting arms, so I am worried. I am cautiously worried I would say.
Greg Low: actually one of the things for me that is a big indicate on that is around developer productivity and I think one of the things that is really important is that they keep the development community thinking this is an efficient way to build things. Again one of the challenges with a lot of the other things, is they are getting rather high levels of productivity and one of the things that again if I look at 2014 one of the things to me that is missing is a developer productivity story. I mean I’m not seeing there are wonderful things going in there but I am not actually seeing things that go here is how I can write T-SQL a whole lot better.
Adam Mechanic: they gave us this thing called SSDT a while ago, I think I know for people who like it. Maybe you are one of them I don’t know.
Greg Low: Again they confuse the naming, so use SQL Server Data Tools is used were both the BI templates working inside Visual Studio. Those I am quite comfortable with although I don’t overly love the 2012 ones because they have removed the colour from everything. So for example in integration services, the difference between a task that is enabled and disabled is how dark the name of the task is. Things like that, I struggle with that as a colour scheme and so on. A lot of those things are problematic.
When also you talk about the things for building objects or for building databases that live in Visual Studio. I so struggle with that, in fact the biggest example, is that I still I would love to know if you know how, I still don’t know how to go in and just build a project where I is just build an assembly and split an assembly out the backend like I used to quite easily.
Adam Mechanic: No, I am actually still doing my SQL CLR development, actually I do a lot of heavy CLR development. I do it in Visual Studio 2010, which if you connected up to SQL Server 2012 it was thrown exception and say you are not allowed to do this. So the work around I have found, for this and I do, do it is you keep a local, this sucks but it works. You keep a local SQL Server 2008 instance and you pointed at that, then if you go win and you find the ms build manifest 12 that it uses internally, there is a line you can take out which is the line that actually deploys the code to the database. If you take that out, it will still run it will produce a script for you, then you just take the script run it on your 2012 instance. It is kind of 2 steps to do the deployment and it is a terrible pain but that is how of been doing my work for the last.
Greg Low: Yes just intrigues me, as you stay in Visual Studio 2008 I could start a project, create some objects, build an assembly, I could deploy from that if I want quite easily if I want. Just for someone working out how to do those projects in the new environment, I don’t find it a quick clean experience at all, in fact one of the things I would love to find is who is the best person to talk about that nowadays. I would love them do a session on hey this is how what we have in mind. I am thinking I must be missing something.
Adam Mechanic: As far as I know most people are no longer working on that team in Microsoft, so it is like pretty strange.
Greg Low: So that particular area is problematic. I do as I said, the other use of the names SQL Server data tools, I do appreciate the fact that they have, when they didn’t take a dependency on all Visual Studio 2012 that they now have shipped the templates for Integration Services, Reporting Services and Analysis Services to work in Visual Studio 2012. Even if I don’t like how they look, just having them available in that environment is just a huge step.
Adam Mechanic: I guess is just a little things except for the hipster design methodology that they are using it is just a bit annoying is in it? That is grey scale, that is what I call it.
Greg Low: Yes the whole thing is greyscale and all the menus are capitalized everything and so on. I am just not sure. I get the design, I see what they are trying to do there but I still look and go. I am mean colour to me, colour is just one of the senses that you can and to me it removes a whole of the potential information when you remove a whole lot of colour out of things.
Adam Mechanic: I guess everything is cyclical and eventually will come back. We really needed to return to the 1940s. I don’t know it is very odd.
Greg Low: Yes colour every where actually on a side note I did get to see the Great Gatsby on the weekend, the Baz Luhrmann film. I didn’t know whether I would like that, I am not a huge Baz Luhrmann fan, certainly I did actually enjoy it. It is just interesting the degree of colour, it is certainly almost over the top but it’s great. Did you get to see that at all?
Adam Mechanic: No, no, it is definitely on my list, over the top and very celebratory.
Greg Low: Yes that was the reviews I kept reading and that it was way over the top and things like that, so I was really expecting the worst but I have to say are really quite enjoyed it and it did take me back to reading that as a child.
Adam Mechanic: I have a 10 month old so I don’t get to go out to the theatre much these days.
Greg Low: Are indeed so listen now we are coming up to time where will people see you? You started to mention before SQL rallies, where will people come across you in the upcoming times?
Adam Mechanic: That is really use I don’t have anything scheduled, September SQL Rally which is summer time here or just kind of starting so I am kind of hoping to get a long quite summer. Do some work, I have a couple of projects that I just can’t announce yet, so watch this space or watch and I will hopefully have some very interesting things to share, but right now I can’t tell you what they are.
Greg Low: Ah that is great, we should mention that in particular Adam and Peter Debetta host and created that in the first place. People look for a generic feed for SQL Server related information, and my own blog and many, many other people are up on which I will put up a link to in the notes as well.
Adam Mechanic: Excellent, thanks so much!
Greg Low: So great, thank so very much Adam and we will talk again soon.
Adam Mechanic: Thanks, it has been fun.
Greg Low: Thanks.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement