You cannot use dynamic SQL here, as that will not work inside a TVF. You could use dynamic to generate the actual code below though.
Given that you are on SQL Server 2016, you do not have STRING_AGG available, so you are going to have to use FOR XML/STUFF method, which is pretty complex with multiple columns.
It's not necessary or performant to keep querying ...
is there a way to reference the columns by ordinal position
Yes there is but I'm not sure how that would help you doing what you want. You put the ordinal position in the predicate, as you already do for row.
Changing '/row/@name' to instead get the third column would look like '/row/@*'. You should be aware of that null values does not create ...
Using three-part names in queries that reference objects in the current database is a terrible practice and you should create a rule against it. The most frequent problem with it is that it prevents you from having two instances of your application database on a single server, and can lead to unintentional cross-database access.
Using two-part names in ...
You can try using a window function like ROW_NUMBER() or DENSE_RANK() to generate an ID that represents the latest rows per your sort criteria. Here's an example:
;WITH HFCTE (DIdentifier, CachedHFFWVersion, CurrentHFFWVersionn, HFFWMessage, HFFWMessageDate, SortId) AS
--Get all HF Firmware Messages for each device
There is something you need to keep in mind for windowing functions: they are performed on every row. Thus windowing functions like lag and lead apply to the previous and next rows. By adding an order by, you are basically saying “in this grouping, up to and including the current row, what is the max value for column item_name”.
This doesn’t make sense ...
The windowing clause you use (in this case the default of "range between unbounded preceding and current row") operates on what you order by.
There is some logic to that: to be able to know what is "preceding" or "following" you have to talk about ordered data.
In query-1 you order by item_index, which is also what you partition ...
If the lock count heuristic is per-session then it is less of potential issue.
It is per-session.
Are there any other relevant database properties to consider with respect to lock escalation?
On the database, no, but you can disable lock escalation on the whole instance by enabling trace flag 1211.
what is the “best” way to prevent table lock escalation ...
max(t.Item_name)over(partition by t.item_index order by item_index) new_column
Let's take a group where t.item_index = 0. It is
When order by item_index is applied then all rows have the same value, hence all of them are included into the frame, and all rows values are used for MAX() selection. So the value 'E' is ...
The explanation for the different results is given in SQL Server's documentation about window functions, the ORDER BY section:
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
If it is specified, and a ROWS/RANGE is not ...
You can use only columns from the joined tables or in your case concatenated version of it
first_name, last_name, address_1_zip,
CONCAT_WS(',', last_name,first_name) AS CombinedName
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(...
Based on trancount="2" in the XML, it looks like these statements are being run as part of an explicit transaction1. I would check to make sure that previous statements in the same transaction aren't holding these unexpected locks.
Also, confirm there are no triggers running on this table that could be taking more locks.
Based on the information ...
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 ...
REBUILD is an atomic transactional operation so this is expected.
If you were to cancel it would need the logged information to roll back.
One possible alternative is to set up a copy table with the compression on and then copy across the rows in batches - of course you still take up extra space in the data file.
You can use sp_rename to change the copy ...
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 ...
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 ...
Firstly, thank you for providing the actual execution plans for both cases, that is one of the best things for us to help troubleshoot performance problems.
Secondly, the issue you're facing is due to the difference in Cardinality between the first query and second query, which in a few words is the number of records your query might return relative to how ...
My guess is that it's related to this Github issue. Another user reported it, but because I can't quickly reproduce it, and the reporting user didn't want to submit code to fix it, we ended up closing the issue.
If you'd like to help troubleshoot and fix that issue, you can make notes in that Github issue, and use the First Responder Kit's Contributing Guide ...
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 ...
Selecting all jobs that have an enabled schedule but no next run date.
sj.name AS job_name,
syss.enabled AS schedule_enabled
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysschedules syss ON sjs.schedule_id = syss.schedule_id
We had a similar issue with a query that was called from EntityFramework.
Was fast in SSMS, but slow from application.
It turned out there was an error in mapping of parameters, their type, which caused the query from application to do a scan, due to the query becomming non-SARGable.
After fixing this issue, the query was fast from application.
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 ...
maybe it's a bug in the SSMS; try to scritp the @schema_option and verity the proper value based on the list here:
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 ...
The execution order absolutely matters, and you shouldn't change it from what was generated. It is likely you have dependencies in one script on objects that need to be created first from another script. For example, if one of your Stored Procedures use a View then it will fail upon generation because it can't find the View it references. This is why ...
so the real problem is this:
Since wildcard searches are being made indexes are also of no use as
it is doing a full table scan and thus creates locking.
Not the fragmentation itself.
But without removing wildcards search the only this i see that can help you is adding full-text searching to those columns, and use the full-text query capabilities of SQL ...
By the time Ola Hallengren script is completed the index will be fragmented again.
Scale up your server (increase memory and CPU)
Substitute HDD with SDD
Buy a license from SentryOne, SQL Monitor, SQL Diagnostic Manager, Database Performance Monitoring, etc... and fix those blocking and deadlocks
The index fragmentation is not a problem, is a symptom of ...
Don't do index defrag is one option. Did you measure the improvements from defrag in the first place?
HQISILON Doesn't say anything to me, your disk subsystem is one aspect. But also the query load, the execution plans etc. Best is to have something load to measure what you actually gain.
A parallel work should be to investigate whether you have index that ...
It's not entirely clear what part of the stack your question is on. Assuming you're running a second server in parallel with SQL Server 2019 with MDS 2019, then you can certainly connect to either instance in parallel. Specifically here's the download for the Excel Add-In for MDS 2019 which should be backwards compatible to connecting to your MDS 2016 ...