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 closedAllan 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 closedShawn Melton shows us how to modify SQL Server startup parameters using Powershell:
Low and behold the StartupParameters property is one that can be read and set. So how do you set it? Well the one thing to remember is you DO NOT need to remove what is already in that property because IT WILL BREAK YOUR SERVER!
Let me be clear, setting the property means you need to append to what is already there, so don’t just go setting it equal to something like “-T1118”. Doing this will remove the required parameters to start SQL Server itself, and no it will never warn you of this…so proceed at your own risk.
Read the instructions; otherwise, you can mess up your installation, and that’d be a bad thing.
Comments closedMike Fal gives us a pattern for managing SQL Server services with Powershell, WMI, and SMO:
I have built a function around using the second method that makes handling this process a little easier. Also, because I’m not a fan of passing passwords in plain text, I built the function to take a PSCredential object to keep my account information secure. In order to spare you the wall of text, you can view the full function on my GitHub repository.
The function can be loaded through a variety of methods, but once it is loaded calling it is simply a matter of creating the credential for the service account and calling the function
Good stuff.
Comments closedSean McCown goes into fixing one example of the “Cannot Generate SSPI Context” error:
Now, this was just a quick tutorial on how to manage SPNs. This hole can go pretty deep. Here’s a decent link on MSDN for troubleshooting SPNs. I don’t think I like their troubleshooting because they don’t really do a good job of showing you the commands, but it’s a good explanation of the problem, what an SPN is, etc. If I remember correctly it’ll also help you choose the right SPN.
This is a classic example of a bad Microsoft error. In this case, it’s bad because there are multiple root causes for the same error and because the message itself is unhelpful.
Comments closedKendra Little shows us something awesome about Query Store and OPTION(RECOMPILE):
YAY! For all my queries that were run with RECOMPILE hints, I can see information about how many times they were run, execution stats, their query text and plan, and even information about compilation.
And yes, I have the execution plans, too — the “CAST(qsp.query_plan AS XML) AS mah_query_plan” totally works.
This is great news. Query Store is going to be a big feature for DBAs.
Comments closedGlenn Berry introduces us to Storage Spaces Direct:
One of the more exciting new features in Windows Server 2016 is called Storage Spaces Direct (S2D), which enables organizations to use multiple, clustered commodity file server nodes to build highly available, scalable storage systems with local storage, using SATA, SAS, or PCIe NVMe devices. You can use internal drives in each storage node, or direct-attached disk devices using “Just a Bunch of Disks” (JBOD) where each JBOD is only connected to a single storage node. This eliminates the previous requirement for a shared SAS fabric and its complexities (which was required with Windows Server 2012 R2 Storage Spaces and SOFS), and also enables using less expensive storage devices such as SATA disks.
This sounds like a technology with interesting potential, and not requiring SAS disks will make it more likely to be adopted.
Comments closedRichie Lee has a quick script to check which objects are compressed. Given that I ended up needing to use this script within a day of his posting it (hey, I’m as lazy as anybody else…), I figured it was worth linking.
Comments closedWarner Chaves reminds us that SQL Server 2005 is within a few months of End Of Life:
As I sit here typing this blog post in my home, we are 145 days or more accurately about 3480 hours until April 12, 2016. That is the date when Extended Support for SQL Server 2005 will be done. Over. Dunzo. Kaput. Yes, Microsoft can do Custom Support Agreements for large companies but it will cost you and you still will be stuck without all the sweet features that have been released in the last 7 years (since 2008 came out). So let’s face, it’s time to upgrade SQL Server 2005!
Chaves gives two good options: either upgrade or move your database into Azure. The unfortunate thing is that there are industries (health care, I’m looking at you) whose vendors are so slow to support new versions that some servers will be stuck on 2005 or (ick) 2000 forever. I feel for you guys.
Comments closedSQL Server 2012 SP3 is now available. Brent Ozar has details. Kendra Little has details on memory grants.
If you’re on SQL Server 2012, this looks like something to test.
Comments closed