A Fine Slice Of SQL Server
Kenneth Fisher shows how to use DB_NAME() and DB_ID():
I actually use them this way on a somewhat regular basis when I don’t want to have to tie back into sys.databases to just get the database name. And also in the WHERE clause when I want to restrict based on a database name.
It’s important to know where you are. Also, where your keys are.
Comments closedPaul White has an article. Read it:
The point is that there is an awful lot more going on inside SQL Server than is exposed in execution plans. Hopefully some of the details discussed in this rather long article will be interesting or even useful to some people.
It is good to have expectations of performance, and to know what plan shapes and properties are generally beneficial. That sort of experience and knowledge will serve you well for 99% or more of the queries you will ever be asked to tune. Sometimes, though, it is good try something a little weird or unusual just to see what happens, and to validate those expectations.
Optimizing update queries seems trivial at first, but as Paul shows, we have a few more tools at our disposal than is apparent at first glance.
Comments closedGinger Grant shows a couple of techniques for sampling from tables:
The random sample that TABLESAMPLE provides is based on the number of data pages, not the number of records. If you want the number of rows to be specifically limited you will need to use Top(n) as well. I’ve written all my samples based upon AdventureWorksDW so you can run them for yourself later. I’ve listed the variety of ways to call TABLESAMPLE and shown the number of records returned.
TABLESAMPLE is useful for spelunking, but is somewhat limited otherwise.
Comments closedMichael Bourgon has a pivot script:
Say you have records from your monitor that happens to Track EventLogs.
We’ll call it EventLog_Tracking for argument’s sake (hint hint http://thebakingdba.blogspot.com/2015/05/powershell-eventlogtracking-capturing.html) and want to look at trends over time.
Dynamic pivoting is possible (I have an example en passant in a tally table presentation I created a couple years back), but it’s not the easiest thing in the world.
Comments closed