Kendra Little talks partition elimination:
Working with table partitioning can be puzzling. Table partitioning isn’t always a slam dunk for performance: heavy testing is needed. But even getting started with the testing can be a bit tricky!
Here’s a (relatively) simple example that walks you through setting up a partitioned table, running a query, and checking if it was able to get partition elimination.
I would have snipped the tl;dr section but it was too long…
Chris Webb takes a look at MDX querying in SQL Server 2016 Tabular models:
There were a lot of limitations when using DirectQuery in SSAS Tabular 2012/4, but for me the showstopper was the fact that it only worked if you were running DAX queries against your model. Historically the only major client tool that generated DAX queries to get data was Power View, and Power View was/is too limited for serious use, so that alone meant that none of my customers were interested in using DirectQuery. Although we now have Power BI Desktop and PowerBI.com, which also generate DAX queries, the fact remains that the vast majority of business users will still prefer to use Excel PivotTables as their primary client tool – and Excel PivotTables generate MDX queries. So, support for MDX queries in DirectQuery mode in SSAS 2016 means that Excel users will now be able to query a Tabular model in DirectQuery mode. This, plus the performance improvements made to the SQL generated in DirectQuery mode, means that it’s now a feature worth considering in scenarios where you have too much data for SSAS Tabular’s native in-memory engine to handle or where you need to see real-time results.
Good stuff. Read the whole post, especially if (unlike me) you know a thing or two about MDX.
Gail Shaw has part 2 of her transactions series up:
Again, exactly the desired behaviour. The changes made in the outer procedure were committed, the changes in the inner procedure, the procedure where the error was thrown, were rolled back.
Used correctly, savepoints can be a powerful mechanism for managing transactions in SQL Server. Unfortunately they’re not well known and as such their use can also make code much harder for later developers to debug.
I’ve used conditional transactions fairly regularly (procedures can have calling parent procedures, or sometimes can be called on their own), but never savepoints.
Ed Elliott has created a tool to auto-name constraints in SQL Server Data Tools:
I have released a tool that will do just that, if you grab the SSDT-Dev Pack at least version 1.1 fromhttps://github.com/GoEddie/SSDT-DevPack/tree/master/release this adds a new menu to the tools menu in visual studio to name constraints. What I like to do is to go to “tools->options–>keyboard” and map an unused short-cut to the command “Tools.NameConstraints”, I used “ctrl+k + ctrl+n” so I can open a table in SSDT and just do ctrl+k and then ctrl+n and it automatically re-writes any tables in the active document that have unnamed primary keys with an appropriate name.
Jack Li on the CSS team answers an interesting question regarding “incomplete” statistics. The key point:
First of all, there is no stats3. SQL Server never stuffs in flight stats to stats blob for use during online index rebuild. Even you are under dirty read, you won’t get non-existing stats3.
Good information.
Tony Rogerson shows us how to get the fourth Saturday of the month, among other things:
You may want to find for example the date of the 4th Saturday in each month for a given year. This function came out of answering the question here: http://stackoverflow.com/questions/33694768/how-to-get-list-of-2nd-and-4th-saturday-dates-in-sql-server.
I’ve created it as a Table Valued Function so you can bind it into any query you wish.
Tony created a Table-Valued Function, which is handy but leads me to the classic User-Defined Function reminder: they tend to cause performance problems. One alternative is a dedicated date table with attributes like day of week and nth day of month.
Derik 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;
Shawn Melton shows us how to make SQLPS load a bit faster, and which comes with the obligatory warning:
WARNING: You are modifying the files at your own risk. You have been warned.
If you are not familiar with the files involved with a module, you can read more on that here. The file I found most interesting is the “SqlPsPostScript.PS1” file, located in the SQLPS module folder for the given version of SQL Server:
Check it out. Those two seconds you save add up over time.
Mike Fal shows us how to use Powershell and T-SQL to get SQL Agent job status:
This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.
Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.
SMO’s a powerful thing.
Andy Galbraith has a tale of woe and a cautionary message:
Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses. I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.
What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS! Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.
Indeed.
Kevin Feasel
2015-11-19
Query Tuning