DBtheDBA

36 reputation

bio	website
	location
	age
visits	member for	8 months
	seen	Oct 19 '12 at 20:16
stats	profile views	7

	36 reputation	bio	website		visits	member for	8 months
3 badges		location			seen	Oct 19 '12 at 20:16

summary answers questions tags badges favorites bounties reputation activity

21 Actions

suggestions reviews revisions comments badges posts accepts all

Oct 12	revised	SQL Server 2008 datetime index performance bug added 265 characters in body
Oct 12	revised	SQL Server 2008 datetime index performance bug added 184 characters in body
Oct 12	comment	SQL Server 2008 datetime index performance bug I agree that it could be hitting the timeout, although in my experience usually the client will return a specific timeout error if that occurred. My sense is something else is occurring on the server side. I have just opened a microsoft connect bug based upon @cfradenburg's advice. I'd love to post the table and data here for anyone to reproduce, however working for a large corporation, my hands are a bit tied when it comes to posting this information publicly. We have disclosure agreements with MS, so should I get their attention to this issue, I'll post anything I learn back here.
Oct 12	revised	SQL Server 2008 datetime index performance bug added 3520 characters in body
Oct 11	comment	SQL Server 2008 datetime index performance bug We read that before during our searches, and I understand what you are saying, but there is a fundamental flaw in something the server is doing here. We've rebuilt the table, and the indexes, and have reproduced it on a new table. The recompile option doesn't fix the issue, which is a big hint something is wrong. I don't doubt that putting clustered indexes on everything could potentially fix this problem, but its not a solution to the root cause, it's a workaround, and an expensive one on a large table.
Oct 11	awarded	Commentator
Oct 11	comment	SQL Server 2008 datetime index performance bug Also, I should add these exact same queries run fine though management studio, which points back to my original concern that this is something between the client (ODBC) and server.
Oct 11	comment	SQL Server 2008 datetime index performance bug I'm having my DBA type a script to reproduce today and will post here. If this is a index or statistics issue, why would CASTING a ORDER BY field change the query plan in such a significant way such that the query would never return?
Oct 10	comment	SQL Server 2008 datetime index performance bug Also, our tables are almost always queried by serial number, not test_date. We have non-clustered indexes on both, and a clustered only on the id column in the table. It's an operational data store, and adding clustered indexes on other columns would only drive page splits and poorer performance.
Oct 10	comment	SQL Server 2008 datetime index performance bug To cover some of these concerns: 1. Statistics have been updated, are are being updated. 2. We've tried indexing in several ways (covering indexes, etc) but the issue seems to be more tied to the `order by` usage against a datetime index specifically. 3. Just tried your idea with the RECOMPILE option, it still failed, which surprised me a bit, I was hoping it was going to work, although I don't know if it is a solution for production.
Oct 10	revised	SQL Server 2008 datetime index performance bug added 136 characters in body
Oct 10	comment	SQL Server 2008 datetime index performance bug I'll get my DBA to type up a script tomorrow to create an environment to reproduce. I don't think it's that difficult. I'll post it here as well should someone be interested in trying it themselves.
Oct 10	comment	SQL Server 2008 datetime index performance bug Yes, and we have rebooted, patched and migrated the system as well. The problem has even carried forward between servers. A DBA in my group as replicated the issue on a new table for testing this problem, and the issue starts after several million rows are added (I don't remember what the actual value was).
Oct 10	awarded	Editor
Oct 10	revised	SQL Server 2008 datetime index performance bug added 80 characters in body
Oct 10	comment	SQL Server 2008 datetime index performance bug I'm a bit confused here. Why would the plan be based upon `the order` by clause? Shouldn't the plan limit itself to the `where` conditions as the ordering should only occur after the rows have been fetched? Why would the server try and sort the records prior to having the entire result set?
Oct 10	comment	SQL Server 2008 datetime index performance bug The client is ODBC. We tested under the both the distributed version, as well as the Native Client, with the same results. We're hoping to fix or avert the issue on the server side so we can re-enable this index and not have to rebuild several client applications that perform this `order by` operation.
Oct 10	awarded	Student
Oct 10	comment	SQL Server 2008 datetime index performance bug I've linked my accounts now to that site. If a moderator wants to move the post to that site, I'm fine either way. One of my developers pointed that site out to me after I posted here.
Oct 10	comment	SQL Server 2008 datetime index performance bug Sorry, good point. I should have included that we tried modifying the selected column space (removing the '*', etc) heavily with various combinations. The behavior described above persisted through those changes.