I've never nagged someone to change an accepted answer before, but in this case I think it is justified — sorry @billinkc if it means you lose 15 internet fairy points
I see what you mean. I think the question is ambiguous though you can read it either way. Your SQL needs tweaking a bit to make it valid because level is a keyword: dbfiddle.uk/…
connect by is a lot easier to understand than recursive CTEs which I have to look up every time I use them :p (to be fair you can do more with CTEs I think)
@JackDouglas And the various DBMS have different restrictions on CTEs (if referencing the cte in the recursive part is allowed more than once or not, for example)
@AndyK Do you think you could expand the ITIL abbreviation somewhere in your post? Unless it's a common enough term in project management, in which case please ignore my request.
@AndyK I wasn't familiar with that term at all, by the way. It was easy to look up, I just thought it would be convenient for whoever is learning about project management to have the link handy. Thank you.
Msg 208, Level 16, State 1, Procedure Trigger_Procedure_Events, Line 11 Invalid object name 'EADDLLog.dbo.DDLUpdateLog'. Msg 208, Level 16, State 6, Procedure sp_WhoIsActive, Line 21 Invalid object name 'dbo.sp_WhoIsActive'.
EA being the prefix of some vendor application connecting to the server, but that is nowhere in the script i'm executing (it's really just whoisactive)
Our product is based on Microsoft SQL Server. Currently, we are using three databases, and have always deployed them on one SQL Server instance.
The three database are OLTP, OLAP, and audit. The OLAP database has massive inbound data on EOD from both OLTP and audit, using cross database queries....
@TomV this is why when speccing a machine for SQL Server you want the fastest cores available at any price, and keep the number of cores to the absolute minimum. And expensive, very fast enterprise-grade PCIe NVMe SSDs are cheap in comparison, and add a ton of ooomph.
@TomV actually every SQL Server I deal with these days is virtualized. I still don't think it's appropriate for "big" servers where you need lots of processing power. Plus you need to license all cores in the box anyway, if you have more than a single SQL Server VM.
So, even when virtualized, it still pays to go with faster, fewer cores.
@PaulWhite Did see already but thanks. Now I'm wondering if I should talk about some of the TFs that change how the histograms are interpreted. But they probably won't help so it might just confuse the issue.
@JoeObbish In my opinion virtualization adds complexity and code overhead that will reduce performance by some measurable percentage. In the case of a large single VM on a single physical machine, the only real benefit of virtualization would be machine mobility; however that can be (easily) mitigated by the various HA technologies offered by Windows and SQL Server. Perhaps there is something I'm not considering.
We tested by turning up the DTUs from 10 (lowest) to 100 (highest for S0). Query went from 30 seconds to 6 seconds. So it looks like it could just be a matter of paying more money.
@MaxVernon I think I get what you mean. It's can be unsettling at times to think about how VM is making your queries run just a little bit slower and there isn't much you can do about it.
I did have a case once where virtualization made a query run around 100X slower
That wasn't fun to troubleshoot
It does seem that the industry is moving pretty hard in that direction
@PaulWhite I keep investigating obscure problems and eventually find a blog post written by you about it. I think it's happened three times in a row now. It's very helpful but also a little frustrating in a way...
@JoeObbish I am so immensely indebted to the knowledge I've gained from @PaulWhite's posts. Its humbling to see that level of knowledge. (Thanks, Paul!)
Yeah I don't get that one either. It seems to contradict MS's message of making things really easy for developers. I suppose that they would say that developers don't need to worry about the differences between standard and enterprise...
I didn't know that MSDN could be so direct: "This topic is about the "Remote Access" feature. This is an obscure SQL Server to SQL Server communication feature that is deprecated, and you probably shouldn't be using it."
Anyone know off the top of their head, after changing the instant file initialization, is it a restart of the SQL Service for it to take affect or is it a relogin/restart event?
DECLARE @PerformeVolumeMaintenanceTasks VARCHAR(255);
SET @PerformeVolumeMaintenanceTasks = 'UNKNOWN';
DECLARE @LockPagesInMemory VARCHAR(255);
SET @LockPagesInMemory = 'UNKNOWN';
DECLARE @Res TABLE
(
[output] NVARCHAR(255) NULL
);
IF (SELECT value_in_use
FROM sys.configurations c
WHERE c.name = 'xp_cmdshell'
) = 1
BEGIN
INSERT INTO @Res
EXEC xp_cmdshell 'WHOAMI /PRIV';
IF EXISTS (SELECT 1
FROM @Res
WHERE [output] LIKE 'SeLockMemoryPrivilege%'
)
SET @LockPagesInMemory = 'ENABLED';
of course, you'll need xp_cmdshell enabled to run it :-)
> Help me with my assignment plz I have erromessage
that should really be:
> Help me with my assignment plz I haz erromessage
everyone. I've got a confusing issue with a query against an indexed view.
select top 10 * from view where type_id = 3
produces the top 10 entries in the view regardless of type_id value, while searching any other type_id value produces the correct results. Putting quotes around the 3 or removi...
Well, let me try to explain what's happening.
First question: Why returns only one row instead of three
Have a look at a simplified main query, filtered by BIL_HotelId and IVC_Id:
SELECT BIL_Id, IVC_Id, DATE_FORMAT(BIL_Date, "%Y-%m-%d") BIL_Date, BIL_Rate, BIL_Type, BIL_Quantity, IVC_Elemen...
And that BIL_Id IN (31,32,33) can be replaced with find_in_set(bil_id, ivc_elements), assuming he wants to check if the element is in that comma delimited string
d'oh. Stupid gold badge. I didn't realize it would re-open unilaterally by me. It seems to me the edit means the question no longer is similar enough to the original "duplicate". Perhaps I'm wrong?
We tested by turning up the DTUs from 10 (lowest) to 100 (highest for S0). Query went from 30 seconds to 6 seconds. So it looks like it could just be a matter of paying more money.
everyone. I've got a confusing issue with a query against an indexed view.
select top 10 * from view where type_id = 3
produces the top 10 entries in the view regardless of type_id value, while searching any other type_id value produces the correct results. Putting quotes around the 3 or removi...
Well, let me try to explain what's happening.
First question: Why returns only one row instead of three
Have a look at a simplified main query, filtered by BIL_HotelId and IVC_Id:
SELECT BIL_Id, IVC_Id, DATE_FORMAT(BIL_Date, "%Y-%m-%d") BIL_Date, BIL_Rate, BIL_Type, BIL_Quantity, IVC_Elemen...
