Press "Enter" to skip to content

Curated SQL Posts

Tuning With BlitzIndex

Published 2015-12-08 by Kevin Feasel

Koen Verbeeck on using sp_BlitzIndex:

What’s great is that this script also provides you with the URLs to knowledge articles on the Brent Ozar website. If you don’t understand one of the results, you can immediately look it up and read about it.

By focusing on the results of sp_BlitzIndex script, I could boost performance in just a few hours of work. This near real-time data warehouse is the source for a reporting application used by dozens of people in the field, and you could immediately tell it worked a lot faster. Awesomesauce.

There are several interesting scripts in the suite; check them out if you’ve not already.

Comments closed

More On Server 2016 Pricing

Published 2015-12-08 by Kevin Feasel

Denny Cherry thinks Windows Server 2016 pricing changes won’t matter that much:

So looking at this chart we can see that any machine with less than 8 cores per socket and two sockets or less will cost you exactly the same as it will today.  Also any machine with one socket and less than 16 cores will also cost the same as it costs today.

I’ll admit that I don’t know much about licensing, but rumblings at my company are that our server licensing costs are going to go up significantly.  As always, check with one of the four people on Earth who understand Microsoft licensing.

Comments closed

Select Failures

Published 2015-12-08 by Kevin Feasel

Paul Randal walks us through a complicated scenario involving corruption:

An interesting corruption problem cropped up on the MCM distribution list yesterday and after I figured it out, I thought it would make a good blog post in case anyone hits a similar problem.

In a nutshell, the problem was corruption such that a simple SELECT * query failed, but a SELECT * query with an ORDER BY clause worked.

This is interesting, but hopefully you don’t encounter these types of problems very often in your environment.

Comments closed

Partitioning On The Cheap

Published 2015-12-07 by Kevin Feasel

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 closed

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