Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to know whether problem related to query or components

Posted on 2009-07-14
12
Medium Priority
?
300 Views
Last Modified: 2012-05-07
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..
0
Comment
Question by:krishna_harik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 520 total points
ID: 24847579
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
 
LVL 4

Assisted Solution

by:udaydidigam
udaydidigam earned 520 total points
ID: 24847584
did u create indexs for the tables.
if not the problem due to index not defined
0
 

Accepted Solution

by:
krishna_harik earned 0 total points
ID: 24847771
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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 520 total points
ID: 24847799
>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
 
LVL 13

Assisted Solution

by:St3veMax
St3veMax earned 560 total points
ID: 24847849
Update the statistics. Just because you restored the live DB into the test db, doesent mean the index's will kick in.

HTH
0
 
LVL 4

Assisted Solution

by:udaydidigam
udaydidigam earned 520 total points
ID: 24847854
Just check the indexes properties are clustered or not if not make it as clustered
0
 
LVL 13

Assisted Solution

by:St3veMax
St3veMax earned 560 total points
ID: 24847865
>>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
 

Author Comment

by:krishna_harik
ID: 24848098
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
 
LVL 7

Assisted Solution

by:Randy Wilson
Randy Wilson earned 400 total points
ID: 24848468
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
 

Author Comment

by:krishna_harik
ID: 24892512
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24892670
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
 

Author Comment

by:krishna_harik
ID: 25184018
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question