Our DW gets hammered by BAs all the time. They don't write very good queries sometimes. It's grossly overpowered but it withstands some pretty nasty abuse.
I struggle to fully keep up with our main production server's needs as I'm the only SQL DBA. It's nice that it can color in the corner by itself without me having to watch it constantly
@KrisGruttemeyer A company I used to work for thought EF was the way forward for a new web app a couple of years ago. They're now going through and removing it all.
Table-Valued parameters always have an implicit value of an empty table. So you can actually call that procedure without any parameters and it would execute but the table would be empty.
So it doesn't really make sense to label a table-value parameter with a default value. Remove the "=null", ch...
@PaulWhite yes, that's what i'm asking. because i'm being asked and it makes me curious. 75% of our queries early terminate optimization because of "timeout"
@swasheck It's a protection mechanism to avoid spending too long on optimization. Designed behaviour and not usually a bad thing. 75% is unusually high. Are you submitting queries with lots of joins and aggregations, but relatively low cost overall?
The "timeout value" varies. It's not a time quantity; it relates to the number of tasks (steps) the optimizer sets itself as a limit. Usually it is related to the best cost it found in a previous round of optimization activity.
Yeah or just do a count of the pattern "LEFT OUTER JOIN" in the query text, and the optimizer should just produce a single warning icon with a middle finger
@swasheck Then I wouldn't be concerned, unless the cost estimations are ridiculously low. Even then, I'd look to fix the underlying problem rather than worrying about TimeOut, which you can't influence anyway.
I got up around 530 this morning and got on my surface to do my morning sql readings and almost spat out my coffee when I hit his site. I think the phrase 'WHY GOD WHY?' came out of my mouth.
I was at the game when Carolina won the Stanley Cup in 2006. Had season tix for 4 years after that. Those 4 years were mired by disappointment. Things have not improved and I can't in good conscience become a Rangers fan jsut because I live in NY
@MikeFal I thought about that. I was born in Manhasset, Long Island so it seemed like a natural choice. Before NC had a team I was a huge Avs fan, I played goalie for 16 years and Roy was my idol).
We have an ETL process that is going to push lots of data from server A to B. I remember a DBA at a former client talk about how they set up dedicated NICs with route pathing something or other so the two servers can communicate directly.
If I'm seeing ASYNC_NETWORK_IO as my primary wait type while this runs, it would something like that would be a decent approach. Anyone ever done something like this? Thoughts, experience, etc?
Yes I've seen that wait often with really slow client consumption, and it had nothing to do with network at all, but SQL Server can't know the difference.
license: Isn't it a rule if you have a server built for Enterprise but only license it for Standard that you are violating the license? Seem to recall that was written somewhere.
Excellent points. And yes, 7 indexes on the target table, clustered on non sequential guid. Nevermind. If anyone wants a stock tip, buy Gillette. Someone's in the market for razor blades
@ShawnMelton No, I don't see how that is violating anything, unless SQL Server is using more cores than you've licensed.
They can't really prevent you from putting 10 instances of Standard on an overpowered server.
These are neither lawyer nor licensing rep answers, just my opinions, so please don't make any licensing decisions based on them - really @swasheck is right, you need to talk to your account rep.
I just seem to recall reading something that a SQL Server Standard instance is going to "see" 32 processes and 255GB of RAM and if you are using a server that is not fully licensed for the CPU count it is a violation.
This is a client server I'm reviewing that just brought up the thought but I can't recall where I read it from
For CAL I believe it is up to SQL Server to only use however many cores and memory is the limit. Imagine if SQL Server Express was only allowed on machines with 1 core and 1GB of RAM?
The CAL-based Enterprise (if you're grandfathered by SA) in 2012/2014 automatically limits to 20 cores during startup. This is a pain and actually causes severe perf issues on high-end NUMA machines.
I think it will be different than that. If you have 4 processors with 8 cores each, and you license for 16 cores, SQL Server will use the first two processors and leave the other two alone.
(I haven't tested this - this is merely based on my observations of how they enforced the 20-core limit in Enterprise CAL.)
Could anyone tell me what's wrong with this command? sqlcmd -E -S REESE-SERVER\OMSQL -Q "BACKUP DATA OMSQL FROM DISK=C:\Users\Reese\Desktop\backup.bak"
I keep getting "incorrect syntax near the word from"
If you have Standard Edition core-based licensing, I'm pretty sure you need to license all the cores that are physically present - you can't get out of licensing them by using CPU affinity or even the BIOS. You would need to physically remove them. Last I checked directly, anyway, that was the rule. So buy more licenses or procure a lesser-powered machine.
Is there any difference (Other than storage since includes are at the leaf level) in having indexes like so: Field1, Field2, INCLUDE Field 3 versus Field1, Field2, Field3.
why oh why do we delete and insert 350k+ rows into multiple tables, multiple times on a daily basis and not want the transaction log to grow exponentially each time so we can ship it?
My Google-Fu found this link: http://datazulu.com/blog/post/table-valued-parameters-and-null.aspx
