Press "Enter" to skip to content

Author: Kevin Feasel

Actual Number Of Rows Read

Published 2015-12-07 by Kevin Feasel

Kendra 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 closed

Naming Azure Servers

Published 2015-12-07 by Kevin Feasel

With 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 closed

Removing Bad Execution Plans

Published 2015-12-07 by Kevin Feasel

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 closed

Check Those Backups

Published 2015-12-04 by Kevin Feasel

Andy 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 closed

Relational Lives On

Published 2015-12-04 by Kevin Feasel

Tony Davis on NoSQL:

There 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:

the 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.

Comments closed

Windows Server Licensing Changes

Published 2015-12-04 by Kevin Feasel

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 closed

Azure SQL Database Threat Detection

Published 2015-12-04 by Kevin Feasel

Troy 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 closed

Ending A Powershell Loop

Published 2015-12-04 by Kevin Feasel

Steve 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 closed

Watch TMP Space

Published 2015-12-03 by Kevin Feasel

Erin 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

Naming Transactions

Published 2015-12-03 by Kevin Feasel

Gail 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 closed