36 reputation
4
bio website
location
age
visits member for 1 year, 9 months
seen Oct 19 '12 at 20:16

Aug
7
awarded  Popular Question
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.