December 2, 2017 at 10:37 am
Ken Wymore - Saturday, December 2, 2017 10:15 AMMaybe that would be a good site survey question? How many people have SSAS installed but don't use it, rarely use it, use it often or don't have it installed? Other than the Stairway to MDX series, I rarely see anything about SSAS or MDX come up here (granted I haven't been checking the forums actively). Maybe I am in the minority concerning its importance and need to start looking into other solutions before it is completely phased out? Hope not!
Surveys get so few responses, usually a few hundred, so not sure this helps. I don't know if MS has this, but like you, I rarely see posts, blogs, questions, notes, anything on SSAS. I think some people use it really well and often, but I think they're a small minority compared to the relational engine.
December 4, 2017 at 8:42 am
Steve Jones - SSC Editor - Friday, December 1, 2017 11:36 AMEric M Russell - Friday, December 1, 2017 7:53 AMMDS (Master Data Services) is one feature I wish Microsoft would deprecate. From a database engineering, management, and performance perspective; it simply sucks.I'd like to see some effort put into the Database Diagramming feature of SSMS. This thing hasn't changed a bit in 20 years. For example, the GUI could be more robust; providing better support for reverse engineering, cross database relationships, and also logical modeling in addition to just physical design.
I think many people wish MDS would receive improvements.
As for diagramming, I agree. I used to wish more third parties would do something here, but it's a hard problem with little commercial viability as a product.
My issue with MDS isn't lack of features but on a deeper level how the framework is engineered. The physical data model is poorly optimized and incomprehensible to understand, it's workflow is cursor based, and it doesn't scale. It needs some major refactoring.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 4, 2017 at 9:04 am
Jason A. Long - Friday, December 1, 2017 2:46 PMdrew.allen - Friday, December 1, 2017 2:37 PMJason A. Long - Friday, December 1, 2017 2:27 PMdrew.allen - Friday, December 1, 2017 2:06 PMJason A. Long - Friday, December 1, 2017 1:14 PMdrew.allen - Friday, December 1, 2017 1:07 PMJason A. Long - Friday, December 1, 2017 12:41 PMTop of my wish list...
#1 Inline Scalar UDFs.
#2 GREATEST & LEAST functions.
#3 that ability to use DISTINCT in windowed aggregates.
#4 add a "print" option to sp_executesql that will allow you see the exact code that is being executed (I'd settle for a separate sp_printsql as well).GREATEST/LEAST already exist. They're called MAX/MIN respectively.
Drew
No they don't... You can use MIN & MAX, along with an unpivot to cobble a solution together but it isn't efficient due to the fact that the unpivot effectively creates a Carteasian product. A CASE expression is more efficient but gets painful to write after 4 or 5 elements.
It sounds like you want an aggregate function that works over columns instead of rows. Aggregates are functions over sets, and columns do not form sets, because that would violate normalization.
Also, you're doing the unpivot wrong if you are getting a Cartesian product.
Drew
What I want is the same damned this that's in Oracle & MySQL.
They aren't aggregate functions. and no, they wouldn't violate any of the normal forms.
I'm talking about functions that takes a series input parameter values (like COALESCE or CHECKSUM) and outputs the greatest or least of the supplied values.As far as me "doing it wrong" please, show me how to to it correctly... I have yet to find a way of unpivoting multiple columns without increasing the number of rows.
There is a difference between increasing the number of rows and a cartesian product. An unpivot is linear, but a cartesian product is geometric.
Drew
If I unpivot 5 columns, it has the same impact, in terms of the numbers of rows being output in the result set, as doing a cross join to a 5 row table. How is that not a Cartesian product?
Because the number of columns in an unpivot tends to be fairly static, whereas the number of rows in a table participating in a cross join tends to be fairly fluid. The unpivot is only affected by the growth of one table, but the cross join is affected by growth in two tables. That's what I mean by the unpivot being linear vs. the cross join being geometric. 5n is not the same order as n^2 just because they return the same results when n=5.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 4, 2017 at 1:51 pm
Jason A. Long - Friday, December 1, 2017 2:46 PMIf I unpivot 5 columns, it has the same impact, in terms of the numbers of rows being output in the result set, as doing a cross join to a 5 row table. How is that not a Cartesian product?
The main difference isn't in the number of rows output, it's in the number of reads. Here is a test setup.
DECLARE @t TABLE (
a TINYINT
)
DECLARE @r TABLE(
a TINYINT,
b TINYINT,
c TINYINT,
d TINYINT,
e TINYINT
)
INSERT @r
VALUES(1, 2, 3, 4, 5)
INSERT @t
VALUES(1), ( 2), ( 3), ( 4), ( 5)
SET STATISTICS IO, TIME ON;
SELECT u.*
FROM @r
CROSS APPLY (
VALUES(a), (b), (c), (d), (e)
) u(a)
SELECT t.*
FROM @r
CROSS JOIN @t AS t
SET STATISTICS IO, TIME OFF
And here are the results:/* CROSS APPLY (UNPIVOT) */
Table '#B5588A48'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
/* CROSS JOIN */
Table '#B5588A48'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B464660F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Notice that the UNPIVOT has one scan and one logical read of the base table, but the CROSS JOIN has two scans (one on each table) and SIX logical reads (five on the base table and one on the other), but they both produce exactly the same results.
Is that enough to convince you that an unpivot is not the same as a cross join?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 4, 2017 at 2:51 pm
December 4, 2017 at 3:22 pm
Jason A. Long - Monday, December 4, 2017 2:51 PMNo... SQL Server is able to choose how it executes the query internally. That has nothing to do with the logical operation.
All you did was prove my point... Logically, they are the same because the produce the same the same physical result.
My point, all along, is that there is a cost incurred when unpivioting... And that is the cost I'd like to avoid by having actual GREATEST & LEAST functions.
Yes, SQL Server is able to choose how it executes the query internally, and it chose to execute the two differently. You can plainly see this in the number of table scans and logical reads based on this very small sample.
1 logical read does not equal 6 logical reads. 1 table scan does not equal 2 table scans. A constant scan does not equal a table scan (from the actual execution plan--not included). The two queries are physically different.
You're also wrong that it has nothing to do with the logical operation. The internal execution has to return the same results as the logical operation, so the internal query is tightly constrained by the logical operation. It has everything to do with the logical operation.
And just because two queries produce the same result for one single instance, does not make them logically equivalent. If I add a sixth row to the second table (which does not change the queries), the two queries will no longer produce the same exact results. The CROSS APPLY (UNPIVOT) will still only produce five rows while the CROSS JOIN will produce six. The two are not logically equivalent.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 6, 2017 at 1:45 pm
In SSMS, when viewing the 'Execution Plan' tab of the result grid and hovering over a plan operator or row pipe, it would be nice if row counts were formatted with commas. So, for example, instead of 211392072 it would show as 211,392,072, and also the row counts could be displayed above the pipes without having to hover over. SQL Sentry's Plan Explorer already does this, but it would such a small and useful thing for SSMS to do that too.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 1, 2023 at 8:19 am
Looking back at this after 5 years....
The thrust of The SQL Hall of Shame article was
The former I find are the most frustrating. It's like someone presented the golden goose and the response was "wring its neck, stick it in the freezer and maybe I'll cook it later".
I should like to add fulltext search as an under-developed feature. I have seen so many ElasticSearch implementations that rely on extracting data from DBs, all to provide barely a minor step up from fulltext search. For the sake of some TLC we have to maintain and secure an ElasticSearch cluster, its data and everything that entails.
November 1, 2023 at 4:06 pm
It would be nice to have the humble PRINT statement support %s and %i arguments similar to RAISERROR and also support the NOWAIT and LOG options.
Also, it would be nice for SSIS to have a first class PowerShell script task with the same level of support as C# and VB.NET scripts. Yes, we currently can use an ExecuteProcess task to shell out to powershell.exe and pass the name of a script file and arguments, but that's just clunky.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 1, 2023 at 5:04 pm
You noted replication in your article about features that need attention. This exactly true. I have used transactional replication for many years, but the limitation that only tables with primary keys can be replicated is a major stumbling block to using a replicated database should the primary fail. In my work, I have replicated Sql Server databases that are part of a 3rd party apps. There are instances where 1 out of 4 of the tables do not have a primary key. In many cases this is poor design but the issue remains that this scenario negates the utility of replicating a database. I think SQL Server should mimic Oracles methodology in that when a primary key is not present, a pseudo key is derived using all the columns of a given table. This would then allow all tables to be replicated which would mean that a replicated database would be a copy of the primary and usable as such should the primary fail.
November 2, 2023 at 1:50 pm
Extended Events!
I know, I know. It's sometimes all I talk about.
The issue is simple. Extended Events, the core underlying architecture, is pretty amazing and clearly used by all the different development streams as we see more and more functionality released.
HOWEVER!!!
The user experience isn't what it should be. Yes, I know, I advocate for Extended Events and I teach about them, a lot. I know I'm crazy, but I'm not stupid. The GUI is hard to use and bit buggy. It also doesn't scale well at all. Better mechanisms for managing events and, especially, consuming the output could be developed. Heck, marry Extended Events with Perfmon Counters and the only thing that Trace/Profiler can do that Extended Events cannot will be eliminated.
Fixing the user experience will go a long way to getting more people to take a second look and adopt Extended Events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2023 at 1:55 pm
Just realized this is a six year old post. I still stand by what I said though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2023 at 5:46 pm
I agree, Grant. Replication is critical to my company, and there needs to be better ways of handling creating replication for large DB's (11TB, for example). It's painful to set up, although it really just works very well if nothing changes. It would be useful, as well, to have better ways of troubleshooting, although the scripts that I've come up with over the years do the job now.
November 2, 2023 at 7:34 pm
I, too, am still disappointing in replication. It's far too brittle in many cases, and the tooling hasn't evolved enough to make it easier and better for admins to deal with issues.
FTS is far behind elasticsearch/lucerne, and there's no reason for that. MS Research is world-class and they should be helping produce something that works better.
XEvents, the XEvents profiler is better, but still a mess as a UI. Hire some UX people and do better.
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply