A Fine Slice Of SQL Server
Paul Popovich notes that certain Perfmon counters could be wrong on certain versions of Windows:
Performance Monitor uses incorrect calculation for certain types of counters in Windows 8, Windows Server 2012, Windows 7 SP1, or Windows Server 2008 R2 SP1
This only cost us a week of reviewing results.
Follow up on the link because there’s a fix available through Windows Update.
Comments closedThere is a new line in the properties of the iterator, showing the number of locally aggregated rows and that number equals 619255, that should be exactly the number of rows that is missing from the arrow connecting 2 iterators:
Gives us our perfect 12627608 rows.
Eureca!
Is there any more information on this operation?
Indeed, just right-click on the Columnstore Index Scan and select it’s properties:
This is tied to some columnstore performance improvements in SQL Server 2016.
Comments closedI have a new script available to reorganize columnstore indexes:
Note that this script requires SQL Server 2016 (or later) because the database engine team made some great changes to columnstore indexes, allowing us to use REORGANIZE to clear out deleted rows and compact row groups together, as well as its previous job of marking open delta stores as available for compression.
The code is available as a Gist for now, at least until I decide what to do with it. Comments are welcome, especially if I’m missing a major reorganize condition.
As mentioned, comments are welcome.
Comments closedChris Webb shows how to generate fixed-width files using Power Query inside Excel:
While it’s fairly common to need to load fixed-width files using Power Query or Power Query (and there’s a nice walkthrough of how to do this here), occasionally you might want to use Power Query and Excel to create a fixed-width output for another system, or maybe to create some test data. You might not want to do it often but I can imagine that when/if Power Query is integrated into SSIS this will be a slightly less obscure requirement; at the very least, this post should show you how to use a couple of M functions that are under-documented.
I don’t see this being a particularly common request, but I guess I can see some scenario in which we’re loading data into a legacy system.
Comments closedJefferson Elias describes the concept of trusted foreign keys as well as their analog:
Specifying WITH CHECK in a statement tells to SQL Server the user wants it to validate the constraint against every single row in the table, then, if successful, enable it.
In contrast, specifying WITH NOCHECK, which is the default for an existing constraint, means that the constraint is enabled but no validation has been made on it. Even if this mode is faster to run, it can lead to severe side effects on performance: SQL Server doesn’t trust the constraint as it has not validated it. We refer to such a foreign key as an « untrusted foreign key ». As a consequence, the query optimizer won’t use the constraint to do his job…
There are benefits to having trusted foreign key constraints. Check out the article for more details as well as how to fix this issue.
Comments closedJohn Martin shows that part of securing your environment includes encrypting SQL Server connections:
In order to demonstrate just how easy it can be to get hold of the information inside the TDS packets I will be using Network Monitor from Microsoft, this will capture the network packets sent and allow me to see the details of what is being sent. Other tools such as Wireshark will also provide a level of insight into what is being sent between the application and SQL Server. I have configured three Windows Server 2012 R2 systems, one with the client (SQLCMD), one with SQL Server, and finally one which will act as a router between the two subnets that each server is on. This configuration can be seen below;
Wireshark is a good friend of mine. It should be a good friend of yours, too, but only if your environment allows you to have a packet capture tool installed.
Comments closedMelissa Yu gives us an introduction to Tableau:
Do you know the difference between a blue pill and a green pill? What happens when you add a green pill to the color shelf? Pills, cards, and shelves are the foundation of Tableau visualizations. If you are new to Tableau or have been playing around with it but not quite sure how to get the view to look the way you want, we have a video for you! In this video, I’ll help you understand pills, cards, and shelves so instead of wondering “Why did Tableau do THAT?” you’ll be in better control of what you want Tableau to do.
Power BI and Reporting Services 2016 are exciting technologies, but there’s a wide world outside of Microsoft, and Tableau is a major player in the world of visualization.
Comments closedJason Brimhall digs into an error where page-level locking is disabled:
You receive the error message similar to the following:
Msg 2552, Level 16, State 1, Line 1 The index “blah” (partition 1) on table “blah_blah_blah” cannot be reorganized because page level locking is disabled
Immediately, you start double-checking yourself and verifying that it worked the previous night. You even go so far as to confirm that the same index was previously reorganized. How is it possible that it is failing now on this index. What has changed? Has something changed?
There’s an interesting troubleshooting story, but the important message is about setting up a good set of Extended Events so that you can troubleshoot these types of problems.
Comments closedMelissa Coates digs into DirectQuery mode on Tabular models:
When developing an SSAS Tabular model, you can choose one of two options for handling the underlying data:
In-Memory Mode (aka Imported Mode). Stores the data in the in-memory model, so all queries are satisfied by the data imported into the Tabular model’s storage engine. This requires the model to be processed for updated data to become available for reporting. This mode is conceptually analogous to MOLAP in SSAS multidimensional models (though the SSAS architecture differs significantly).
DirectQuery Mode. Leaves the data in the source and sends the queries to the underlying database. In this case there’s no processing and SSAS serves as a semantic model to improve the user experience. This mode is conceptually analogous to ROLAP in SSAS multidimensional models (though there are architectural / implementation differences between DirectQuery and ROLAP).
It looks like DirectQuery mode doesn’t fit all circumstances, but there are a few cases in which it makes a lot of sense.
Comments closedMicrosoft quietly announced SQL Server 2016 RC1 is available:
In SQL Server 2016 RC 1, we made enhancements to SQL Server Reporting Services, including:
Updated preview of the new web portal: The new web portal by default, and the classic Report Manager now removed. Additionally, open the Mobile Report Publisher and Report Builder from the new web portal using any modern browser.
Custom branding: Customize the web portal with your organization’s logo and colors.
KPIs and mobile reports: Click a KPI and see a view with more details, and connect KPIs and mobile reports to parameterized datasets.
Modern paginated reports: Design beautifully modern paginated reports with new, modern styles for charts, gauges, maps and other data visualizations.
It looks like Reporting Services is getting to release shape.
Comments closed