A Fine Slice Of SQL Server
Kendra 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 closedSQL Sasquatch shows that his computers go up to 11:
I trust the utilization reported by “Processor Info”. Note that the greatest reported “Resource Pool Stats” utilization (approaching 120%) is when total “Processor Info” utilization is near 100% across all 12 physical/24 logical cores. Nominal rating of the core is 3.06 GHz, top SpeedStep is 3.46 GHz. That would give a maximum ratio of 3.46/3.06 = 113%, which is still under the number reported by SQL Server (for Default pool alone, I’ll add). Even if the numbers made it seem possible that SpeedStep was responsible for more than 100% utilization reported by SQL Server, I don’t think SpeedStep is the culprit. The older Intel processors were by default conservative with SpeedStep, to stay well within power and heat envelope. And no-one’s been souping this server up for overclocking 🙂
So… if my database engine will give 110% (and sometimes more…) I guess I better, too. 🙂
Math is hard.
Comments closedPowerShell is an ideal tool for doing health-checks of a collection of SQL Server instances, and there are several examples around, but few acknowledge the fact that individual DBAs have their own priorities for tests, and need something easily changed to suit circumstances. Omid’s Healthcheck allows tests to be SQL or PowerShell and requires only adding, altering or deleting files in directories.
Grab the tool from his GitHub repo.
Comments closedAndy Mallon teaches us when we need to use WITH OVERRIDE on RECONFIGURE:
Should I use WITH OVERRIDE?
Probably not.
That was an easy post. All done! Thanks for reading.
……Oh, right. Let’s talk about why. Books Online actually has the answer:
If that’s too too heavy of reading, you can spend the day on the Transformers Wiki. I mean, it is Friday, after all.
Comments closedSlava Murygin shows ways to simplify starting and stopping SQL Server services on local instances:
Step 3: Easily Stop SQL Server Service.
That is very possible you performed some very heavy and memory intense operation by your local SQL Server instance and it ate all your free memory. You do not need it anymore, but SQL Server won’t easily give memory back.
The easiest way to claim all your memory is to stop your SQL Server.
There are some easy ways doing it:
– First way us using SQL Server Management Studio. You just have to do a right click on your local SQL Server instance and choose “Stop”.
The use case for Slava’s advice is a scenario in which you have SQL Server installed on a local machine with very little RAM.
Comments closedDerik Hammer shares a couple of snippets he uses to find objects and SQL Agent jobs.
Here’s one of my favorites, which searches for code within stored procedures, functions, and views:
SELECT OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName, OBJECT_NAME(sm.object_id) AS ObjectName, CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id)) AS FullName, CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id), ',') AS CommaSeparatedName, definition FROM sys.sql_modules sm WHERE sm.definition LIKE '%DEFINITION%' --AND OBJECT_SCHEMA_NAME(sm.object_id) = 'Something' --AND OBJECT_NAME(sm.object_id) = 'Something' ORDER BY SchemaName, ObjectName;Comments closed