9

The strong general advice is to never shrink of course. Still, the question here is: Can shrinking ever improve performance? Well, maybe. Consolidating all the used pages at the start of each file might have benefits for ramp-up reads, and have favourable cache effects at multiple levels, including at the storage layer. In addition, shrink will remove any ...


5

It looks to me like it’s doing a Scan because it may well need rows from T553 if the condition in T1011 holds. On the other hand, if any of the conditions on T553 hold, it’ll need rows from T1011. So indexes would have to be able to handle finding rows in T553 and then pulling in the relevant rows from T1011, and also finding rows in T1011 and pulling in the ...


3

What is best practice for changing the compatibility level - before or after the SP2 and cumulative updates are installed? SQL Server 2016 SP1 is out-of-support so you should upgrade to supported service pack first. Then the best practices for altering the database compatibility level are documented here: Change the Database Compatibility Level and use the ...


3

The clustered index is the table. It includes all columns. It is (basically) impossible for the clustered index to be smaller than any one non-clustered index. You probably looked at only the clustered key column size or only the non-leaf level when you read that 7GB size. So if you rebuild the clustered index, then you rebuild all columns - including the ...


2

You can use only columns from the joined tables or in your case concatenated version of it SELECT first_name, last_name, address_1_zip, CONCAT_WS(',', last_name,first_name) AS CombinedName FROM CPSQL.dbo.legal_entity AS LE INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style WHERE C.create_date >= Convert(...


2

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's reasonably fast. It is valid (= guaranteed to always produce correct results) to push a selection (aka filter, predicate) below an inner join when the selection ...


2

Do I then install the last cumulative? Yes, you should install the latest cumulative update. Some folks would advise to install the "next to latest" version. Since the latest one just came out 2 days ago at the time of this writing, it might be prudent to wait and see if any issues arise before going there. Are there any tools that would help ...


1

maybe it's a bug in the SSMS; try to scritp the @schema_option and verity the proper value based on the list here: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15


Only top voted, non community-wiki answers of a minimum length are eligible