a.) "Select *" - YEARS AGO: Developing VB6 (using ODBC, DAO, OLEDB, etc.) it was said that it´s better/faster to use "SELECT *" because database just returned a cursor (dep. on Recordset-Type). b.) "more sort space required" - I let clients use their CPU-Power to sort (defining query with SORT also gives me warnings). PS: always searched for this kind of deeper information on SQL Server! Lucky to have found it! appreciate!
I heard your bit about OPTION RECOMPILE being slapped everywhere and it made me lol. Unfortunately it also reminded me of one of my colleagues, who insists on putting SET TRANSACTION ISOLATION LEVEL REPEATABLE READ at the top of every sproc reading from the DB and SET TRANSACTION ISOLATION LEVEL SERIALIZABLE at the top of every sproc writing to the DB
Рік тому
In a datawarehouse schemas are quite useful to limit access for certain reporting tools or exports.
Thank you for these videos. I'm currently rebuilding a query that parsed XML to a table variable. I did not think to check that logic vs temp tables. Thank you very much good sir
I agree with most of what you say, and I learned a lot from you. Yet, I do not totally agree about triggers though. Triggers are life savers, even at the cost of performance when well and correctly used. But they should be built from the bottom up to be effective. They should be an integral part of the total solution, and not just an 'add-on' or 'quick-fix'. Regarding the example you gave about triggers means the whole database logic is incorrect since its inception. Unlike what people think, the database is the heart of any data system, in the end, we all use and communicate with data. 'Code first' is a bad example of data structuring. That new trend of two weeks/iteration (DevOps/Agile/Stuff) is a kill for building a sound and robust system, as you are burning steps and skipping security and performance. Finally, as you said earlier, whenever I am interviewing someone, I also tend to find out how they map their web application to the database, and guess what, 99% are use 'sa' user and I guess their password is 'sa' too :)
It's not only the amount of columns using select *, but also what's in the columns. Once had a guy ask why his qury was slow with only 3 columns and 3 records. After some checking, i found that he had 1 blob column, with isos, so he read only 3 records, but also on average 3GB per record. So still love getting only the columns you need.
What about taking those Table Variables and putting them into a bunch of 'WITH' nested CTEs contained within a Multi-Statement Table Function? 🤔 Orrrr...should I just chuck those tables into a view orrrr.....would I just do better using temp tables from the SP itself?
We use DACPACs to deploy on a daily basis as part of our DevOps and BIOps process and we have not seen the issue of tables being deleted and recreated as described here. We do however notice that DACPACs are very picky and the slightest difference in definition will trigger a rebuild of the object (always block if data loss might occur). We do work on SQLServer 2016+. What we do is build our solution (even for BI) in Visual Studio and always deploy from the project build, making sure that what we do is consistent over time. We try to do EVERYTHING in the project, not in SSMS. This is probably why we don't have issues compared to other teams we know who have had such issues but they work mostly in SSMS and just archive SQL scripts and DACPACs are the exception for them. Will investigate further.
We get the varchar(max) for ALL and no primary keys thing all the time. I remember a developer arguing that a UNION ALL was faster than joining tables... maybe its just me :)
Oh I'm all about the DACPACs. I wish they'd make them suck less though. That import wizard needs all the SqlPackage options. Other than DACPACs, what is the "best" way to make sure your production database is in sync with your idea of what it "should" look like?
hi Brent, great talk as always, however I have an off topic question, which mic are you using? Its quality is awesome, want to pick one for my online meetings.
As far as I'm concerned, unless you need really large storage, SSDs are better for data, not just for tempdb. Even cheaper enterprise SATA SSD is better than HDD. Even RAID-1/10 consumer SSD in my experience tends to be faster and safer than HDD.
The issue with EF is again people not knowing how to write good EF so you are in the same situation. Things like making them virtual and then making multiple calls when one would do, including just the columns you need. I have seen so may badly written EF queries that would be so much cleaner done in just SQL, so in my experience EF creates a mases of rubbish over complicated queries. If people are using EF then they should really know SQL. A strange divide you are talking about as a developer we do all the layers, many companies wont have a dba.If devs dont know SQL then maybe they just shouldn't even touch ORM's!
This is just my opinion, but I think if a developer isn't trained on either T-SQL or EF, but they're using .NET to build their app, then they'll build a better app, faster, by just trying to learn one tool (EF). I wish everybody had an unlimited amount of training time to get to know every one of their tools, but that's just not realistic.
EF/ORMs is a nightmare at times. My job is performance tuning across the whole system spectrum from hardware, apps and SQL etc. for the last 30 years and EF has caused so many problems in anything above a simple database that I give warnings all the time about making sure the developers using it know SQL also. Many don't even know SQL is a maths/set based language and that just applying standard structured programming techniques gets you into all sorts of problems. There is the possibility of being able to call SPs from within EF which can get you around some tricky SQL issues (e.g. compile timeouts due to crazy large EF generated SQL) or getting the devs to split up the EF code to make the gen SQL smaller and avoid the timeouts. The lack of a good DBAs guidance is definitely the cause of a lot of EF problems.
@@BrentOzarUnlimited I have actually seen whole projects die on the alter of EF to such an extent that they were totally rewritten within 2 years - it's performance was such a mess.
But you can't learn EF without understanding what is actually happening in the background.. I am currently also struggling with EFs performance. But not because EF writes bad queries, but because I am requesting data that takes a long time to be assembled and delivered. I am now beginning to understand performance tuning, and there are a lot of things you can, do outside of EF, to improve performance. Also a lot of your points apply to EF and can be implemented there aswell
There are (almost only) two reasonable reasons for table variables: a) you are using BEGIN TRANSACTION / COMMIT or ROLLBACK and want to prevent some logging stuff from being rolled back -> write it into a @table_variable b) you need to pass stuff from one procedure to another -> create a table variable type and use it a parameter. On the other hand you could either use a regular (in memory schema only) table for this purpose too. Or even a string list, if you just need to pass a bunch of id's.
Hi Brent, great video! I have a question about indexes on new tables: You said that you create only indexes on PK, right? But how about the indexes on the other side of the foreign keys, don't you create them when you create the FK? Thanks in advance!
@@TheBrentOzar you say they would create a new database and copy everything to it, guess it was a slip of the tongue. Either way, never had an issue with tables being dropped either 🤔
@@MiningForPies oh yeah, for serious deployment changes, yeah. You might just be doing more simplistic changes, like only adding columns rather than refactoring or removing them.
a.) "Select *" - YEARS AGO: Developing VB6 (using ODBC, DAO, OLEDB, etc.) it was said that it´s better/faster to use "SELECT *" because database just returned a cursor (dep. on Recordset-Type).
b.) "more sort space required" - I let clients use their CPU-Power to sort (defining query with SORT also gives me warnings).
PS: always searched for this kind of deeper information on SQL Server! Lucky to have found it! appreciate!
I heard your bit about OPTION RECOMPILE being slapped everywhere and it made me lol. Unfortunately it also reminded me of one of my colleagues, who insists on putting SET TRANSACTION ISOLATION LEVEL REPEATABLE READ at the top of every sproc reading from the DB and SET TRANSACTION ISOLATION LEVEL SERIALIZABLE at the top of every sproc writing to the DB
In a datawarehouse schemas are quite useful to limit access for certain reporting tools or exports.
Thank you for these videos. I'm currently rebuilding a query that parsed XML to a table variable. I did not think to check that logic vs temp tables. Thank you very much good sir
You are welcome!
DJ Khaled: "Those are nice columns you got there. Would be a shame if someone added ANOTHER ONE!"
I agree with most of what you say, and I learned a lot from you. Yet, I do not totally agree about triggers though. Triggers are life savers, even at the cost of performance when well and correctly used. But they should be built from the bottom up to be effective. They should be an integral part of the total solution, and not just an 'add-on' or 'quick-fix'. Regarding the example you gave about triggers means the whole database logic is incorrect since its inception.
Unlike what people think, the database is the heart of any data system, in the end, we all use and communicate with data. 'Code first' is a bad example of data structuring. That new trend of two weeks/iteration (DevOps/Agile/Stuff) is a kill for building a sound and robust system, as you are burning steps and skipping security and performance. Finally, as you said earlier, whenever I am interviewing someone, I also tend to find out how they map their web application to the database, and guess what, 99% are use 'sa' user and I guess their password is 'sa' too :)
It's not only the amount of columns using select *, but also what's in the columns. Once had a guy ask why his qury was slow with only 3 columns and 3 records. After some checking, i found that he had 1 blob column, with isos, so he read only 3 records, but also on average 3GB per record. So still love getting only the columns you need.
Having watched this, I have greater respect for my DBA
awww, thanks!
What about taking those Table Variables and putting them into a bunch of 'WITH' nested CTEs contained within a Multi-Statement Table Function? 🤔
Orrrr...should I just chuck those tables into a view
orrrr.....would I just do better using temp tables from the SP itself?
We use DACPACs to deploy on a daily basis as part of our DevOps and BIOps process and we have not seen the issue of tables being deleted and recreated as described here. We do however notice that DACPACs are very picky and the slightest difference in definition will trigger a rebuild of the object (always block if data loss might occur). We do work on SQLServer 2016+.
What we do is build our solution (even for BI) in Visual Studio and always deploy from the project build, making sure that what we do is consistent over time. We try to do EVERYTHING in the project, not in SSMS. This is probably why we don't have issues compared to other teams we know who have had such issues but they work mostly in SSMS and just archive SQL scripts and DACPACs are the exception for them.
Will investigate further.
I totatly agree. We've be using SSDT for database development in our projects with great results.
@@cytwc Use a publish profile and add IgnoreColumnOrder=True or pass in /p:IgnoreColumnOrder=True
@@cytwc never had an issue using them, never had a table drop.
Who is another C# Developer that likes T-SQL optimization but without being insane on it ?
What kind of developer doesn’t get insane about some things?
We get the varchar(max) for ALL and no primary keys thing all the time. I remember a developer arguing that a UNION ALL was faster than joining tables... maybe its just me :)
Oh I'm all about the DACPACs. I wish they'd make them suck less though. That import wizard needs all the SqlPackage options.
Other than DACPACs, what is the "best" way to make sure your production database is in sync with your idea of what it "should" look like?
For general questions, your best bet is a Q&A site like dba.stackexchange.com.
hey-hey...a fellow 'The Prodigy' fan...nice
hi Brent, great talk as always, however I have an off topic question, which mic are you using? Its quality is awesome, want to pick one for my online meetings.
Thanks! It's a DPA d:fine broadcaster headset. (Beware: they're about $1,000.)
@@TheBrentOzar Kind of pops most of the time though. What happened? You lost your pop filter for it or you don't like how it looks with it on?
@@PaulSebastianM I totally forgot about it, actually! Should throw that back on. Thanks for reminding me!
As far as I'm concerned, unless you need really large storage, SSDs are better for data, not just for tempdb. Even cheaper enterprise SATA SSD is better than HDD. Even RAID-1/10 consumer SSD in my experience tends to be faster and safer than HDD.
Awesome talk!
The issue with EF is again people not knowing how to write good EF so you are in the same situation. Things like making them virtual and then making multiple calls when one would do, including just the columns you need. I have seen so may badly written EF queries that would be so much cleaner done in just SQL, so in my experience EF creates a mases of rubbish over complicated queries. If people are using EF then they should really know SQL. A strange divide you are talking about as a developer we do all the layers, many companies wont have a dba.If devs dont know SQL then maybe they just shouldn't even touch ORM's!
This is just my opinion, but I think if a developer isn't trained on either T-SQL or EF, but they're using .NET to build their app, then they'll build a better app, faster, by just trying to learn one tool (EF).
I wish everybody had an unlimited amount of training time to get to know every one of their tools, but that's just not realistic.
EF/ORMs is a nightmare at times. My job is performance tuning across the whole system spectrum from hardware, apps and SQL etc. for the last 30 years and EF has caused so many problems in anything above a simple database that I give warnings all the time about making sure the developers using it know SQL also. Many don't even know SQL is a maths/set based language and that just applying standard structured programming techniques gets you into all sorts of problems. There is the possibility of being able to call SPs from within EF which can get you around some tricky SQL issues (e.g. compile timeouts due to crazy large EF generated SQL) or getting the devs to split up the EF code to make the gen SQL smaller and avoid the timeouts. The lack of a good DBAs guidance is definitely the cause of a lot of EF problems.
@@BrentOzarUnlimited I have actually seen whole projects die on the alter of EF to such an extent that they were totally rewritten within 2 years - it's performance was such a mess.
But you can't learn EF without understanding what is actually happening in the background..
I am currently also struggling with EFs performance. But not because EF writes bad queries, but because I am requesting data that takes a long time to be assembled and delivered.
I am now beginning to understand performance tuning, and there are a lot of things you can, do outside of EF, to improve performance. Also a lot of your points apply to EF and can be implemented there aswell
Fantastic insight! Thanks for sharing
great ep
In what case would you consider table variable a reasonable solution?
There are (almost only) two reasonable reasons for table variables:
a) you are using BEGIN TRANSACTION / COMMIT or ROLLBACK and want to prevent some logging stuff from being rolled back -> write it into a @table_variable
b) you need to pass stuff from one procedure to another -> create a table variable type and use it a parameter. On the other hand you could either use a regular (in memory schema only) table for this purpose too. Or even a string list, if you just need to pass a bunch of id's.
Did I miss the discussion about how bad UDFs are? 🤔
Hi Brent, great video! I have a question about indexes on new tables: You said that you create only indexes on PK, right? But how about the indexes on the other side of the foreign keys, don't you create them when you create the FK? Thanks in advance!
Luis - I don't do Q&A here in UA-cam comments. For Q&A, feel free to join during one of my Office Hours sessions. Thanks!
I’m using dacpacs in production, never needed to drop a database to update 🤔
I don't believe I said drop a database - I said drop a *table*. Big difference. Try refactoring two columns into one (or vice versa), for example.
@@TheBrentOzar you say they would create a new database and copy everything to it, guess it was a slip of the tongue. Either way, never had an issue with tables being dropped either 🤔
@@MiningForPies oh yeah, for serious deployment changes, yeah. You might just be doing more simplistic changes, like only adding columns rather than refactoring or removing them.
What if identity col expired ? Reached its limit
I wouldn't say that's a top mistake - it's fairly easy to fix by starting again with negative numbers, and then switching to bigints.
out of curiosity why do you wear a cap?
Because I want to.
still a great video. "I'm a butthole" hahha
Nice video
Radio Shack CAT!
I have 8 10GB temp dbs :)