SQL Server & BI Blog by Andreas Wolter

Thank you, S.E. Btw: this Blog is actually "closed". I have moved to http://andreas-wolter.com/en/blog/ Andreas

Hi Michael, I saw that. And only today I found time to do the repro - successfully, indeed. See my complete answer at: http://andreas-wolter.com/en/sql-server-database-ownership-survey-results-recommendations

Hello Michael. First of all: My blog is now officially at http://andreas-wolter.com/en/blog/ You are welcome to post or even re-post your comment there. As of now to me this is very odd. Maybe I am missing something, but this is what you did: Create WindowsLogin (disabled in Domain) Alter database set authorization = thatLogin ->Login disappeared I have never seen that, but I will try a repro and get back to you, preferably on my new blog.

PS: Here the link, just stumbled over it: http://markread.net/2014/05/04/how-to-change-the-owner-of-a-secondary-replica-database-to-sa/

Hi Andreas, in meiner Anfangszeit als Konferenzbesucher lag der Fokus darauf, relativ günstig viel Wissen abzugreifen bzw. Neuheiten oder neue Ideen abzufangen. Dies ist zwar immer noch eine Antriebsfeder, aber generell mit den diversen Leuten zusammenzukommen, alte und neue Gesichter (wieder) zu sehen ist für mich ebenso Motivation. Darüber hinaus kann man auf sehr direktem Wege sich mit anderen Leuten bzgl. Problemstellungen, interessanten Projekten und dergleichen austauschen. Und das bietet meines Erachtens einen richtigen Mehrwert. Gruß Dirk

PS: I just added a reference to "included columns" and found a small error in rule No10 which I corrected

Hi Adrian Thank you for your helpful feedback and additions. Yes, included columns remove the limit of 32 columns in a nonclustered index. But “key columns” are still only 32 allowed. I will see if I can at least add a small note. Of course included columns are a very important feature. Similar goes for partitions. Actually the structure will be extended and be rather “multiple b-tree” indexes instead of a completely new structure. That was the reason (besides space) to leave them out. Probably it will make sense to have a second sheet “extension” with those let’s say advanced features. Spatial as well as XML indexes are left out on purpose. That would definitely be a call for a second sheet/page. Especially for spatial indexes there will be several terms that are not even slightly comparable to those listed here. Hence my comment “Generally, the latter are all still based on the page-based b-Tree indexes, but are significantly more restricted due to the data types partially based on CLR.” If I see huge feedback asking for them I will consider including them. So far from my field experience they are a niche topic. (Although I personally love the spatial data functionalities personally very much). Yes, clustered primary keys are still a requirement for XML as well as spatial indexes. Again, thanks for your valuable feedback. I will integrate some of your notes for sure. It just might take a bit due to my workload. Andreas

Hi Andreas, first of all good job for putting together all the info about indexes, and this in a clear and concise form. What I’m missing from the cheat-sheet are the nonclustered indexes with included columns, which if I can remember correctly can support up to 1,023 columns in the INCLUDE clause. I consider them as important because remove the 900 bytes/32 columns key size restriction of indexes, plus the fact that they provide coverage for queries. In the first tabular representation one could consider also the partition as structure, this because it provides a different structure for tables as well for indexes. There are also some additional restrictions and further implications (see [1]). Apparently hash indexes and nonclustered columnstore indexes don’t have any restriction on index key size (see [2]). When working with indexes in particular, and with other SQL concepts in general, I think is important to know when there are as well when there are no limits altogether. Probably the "index limits" section can be represented in a table as well, as for each index one has to know the max number of indexes per table, the number of columns in the key and the maximum key size. Index limit 6: a limit of 16 columns applies for foreign keys as well. In SQL Server 2008 R2 spatial Indexes could be created only on a table that has a clustered primary key (see [3]). Was this restriction removed with coming versions? References: [1] MSDN (2016) CREATE INDEX https://msdn.microsoft.com/en-us/library/ms188783.aspx [2] SQL Server Database Engine Blog (2016) Increased nonclustered index key size with SQL Server 2016, by Jos de Bruijn https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/26/increased-nonclustered-index-key-size-with-sql-server-2016/ [3] MSDN (2016) Restrictions on Spatial Indexes https://technet.microsoft.com/en-us/library/bb964740(v=sql.105).aspx

You are welcome. And for the other readers why not include a link to your article here. Sounds useful to me. So feel free to post the complete URL cheers Andreas

Andreas, many thanks for such an insightful summary. I've linked your blog in my article how-to-change-the-owner-of-a-secondary-replica-database-to-sa so others may benefit from your article. Cheers, Mark

Hi Andreas, you already got an email from me with my solution to get it up and running. It seems that it now works like a charm. To answer oyur "call to action": AffectedProcesses Number of Deadlocks 2 102 3 348 4 106 5 5 7 1

Hi Dirk, indeed there is an issue that under circumstances the current duplicate avoidance is not sufficient. I already have a fixed version but not tested and uploaded yet. It's on my workbench for months already :-( I hope by May I can provide you with the code Sorry about the inconvenience.

Hi MH glad it helped. I actually cannot say that filtering Extended Events is actually supported for Analysis Services. Even in SQL Server 2016 with the GUI there is no option to do that. So I suppose you really can only filter the traced data after the fact. Bummer...

Thanks for great article, It helped me a lot. Is it possible to add some filter to SSAS XE XMLA script? I need to filter using ApplicationName.