SQL Server provides multiple ways to perform most functions. In order to maximize your understanding of how SQL Server works and have as many different mechanisms as possible for getting work done, you’ll use the GUI and TSQL to learn how to create and drop databases. You can then use whichever mechanism works best for you at the moment. Using each method, we’ll first create a database using the least amount of work possible, just so you can see how easy it is create a database. We’ll go over how to remove databases from the system, getting rid of the database you just created. From there we’ll drill down to create another database, exploring some of the different mechanisms you can use to change how databases get created. Then we’ll…
The hardest part about implementing DevOps is not the tools you choose, but the processes you use to make DevOps work. That said, you do need to think about the tools you're going to use to automate those processes. Frequently the emphasis is on third party tools, but it doesn't always have to be. Microsoft's Visual Studio has a number of tools that you can use to automate your DevOps methods. Visual Studio Team Services Connecting a project into Team Services opens up the world of DevOps pretty handily. You can host this all locally and do an install to a server to support it. With more and more of us working with teams that span continents and oceans, it probably makes more sense to use the online version. There's…
I believe in DevOps. Actually, that's a pretty horrible way to put it. It's not about belief, like keeping Tinkerbell alive. I have successfully worked within an environment that implemented a DevOps approach to development, deployment and maintenance. I also provide classes and consulting on how to approach DevOps from the Ops perspective as well as writing books on the topic. Because I've seen the DevOps approach work, and work well, despite the fact that my principal job description is in the Ops side of DevOps, I am a very strong and passionate advocate for DevOps. But! DevOps Despite the fact that I absolutely support the concepts of DevOps, moving development & deployment into the production space, and moving operations into better support of the development space, I frequently find…
Data Definition Language queries don't go through the optimizer, right? While normally, my short answer to this question in the past would have been, yes. However, with testing comes knowledge and I want to be sure about the statement. I'm working with a team of people to completely rewrite the SQL Server Execution Plans book. We'll probably be published in April or May. It's going to be amazing. The structure will be completely different and the book will represent five years of additional knowledge in how execution plans work and how to read and interpret them since the last book was written. However, enough on that. Let's answer the question about Data Definition Language. First of all, we need to quickly define our terms. Data Definition Language (DDL) represents the syntax for queries that build…
I love presenting sessions because you get so many interesting questions. For example, what happens with Optimize for Ad Hoc when Query Store is enabled? Great question. I didn't have the answer, so, on to testing. For those who don't know, Optimize for Ad Hoc is a mechanism for dealing with lots and lots of ad hoc queries. When this is enabled, instead of storing an execution plan the first time a query is called, a plan stub, basically the identifying mechanisms, for the plan is stored in cache. This reduces the amount of space wasted in your cache. The second time the query is called, the plan is then stored in cache. I'm going to set up Optimize for Ad Hoc and Query Store and, to clean the slate,…
You need to be aware that you're going to see differences when you're working with Azure SQL Database when it comes to wait statistics. If you're running a v12 Azure SQL Database (and if you're not, go fix it), you can run a query against sys.dm_os_wait_stats. Here's the output for the TOP 10 waits ordered by wait time on one of my databases: Everything you're used to seeing, right? Well... not quite. This is Azure SQL Database. So, let's use sys.dm_db_wait_stats, a DMO that is only available within Azure. This lists waits by database. The TOP 10 here are: You'll notice that these results are wildly different from those above. What we're looking is largely a server versus a database, but not completely. I mean that sys.dm_os_wait_stats is showing the waits for the…
Query Store is pretty amazing. I'm loving working with it. I think it's likely to change how query tuning will be done in the future. Lots of people are probably going to just use the reports and tools in SQL Server Management Studio. However, a pretty healthy chunk of us will start using the system views in order to programmatically access the information stored in Query Store. One of the first things you're going to want to do is track down your query. The primary views you'll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let's take four scenarios and see if we can retrieve the correct query. First up, an ad hoc query: SELECT e.NationalIDNumber, p.LastName, p.FirstName, a.City, bea.AddressTypeID FROM HumanResources.Employee AS e JOIN Person.BusinessEntityAddress AS…
With all the new stuff on the Microsoft Data Platform, it's really hard to keep up with it all. I had announced my plans to charge down the DocumentDB road to try to get the basics of that in my head along with learning some JSON so I could get what all the hoopla is about. However, after a lot of thought and some extensive meetings at Redgate, I'm looking to shift my learning in a new direction. First up. Arrrrrrrrr! No, it's not yet "Talk Like a Pirate Day." I'm going to start learning the R language. It's a language for statistical computing and is one of the many underpinnings for what's going to be happening with a lot of the Machine Learning capabilities in the Data Platform. With Azure…
The Nominations Committee has done their job and the final report has been published. Good luck to everyone running. Thank you for stepping up and taking part in this process. This is the first time in a very long time that I don't get to put out a "You should listen to me and vote for..." blog post. Since I'm on the board I feel more than a little squeamish coming out for any candidate. If I openly support Person Y over Person X, have I just yacked off all the supporters of Person X? Have I yacked off Person X? Most importantly, have I potentially poisoned a relationship that I need in order to be effective while on the Board? Add to that the fact that I was ask…
I was actually travelling for most of July and some of that was on holiday with the family, the real family. That's why I didn't get a June update posted. I don't have anything much exciting to report. We're finally getting data out of our Chapter database and can report some interesting stuff. We've added 29 new chapters in the fiscal year of 2015. 10 in the US, 12 in EMEA, 2 in LATAM and 5 in APAC. There's a bunch more. Not sure when it's all supposed to be published, but I'll share some of it now, just 'cause. Our chapters logged 1,405 meetings in fiscal year 2015. That's a pretty serious amount of training. Well done everyone! Thanks for all your work Carmen. I missed the July board meeting…