You can restore the mssqlsystemresource database and see the INFORMATION_SCHEMA views definitions.

USE [master]
GO
CREATE DATABASE [mssqlsystemresource_copy] ON
(FILENAME = N'C:\SqlData\mssqlsystemresource.mdf' )
FOR ATTACH_rebuild_log

Then you...

Excellent. Thanks.

Sometimes you need to use the function in a query, so that it needs to return INT instead of a set.
This is a little amendment.

sam 55243 (1/6/2017)

---

Hi,

I modified the query to use SQL count function instead of xml count as suggested by drew .With this change i got 25~30 percent improvement in comparison to...

Fantastic question!

None of the indexes options will improve the execution plan, but the proposed statistics.

One option is to try to replace the Table Valued Function, as drew.allen proposes.

Do you have xml and xml path indexes on the table(s) that is used in TVF? Try...

Additionally: if that column takes part in constraints, indexes and etc. you'll have to re-create them.

1. Yes, every one other index having N2 as a column will be also a unique index.

2. Yes definitely, it helps the optimizer.

skeleton567 (12/21/2016)

---

gfish@teamnorthwoods.com (12/21/2016)

---

Let me suggest a much simpler way of avoiding the CPU spike caused by running multiple jobs at once. Simply combine them into one job, with the contents...

skeleton567 (12/23/2016)

---

crmitchell (12/23/2016)

---

Until that one occasion where it does matter. As Murphy and Sod will...

Send some more details like the Table definition (structure, indexes, constratints...), the query you use and the execution plan, so that someone could do a better view on your problem.

Your question is not set well.

If you mean what service pack is latest in your instance then you should

SELECT @@version

Jeff Moden (12/20/2016)

---

Spreading out jobs to avoid spikes is a good idea BUT... the real fact is that even after all you did, you're still taking spikes to 40% of...