| Author |
Topic |
|
maican
Starting Member
6 Posts |
 Posted - 2008-05-20 : 04:30:43
|
| Hi,I have an ASP.NET application used in my university for student management. Any now and then the application throws an error like "DataBinding: 'System.Data.DataRowView' does not contain a property with the name XXXX" where XXX may be different. Of course that this properties exist and they are generated ok by the stored procedures.After an recopilation everything seems to work nomally for a while.I did not succeed in identifying the moment when the error is thrown but I suspect some reports in sql reporting services that lead to a server overload ??? that also is strange because the server specifications are quite ok, I think:- OS: Windows 2003 R2, 64 bit- SQL Server 2005 Standard, 64 bit- 8 GB RAM- Intel Xeon E 5345 2.33 Ghz (2 quad-core)I would need help for:- identifying the moment when this error occurs and _why_- how could I repair it- how could I distribute the load on more cores (from time to time Process Explorer from Sysinternals shows that only 1 core from 8 is working at full capacity)Thanks,Catalin |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-20 : 22:31:29
|
| How often do you rebuild index or update statistics in the db? |
 |
|
|
maican
Starting Member
6 Posts |
Posted - 2008-05-21 : 02:27:25
|
| Hello,Thank you for the answer.We rebuild the indexes and update statistics using Database Engine Tuning Advisor once every 2-3 months.Catalin |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-21 : 02:55:53
|
| Maybe change the rebuild to once a week. You don't need to update statistics of an index that has been rebuilt though.Tuning advisor? I wasn't aware that rebuilt indexes. It suggests new ones based on a workload.--Gail Shaw |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-21 : 22:45:22
|
| We rebuild index weekly and update stats daily for most dbs. For some heavily updated tables, we rebuild index daily. |
|
|
|
maican
Starting Member
6 Posts |
Posted - 2008-05-22 : 12:48:45
|
quote:
Originally posted by rmiao
We rebuild index weekly and update stats daily for most dbs. For some heavily updated tables, we rebuild index daily.
Follosing your advice I did the following:1. rebuilt indexes2. update statistics with fullscan3. EXEC sp_recompile [procedure] for updating the execution planThere where some very slow SP that were used in reports (reporting services). After these steps the execution time decreased significantly for these.Although: even if a report from the Reporing services lasts now only ~40 sec. for a large faculty (it returns ~6500 _calculated_ records_), in this time the DB used for generating the report does not respond almost at all (the previous errors still appear). I've came to a conclusion that, somehow, the query engine is not able to return all the fields/columns thus the errors appear. Is it possible???????What can I do so that the DB should fully respond to queries? Catalin |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-22 : 13:36:56
|
| Rebuilding index will automatically update stats. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-22 : 13:41:58
|
quote:
Originally posted by maicanFollosing your advice I did the following:1. rebuilt indexes2. update statistics with fullscan3. EXEC sp_recompile [procedure] for updating the execution plan
You don't need to update statistics of an index that has been rebuilt.What rmiao's doing is updating and rebuilding at different intervals.An index rebuild updates with fullscan its own statistics.quote:
I've came to a conclusion that, somehow, the query engine is not able to return all the fields/columns thus the errors appear. Is it possible???????
SQL will always return all the columns asked for in a query, and will throw errors if for some reason it can't (eg columns don't exist)It's possible that reporting services has a timeout set and, if the query hasn't returned a result in that time, gives an errorCan you post one of the slow procs please?--Gail Shaw |
|
|
|
maican
Starting Member
6 Posts |
Posted - 2008-05-23 : 01:43:52
|
| Here is one of the slow SP, the views used in it and a diagram.[url]http://econ.unitbv.ro/SP%20si%20diagrama.zip[/url]Regarding the columns retrieved by SQL Server, my flow for the error is as follows:1. normal operation -> everything works normal;2. call a long-time report -> report displays after 2-3 min in browser;2.1. during these 2-3 minutes (or maybe more) the web application throws errors that it cannot find certain columns among those returned by the SQL Server (the application worked fine before and after a certain time!)How could I explain this but the SQL Server not returning all the columns? (web controls that throw exceptions are built using SqlDataSource and custom DAL + BLL)catalin |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-23 : 04:34:35
|
| Your link give me11001 - Host not found.If you run the slow query in management studio, do you get an error? How long does it take?If it runs fine in management studio, then the errors are not the fault of SQL Server--Gail Shaw |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-05-25 : 12:17:53
|
| How many CPU and memory you have been using in the server?ManojMCP, MCTS |
|
|
|
maican
Starting Member
6 Posts |
Posted - 2008-05-26 : 09:32:54
|
| Hi,the server has 2 * quad core Intel and 8 GB RAM/ win 2003 x64Usually sql uses ~ 2GB and IIS ~ 500 MB -> 1GBRecenlty I've moved IIS and Reporting Services web application in 2 seperate application pools. --> Same error but not so oftenCatalin |
|
|
|
|
performance degradation ?