Yes, I understand about the clustered index scan, of course without indexing that will happen. I am just concerned whether this type of sluggishness will happen, as I said...

Hi Grant,

Yes it was in NYC! I talked with you briefly on Saturday, in fact. Nice to put a face to the message board login!

Nobody at SQL Saturday...

Followup: As a test, I dropped the composite nonclustered index (the one mentioned near the end of my last post above), and while leaving the PK clustered on ID...

I've also seen the following command mentioned somewhere on SSC:

DBCC TRACEON(652)

...which is supposed to turn off read-aheads. I haven't tried it yet, but I wonder if this would make...

Craig Farrell (11/17/2010)Welcome back, Jordan. You've come a long way since your last discussion. Glad to see your interest hasn't waned when you saw the size of the...

Sure, here ya go...

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 264 ms.

SQL Server Execution Times:

CPU time =...

Lowell (11/17/2010)

---

just a basic double check....after clearing cache, the FIRST time a proc gets run, it's slower, as a new execution plan gets created, adn that takes time...but subsequent calls...

Clarification: This is on a test server where no other SQL is currently being run. (I checked sp_who and nobody is talking to it except me.)

I guess it...

I'd also like to invite comments on the "hot spot" issue I've read about elsewhere: With a clustered index, all new rows are written to the same page, causing...

Yes, the paging is on C: and is set to a custom size: 2046 to max 4092, rather...

Additional note: I'm pinpointing the real reasons for our performance issues and tackling those already -- missing indexes, bad query plans, poorly written SQL, etc. I'd rather not...

Thanks to everyone for the additional ideas on this issue.

TheSQLGuru: Your post was really helpful. I wasn't aware of how to view physical stats.

scott.pletcher (9/28/2010)

---

The WidgetDate field is...

Thanks a lot Craig!

Just to clarify, there are a number of non-clustered indexes on the Widgets table. They are all single-column indexes, not composite. However, there is no...

Thanks for the replies so far...

The Widgets table has no clustered index. The WidgetDate field is the single most relevant column for searching/querying, however it can change often as...