A Fine Slice Of SQL Server
Andrea Allred shows one way of removing a bad query plan:
If you click on the query_plan link, you can see what the plan looks like. After you have reviewed it and determined the plan is bad then you can paste your plan handle over the one below to remove it from the proc cache.
DBCC FREEPROCCACHE normally is something you don’t want to play with in production, but this is narrowly focused enough not to harm you down the line.
Comments closedAndy Galbraith walks us through a backup issue he experienced recently:
These messages showed that a process of some kind ran just after 9 pm that switched the databases from FULL recovery to SIMPLE and then back again. This broke the LOG recovery chain and required new FULL backups before any LOG backups could succeed, which is why the LOG backup job was failing.
This sort of interesting user behavior is why it’s so important to have automated systems in place to check for issues and, whenever possible, fix them.
Comments closedThere have been some spectacular examples where the lack of transactional integrity of NOSQL databases led to financial disaster. Even ardent NoSQL enthusiasts did U-turns on the value of ACID-compliance. And therefore, slowly, inexorably many NoSQL database begin to acquire the essential characteristics of a relational database. MongoDB now offers joins; N1QL and U-SQL bring good old SQL-style querying to “NoSQL” data. Many of the NoSQL databases are now laboring towards some form of proper transactional support.
I enjoyed Robert Young’s first comment:
Comments closedthe notion that NoSql “databases” are more flexible isn’t even true: chaotic, yes. but flexible means being able to move without breaking, and NoSql, due to the lack of schema, means that all manner of inconsistencies and redundancies are allowed. that’s not flexible, that’s nuts.
Allan Hirt prepares us for a licensing letdown:
Say Hello to Core-based Licensing for Windows Server
This is the one that may annoy most folks. Like SQL Server, Windows Server 2016 licensing will be core-based, including the Core Infrastructure Suite SKU. Historically, Windows pricing has been MUCH lower than SQL Server, and no prices have been announced. So before anyone has a conniption, let’s see what the core pricing will be based on the chart shown on page 2, there are cases where the cost may be the same as it is today.
I’m now curious about how many people will hit a wall with Windows Server editions like we’ve seen with SQL Server 2008 R2.
Comments closedTroy Hunt introduces us to a fantastic new feature with Azure SQL Databases:
Azure has just introduced another tool to help in the fight against SQL injection known as SQL Database Threat Detection. You can go and read all the Microsofty bits there or watch it work in a real live app here.
Firstly, this is threat detection, not prevention. In a nutshell, this feature will tell you when an attack is mounted against your database and in order to do that, the upstream app has to have a vulnerability in it that’s allowing the attack to get that far. Now before you give it a bit of “well that’s pretty useless then”, the main reason this makes sense is that you can go and enable it with a single checkbox tick and it won’t break your things. Plus, even if the code is solid and you have a device or a service like a WAF, this is just one more layer that’s good to have in place. Let’s just jump into it.
This is a useful tool. If you’re using Azure SQL Databases, go forth and activate this.
Comments closedSteve Jones shows how to end a loop early:
That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.
I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.
I’d just as soon use a break statement, but there are many ways to skin a cat.
Comments closedErin Stellato shows us that Extended Events and Profiler both use local temp storage:
Depending on what events you have configured for Profiler, your filter(s), the workload, and how long you run Profiler, you could generate more events than the UI can handle. Therefore, they’ll start buffering to the User TMP location. If you’re not paying attention, you can fill up the C: drive. This can cause applications (including SQL Server) to generate errors or stop working entirely. Not good.
Reference: https://msdn.microsoft.com/en-us/library/ms174203.aspx
Now, back to the original question. Does the same problem exist for Extended Events? Only if you’re using the Live Data Viewer. After you have an event session created (you can just use system_health for this example), within Management Studio, go to Management | Extended Events | Sessions, select the session and right-click and select Watch Live Data
This is one of those things you hardly think about, but it makes sense: that data’s got to be stored somewhere if things are moving too fast for the app.
Comments closedGail Shaw asks, why name transactions?
So what conclusion can we come to here? Pretty much that naming of transactions has one real use, as a form of documentation. Instead of putting a comment above an BEGIN TRANSACTION we can give the transaction a name that indicates what the transaction does, That’s about the only real use.
With two exceptions.
The one reason I have to name transactions: name and shame.
Comments closedMickey Stuewe introduces us to Azure:
At the bottom of the portal, there is a New link and a Delete link. These are for creating and deleting databases.
After clicking the New link, I went through a series of screens to create my database.
The first screen asked me for the name of my database and what size database I wanted to create. This is an important step, since it will affect my monthly charges. Remember, I only have $150 in free credits each month. You can go here to see the pricing for the various service tiers and the performance levels. I chose to create the smallest database I could (2 GB, and 5 DTUs). I also created this database on a new SQL Database Server (I kind of have to, since it is the first database).
Louis Davidson has begun to embrace(?) the cloud as well:
Both of the products, the On Premises versions and the Azure SQL Database versions are part of the Relational Database family of products. They share a common base, and a common purpose: to work with relational data. They look basically the same, and operate mostly the same, and serve (at their core) very same purposes.
As such I will make sure that all of the scripts that end up in the final book have been validated on the different editions of SQL Server (as I have always done), and have been executed at least once on Azure SQL Database as well. What I won’t do is go into many details of how to connect to an Azure SQL Database, mostly because it takes quite a few pages to do so (I just tech edited a book that covers such details, and I will direct readers to that book for details on how to connect… Peter Carter “Pro SQL Server Admin” http://www.springer.com/gp/book/9781484207116).
We’re already seeing Microsoft move to a cloud-first philosophy, so get in on Azure if you’ve avoided it thus far.
Comments closedAaron Bertrand shows us how to find hidden features in CTPs:
In honesty, I’m just meticulous about installing each new build and immediately digging into the metadata. It would be hard to take a look at
sys.all_objectsand identify what’s new by sight; even columns likecreate_dateandmodify_dateare not as accurate as you might expect. (For example, in CTP 3.1,sp_helpindexhas acreate_dateof 2015-11-21 18:03:15.267.)So instead of relying on photographic memory or hoping that something new will jump out at me while scanning the new catalog, I always install the new CTP side-by-side with the previous CTP (or, in the case of the very first CTP, side-by-side with the previous version). Then I can just perform various types of anti-semi-joins across a linked server to see objects and columns that have been added, removed, or changed.
Very interesting.
Comments closed