srinivas_ganamur
asked on
Time out error - sql server
Hi,
when i am generating report, i am getting time out error. but some time the report was generating properly (it will take time coz heavy data). but this time i am getting time out error. it is not one report, more reports giving time out error. Here i am giving one procedure details:
1. in that procedure no physical tables that is almost temp tables (i have created index on that table).
please find the database & server configuration details:
server configurations:
memory:
Total RAM : 32 GB
server configurations:
MIn server memory: 4096 MB
Max server memory:26622 MB
min memory per query:1024 KB
parrelism:
cost threshold for parrelism: 5
max degree of parrelism:0
locks:0
max worker thread:567
database configurations:
number of users: 5
data files: (1 P F, 12 NDF files).
data file size : currently allocated space (249764.81 MB)
availble free spcae (22689.56 MB (9%))
log files:
currently allocated space (18055.75 MB)
availble free spcae (18018.87 MB (99%))
tempdb configurations:
log files:
currently allocated space (0.50 MB)
availble free spcae (-23.37 MB (-4674%))
please let me know what could be the reson.
when i am generating report, i am getting time out error. but some time the report was generating properly (it will take time coz heavy data). but this time i am getting time out error. it is not one report, more reports giving time out error. Here i am giving one procedure details:
1. in that procedure no physical tables that is almost temp tables (i have created index on that table).
please find the database & server configuration details:
server configurations:
memory:
Total RAM : 32 GB
server configurations:
MIn server memory: 4096 MB
Max server memory:26622 MB
min memory per query:1024 KB
parrelism:
cost threshold for parrelism: 5
max degree of parrelism:0
locks:0
max worker thread:567
database configurations:
number of users: 5
data files: (1 P F, 12 NDF files).
data file size : currently allocated space (249764.81 MB)
availble free spcae (22689.56 MB (9%))
log files:
currently allocated space (18055.75 MB)
availble free spcae (18018.87 MB (99%))
tempdb configurations:
log files:
currently allocated space (0.50 MB)
availble free spcae (-23.37 MB (-4674%))
please let me know what could be the reson.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
last one month back i have executed dbcc checkdb on my user db....it was taking long time..at the same time tempdb is also full
You are going to have to post the actual query that is timing out.
ASKER
What is happening is that as you have added data, it is taking longer to process and unless you are prepared to optimize the queries in the Stored Procedure you will have to resort to lengthening the timeout.
From looking at your stored procedure, it looks like you have your (or your DBA) work cut out for you. You are going to have to do some homework. What I would focus on is to lose the CURSOR, I suspect that is your bottleneck and there may not be any need for it or the temp table it uses. In other words, see if you can change History_Omni_MembershipLed
Good luck.
From looking at your stored procedure, it looks like you have your (or your DBA) work cut out for you. You are going to have to do some homework. What I would focus on is to lose the CURSOR, I suspect that is your bottleneck and there may not be any need for it or the temp table it uses. In other words, see if you can change History_Omni_MembershipLed
Good luck.
>>no perfect solution<<
Did you expect to do your work for you. If that is the case, I am afraid it should be clarified that EE is not intended for that purpose.
Agaon, good luck.
Did you expect to do your work for you. If that is the case, I am afraid it should be clarified that EE is not intended for that purpose.
Agaon, good luck.
ASKER
i got the solution
ASKER