Curious_sqldba:

I can see where your IT team may not want you installing software on your PC without their approvals, but something like git (or SVN or CVS) should be deployed...

Quick google and I found this blog post:

https://blog.sqlauthority.com/2010/07/24/sql-server-find-queries-using-parallelism-from-cached-plan/

Pinal Dave has a query there that will pull all parallel plans from the plan cache (not from the query store).  I would...

Quick look at your execution plan, it looks like your slow operations are in your loop.  The insert is the highest cost operation.  That being said, you should get a...

Thanks for the article.  It was very informative and you demo'ed the tool that we actually use at work (sourcetree).

There are some things that I think are not...

I am glad they got things fixed.  Something to note though - depending on what type of audits you have and certifications your company has (SOX, ISO, etc), having technical...

That is a long stored procedure, but I will offer some input.

First, my advice is unless you NEED a query hint, don't use them.  The SQL optimizer generally will take...

• This reply was modified 5 years, 9 months ago by Mr. Brian Gale.

Thanks for the article.  It was very informative and you demo'ed the tool that we actually use at work (sourcetree).

There are some things that I think are not clear in...

Thanks Tim and Phil!

It is nice to hear that our setup isn't anything out of the ordinary and that other experts have similar issues with it that we do.

Our plan...

• This reply was modified 5 years, 9 months ago by Mr. Brian Gale. Reason: changing some "my"s to "our"s, I's to we's and fix some grammar

I imagine you could.  I am currently not using Query Store on my servers (too old... need to do upgrades), but my understanding is that it stores the execution plan...

Pretty sure the problem there is with the XML NameSpace (xmlns).  You are defining a namespace in the XML, but then not specifying the namespace in the SQL.  You have...

Quick and easy fix to this - need to mark the variable as an OUTPUT variable in the parameter assignment part of sp_executesql.

In your EXEC statement, change:

@xml=@xml

to

@xml=@xml OUTPUT

Something to note...

I'd recommend against implicit conversions wherever possible.  There are a lot of articles online about implicit conversion and even SQL Server, via the execution plan, will give a warning about...

First, this sounds like a huge security risk.  linked servers with sysadmin permissions on them sounds like a good way for someone to toss a "drop database" or a shutdown...

I think either are acceptable, but I think one will have better performance.  I am pretty sure "NOT NULL" is not sargable so you may have a performance hit by...

That sounds like a maxdop and cost threshold configuration issue.

But if you execute sp_who2 and look for multiple copies of the same SPID, that'll show you how many threads are...