It's also not a question of web servers vs. something else. Query behavior is query behavior. Nothing all that special about queries coming from a web interface vs. queries coming...

I'd suggest using extended events for this. They're more light weight than trace events and their filtering is much, much better, so you'll only be capturing the data you want....

If you have the clustered index key value in the WHERE clause and you're still getting a scan, the suggestion is that you must have one of two situations, your...

That's one way, sure. I'd probably use extended events, or, on a 2008 system, trace....

But you can see an aggregation of the queries currently in cache in sys.dm_exec_query_stats. It shows execution times, counts, other stuff like that, but not who did the executing or...

It goes back to the histogram on the statistics. That is only build on the first column in the index key. The others are used to help establish the density...

Query performance measures alone won't show you what is blocking or being blocked. Since you're on 2012 you can use the blocked_process_report extended event. That will tell you what is...

Best thing to do is get that thing local. Any cross network process you go through is going to be extremely painful.

You measure the speed at which queries that access that index are performing before and after you defragment the indexes.

patrickmcginnis59 10839 (4/3/2014)

---

Grant Fritchey (4/3/2014)

---

It's an approach taken by a developer or development team that doesn't understand SQL Server, or, just didn't want to take the time to do things...

Cody K (4/3/2014)

---

Just to play the devil's avocado:

- XML is a first-class citizen in SQL Server and as far as validation goes that's what DTDs are for. Right?

- I'm not...

Not a problem. Happy to pitch in.

It's an approach taken by a developer or development team that doesn't understand SQL Server, or, just didn't want to take the time to do things correctly. It's very fast...

Hard to tell for sure what's going on without seeing the execution plan. Chk001 sounds like a column derived somewhere else within the plan. A key lookup suggests that you...

Probably not. You'll note that the leading edge of the key, the first column, is different between the two indexes. That's one of the bigger driving factors for index selection...