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 |
|
senthil22
Starting Member
7 Posts |
 Posted - 2010-08-27 : 05:34:26
|
| i am executing sp in one of my database it is taking 4 minutes to complete execution. where as i am executing same sp in different database it is taking only 1 millisecond to complete execution. i have checked the table settings such as index and table data also everything is same. now i want to find where is the problem. what are steps to find such kind of problems. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2010-08-27 : 06:21:57
|
| CPU, RAM, statistics, diferent indexing, storage available etc could all make a difference |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-27 : 08:51:26
|
| you need to review the execution plan of the query. probably you need to rebuild indexes.you can post the execution plan here if you need help interpreting italso check for blocking (sp_who2) while the SP is executing |
|
|
|
senthil22
Starting Member
7 Posts |
Posted - 2010-08-28 : 03:09:08
|
quote:
Originally posted by russell
you need to review the execution plan of the query. probably you need to rebuild indexes.you can post the execution plan here if you need help interpreting italso check for blocking (sp_who2) while the SP is executing
Hi ruseell, Database which is taking very short time to execute the query it is using index clustered seek, where as database which is taking more time to execute the sp it is using index clustered scan.But both the sp queries are same. where could be problem? and mean while how should u want explain plan whether in .xml format or in .sqlplan format. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-28 : 13:09:28
|
| is the data the same on both?does the SP take any parameters? |
|
|
|
senthil22
Starting Member
7 Posts |
Posted - 2010-08-30 : 02:53:15
|
quote:
Originally posted by russell
is the data the same on both?does the SP take any parameters?
Yes data are same for both the database.when i passing values in both database different table is referred i.e., fast executing database is referring less value table. where slow execution database referring large value table. how this can possible for same query. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-30 : 08:25:41
|
| if they are referencing different tables then they aren't going to be the same. this SP contains dynamic sql?perhaps if you show us the SP in question. |
|
|
|
|
|
|
doubt in sp