A Fine Slice Of SQL Server
Aaron Bertrand shows us how to partition on the cheap:
The TL;DR of this is that you can use filtered indexes to keep all of your “hot data” in a separate physical structure, and even on separate underlying hardware (you may have a fast SSD or PCIe drive available, but it can’t hold the whole table).
Using filtered indexes is an interesting way of solving issues that partitioning normally would help you out with. I’m looking forward to the next part in the series.
Comments closedKendra Little shows us something new, the Number of Rows Read tag in an execution plan:
Execution plans got a cool new piece of diagnostic information in SQL Server 2012 SP3, and we believe we’ll soon have this change in SQL Server 2014 and the coming SQL Server 2016: “Number of Rows Read”.
In fancy language, this is “better diagnostics” when a query plan has “residual predicate pushdown” (KB 3107397).
In human language, SQL Server will now tell you “How many rows did I really have to read, even if I have a hidden filter in here?”
This appears in actual execution plans only. Sorry, there is no such thing as “Estimated Number of Rows Read” that I can find.
This is another piece of useful information now available in execution plans. Hopefully it’ll be supported in SQL Server 2014 and 2016 soon enough.
Comments closedWith Azure SQL Database, you can choose the server name:
Once you select the option to create a new server, you see similar options to those you saw in the Azure Management Portal. However, this time you see a server name.
Alas, you can again name your own database servers! As you can see above in the Azure Preview Portal, the server has been created with the provided name. Then if we switch over to the Azure Management Portal, we will see the same.
Choose your names wisely.
Comments closedAndrea 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 closed