Video

can we use Service principal(SPN) credentials instead of sql user to create database scoped credentials?

Hi, I am trying to create Database Scoped Credentials for Service principal where I have given the IDENTITY = "CLIENT_ID_OF_SERVICE_PRINCIPAL" and SECRET = "SECRET_KEY_IN_SERVICE_PRINCIPAL" but when I am trying to access the database by the help of this Database Scoped Credentials, I got error message : Msg 46832, An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '1CLIENT_ID_OF_SERVICE_PRINCIPAL'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'CLIENT_ID_OF_SERVICE_PRINCIPAL'. i have check the credentials, can you please provide me help on what can I do next, thanks

Super interesting video. Advice on indexing on the internet is riddled with "common sense" and "rules of thumb", so it's very useful to see a presentation where you're not only demonstrating the claims with data; the data is also presented in such an understandable format. The premise of the video is GUID fragmentation but this video also serves as a really nice visual demonstration of the concepts of fragmentation, pages, and indexes. I've worked with SQL for a long time, but mainly on surface level. My company is not really at a point where our indexing strategy (or lack of) affects us but we have a huge blind spot on the actual workings of SQL Server and indexing and I worry that lack of knowledge is going to cause my CTO to push us to a NoSQL solution as soon as performance becomes an actual issue. I only wish the mic quality was better and that the ads were placed at natural pauses (and normalized audio levels) but I know this is an old video so maybe those things improved. Thank you!

Awesome, we'll explained.

Hi sir, You might have wrong regarding the change of colation. This will let you change the colation : (to execute in the right folder 😄) sqlservr -m -T4022 -T3659 -q"Latin1_General_CI_AS"

SQLKOVER delivers again

The challenge with dbatools, is it has so many commands, it becomes little hard to find them. This presentation gives a good overview on the module. This is an awesome presentation! Thank you so much Daniel!

Thankyou❤

You are basically right (not that I had expected anything else from you), but I still disagree with the (indirect) recommentation to use GUIDs over ID columns or natural keys. In the most databases (regardless, if it are orders, customers, products, StackOverFlow posts/comments ...) only a very small percentage of your data are hot, the rest are cold data that will almost never be touched (written) again. When you are using GUIDs as (clustered) index key, you would spread all your data over the whole table. Yes, it allows you to do a way more inserts per second as with the hotspot in the last page caused by an Identity column, but there are only a few special cases (as DWH like loadings or saving tons of sensor data of a high speed experiment) where this speed really matters. Remember, that a day has 86400 seconds, so even 100k inserts are just slightly more than 1 insert per second with a new page inserted every maybe 200 records or ~ 3 min. The reduction of fragmentation is paid with 20 or more unused space on the whole table and big / expensive rebuilds every x days / weeks (depending on the table size, but simply forward your experiment by 5 to 10 years). When you would use an ID column you may could use partition too (on some tables as orders / posts, but it would not make much sense on customer / products), which would allow you to do index rebuilds only on the last few partitions (which had a few bad page splits) and allows to use a much higher fill factor (even 100%) for the whole table. Example: you have 100k orders per day, that are usually fulfilled in a few days and will rarely be modified (by expansive) updates after a month. So you could set the fillfactor for the order table to e.g. 98 % and partition the table by the order_id creating a new partition at the first day of a month at 00:00. The orders will all go into new pages at the very end (1.15 inserts per second are no problem with todays storage 🙂). When you update the orders and add comments there may occur bad page splits (that takes a small fraction of a second), but since they are made usually by an "analog" user / delivery system etc., it will be spread over the whole day too, so still no real performance problem. Yes, you may end with a ton (50%) of fragmentation in the last partition of the table, but since this is considered as hot and contains only 1 of 60 months (=5 years) of data, it is just a small fragment of your whole table and not a real problem. Once per month you could do a rebuild on the partition of the previous month, which removes those fragmentation, without permanently wasting 20% of storage / memory with a fillfactor of 80 and without the need to rebuild the whole table every month.

