sickpup (2/21/2011)

---

Just a quick note to say that everything went smoothly over the weekend. I identified and dropped the nc indexes on the bad DB files. Then, ran shrinkfile, the...

There is no direct way that I know of to do what you wish.

You could try creating a MASSIVELY complicated pile of dynamic sql to do it. ...

Nicely done.

"...for correcting grammar errors, illogical sentences and an appalling dearth of punctuation."

Those things are vastly overrated in technical articles anyway Gail! 😀

d@mmit! I had a nice response typed in and submitted and it isn't showing up! 🙁

Anyway, let me try to recreat.

One thing I didn't see mentioned is if the WHERE...

One potentially HUGE performance item I didn't see touched on is if the column(s) in the WHERE clause are indexed you should do some initial testing to adjust batch size...

Interesting query for objects sizes!

Are you perchance doing statement level completed tracing (RPC or TSQL)?? Those can be DEVASTATINGLY HARD on the server if you have UDFs in plan, or cursors, triggers, etc.

You can check the fragmentation and 'page fullness' using the index physical stats DMV. You might want to do that on the other file groups just to be thorough....

There are MANY reasons to not use autogrowth to manage your file sizes. quickie list (NOT inclusive at all!!): delays for initialization if you aren't on instant file initialization...

sickpup (2/17/2011)

---

Er, uh... well, this is embarrassing.

So, looking back through the settings on the index rebuild maintenance task, I see that "Change free space per page percentage to:" is set...

There are numerous places where cursors are acceptable if not preferred, IMNSHO. What we REALLY need is faster-performing cursors from the Microsoft engine team!!

The missing indexes engine is a good place to check for candidate NC indexes. Make sure to check for similar indexes already existing though - that subsystem doesn't know...

1) That's an interesting script for dupe index checking Jason. Haven't seen one like that before. I need to give it a try.

2) Duplicate indexes can indeed cause...

1) ALWAYS have a max memory set for sql server. leave 2-4GB for OS and other things

2) what is server configuration please?

3) the commit slowness could be from MANY...

1) someone added a bunch of indexes? Top suspect there is using Database Tuning Advisor. BAD thing that, at least in the hands of the unknowing!!

2) when indexes...