Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kakuh0
Starting Member
2 Posts |
 Posted - 2008-06-22 : 17:41:56
|
| Hello friends,Can some one help me in identifying a strange problem that we are facing currently.We have a query which is taking 3 seconds to return result in Production environment and 1 second in INtegration environment. We have written this query in function and swe are calling this function from a stored procedure which is returning 31 records i.e., 31 * 3 seconds i.e., 93 seconds. Because of this time we are ending up with Timeout errors in the application.Can some one suggest what need to be verified with respect to this difference. Both the enviornments have same amount of data but only difference is production database server is a clustured one and integration server is a normal server.Please help me ASAP.Thanks,Hemant |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-22 : 19:34:25
|
| Do those servers have same load? Did you compare excution plans and i/o statistics? Checked table fragmentation? Are statistics up to date? |
 |
|
|
kakuh0
Starting Member
2 Posts |
Posted - 2008-06-22 : 19:41:30
|
| I am sure that the database have equal load with respect to transactions but the production servers have multiple jobs like Transactionallogs update to another server for every half an hour and we have four such jobs running on the server.Can you please tell me how should i compare execution plans, i/o statistics and how should i verify table frag |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-22 : 20:36:14
|
| You can get execution plan in query analyzer -> query -> show execution plan. Get i/o statistics with 'set statistics io on', and check table frag with 'dbcc showcontig'. Read books online for details. |
|
|
|
|
|
|
SQL query time difference