The demo is superb..can we have the scripts used in the Demo please..

Thank you Eric for this great session, appreciate!

hi Ben, thanks for the great lecture. A word about the help and the function: Add-DbaAgReplica -SqlInstance sql2017b. How do I know this function works - as you stated in the minute 00:46:00 only when getting an input from the pipe like that: Get-DbaAvailabilityGroup -SqlInstance sql2017a -AvailabilityGroup SharePoint | Add-DbaAgReplica -SqlInstance sql2017b. Sure, I can look through the help of this function and see some examples, but there is no word: only with pipe input from Get-DbaAvailabilityGroup?

wow great content Thank You

Very nice and informative

I totally disagree. At 1st: Below 5 million normal-sized records, I wouldn't even think about indexing, let alone index maintenance. (Performance) Problems with GUID based indexes arise at larger tables. So those tests are as meaningless as the "best practice recommendations". At 2nd: What might be the idea behind the sorted storage of random letter-number combinations without any information? Or behind sorting by more or less meaningless numbers? Such indexes are (mostly) useless, and thus their maintenance, and thus they are a burden for the system. But you have not even begun to get to the heart of the problem. At 3rd: The last-page-hammering performance problem was solved since 2019 (OPTIMIZE_FOR_SEQUENTIAL_KEY). At 4th: You've only looked at the leaf level (otherwise you couldn't talk about the fill factor). Meaningless index fields become a problem especially in multi-level tree structures because no reasonable density and distribution can be determined without additional computational effort.

Repetitive, rudimentary, and overly wordy. In the hands of a skilled presenter, this is a 30 min. Presentation.

Thank you for this, I came to this from your answer on SO, and while your answer there was enough to make me realise the problem the information here definitely helps fill in the gaps. I'm a developer, not a DBA so maintenance is usually out of my wheelhouse but it is super useful information to have when designing new systems. I never really paid much attention to fill factor and what the true effects of reorganise. I having been going back and forth on how I should configure a DB for something that will likely eventually become a distributed system and it's amazing to think that not only will using GUIDs not impact performance negatively as the bedtime stories say, but it will likely improve the performance of the application even before horizontal scaling by performing the out of order inserts and updates! I also think that the best practice method may actually be costing one of my clients significant amounts in failover replication (full server imaging, not DB) on azure. I'm yet to confirm this but my thoughts are that these page splits are modifying the file system so many times a day unessecarily that the constant updating is causing significant unnecessary extra costs in replication bandwidth. Thank you for putting this old myth to bed!

It is not quite so. All these features have heavy limitations, or were not implemented as we might expect, or suffer from al sort of misleading declarations. I have tested or learned the majority of them and what I can say about them is pay attention to the crocodiles that are hidden behind the surface!

It is an amazing explanation , thank you so much.

Thank you very much the presentation has helped me a lot and great work!

Fantastic stuff, thanks for sharing!

Do the ads really need to be 3 times as loud as the speaker? Every time I watch a Group By video I get multiple heart attacks. Just keep the ads in, but mute the music please.

why cant we see data in tacpac file once we extract dacpac and execute it will not display any data why ?

this deserves thousands. Erland's 10-ish articles are the holy grail of tsql.

Thanks.

Fantastic session. Sort of one stop shop for multiple features explanation.

Could you please share code sources?

p͓̽r͓̽o͓̽m͓̽o͓̽s͓̽m͓̽ 😃

Interesting

Anyone wanting the code Google his name and tsql bad habits to bring up recent code repos for him and updated presentation for SQL 2019.

great topics

Great video! I definitely came away with a couple of useful tips.

The site link do not work

Thanks, this is so nice, can we use logic on dbatools commands, for example I m starting job using start-dbaAgentjob , and job got failed, so based on failed flag I want to run other command, how to achieve this

Excellent discussion... Lots of good information. Thank you

Perhaps I should have named this "Here's how you're destroying performance with the Best Practices of Index Maintenance". 😁

Thanks Gavin, I needed this! The other videos on tSQLt aren't nearly as explanatory and in-depth.

BWAAA-HAAAA-HAAAA! Everyone picks on GUIDs... As I say in my presentation... "They're not the problem... WE ARE!" Here's the link if you're interested. As you watch, also remember that a whole of our non-GUID based non-clustered indexes are just as random as GUIDs or, worse yet, they create "Random Silos". czcams.com/video/rvZwMNJxqVo/video.html I'm also working on a write-up on the subject for BOL. It's just taking a bit of time because it needs to be short, to the point, and yet entirely accurate so I'm still doing a whole lot more testing and not just for GUIDs. Shift gears back to your presentation... VERY NICELY DONE, William. Thank you for taking the time to put this together.

This is very useful video! Thank you very much!

Does this mean, there is no point in subscribing to services like Azure Synapse Analytics (Dedicated SQL Pool)/SQL Server on Azure etc., which are the services for hosting data warehouses?

Can SQL Server Audit and Extended Events Audit co - exist ( I mean having the two on the same server but for different purposes)

Whoever said ‘size doesn’t matter... was not a DBA! 👍

Can we create on-prem SQL server as external data source in Azure SQL?

If a transaction table is continuously growing very large how to manage?

Thanks for sharing the valuable info. Plus one for presenting it in easy to understand manner

This was an amazing session. Thank you for posting this. Time to dive into my servers. :)

