How to know whether problem related to query or components

Hello,

when we are running a select query in test environment it is taking only 1 sec time to execute but when we are running the same in our production (clustered environment) it is taking around 4 min to execute.

how to know that whether the problem related to query or component or network performance? please, let me know the way or steps to resolve the issue.

thanks..
krishna_harikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
check the explain plan of the query in both environments.

possible issues:
* not the same indexes
* not the same amount of data
* not the same RAM/CPU config
0
udaydidigamCommented:
did u create indexs for the tables.
if not the problem due to index not defined
0
krishna_harikAuthor Commented:
we restored the same database in the test environment and hardware is also similar.
if it is problem with indexes, then how to find the problem or is their any other approach.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>we restored the same database in the test environment
after restore, you need to refresh the statistics

>and hardware is also similar.
similar is not equal. please clarify the differences.

anyhow, get the explain plans on both servers to see the differences.
0
St3veMaxCommented:
Update the statistics. Just because you restored the live DB into the test db, doesent mean the index's will kick in.

HTH
0
udaydidigamCommented:
Just check the indexes properties are clustered or not if not make it as clustered
0
St3veMaxCommented:
>>Just check the indexes properties are clustered or not if not make it as clustered

be aware that you cannot have more than one clustered index per physical table...
0
krishna_harikAuthor Commented:
st3ve,

how to check whether the indexes properties are clustered or not. since, the select query consists of more joins having 8 tables in it. so, i have check all the tables properties.
0
Randy Wilson.Net DeveloperCommented:
Have you turned on SQL Profiler and checked to see how long it takes the query to "reach" the server, and how long it takes to return a resultset?
0
krishna_harikAuthor Commented:
Hi all,

Thank you very much for your valuable inputs. The issue was resolved by running 'UPDATE STATISTICS' command on all the tables using by the script to get the output.
 
Now the script executing in 3 sec. Based on the situation, i have queries

Queries:
1. Before running the script i ran Rebuild Index command on all the tables in the databases and ran the same script but still it took 4min to execute. Rebuild Index will update Statistics, so why it was not effected the performance.

2. What is difference between running UPDATE STATISTICS WITH FULL SCAN and UPDATE STATISTICS WITH FULLSCAN, NORECOMPUTE.

3. we are runnig a job through maintenance plan for Reorganize Index and Update Statistics running it once in a week. I found in job report txt file that few tables are having
'update statistics with fullscan' and few tables with 'update statistics with fullscan, recompute'. May i know the difference?

Please, let me know the answers for above queries.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
RTFM:http://msdn.microsoft.com/en-us/library/ms187348(SQL.90).aspx

NORECOMPUTE
 
    Specifies that statistics that become out of date are not automatically recomputed. Statistics become out of date depending on the number of INSERT, UPDATE, and DELETE operations performed on indexed columns. When specified, this option causes the Database Engine to disable automatic statistics rebuilding. To restore automatic statistics recomputation, reissue UPDATE STATISTICS without the NORECOMPUTE option or run sp_autostats
 
 
it is based on the table's setting if the statistics are to be recomputed automatically or not.

Open in new window

0
krishna_harikAuthor Commented:
Hi,
please, could any one help me out on below query.

Query:
Before running the script i ran Rebuild Index command on all the tables in the databases and ran the same script but still it took 4min to execute. Rebuild Index will update Statistics right?, so why it was not effected.

If the above was not clear, please read the previous posts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.