A Fine Slice Of SQL Server
Kenneth Neilsen alerts us to a new Power BI feature:
Yesterday the Power BI team released a new version of Power BI, which have included the most wanted feature ever.
The ability to share your reports outside your organisation, and easily do that. The feature was the most upvoted on the Power BI forum, and it show very clearly that Microsoft and the Power BI team is listening to the end users.
Very cool.
Comments closedThat’s not good. I suspected this was because of the format of the file, so I added a row terminator.
BULK insert MyTable
from ‘C:\SampleFiles\input.txt’
with ( ROWTERMINATOR = ‘\r’)That didn’t help. I suspected this was because of the terminators for some reason. I also tried the newline (\n) terminator, and both, but nothing worked.
Since I was worried about formatting, I decided to look at the file. My first choice here is XVI32, and when I opened the file, I could see that only a line feed (0x0A) was used.
Little annoyances like this make me more appreciative of Integration Services (and its mess of little annoyances…).
Comments closedAaron Bertrand talks about “runaway” queries:
Sometimes people will start a query, wait five seconds, and then declare that this must be a runaway query. Sometimes a query’s runtime can vary based on other things going on in the system, so four seconds on one run and six seconds on another is not necessarily “running forever.” Be sure that you’ve given a query adequate time to start returning results before giving up on it, and remember that Management Studio might seem “stuck” before it starts to render any grid results, especially if the resultset is large. If you feel you’ve waited a reasonable amount of time, and you’ve tried both Results to Grid and Results to Text, then…
This is a big question and can take years of experience to get correct. Aaron’s post is introductory-level on purpose and does a great job of answering the initial “what are some things I can try?” question after you determine that yes, there is a problem.
Comments closedI quickly honed into the fact that the bad query was doing a lazy spool with 6 BILLION rows versus the 229 million in the good query. However, my friend who was looking at Management Studio was asking where I was getting those numbers. This is what the XML from the original plan showed:
<RelOp AvgRowSize=”51″ EstimateCPU=”0.00889634″ EstimateIO=”0.01″ EstimateRebinds=”0″ EstimateRewinds=”139581″ EstimatedExecutionMode=”Row” EstimateRows=”48868″
LogicalOp=”Lazy Spool” NodeId=”55″ Parallel=”true” PhysicalOp=”Table Spool” EstimatedTotalSubtreeCost=”1242.86″>
This is a helpful feature in scenarios like this, where operator weight is skewed because it only shows a single run but in reality happens more than once.
Comments closedYeah, that’s a DAX-powered, Power BI dashboard, right here in our website – a website that runs on WordPress, which is Linux for crying out loud. Don’t know what Linux is? No worries, just translate it as “there’s zero Microsoft software behind PowerPivotPro.com, and yet – BAM! Power BI, right here!”
And the dashboard in question is a near-real-time view of the traffic on this very site! Check back in an hour and you will be able to “see” yourself on the map (especially easy if you use one of the “rarer” browsers.)
Check out the technical walkthrough if you’re interested in doing something similar yourself.
Comments closedTara Kizer shows how to shrink a recalcitrant tempdb:
I came across this solution recently when I had to shrink tempdb. I tried shrinking each of the 8 data files plus CHECKPOINTs, repeatedly. It would not budge. I almost threw in the towel and emailed out that the space issue would be fixed during our next patching window, but then I found David Levy’s reply. DBCC FREEPROCCACHE worked like a charm.
Word of warning: understand what FREEPROCCACHE does before running it. In an emergency like the scenario Tara describes, the benefit outweighs the cost, but do be aware that there is a cost.
Comments closedNiko Neugebauer found a “compression delay” option on columnstore indexes in SQL Server 2016 CTP 3.2:
In SQL Server 2016 the OLTP Systems have received a significant improvement – support for the Columnstore Indexes (disk-based Nonclustered Columnstore & In-memory based Clustered Columnstore).
In both cases we have as the base the underlying OLTP-style table, with a Delta-Store object (or Tail Row Group for InMemory tables), that will hold the new data being inserted or updated by the final users. The data that is being frequently updated in OLTP-style systems is called Hot Data. The data that just being inserted into your table is definitely a Hot Data.
The important moment for the table is when the data becomes Cold or mostly infrequently read-accessed, and meaning that it can be compressed into Columnstore format.
This does seem interesting and can be very helpful in using columnstore indexes across different data patterns.
Comments closedJames Serra has a post on scaling Azure SQL Database:
Horizontal scaling refers to adding or removing databases in order to adjust capacity or overall performance. This is also called “scaling out”. Sharding, in which data is partitioned across a collection of identically structured databases, is a common way to implement horizontal scaling.
Vertical scaling refers to increasing or decreasing the performance level of an individual database—this is also known as “scaling up.”
It’s not free and application changes might be required (especially for horizontal scaling), but scaling with Azure SQL Database is pretty straightforward.
Comments closedCTP 3.3 is now generally available:
In SQL Server 2016 CTP 3.3, available for download or in an Azure VM today, some of the key improvements include:
Continued enhancement of Stretch Database: Stretch Database allows you to stretch operational tables in a secure manner into Azure for cost-effective historic data availability. CTP 3.3 includes multiple improvements to Stretch Database, including Azure Stretch database edition preview with support for up to 60TB, Point-in-time restore and geo-failover support.
Enhancements to In-Memory OLTP: In-Memory OLTP, which dramatically improves transaction processing performance, has added support in CTP 3.3.
Enhancements to Analysis Services DirectQuery models: Analysis Services Tabular Models running in DirectQuery mode now also allows us of DAX filters when defining roles and creation of calculated columns.
Enhancements to the new Reporting Services web portal: An updated preview of the new web portal now enables you to add the KPIs and reports you use to your Favorites, to create and edit shared data sources for your KPIs and reports, and to perform other management tasks.
Admittedly, none of those strikes me as compelling “must-download” reasons but the technical overview does have some more details.
Comments closedMichael Swart shows how reversing index sort order can expose invalid assumptions in code:
Remember that this is an application problem and is not a SQL problem. We only get into trouble when applications (or people) expect results to be sorted when they’re not. So unless you have a tiny application, or a huge amount of discipline, it’s likely that there is some part of your application that assumes sorted results when it shouldn’t.
Here’s a method I used that attempts to identify such areas, exposing those assumptions. It involves reversing indexes.
It’s an interesting idea to try out in a dev environment.
Comments closed