Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Cloud Optimizing T-SQL: What You Need to Know if You Use SQL Database or Microsoft SQL Server 2012

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
[Cloud Optimizing T-SQL: What You Need to Know if You Use SQL Database or Microsoft SQL Server 2012] All right, the green light here on the podium turned itself on, indicating to me that the time has come to start the session. We'd like to welcome everybody to Cloud Optimized T-SQL: What You Need to Know if You Use SQL Database or Microsoft SQL Server in the cloud. This is looking at both our cloud offerings, not as much to the actual box. We had a good start into the second day, and I hope you enjoyed it so far. Let's get it started. So the agenda for this session—basically, I want to show you, just conceptually, what are the differences between the different offerings that we have for SQL Server. And this is somewhat background information to get your mind set up why we actually look into doing things slightly differently in the cloud. Then we're going to look at how to cloud-optimize your design, and this is on a slightly higher level, looking at what are the approaches you should take, what are things you should think about. And then we're going to look in a little more detail into connection reliability, which is one of the topics that we find customers hit a lot. We're going to talk about how to reduce roundtrips, because those are specifically costly in the clouds. And then towards the end, I want to have a few examples on how SQL Database actually supports you in doing caching, which is a thing you should definitely consider once you run in the clouds. All right, so today if you look at it, at what we offer, we essentially have three ways to run SQL Server. Depending on which one you choose, you get either shared resources or dedicated resources. You get high control or low control. So the first one that probably most of the people at some point come across is the SQL Server on a standalone installation that you have in your own datacenters, where you control the hardware. It comes at a higher cost, obviously, because you have to control the hardware, and you have to do it all by yourself. You manage the whole system yourself. You set up the high availability, the disaster recovery, and you are essentially responsible for doing the scaling. And scaling here is a scale-up approach. So if you reach your resource limits, what you do is you buy more cores or add more memory, stuff like that. So the SQL Server on premise is essentially like the raw iron. The first step of abstraction is to go to SQL Server in a VM. There you still have the full API, so the SQL Server itself feeds like the on-premise installation. The difference is that you don't have to manage the hardware any more. So it's one layer of abstraction that you put in place there. And you still can set up your own, or have to set up your own, high availability disaster recovery, and you would still have to do the scaling, obviously. The difference here in this virtualized environment is that you will reach certain limits in terms of hardware, which is simply what we provide. So here, instead of being able to scale up and just add more power to the machines, you have to rethink, and you have to start to scale out, which is just adding another SQL Server into the mix. So overall, it's still viewed somewhat the same, but it starts to have some limitations. And then the last one that we offer is the SQL Database, which is our platform as a service offering. And this is a fully virtualized database. So we have the notion of a virtual server, but really what you get is a database. The interesting thing here is we manage this thing completely for you. We take care of high availability, fault tolerance— We make it very easy for you to get started with the database. You can just go there and say CREATE DATABASE, and what we do is we're going to set up a highly available database for you, with at least three replicas. These are all things that if you would do it by yourself, there will be quite some work going into it. But, given that we take care of all of this, you have relatively low control. And you run on a set of shared resources. So, just looking at this, and— we don't really want, or I, especially, don't really want to tell you like, you have to do this or you have to do that. It really depends on what kind of application you're running and what kind of cost is associated with running this application. So if you think about—for example, you have a third-party application, where you don't have any control over the source code. But this just needs huge resources. Well then, SQL Database might not work for you, because you can't get a lot of resources. You would have to change the design of the application to use multiple databases. But if you don't own the application and if you don't own the source code, you can't do that. So there the raw iron approach is probably the one that you have to go with. And it might in the end be even cheaper for you, rather than trying to redesign the whole application and trying to get it run somewhere else. So it's a weighing of costs. Then also, just like of giving a picture of the whole thing, so if you want to cut down a tree, right? You probably want to come with a chain saw and just get it done, right? And you don't want to use a handful of scissors that we would give you as the SQL Database. That would take forever, and then you would probably at some point come to us and say here, we can't get the tree to fall down, right? And then on the other hand, if you want cut someone's hair, the chain saw approach, well, could become questionable. So these are just a few things to think about, and to think about what you actually target. Now that we know there are the different things, what is actually the difference between those? There's this traditional versus the cloud datacenter layout. And there is the, what I already somewhat mentioned, there is the SQL instance versus the virtualized database. If you look at the traditional side of things, well, you buy big machines that have lots of capacity. You have probably one machine for the application server that connects then to a second machine where your database runs, and you scale it up, right? You have, I don't know, 50, 60 cores running there. Your databases are co-located. You have your master right next to your actual database. Then you put lots of money into place to actually have the connection reliable. This approach is spending lots of money to get availability and somewhat the guarantee that things will work, that things won't fail. Plus you get the resources that you need. The approach in the cloud datacenter is to use commodity hardware have smaller machines, but therefore have tons of them. So what we do is have lots of machines that are all the same, that at some point take different roles. What we then do is we put multiple, for example, instances of application servers or VMs on such a machine. And the same is true for the database as well. So we have machine—there's a SQL Server actually running on it, but what we do is we put a lot of databases on this machine. So the overall approach is, well, we spend a little less money, but given that we have so much of it, this how we achieve the high availability for you. Other things to consider here is the machines are not necessarily co-located. So if you run your own datacenter and have your own setup, you will probably make sure to reduce latencies between machines that they are co-located in the same subnet. Again, put a proper network in place. You cannot make any of these assumptions once you run in the cloud. We wouldn't tell you where your database actually is, and we also wouldn't give you that if you get two databases, well, they're not necessarily on the same machine. In fact, what we do is we abstract this layer completely. So, as I said, you have the notion of a logical server, you have a master database there, but this is just one of the databases running somewhere. And then you create your user database, and that is just running somewhere else and is running on three different machines where we replicate it to to achieve the high availability. So, getting into the whole idea of cloud optimizing your design, some of you might have seen that, maybe not. This slide is not necessarily extremely new. But it still holds true, actually, for what we're doing here. If you look at your local installation, what you can get is you have your server, and you have your master there. You have your user databases there. You potentially have other user databases running on this server as well. You have MSDB, you have TempDB, and you're in control of all of that. So, for example, you control the instance collation. You control the logins, the credentials. You can set up linked servers. You can add CLR to your server if you need it. You're further in control of the TempDB collations. You can connect to other databases directly. You have agent replication, DB Mail, all these things that you have available. Now, if you move to the very extreme, to the platform as a service offering, if you want to move a database there, this all doesn't work any more, which is due to the layer of abstraction that we give. Or that have to actually put in place. And this is to some degree—you would also have to consider this once you go in a VM in the cloud. Simply because of the overhead that it takes you to manage these VMs if you go on a scale out approach, if you want to run multiple VMs, if you want to put new ones in place. You might want to start, consider, if you have to move databases around, to actually decouple your database from any relations to the actual server that it is running on. A way to think this is, you want to—and this goes into the direction of the concept that we introduced with partially contained databases. You want to restrict all the stuff that you need, actually, into the database. And you can think of there are host features, and there are features that belong to the database. Host features are, for example, high availability or disaster recovery. These are things that are managed by the host, and potentially, depending where you run it, every host sets it up slightly differently. And then there are things that actually belong completely into the database and that can be moved around with the database without having any impact, which are like tables, triggers, for example, what we also in the platform as a service offerings saw in the database, for example, your firewall rules. So if you have set up a SQL database already, you notice that you have to set up firewall rules, so that you can actually access your SQL database. This is information that we store within the database so that we can fail over this database between different machines that we have set up, because otherwise if this would be somewhere stored in the server, for example, well, we fail over your database, and you can't connect any more. So this is obviously a scenario that you wouldn't like, and this is one problem on our side, but it also could become a problem on your side. And then there are obviously a few features that are like somewhat in the middle. If you think about CREATE DATABASE, for example, you can argue, well, that's obviously a host feature, because creating a database can't be within the database. Then, on the other hand, you might want to do programmatically creating databases, for example, so then you see, there's like a—difficult to actually draw a hard line there. What we do, we actually are planning on trying not to add any more features that fall into this bucket, simply because for our sake of being able to manage these and abstract things, and being able to move databases around. And this is something you definitely also should think about. One thing here, the fact that we abstract the whole database concept in the cloud adds a little bit of management overhead that we have to put in place to be able to, for example, find your databases within the cluster, so if you connect to the cloud, we need to make sure that we actually find your database. So one thing to consider, once you actually move to platform as a service, especially if you want to migrate things, you cannot script around CRUD operations. So if you want to create a database, you cannot have an IF statement around it. We wouldn't allow this, which is simply because we need to intercept these statements. So if you say CREATE DATABASE, as we don't give you an actual server, and we abstract that for you, we need to intercept that call and provide you the new database and hook it into your virtual server. This is one thing where lots of people actually stumble over. It's like you have a script, you run that script, then it fails and you don't know why, and sometimes it's actually because the CRUD operations have to be the only statement in a batch. All right, then going into some best practices. First thing is instrument, instrument, instrument. What that means is essentially you need to gain insights into your databases whenever you start a scale-out approach. If you have a local SQL Server, some sort of Superdome where you have really one server that you can connect to, well it is probably feasible to connect to the server and do some ad hoc operations, see if things run smoothly. If you have hundreds of databases, you won't be able to do that. You can't connect to all 100 of these databases. So you need to put things in place that actually help you getting insights into what is going on in your system as a total. And we will look at this in slightly more detail on the next slide, but goes into the direction of actually gathering data, use DMVs, etc., store it locally, and look at it there. The second and third thing actually together is like automate, automate, automate and optimize database design and queries. These are more like general tips. If you think about it from our perspective, obviously, if you create a database, we can't manually go in and set it up somewhere for you, so you need to automate these things. Probably, from your perspective, if you're looking into scaling out over multiple databases, and, for example, you partition a table across these databases, well, whenever you need a new partition, you don't necessarily want to go in and do this by hand. So you want to automate your processes. The next thing—optimize your database designs and queries. Again, this is more like a general hint, but lots of people actually do forget this. The SQL Server instance that you have on premise and the SQL database are simply two different things, and you need to think about these as two different things, so you cannot just take a query, run it in one place, and put it in the other place. This wouldn't work, right, so you need to actually dig into the design that you chose, and you need to optimize your queries, and you need to analyze how your queries run against the different offerings and see what you actually have to do to make them better performing. The next thing—and this is on the agenda, we'll go into slightly more detail there as well—you need to make your application more resilient for failures. The whole setup in the cloud, given that we have commodity hardware. given that we achieve high availability due to just more machines, you will get failures when you run against the cloud offering, and this is true for the platform as a service as well as for the VMs themselves. At some point, they will fail over, and you will get a reconnect, stuff like that. So you need to make sure that your applications can actually handle this. Whereas on premise, you can almost just assume that the connection is always there. This is not true. And then, yeah, minimize roundtrips. I have a demo for this one as well. This is, I guess, also somewhat a common best practice, but you want to just try to reduce the overall number of connections, or number of roundtrips, that you do to your database and try to batch things together. And I'll show some examples here. All right, so instrumenting your app, as I already said. The general approach that we suggest to people and that we actually take ourselves is to query databases and query performance data within those databases and store it somewhere else. Think of it as taking snapshots every once in a while. Store all this data somewhere else, and then query on the central data store and generate reporting and alerting out of this central data store. Considerations here are like not all data you probably need all the time. What we look into very much detail is performance data. For us it's very important to know how our different databases performing on our server, and we store that data quite frequently in a data warehouse, and then put alerting in place, which queries trends over this data. Whenever we see a trend going in, hopefully up, but sometimes down, we would put alerting in place and actually look into what reactions do we have to take here. And this is a very good approach that you should consider, especially if you have a lot of databases that you have to handle. Obviously, snapshotting. What you can do is DMVs to get insight into your data. The other thing is collecting Xevents. Collecting Xevents does not work in platform as a service, as of today, because we simply don't support Xevents just now. You can do this, obviously, in your VM, when you run SQL Server there. So, to give you some idea of what you can actually do, let's have a look at a few demo queries that I actually also took from the Internet. So folks in our group work with customers in trying to put in debugging mechanisms, try to optimize databases, etcetera, and this data normally gets published as white papers. So lots of resources, and especially these kinds of things, are actually available on the web, and I can only encourage you to look out for these things. And the SQLCAT team, for example, would be one source of those. So just looking at, I think it's four different queries that you can actually do, so you can look at top batches by total CPU time. What this does is it simply queries the dm_exec_query_stats, and cross-applies this SQL text for better readability. And what I'm going to do, I'm actually going to run these demos locally. And just for the sake, I'm going to use Adventureworks. The data that you will see here doesn't really make lots of sense, because I actually don't run everything against my machine. This is more like for giving you an idea of what you can actually do. So if I just execute this—I'm getting the top 25. Well, you will see, it outputs the actual query text that run. That gives you the CPU time, and so forth. Lots of numbers here. If you want to go through those in detail, I'll share the demos for these sessions. But again, just think of it as an idea of what you can do to actually gain some insights into what is going on on your machine, on your databases, for that matter. The next one I have here is the top 50 query plans. And this is specifically written for Azure Database. So again, see the query gets slightly more complex. I wouldn't go into any details here. It again queries the dm_exec_query_stats, applies the text, and looks into the plans as well. Let's see. I believe this one also returns some results here. Again, lots of numbers. You get the query plan, and you can see indexes that were used, waits, and so forth, and the actual statement, obviously. Going slightly further down, you can just get a snapshot of your dm_exec requests. And you can add wait types to it. This query is essentially a loop that runs. Given that I don't have anything running, this should show no results if I run it, but essentially what happens— yeah, so we iterate a couple of times over dm_exec request, and cross apply the plan and the SQL text again. Just capture this data, put it in a temporary variable, and then put it out. Okay. Last time around, it didn't get any results, but so, you see, it works. And then the last query I have here is missing indexes. And this one nicely, like when you actually get an output, it will give you even the SQL that you have to execute to create the index. And it queries the missing index DMVs that we, I believe, last year we actually exposed them, and this is, not surprising, empty. But as I said, it actually—so, if you look into it over here, it will actually create you the statement that you need to execute. So these are just a few interesting things to look at once in a while. And actually to snapshot those and put them into your warehouse and look into this data in a more realistic view. I'm going back over here. A few more things to keep in mind here. Especially if you run against platform as a service, you might want to get some sort of baseline that you compare your data against. You have to take into consideration that you're not alone running on this particular server where your database is. And your database keeps failing over within our system, so even that changes. It is not normally good enough to just run one test against your database and use this is as a baseline. You would have to do these a couple of times, then actually average over the data to get an interesting baseline. Another thing to think about is when you look into your data, and you do some sort of reporting—and normally what we do as well— is you would aggregate your data over some timeframe to look over trends and stuff like that. Average is not always the best choice. So if, for example, you have ten queries—you execute the same query ten times, and nine it comes back like this, and one time it takes forever. If you just do an average over those, you get a wrong impression of what is actually going on in your system. You would want to think about percentiles and stuff like that. I think that's mostly it for here. Then let's jump into connection reliability. As I said before, given the architecture of the system itself, when you run in the cloud, the whole thing is somewhat unreliable. This could be due to load balancing that we do, due to simple machine crashes we need to fail over, things like this. So you cannot assume that in a standard design, as we have it here, where you have like a user or user application, a web server, and the database server, the most reliable is probably your person that's translating something. But anything else is actually not reliable. Especially the connections are not reliable. Also good to know is that the only thing that's actually durable is the database, so when you have a web server running in the cloud, these are normally stateless machines. What you can do is you can maintain your own states. But they are normally stateless, and if they fail over, you just lose everything that was on there. Whereas for the database, obviously, once you write something in there, we make sure that you wouldn't lose it. So this is the only durable part in this whole picture. So what can happen is you execute a request, and that goes from the user up to the client, to the web server, to the database server, and you just get an error message back at some point. So whenever the system actually gives you a message, you're in a somewhat known state, because you either raise the error yourself or you can at least get a hint of what the error is. The other thing is you can just have timeouts. So you execute a query, and for some reason you never get a response. And you don't really know why. You don't know where it happens. And the question is then what do you do in these kind of situations? And if you just think of a customer on a website who tries to get a request in, well, he would just keep clicking, right, until at some point he gets a response. And this is something that you would have to do as well. So let's look actually into what can happen if you put these kind of retries into place. And this picture is slightly reduced to only the web server and the database server, because this is, from our perspective, the most interesting part. So you just issue a request to the server, and you get a response, right? This is the normal approach, and if something goes wrong, you would just retry. If you do that, there are different things that can happen. One is the connection actually doesn't work on the way to the server. In this situation, well, it's obviously safe to just retry your reads and writes, as nothing really happened on the server. No information was stored. The interesting part here is once your request actually reaches the server, and the server starts to execute something. Then, all of a sudden, it is not safe to retry any of the writes any more, because you simply don't know what actually already has happened or not. And if you do a write and then just retry this write, you might enter data twice. And so, obviously, then you corrupt your own data. The other thing that can happen is that actually on the way back, you get a timeout. And in this case, again, it is obviously somewhat the same scenario as when the request reaches the server. You cannot know—it is again not safe to retry the writes, and essentially, no matter what happens, you don't really have an idea, especially for writes, what is actually going on and what is the state on your server. So what you need to do is, you need to put a retry mechanism in place that is somewhat safe. So you might argue, well, let's just do client-side transactions, and everything should be fine. Well, we can look at this, and it turns out that this actually not sufficient. If you look at it, that's a simple program running against my local SQL Server here. What I do is I open a connection, start transactions for it, and then I will update and I have to create this table. Update an accounts table and change the balance in it. And then I commit this transaction. Just quickly connect here. CREATE TABLE Accounts. So we make it simple. It's an Id. It's an INT. PRIMARY KEY. And we add the Balance, also an INT. And it should probably be NOT NULL. Here we go. So now we have this table, and let's actually also insert an account. ID Balance. VALUES. Well, let's say 1, and let's say I'm rich, I have a thousand bucks. Add this day as well. So now when you execute this whole program here, and the whole thing works, well, then you have a transaction and everything is fine. I have a profiler running here as well, just to illustrate what actually happens on the server side once I execute my program. Oh, okay, what I'm doing is I'm actually changing— I'm actually going to change the balance. It's not an ideal example, because I would change it for all accounts here, but so the -= just say take the value that it has and just subtract the 50. Oh, yeah, now that's a good question. And any questions in between, just feel free to just shoot them. I'll take them. So yeah, so this is kind of like we have it in other programming languages. So before executing it, I'll actually just put a breakpoint in place. And when we start the transaction, just to see what happens— And let's execute it. And I run into the breakpoint here, and you can see in the profiler nothing yet has happened, because I haven't executed a command, actually. Let's jump to the next step. So now you can see we issued a batch and just said we want to begin our transaction. So this is the first roundtrip that this simple program made to our SQL Server. Then I just put this statement in the command, and I want to execute the statement. And then here you can see the batch has completed. And if we would press Append once more, we would commit the transaction and everything would be fine, right? So the question now is what actually happens if I kill this program? And this is essentially simulating— well, you issue this COMMIT statement, but it never reaches the server. So now you're actually in an unknown state. Even though you had a transaction running, you actually don't know what happened on the server. And especially you don't know if it is safe to retry now. So, see what happens. Just quickly shoot the query, so actually nothing has happened, because the COMMIT never reached the server, and it never worked. But think about the scenario where the COMMIT actually reaches the server, and, on the way back, your connection fails. Then your program never receives the information that your statement actually executed. But you would have changed the data already. So then if you retry it, well, you would lose a hundred bucks instead of fifty. So the client-side transaction is actually not sufficient in handling this particular problem. So just jumping back to the slide here, what you need to put in place is actually an algorithm that allows you to safely retry, especially writes. So how does this work? What you have to do in the first step is you need to generate a unique ID on the client side that identifies the particular request that you sent towards the SQL Server. Then you obviously send your request, including this request ID. And then on the server side, whenever you receive this request, the server should look into first, have I ever executed a request with this particular ID in it? If I have this ID, I'll just return an error and say, well I already did that. And you would just refuse this particular request. If this request has never been issued before, so, in other words, you couldn't find this ID, you begin your transaction and you store the ID that the client gave you. And then you execute everything that came with this request. And then you commit the transaction on the server side. And then you return whatever results actually should go back to the client. On the client side, once you receive your data or you receive the confirmation that your query was executed, what you do is you send a receive notification back to the server. So you do an additional roundtrip, and what we do then on the server is we would just delete this transaction ID and say, okay, I don't need to think about this data any more. I'm good to go, the request was executed, the client got the information. Obviously, this protocol has the flaw that if the send, the receive information, doesn't work, well then you would accumulate all those request IDs. So you have to think about a retention cleanup if you have a table that stores your request IDs. Like for a small database, it's probably not that critical, but if you have a huge database with tons of transactions going, at some point if you wouldn't clean up your system, it would first of all get slower to look up those IDs. You would run into potentially index fragmentation, stuff like that. So you want to have a retention policy in place and a cleanup. Things to consider here: It is not necessarily true to just go well, whenever after five minutes I didn't get a confirmation that the request was received on the client that it's good to delete this data. This is very much—what data you can clean up at one point is very much depending on what business requirements you actually have. And also how long a query potentially runs. So if you think about a query that maybe runs for an hour, well, it's probably not good enough to clean up the request ID after five minutes, because the client might retry after two hours, because it had expected to run the query for one hour anyway. So these are things to keep in mind when you think about retention, cleanup, and policy. So it's not always just like put one in place and it works for everything. So let's also look at how this protocol works. For the ease of the thing, I'm going to just simulate everything out of SSMS. I'm going to simulate that client and show you how it works on the server. First thing that we need to do is we actually need to create a table that will hold our request IDs. Call it Request. It has an ID. It's a unique identifier. It's also the primary key. And so if you think about doing like this retention cleanup stuff, at least you would need an additional date in there, so that you actually know when this request came in. You can also think about having information that the client can actually set, in terms of, well, what should be the retention policy. So the client can just permit, well, do it after one hour earliest, for example. Just a thing to think about, but let's just put the date in here. So let's say IssueDate DATETIME2. And should not be NULL. And just default it to the current timestamp. So this should do as our table for storing the requests. So the next thing, let's simulate this protocol that I just talked about. So on the client side, you would have to generate a GUID. So just do this now. Generates a GUID, obviously, and this you can just do in any client application as well. So this is a Windows API that is available that generates the same GUIDs. We actually use this one under the covers as well. So now for getting this protocol in place, what you say is you say BEGIN TRANSACTION. And the first thing that you do is a BEGIN TRY. And you insert your request ID into the table to store it. So INSERT INTO Requests that ID and that value. And then I'm just going to copy and paste the ID that I generated. I'm going to probably just generate a new one into this one. And then you say END TRY here. And this is essentially this first probe that I showed you. So the server gets the request ID, right, you insert into this table, well, and if the ID is already there, because it's a primary key, it will fail. So what you do here is a BEGIN CATCH. END CATCH, and what you do here is just you roll back the transaction. And you can also throw an error. Something like ID exists, for example. Everybody knows THROW. Actually, let's request, semicolon here. This goes there. And what you can just do here is you do the same update that we saw from the client side earlier, so you say UPDATE Accounts SET Balance -= 50. And I'll probably just to do it more properly and say WHERE Id actually 1, although we only have one account, but we wouldn't want to override everybody's account. And then you say COMMIT the transaction. Okay, and then the last step in the protocol, going back— so this is essentially—let me put this here. This is client, right, this would be executed on the server side. And then you go back to the client. And you say UPDATE Requests. Actually, DELETE FROM Requests WHERE Id = the GUID that we want to put in. So let's just go through it. Let's do it. So this is the GUID that I want to use. So the client generated it. Execute this. Then I'm going to put this GUID in here as well. So what happens is if you execute the whole batch here, everything is fine. What we do is we insert the GUID into the Requests table and we make the updates. So now let's just assume on the way back you had a connection failure, and you never got the confirmation that this update actually went through. If you do a retry now, you'll see that it actually fails. And the transaction will get rolled back, and we will tell you, well, oh, I already have this ID. You cannot execute this again. And, assuming you actually got the confirmation, and you sent the delete message here as well, well, you just clean up behind you, and then what you can do, even though we probably wouldn't recommend this, you can actually shoot the same thing again. It will work. So the other thing is the statement, so if you just say— you begin your transaction and you do the insert, everything works fine here, and now your connection doesn't work. Your connection fails. Let me just simulate this here, just say Disconnect. So now what happens? So this is the example where something goes wrong within the statement on the way there. If we now reconnect here, and also use the TechNet database, see, that's a—ah, made a slight mistake, so— I don't really know what the value was before, did someone count how many times I updated it? Twice at least, right, I would actually argue it's almost three. So if twice is correct, it should be 900, right? Well, okay, so someone did the proper counting. Thanks for paying attention. So you see, actually the statement, the updates, didn't get executed, because the connection failed in between, and it's safe to retry this one. You can see here I didn't do a rollback anything, I didn't do a cleanup. Still, it would succeed, because there was a transaction around it, and the INSERT statement was executed in this transaction. So the request was never entered, hence everything worked fine. Any questions so far on the retry? No? Okay. Let's move on here. Just generally, to sum this part up, a few considerations. So reads—as I said, it's essentially fine to always retry them. Just from a conceptual perspective. For the writes, as we saw, you need to generate some sort of transaction ID and store this one and do some cleanup after this. A thing to consider, though, is you have different types of queries. You might have low-cost and you might have high-cost queries. So for low-cost operations, something like select 1. You can probably just shoot that in an infinite loop, and it wouldn't really harm the server, but if you have a query that has a very high cost of executing, you might want to consider how often and how aggressively you actually retry this query. Because eventually, if you keep retrying and retrying and retrying it, you run the risk of actually bringing down your own database. Especially in an environment where you have shared resources, stuff like this, you will get to the point where you just overload your particular database. So what you want to think about is, first of all, how aggressively can you actually retry high-cost queries. And you also need to put some sort of backup strategy into your retrying. For example, if you have a query that you believe should execute in a millisecond, or come back to you in 10 milliseconds, which is probably more realistic in a cloud environment, where there are more latencies, you can probably retry it like every 10 seconds, but after an hour, you will probably figure out, well, maybe it's never going to come back. Maybe something is fundamentally wrong. And I hope you'll never experience an outage like this. But it can happen, right? Whereas a query that is very important to you, maybe you want to actually retry it more often, but you want to deck up, right? You want to give the system a chance to actually recover from potentially what was going on, so maybe you retry it every 10 seconds, and then you do it every minute, and then once after an hour, and then at some point, you need to think about, well, how will I actually recover from if something happens. But just a few thinking points to keep in mind here. Okay, so the next one, reducing roundtrips. Again, I stated it a couple times already, we have—you have higher latencies in the cloud. This is simply due to the design, due to the hardware that we use. You will never get the performance that you get when you just query something locally or at least on co-located servers. Just one thing to just give you some insight or some thought around why this is happening. For example, if you do a log in to a SQL database, it actually takes us eight steps to get you the connection to your database. So what we do is we get the connection request. We actually have to look up where your database is. First of all, we have to look up where your master is. Then we go to this master, we authenticate you, and then we establish the connection. Then we need to figure out where is your database, and then we connect you to the database, so there are a bunch of steps that are involved that just add up to the latency that you have anyway. And then again, there's obviously for the connection part of things. But just things to keep in mind. If you're extremely unlucky, for example, you have a failover in between. Well then you would actually get reconnected. But failovers during log-ins, for example, add to the latency. So to minimize these kind of effects, you want to start to bundle your things together. So instead of shooting 50 SELECT statements, you probably want to just shoot one and get all the data that you need to get it into a layer closer to the client and actually process the data there. Interesting consideration, if you use XML, anyway you probably for easier handling can actually put the data into XML directly and just transport a whole XML document—and I'll show you how this works in a little bit. So this is for getting the data. The other thing is, if you want to actually make updates to your system. Well here as well, you can actually batch those updates in certain ways, and an example here is, well, I want to update 1000 records. Rather than doing 1000 roundtrips with executing one update statement at a time, you will probably just—ideally, you would only do one and just send the whole batch. A few things that you can do—again, use XML. You can use delimited lists. You can use table value parameters. And one thing that actually already helps a lot is just executing it all in one batch, rather than shooting one batch after another to the system. So let's have a look at this as well. Let's switch over here. So for this example, I'm going to use Adventureworks. And I promise that's the last example I'll type. Let's just do something—let's look a typical querying for orders scenario. Actually, let's in fact connect there. So what you could typically do is something like, well, top 10, top 20, something like that. You say—let's, for the sake of easy to use afterwards— you get an order ID. Here we go. And you probably get the order date. And you just query this information. And you get the top 10, right? So this is a scenario, for example, think if you have a website and somebody logs in and wants to see all his orders, you typically first have an overview of the orders that he actually did, right? So the next step, from a client's side, what a client would do, probably click on one of the orders. So let's query the order details. And let's choose an order that is actually interesting. And I know, or I believe, it is 46664. That is an interesting one. It's simply the other ones only have one line item, stuff like that. Just to look at this. And here we just say, to get a little more details, well, it's the product ID, it's the quantity, and let's also do the price. That's typically, I guess, information that everybody would like to have. So in the next roundtrip, you query this information, and you see, well, then you get the list of things that you want to do. This is a fairly simple example, but you still have two roundtrips. And if you think about, well, the customer goes back, choose another one, and back and forth, right? So you get a bunch of roundtrips that you would want to eliminate. The easiest way to just combine these two is to have a simple JOIN between them. So let's just create this. You just do JOIN between those two tables and you say ON o.SalesOrderID = od.SalesOrderID and let's just do it for this one example. This obviously works if you don't have the WHERE clause for all of them. So if you execute this, you get the whole data together in one result. So this just reduces this one extra roundtrip that you had to do. The tricky thing here is, well, you need to decompose this data on the client side again, so you would have to get the— if you query over more orders, you would have to filter out what are the actual order entries and separate them from the details. So here I would, as I said before, XML can actually help you in doing this, and again if you already use XML anyway, this will make your life easier there. Let's rewrite this to generate some XML. All right, so. We just select all these three. And then we just take whatever we did for the JOIN, take this out here, and this is then essentially correlated, it's our query that we generate here now. And we just say WHERE, so this is joining essentially those two together again, and we say FOR XML, give it a PATH, and we say OrderDetails here. And then we have to actually tell it this is a TYPE. Otherwise, we'll just generate a string, which we don't want. And then we just call it AS OrderDetails here as well. Then, obviously, we don't need that JOIN here any more. And then we do the same thing here, FOR XML PATH ("Order"), like this. So this would already work. One small update that I do to this query is I'm actually going to say—I give them an alias and say something like @Id. And I do this for all of them. And obviously not all of them @Id, but @Date. And AS "@ProductID". AS "@Quantity". And AS "@Price". So what this does is, instead of showing it in XML as elements, will actually show it as attributes. So this is essentially what this does. And if I didn't make any mistakes, okay— What's that say? Centric. (inaudible remark from audience) Say that again? Oh. Okay. Here we go. See, I thought I learned from last TechEd that typing in sessions is difficult. Probably it takes me the second one to finally acknowledge that it is actually difficult. All right, so, it generated this XML for us. And if you look at it, it's nicely formatted and it's way easier to handle, obviously, on the client, and if you do processing, the XML on the client, well, it is easy to get out of there. So these are the main considerations for just getting stuff together. And it's a very simple example, but I think it illustrates what the overall idea is behind it. So the next thing—let's just save this and get rid of it. The next thing I want to do is I'm going to show— and no typing anymore— I want to show some testing about batching updates. Generally, so, I'll show a C# program in a little bit that actually does this work for us. But we have to set up the database. What we're going to do from the C# program, we're actually going read a file, and I'll show you that in a little bit. and then call stored procedures on the server side. So let's just quickly create those stored procedures. So the first one executes a SingleRow that just shoots one statement. Whenever it's executed, it shoots one statement and inserts the values into a table. And I already have the table pre-created, so this should just work. All right, just so, I actually see that that works. What I do here is I just execute that stored procedure and put some data in, and query the table, and it shows us the results. The next thing we're going to do is what I said before, is we're going to do a delimited string. What we do here is we just hand in this delimited string, and then we have to process this on the server side. So this stored procedure here is slightly more complicated. It actually uses a CLR function that we created before that does the splitting for us. And then we need to overall pivot this the other way around, so that we can insert it. One caveat here: You cannot do this in the platform as a service SQL Database, because we do not support user CLR at this point in time. So the whole CLR function thing, it's something that you could do in the VM, but we, unfortunately, can't do this in the cloud itself, in the platform as a service part of it. We currently don't support it. And just do this one as well, and I hope you trust me that the data is actually—that it actually works. Same thing for XML. This one just uses an XQuery over an XML document that comes in and transforms it this way. And let's also create this one. And then towards the very end, we have the table value parameter. So here what you do is, from the client, you just hand in a table with the data. One thing you need to do there first is to actually create a table type that essentially gives us an idea of how this table looks like. We do this here, and so it gets the same data, right? It's the price, and it's the name. Let me just create this type as well. That worked, and then the last stored procedure we can see, using this TVP, is very simple. We just do a SELECT from it, and do an INSERT and select from it. Here we go. So now we have the database, the stored procedures that we're going to execute from the client created, and I hope they were all straightforward. Any questions on those? Okay. Just use one of those TRUNCATEs here to empty the table quickly. All right, this one we don't need any more. So this is the actual program that I want to be running in a second. Just so that the main one is just getting some input and so that you can actually find what operation you want to execute. That should be straightforward. A little more interesting is this one. So you can see that as our connection stream up there, I just connect to my local database. The first function that I have here executes the SingleRow. So overall, it does the streaming. So it reads a file and just streams this and sends one update after another. Do you want me to go into any more detail on those functions, or— Okay, so yeah, this is it. So it reads a file, and I have a file with 950,000 entries in it that I want to be executing on, and it just creates a transaction— it does some simple time measuring—and executes this. The next one that we have just does the same thing, just commits one batch, and we'll see how this can actually accelerate things. The next one is the delimited string, so the file that I have this already delimited string, so just concat that string and hand it in. And then XML, we'll regenerate the XML file and commit the XML document to the database, calling the appropriate stored procedure. And then the last one is a TVP. Again, the TVP approach—I'll just create this TVP and just insert it that way. And then the last one is more informational. You can actually do some optimization, which not necessarily turns out to be faster in the test runs that I did so far. So you can actually implement the streaming for the TVP, so that you, whenever you start reading your file locally, and then you don't have to— the advantage here is you don't have to read the file locally into memory and then do the processing. It just starts streaming this into the TVP. So you get some benefits here. It's just the TVP execution here is slightly different. Again, I'll share the sources. So let's execute this program. And you can see it just shows me a bunch of different options. And the first one will probably take a while, while this execution starts. And the first one is just doing the roundtrips, right, doing one statement after another and—takes even longer than I thought it would. Again it's 950,000 entries that we actually want to put in there. That's why you shouldn't do it. So we have 11 minutes for this session left, so I think it will eventually finish within that timeframe. Yeah, this all running against the local laptop. I didn't take the risk of trying to do the demo against the cloud, actually. So a little bit of chickening out, but you can see, even with it running against my local laptop, like how long it already takes to execute these, right, and you will see the other ones aren't much faster when I do them, and it's— yeah, if it takes long, you could just do it on the box itself. Then you think about like adding a couple milliseconds latencies per roundtrip in the cloud. You will easily see that this will actually take a while. And it keeps going and going. I should have probably executed one of the others first, just for the sake of making progress. So it's 85,000 milliseconds is what that took, and I think you all got a feeling of how long that actually is. Let's just do, in comparison let's do the batch updates. So this is the same thing, just doing—batching it all together. And it will hopefully run slightly faster. You can see it was significantly faster, actually, right? Even though we still have the 950,000 statements. And then, yeah, just going further through the list, if you do the delimited string, well it's only one-fourth of the time, and XML is number 4. If we do this one, we'll probably be—well, it's slightly slower, actually, than the delimited string. Because XML, the transformation takes some time. And then let's also show the TVP. Well, that's fast. So just think about it. Like 85,000 versus 700. So that's a gain that's probably slight overhead, but it's, I guess, definitely worth doing. All right, and then again, the streaming doesn't add— just for the sake because we have it, let's do that as well. I think it's, yeah, it's slightly slower, actually, than the TVP itself. And I honestly don't know why exactly, but that's what it is. All right, any questions regarding this demo? (inaudible question from audience) Yeah, I'll get it up later. (inaudible) Yes, generally speaking, yes. So what we would actually like to do is to get to the exact same surface area between all products. It might not always be achievable—hard to tell, right? But I think, I mean, we definitely know about these things. And we think about generally having a nice synchronous execution, for example. Execution model for these connection things, to just make things easier. And we have those on the agenda, but I can't give you any— like it will be there next week, unfortunately. But we definitely know about these things. All right, let's jump quickly back here, as the demo took forever to execute. Yeah, just a few considerations here. So roundtrip per execution—well, it's full streaming, because you do it one after another over an open connection, right? It's easy to use. It's probably very intuitive, because that's how you would do it anyway. But, as we saw, it has a very poor performance, and, depending on how you do it, you also expose yourself to SQL injection, because you put the thing together on the client side. Delimited list—it's definitely faster. You don't have any SQL injection, because we transform the thing on the server side. As you saw, it required SQLCLR, so you can't really do it in the platform as a service. It is not strongly typed, so it's just—well, you get a string and you need to transform it to the types that you want to insert, so you need to be careful there not to get into reverting errors and stuff like that. Implementation is slightly more difficult. You saw, you need to do some pivoting, and you need to have the CLR in place. Well, you can simplify it if you get TVP for, like, each type. It's more like good programmer practices. The XML—well, it can be strongly typed, so you can actually have types in the XML, which helps you with this. You wouldn't have SQL injection as well. Obviously, again, it is a nice option if you use XML anyway. And it is extremely flexible, obviously. You saw there's a little bit of a performance overhead compared to the other approaches, and it is actually also negative point that it is not strongly typed by default, right? So you have to put this in place, and then add even more overhead to it. Again, it's a little less cumbersome than the delimited list, but still you need to do some Xquerying, stuff like that. And then, lastly, the TVP— well, it's strongly typed, no SQL injection, it performs great, as we saw, even with a huge amount of data. It's fairly easy to use, actually, well, it allows some degree of streaming, you know. Talk again to the person who made the demo like— where I should actually see the performance there. Then again, it's slightly less flexible than the XML, because you just can't format things around there. (inaudible question) Yeah, maybe, yeah. So if you go, yeah, that might very well be the case. All right, then going to the last one, caching. Overall, what we suggest is, well, cache aggressively. Because simply of the fact that you have the latencies in the system. Again, it should be driven by the business needs that you actually have. So if you have data that needs to be extremely fresh, then caching might not be the best option. You might actually just introduce overhead there, but if you think of a list of cities, for example, or countries, stuff like that, well, that data doesn't necessarily change every couple minutes. It's perfectly fine to just have this on the client side and readily accessible, to just reduce the amount of roundtrips that you have to make. And then, obviously, depending on what kind of caching technology you use, you can get advantages. Whereas the caching itself is obviously client-side stuff, some things that SQL Database actually helps you with when you do caching—we have a construct called Rowversion. What Rowversion does is, if you specify it, it will automatically generate an ID per change in a row which is unique over the whole database. And it functions as a high-water mark. So what that means is it actually increases, and you can do something like, get me everything that is higher than this Rowversion number. One of the disadvantages here is that the Rowversion actually always changes whenever you touch a row. So even if you do an update and say ID = ID, well, then it would still change, even though you haven't changed the data at all. So that's just something to keep in mind. So for invalidating caches, you might get some false positives if you have operations like this. Then again, you hopefully don't do an update where nothing happens. But it could potentially be—or if you, for example, only cache a certain set of columns, and you change something in columns that you're not necessarily concerned about for the caching purpose, if you do changes there, then the Rowversion would change, and you would refresh the cache, even though the data is the same. A way out of this is to use checksums. Checksums you can define over a certain set of columns and over a certain set of—yeah, columns in a set. And this one you can just calculate only there, and so if you change something else, that is outside of this, of the reach of the hash function, you wouldn't run into the problem. So there are two different things that we have. We have a checksum that is just a simple implementation based on BIGINT. so it's not that flexible to detect lots of changes, because you just at some point run out of values in the range. And the HashBytes are slightly more convenient, because they're more powerful and generate a higher range of unique values. The disadvantages for HashBytes, compared to checksum, is that for HashBytes, you actually need to do the concatenation of the string that you want to build the hash sum over by yourself. So you would have to do like, for example, use CONCAT, things like this, to actually generate the string, whereas for the checksum, you can just hand in the column names, and it does that automatically for you. So just a few considerations for querying it efficiently. So the Rowversion, you can just convert into a BIGINT and just have it as a human-readable number, which is way easier to interpret on the client side than the binary representation that we have. You should generally not create indexes on those rows, just because you get fragmentation. What you can do is when you actually query data, you should always not only query for this Rowversion number or your hash sum, or something like that, but also always include a column that you have indexed, so—like, get me all rows with an ID greater than something, and the Rowversion is greater than something else. These kind of things. And so that was my last slide for today. We have only one minute left. Just quickly some related content. We have some data tools presentations. There's Query Performance Tuning Techniques for SQL Azure Databases. I, unfortunately, don't have the times here, but all of those that I show are either all the time or in the future. So none of these sessions actually has happened. And then some best practices and lessons learned for the Azure ones. And we have hands-on labs for the new features in SQL 2012, things like window functions, stuff like that. I think that's great to look into. And I'll be in the TLC area from 12:15 on. And I'll be at the Experts tables, and I'll be in the TLC area tomorrow as well, should you have any questions. Some track resources, other resources overall. You probably have seen that before. Please complete an evaluation, and I would like to thank you, and I'll be sticking around a couple more minutes if you have any questions. Thank you. (applause)

Video Details

Duration: 1 hour, 14 minutes and 50 seconds
Country: United States
Language: English
Genre: None
Views: 6
Posted by: asoboleva99 on Jul 9, 2013

Langauges to MT; Fra, Ger, Spa, Bra, Kor, Jpn, Rus, CHT, Ita

Caption and Translate

    Sign In/Register for Dotsub to translate this video.