A Fine Slice Of SQL Server
Grant Fritchey shows us how to find a query in the Query Store:
The primary views you’ll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let’s take four scenarios and see if we can retrieve the correct query.
This is a little more complicated than I would have hoped, but as Grant notes, this is the CTP, so maybe there will be some tooling added to make life a little easier.
Comments closedGail Shaw proves that sometimes, an index scan isn’t a full index scan:
A scan of the entire index is over 1600 pages. This query read three. It’s a scan, but it’s a scan which stopped after reading one page of the leaf (the other two are likely the root and intermediate pages, used to locate the first page in the leaf).
The scan read one row and then stopped, because that’s all that was needed. It did that, because there was a 1 row row-goal added to the query. For more details on row goals, see Paul White’s article on the subject.
Read the whole thing.
Corollary: sometimes a seek isn’t really a seek; sometimes it’s a scan even when the icon says “seek” because that’s more efficient.
1 CommentMike Fal shows us how to mange SQL Server error logs using Powershell:
Reading SQL Server error logs is pretty simple, but requires us to make use of the SMO objects and the .ReadErrorLog() method to get the data from the log. Let’s start by simply reading the SQL Server error log and see what the output looks like (I’ll be using the SQL Server provider for this)
The big benefit, as Mike points out, is that the log rows are considered objects, meaning you don’t need to try to parse out things like the log date.
Comments closedKendra Little shows us how to fix a common partitioning issue:
Everything was fine, but we forgot to put a boundary point in for Jan 1, 2016… and now it’s well into January. All our data for January 2016 is in the partition with the December data.
The more time passes, the more data is going to go into that partition, and it’ll get bigger and more lopsided. If we’re switching out old partitions by month, eventually that’s not going to work. And partition elimination won’t work for anything after Dec 1, 2015, either!
Be sure to keep those empty partitions around, just in case .
Comments closedBill Anton digs into the SSAS Flight Recorder:
During a recent engagement involving an Analysis Services health assessment, one of the things I noted was that the Flight Recorder was enabled. During the post-assessment review with the client, where we walked through the report and recommendations, I quickly explained what the Flight Recorder was, why it should be disabled (answer: never used, wasted resources, etc), and that disabling it would not produce a noticeable performance gain – the recommendation was mostly out of habit.
Bill’s answer is to build custom performance monitoring.
Comments closedKenneth Fisher talks SQL Server Agent schedules:
Because you can re-use the schedules you want to be careful naming them. I’ve seen far to many schedules named Schedule 1 or Job Name Schedule. The first is non-descriptive and the second too specific (it would look silly as the schedule for another job with a different name). Give your schedules names like Every Weekday at 4AM or The third of the month at 6AM. This way when you go to pick the right schedule you know which one is which.
Word of warning (which Kenneth also notes): be careful about modifying those schedules; if you think you’re editing the schedule for just one job, you might actually be modifying schedules for a bunch of jobs.
Comments closedSteve Jones explains database snapshots:
I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.
However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick “create database xx” syntax I often use, with a snapshot I need to be more specific.
Word of warning: don’t have more than one active snapshot of a single database. If you do, you’ll likely have major performance problems. My favorite use case for snapshots was building some semi-automated integration tests a few years back. I created a tool for devs to create snapshots, and then they could run all the tests they wanted and revert the snapshot afterward. There are some good uses in production environments as well.
Comments closedLaerte Junior has an article on writing re-usable Powershell scripts:
There are the rules that I use in my day-to-day work in PowerShell. They’ve worked well for me over the years, but I’m not saying that they are carved in stone.
Learn the languageYou need to treat PowerShell as a serious .NET language. Like C#, F# or VB, it is impossible to know what the language can do, such as its features or commands without understanding the language and its paradigms? You can cut and splice other people’s scripts but you must have a good feel for the way that the language works before you can proceed any further
Use The HelpFor PowerShell, the help system is the first thing you must reach for: it must be your best friend. The designers of the language intended it to be well-used.
Use the PowerShell CommunityThe PowerShell community is unique, because it has people who have come from a wide range of IT backgrounds. They bring their experience and wisdom with them. They will know more than you. The combination of skills multiplies the speed at which the language develops. Read their posts, download their script and learn from them.
Keep It Simple
Do not use Aliases, except for deliberate obfuscation.
Write with considerationDo not try to cram all your scripted process into one line. In the Shared and Corporate environment other people will maintain your code and will not necessarily have the same PowerShell knowledge as you. Be kind in your code.
After reading his article, check out Carlos Perez, et al’s Powershell Best Practices and Style Guide.
Comments closedMichael J. Swart has an Extended Event and a query to help monitor deadlocks:
Guess what? Apparently I “reinvented the wheel”. The extended events session I created is equivalent to one that Jeremiah Peschka wrote two years ago in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. The embarrassing thing is that in Jeremiah’s article, he references a tool I wrote. And the first comment was written by yours truly.
There are a bunch of ways to capture deadlock information. This is a good one.
Comments closedLouis Davidson on naming constraints:
It has long been a habit that I name my constraints, and even if it wasn’t useful for database comparisons, it just helps me to see the database structure all that much eaiser. The fact that I as I get more experience writing SQL and about SQL, I have grown to habitually format my code a certain way makes it all the more interesting to me that I had never come across this scenario to not name constraints.
Temp tables are special. There’s another reason to have non-named constraints on temp tables inside stored procedures: it allows for temp table reuse, as shown on slide 21 in this Eddie Wuerch slide deck from SQL Saturday 75 (incidentally, the first SQL Saturday I ever attended).
Comments closed