@PaulWhite Just observed the same plan shape but correct result if you do a calculation on the column.
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
SELECT COUNT_BIG(*) AS c1
FROM
(
SELECT T1.c1 + 0 as c1
UNION
SELECT NULL
) AS U
) AS C
OPTION (CONCAT UNION);
@MikaelEriksson Yes I tried the same thing early on (adding zero etc). I'm pretty sure the root cause is an unsafe byref/late evaluation/binding issue it's just too hard to capture.
BTW I just reproduced it in 2005.
@MikaelEriksson So my saying things like "problematic plan shape" is a simplification, hopefully not a gross one. It was a conscious decision; I have to draw the line somewhere between being intelligible and being last-level-of-detail accurate.
I was really trying to see if I could create the error without having the Distinct Sort in there (no luck so far) when I stumbled on that. I guess the sort is what is making the optimizer to use the spool in the first place.
I thought it a likely culprit to mix things up in an internal structure.
But since it does not do that with a + 0 that is probably not it.
@MikaelEriksson Partly, yes. Try a TOP (1) with ORDER BY instead of the aggregate. Also OPTION (HASH GROUP) or OPTION (QUERYRULEOFF GbAggToSort).
@MikaelEriksson There's just not enough information in showplan to narrow this down. It's a couple of layers deeper in the way expressions and column references are passed around at the execution engine code level.
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
SELECT TOP (1) c1
FROM
(
SELECT T1.c1
UNION
SELECT NULL
) AS U
) AS C
OPTION (CONCAT UNION, HASH GROUP);
Recently, a question that I answered was voted off-topic. I would like a clarification why exactly is this off-topic?
I think the question was relevant to this site. It might not have an exact answer, but imperfect information that can be gained in this situation is better than no information at...
Microsoft has fixed something that I would think impossible to fix "Incorrect choice of a nested loops join strategy causes poor query performance in SQL Server 2014" (https://support.microsoft.com/en-us/kb/3042544)
"This behavior occurs because the query optimizer chooses an incorrect nested loops join strategy when incorrect cardinality estimates or incorrect operator costing is used."
The cardinality is incorrect and the costing is incorrect and yet the optimizer now manages to choose the "correct" plan. Sounds strange to me.
what-are-best-practices-to-setup-mariadb-galera-10-0-on-shared-nfs-storage-emc
I'm not really sure how to narrow the question, as I'm basically looking for a link/site, or a white paper which documents established best practices.
I'll be happy with a rather vague bullet-point list of do/don't, ...
As the subject states, I'm interested in setting up a MariaDB Galera cluster, using a shared nfs storage to host the db data.
I've searched but couldn't find any resources with "best practices" for such a setup.
I'm not really sure how to narrow the question, as I'm basically looking for a link...
@PaulWhite Ah, no, I was reading this review: dba.stackexchange.com/review/suggested-edits/45100 and noticed that @ypercube rejected and edited the suggestion but it didn't count for him as a rejection.
tried to post it as a quotation, couldn't post a multiline message
No, I meant it didn't count towards @ypercube's rejection count. It says there, "ypercube has approved 0 edit suggestions and rejected 0 edit suggestions".
Not sure if it's a bug or it's meant to be like that.
We are applying SQL Server 2012 Service Pack 2 (SP2) to a two-node active/passive cluster, starting with the passive node (as per best practices):
How to install a Service Pack at a SQL Server 2012 Failover Instance:
Best Practices
(http://blogs.msdn.com/b/john_daskalakis/archive/2014/09/05/how...
> Most of the trees in Denver are not native. Being a high desert plain, the only trees indigenous to Denver were some cottonwoods and scrub oak that grew along the Platte river. All the others are "immigrants."
Doing some part-time consulting on the side for a friend. Their DBA, when asked why he never runs DBCC, responded with 'It interfered significantly with our processing (we know that, there are other ways to run DBCC CHECKDB besides just on the actual, live DB)... In addition, I have not seen any other signs of data corruption'
I get it, you have a 24/7 system that cant afford the performance hit but you also have a test instance. Idea, RESTORE your backups (takes care of testing your backups and making sure they're good) and run DBCC on the test instance.
What exactly can run in batch mode as of SQL Server 2014?
SQL Server 2014 adds the following to the original list of batch mode operators:
Hash Outer join (including full join)
Hash Semi Join
Hash Anti Semi Join
Union All (Concatenation only)
Scalar hash aggregate (no group by)
Batch Hash ...
At least in SQL-Server and MySQL, clustered indexes are the tables. And secondary indexes only have the PK, as a reference, no other "point" to anything.
@mmarie Yes, it seems so. From the 3rd part (link above):
> In a highly simplified view, you can think of a Vertica projection as a single level, densely packed, clustered index which stores the actual data values, is never updated in place, and has no logging.
@ypercube if you create a primary key, unique key, or foreign key, Vertica will not enforce it at load time. you can opt to not commit the load and run a function to check the uniqueness, otherwise, it will enforce at query time
it's too costly to check for duplicates during load
Ok, this is too much. The same DBA that gave me that excuse of not running DBCC CHECKDB because he hasn't seen signs of corruption also told me that because he has transaction log shipping that his backups must be valid.
@JNK I'm doing part time consulting, just helping out a colleague who needs some help getting her servers under control. I asked their DBA when the last time his backups were tested was and he said he didn't need to because he has log shipping and that covers it.
He also said part of the reason he doesn't run CHECKDB is because he 'hasnt seen any signs of corruption so far'. That and because he cant get a maintenance window. He has a test server, just restore your backups there and run DBCC on the test instance
Is there a good article that talks about the conditions of when it would be better to do a CCI vs just regular indexes? I have found stuff like this: sql-server-performance.com/2014/…
but i am looking for something more specific.
I inherited a data mart with 6 dims, 1 fact table. The fact table has 6 million rows and uses a clustered columnstore index. I don't really understand why, other than they wanted to try it. I think it would have been fine without it since the only thing that queries it is a tabular model when it is refreshed twice a day (and that is not likely to change over the next year or two).
I guess it's not hurting anything, but it just seems odd.
my extremely limited understanding of CCSI/CSI would lead me to believe that you'd not experience a tremendous gain from them ... and you'd have to introduce some index maintenance into the data store to handle anything not caught by the mythical tuple-mover
A client broke ties with their former consultants and called us, and that is what I found when i looked at their existing solution.
Normally, i wouldn't care, but this was for a company with no DBAs and no BI staff. They just needed something easy to maintain and easy to understand. I have a soap box about consultants who do things that are complicated/new that aren't appropriate for their clients based upon ability to enhance/maintain after they leave.
We are applying SQL Server 2012 Service Pack 2 (SP2) to a two-node active/passive cluster, starting with the passive node (as per best practices):
How to install a Service Pack at a SQL Server 2012 Failover Instance:
Best Practices
(http://blogs.msdn.com/b/john_daskalakis/archive/2014/09/05/how...
What exactly can run in batch mode as of SQL Server 2014?
SQL Server 2014 adds the following to the original list of batch mode operators:
Hash Outer join (including full join)
Hash Semi Join
Hash Anti Semi Join
Union All (Concatenation only)
Scalar hash aggregate (no group by)
Batch Hash ...
