Won't be around for long, methinks.

Also, still on 2008, and XE was not friendly in the slightest back ...er... now 🙂 That said, can't wait to start playing with it...

As mentioned, the index is created post-load.

Thanks for taking the time to respond. Trigger, here I come!

I'll let you know what I find.

Thanks again

P

Ah, I should have been clearer. It is dropped and re-created monthly, populated with 1.8m rows, and then create the index. Within 2 days, the dm tables are showing fragmentation.

Could...

If you have space on prod, restore it to  second DB on prod server, remove unneeded tables/content, then back that up and restore to dev.

Thank you all.

This was a purely educational matter.

I share your concerns re: cursors and dynamic SQL, but sometimes there is no other way.

At any rate, this report will likely run...

I could have been clearer: the logical reads are on the target of the INSERT.

It's an INSERT #Action/EXECUTE dbo.sp_executeSQL, inside a cursor. Could it be because it's dynamic SQL?

I SET...

• This reply was modified 4 years, 4 months ago by schleep.

I need to call the sprocs with named parameters, so I ended up just building a command in the cursor declaration.

I think there was always going to be some hard-code,...

So I'm running a bunch of reports through a cursor. Different DBs, different subsets of the same list of reports.

A couple of the reports have additional parameters. I was hoping...

Thanks for the succinct reply 🙂

For that matter, is there a way to even list the variables in the current scope?

Yup, that does it. I'd distilled Johan's solution down to yours. Perf not completely horrible in real life, and I only need to run this once a month.

Thank you both.

Hmmm... my OP had appeared to time-out without posting, which is why I reposted the question.

The solution was to restart the SQL Server service.

...and then restart the server. D'oh!!

Skipped right over that line in BOL

The .Net directory searcher and PoSh is the way to go. The AD cmdlets and the QAD cmdlets (Qwest AD -  or OneIdentity now, as mentioned in one of the...

Since all operations inside the loop are in a single DB, USE @db_name makes the code a lot easier on the eyes.

SET NOCOUNT ON, only to print out a custom...

scdecade,

Trust me. this works to eliminate the DBs where the table to be updated doesn't exist from the cursor that is supplying the @db_name.

SELECT Name FROM sys.databases

WHERE OBJECT_ID(Name + '.dbo.<tablename>')...

• This reply was modified 5 years, 4 months ago by schleep. Reason: Edited to add missing '.' before 'dbo.'