czcams.com/video/uzTdmEQiGAk/video.html

even firewall rules, czcams.com/video/sVCivarAHqE/video.html

Slide content are partially appearing on screen, your recording screen width is too wide creating cut-off on both sides.

ERRATA NOTES: It was way too early in the morning for me when I gave this presentation (it was on the European track) and I was still a bit caffeine depraved. There are a couple of places where I misspoke and I'll list them and their corrections here as I find them. Make sure that you click on the "Read more" near the bottom of this particular post to see them all. There's not a lot but there is more than this paragraph unless you click on "Read more". TimeStamp 18:20. I say that it has to create PAGES between those two old pages and the new one. I left out the word "links". It should have been that is hast to create PAGE LINKS between those two old pages and the new one. TimeStamp 24:51. I say that it will not clear the space above the fill factor like a REORGANIZE will. I meant to say the word REBUILD instead of REORGANIZE. TimeStamp 37:49. I say that we start out at "37" (thousand implied by the graphic) and I meant "30". TimeStamp 39:49. The formula for the rows per page has an error in it. I forgot to add 2 bytes for the "Slot Array" entry to the 123 to make it 125. In other words, the 123 came from sys.dm_db_index_physical_stats but that does NOT include the 2 bytes per row for the "Slot Array" and you need to add "2" to the number returned by sys.dm_db_index_physical_stats. For this example, the formula should have been 64 (8096/(123+2) = 64.768 = |64|) where the +2 is for the 2 bytes in the slot array for each row. TimeStamp 1:03:35. I misspoke on a couple of spots on slide 66. To summarize, use BULK LOGGED OFFLINE if you can no matter the Edition of SQL Server. If your database is 24/7 and you can't do OFFLINE rebuilds AND you have the Enterprise edition, use BULK LOGGED ONLINE. If you have the Standard Edition and so can't use ONLINE, then do BULK LOGGED OFFLINE rebuilds when you do have some downtime. If you have something that prevents the use of BULK LOGGED (like AG, etc), then use the FULL RECOVERY model either offline when you have time or ONLINE if you have the Enterprise edition. Just DON'T EVER USE REORGANIZE because it'll put the screws to you in the form of pages splits all day every day. It's better to do no index maintenance than it is to do it wrong and also won't matter amyway if you're doing single row OLTP.