locked
Query performance data RRS feed

  • Question

  • Hi,

    I have archived our dev database and reduced the table size from one million to 45000 records. The same table is having 1 million records in PROD. Before implementing the archival job in PROD I want to see the difference in query performance. When I ran the same query in DEV and PROD both are taking 1 sec for execution. On a basic terms, there is not much performance improvement. But I ran io statistics and statistics time on for the queries in DEV and PROD.

    DEV - Clustreredindex look up shows-88% and nonclustered index lookup is 2 %

    PROD- Clustreredindex look up shows-18% and nonclustered index lookup is 67 %

    I am not sure why this difference in query plan. But the logical read in DEV-1657 and CPU time = 12609 ms

    PROD-logical read19434 and CPU time = 13625

    Can any one help me in analysing the key differences to come to a conclusion that says there is some performance improvement?

    Thanks

    • Moved by Kalman Toth Wednesday, July 9, 2014 6:52 PM Not database design
    Monday, July 7, 2014 8:07 AM

Answers

All replies

  • The comaprision really wont work unless both the severs  (DEV & PROD) have similar configuration in terms of CPU, RAM, Drives etc.

    do you get the same execution plan on both the servers? (cost may be different)

     

    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, July 7, 2014 8:40 AM
  • It completely depends on the hardware. If your prod server is a really powerfull server which dont struggle much to run your queries and if you are doing proper database maintenances, then you might not see much of a difference in the query executions.

    But, Even if the performance of individual queries has not improved, you will gain a lot of improvement with the maintenance of the database. Things like backup/index maintenance/statistics maintenance/Checkdb etc is going to be a lot faster.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, July 7, 2014 9:44 AM
  • Hi,

    The Ram and memory of PROD is higher than DEV. I can see the cost reduction in DEV. But can you please tell me if clustered index scan should be high or non clustered scan.

    Tuesday, July 8, 2014 2:06 AM
  • Ideally there should be no scan happening, what you would want to see is all index seek. 


    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, July 8, 2014 2:21 AM
  • Can you post the result of SET STATISTICS TIME ON?

    If the hardware ,RAM,CPU are different  so it would affect the execution plan as well.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 8, 2014 5:53 AM
    Answerer
  • Hi

    The statistics on DEV:

    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'AirlineGroupMaster'. Scan count 0, logical reads 158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#2F8E53E2'. Scan count 1, logical reads 79, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TimefenceSubGroup'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#3082781B'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 16 ms,  elapsed time = 10 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 15 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

    (492 row(s) affected)
    Table 'AirlineGroupMaster'. Scan count 0, logical reads 1024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#2F8E53E2'. Scan count 1, logical reads 512, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FLIGHTDETAILS'. Scan count 5, logical reads 1607, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#3082781B'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 13000 ms,  elapsed time = 13373 ms.

     SQL Server Execution Times:
       CPU time = 13000 ms,  elapsed time = 13373 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 13031 ms,  elapsed time = 13392 ms.
    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

    IN PROD:

    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'AirlineGroupMaster'. Scan count 0, logical reads 158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#58010028'. Scan count 1, logical reads 79, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TimefenceSubGroup'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#58F52461'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 96 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

    (518 row(s) affected)
    Table 'AirlineGroupMaster'. Scan count 0, logical reads 2196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FLIGHTDETAILS'. Scan count 20, logical reads 19445, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#58010028'. Scan count 5, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#58F52461'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

     SQL Server Execution Times:
       CPU time = 13859 ms,  elapsed time = 16827 ms.

     SQL Server Execution Times:
       CPU time = 13859 ms,  elapsed time = 16827 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 13859 ms,  elapsed time = 16972 ms.
    SQL Server parse and compile time:
       CPU time = 0 ms, elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

    Tuesday, July 8, 2014 6:18 AM
  • Hi,

    it would be really if you could upload the execution plans of both environments and share it with us. You can upload the information to e.g. OneDrive and then share to everyone, posting the link to the shared resource here. Did you consider that the MAXDOP setting is a different one, having multiple workers getting and processing the data, thus making additional calls ?

    -Jens


    Jens K. Suessmeyer
    http://blogs.msdn.com/Jenss

    • Proposed as answer by Sofiya Li Tuesday, July 8, 2014 8:27 AM
    Tuesday, July 8, 2014 6:18 AM
  • Thank you. Are the Dev and Prod different in terms of hardware ( tempdb location) , CPU, RAM?

    SQL Server Execution Times:

       CPU time = 13031 ms,  elapsed time = 13392 ms.

    SQL Server Execution Times:
       CPU time = 13859 ms,  elapsed time = 16972 ms.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Tuesday, July 8, 2014 8:27 AM
    Tuesday, July 8, 2014 7:02 AM
    Answerer
  • Hi,

    Yeah PRODhas 32 GB RAM, 4 physical and 8 logical CPU

    DEV has 16GB RAm, 2 physical and 4 logical CPU.

    Wednesday, July 9, 2014 12:45 AM
  • Have you set MAX memory param to SQL Server? What is about a tempdb database locations.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 9, 2014 5:27 AM
    Answerer
  • To compare:

    1. REBUILD all indexes

    2. UPDATE STATISTICS

    3. Make sure that the execution plans are the same

    After the above preparation, server platform and server load will make the difference.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    • Marked as answer by Sofiya Li Tuesday, July 15, 2014 2:47 AM
    Wednesday, July 9, 2014 6:51 PM