sql query execution takes more time.

karunakar Panja 1 Reputation point

Actually my Query Execution takes 44 seconds with only 195 records how can reduce the time to milliseconds.

I have one of the table i.e. "table1" is stored in two servers . in the server one "table1" table takes only 2 milliseconds of time to execute the query but the same table which is in the server2 takes 44 seconds .
Why it is taking so much of time in second Server ?

Could You please any one give me an answer.......

thank you,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,736 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Ronen Ariely 14,636 Reputation points MVP


    but the same table

    According to your description these tables are in two different instances/database which mean these cannot be the same table. You probably means that the two table has the same structure since you see that the tables has the same columns when you execute query

    The fact is that this means nothing regarding the real structure of the table behind the scenes. For example you might have hidden columns which take space in each row.

    For more information on such cases, search google for: table structure ronen ariely

    There are recording in Hebrew and in English from different conferences.

    Note! This can be fixed usually by ALTER TABLE... REBUILD as presented in the recordings.

    stored in two servers

    This mean that you do not use the same connection and you do not connect to the same server.

    Differences can be related to connection properties (connection string) for example and to server setting.

    Why it is taking so much of time in second Server ?

    Without the ability to connect to your server or read minds how can anyone answer this?!?

    We can only guess and/or lead you to bring us more information, which is what we will need to do next :-)

    (1) Please provide the exact query which you run.

    (2) Please provide the full queries to create both tables including hidden objects like indexes, triggers, constraints, relations to other tables (all these and more can make a different).

    Get this information from the SSMS by right click on the table name in the Object Explorer


    (3) Compare the data in both table and make sure the number of rows is the same

    (4) Check the size of the data in both tables (related to what I mentioned above)

    You can do this by right click on the table name in the SSMS Object Explorer -> select properties and move to the "Storage" tab


    Or much better option to get size of related indexes and table raw data is to use queries to get the information

        t.NAME AS TableName,  
        s.Name AS SchemaName,  
        SUM(a.total_pages) * 8 AS TotalSpaceKB,  
        SUM(a.used_pages) * 8 AS UsedSpaceKB  
        sys.tables t  
    INNER JOIN        
        sys.indexes i ON t.OBJECT_ID = i.object_id  
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
        sys.allocation_units a ON p.partition_id = a.container_id  
        sys.schemas s ON t.schema_id = s.schema_id  
    WHERE t.NAME = 'Person' -------<<<<< Change the table name to your table  
    GROUP BY   
        t.Name, s.Name, p.Rows  

    Please provide all the information and we will continue from there

    0 comments No comments

  2. YufeiShao-msft 6,986 Reputation points

    Hi @karunakar Panja ,

    If you can trace exec plan using profiler, can you find that the plan is the same both times, sometimes small differences can create a different environment for query execution, the assumption is that the database are the same and the data and statistics are the same, but the system or database setup is different, these different can lead to different execution plans and thus different execution times

    You can compare your execution plan, and compare the properties of the operator, this is perhaps the quickest way to identify issues that may cause discrepancies between servers.


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments