SQL Server Performance

identical database with huge performance differenc

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by masqazi, Jul 3, 2007.

  1. masqazi New Member

    i have two identical database at two different branches. their sizes differ by about 20% and server machine are also identical. but the performance of one is very very low compare to another.

    what can be the reason and how to find them / solve them.

    your suggestions are respectfully welcomed.
  2. masqazi New Member

    to further investigate the reason, i restore both the database in my test machine, still the situation is same. one is performing normally while other sucks.

    so the hardware, server, application, etc issues seems irrelevant. i suppose its more to do with database itself.
  3. satya Moderator

    Isolate the query in question from your application. Apart from the SHOWPLAN, you must enable STATISTICS IO and STATISTICS TIME before running the query (refer to the Books Online for more information about setting these three set statements on). You must run UPDATE STATISTICS on all objects involved in the query. If the query is in the form of a stored procedure or view, it must be re-created and rerun.

    Consequently, you need to run the query in question several times, as the required data in the cache may affect query performance. For a valid comparison, the queries must be the same, and must be running against the same data set.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. masqazi New Member

    the result of one of the query is

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

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

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

    (59 row(s) affected)

    ----

    my concern is that the complete application is slow. so its not much to do with queries. I believe its more to do with database or indexes.
  5. Raulie New Member

    Have you followed Satya's suggestions? He gave many tips on how to get your queries to run the same on both databases to narrow and troubleshoot your situation.

    Raulie



  6. satya Moderator

    You have to mention about the hardware too and what times you are having performance loss, the result you have mentioned is from STATISTICS. Also copy in the queries used over here for any tips.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. masqazi New Member

    to find out the problem, i put both the database on my test machine for testing, so the hardware should not be issue. its IBM x236 with 2 GB ram and 3 Ghz. the database size is around 3 gb only.

    there is no particulare query, so that i can put up here. its overall application which is slow.

    when application is same, hardware is same, and the queries are same, then what can be the difference which is making this performance loss.
  8. satya Moderator

    Then you need few blogs in this case for a thorough checkup:
    http://sqlserver-qa.net/blogs/perft...-optimizer-reuses-for-better-performance.aspx
    http://sqlserver-qa.net/blogs/perftune/archive/2007/04/26/high-cpu-spikes-affecting-performance.aspx
    http://sqlserver-qa.net/blogs/perft...owcount-affects-the-performance-sql-2005.aspx
    http://sqlserver-qa.net/blogs/perft...-dashboard-reports-making-most-out-of-it.aspx

    Also see whether you have any petty hardware issues such as loose NIC card or any tangled cables.
    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. masqazi New Member

    below is one set of queries having an issue.

    SELECT DISTINCT SM.SM_CODE,SM.SM_NAME FROM SalesMan_Master SM JOIN CDTNOTE_HEADER CH ON SM.SM_CODE=CH.SM_CODE WHERE CH.DELETED_YN='N' AND
    CH.APPROVED_YN=1 AND CLOSED_YN='N' ORDER BY SM.SM_CODE


    /// salesman_master has pk on (sm_code,mcu)
    cdtnote_header has index with field ([LINEID] ASC,
    [MCU] ASC,
    [CDTNOTE_NO] ASC,
    [CDT_DATE] ASC,
    [CUST_CODE] ASC,
    [SM_CODE] ASC,
    [ROUTE_CODE] ASC)
    and primary key on cdtnote_no


    SELECT CDTNOTE_NO,CDT_DATE,(NET_AMOUNT - ALLOC_AMT),CUST_NAME FROM CDTNOTE_HEADER WHERE CLOSED_YN = 'N' AND DELETED_YN='N' AND ALLOC_TYPE <>
    'F' AND SM_CODE ='4400' AND APPROVED_YN=1 AND CUST_TYPE='CASH' AND NET_AMOUNT > 0


    SELECT IH.INV_NO,convert(varchar(50),IH.INV_DATE,106) AS INV_DATE,IH.CUST_NAME,IH.NET_AMOUNT,IH.ALLOC_AMT,0 as [Allocated
    Amt],IH.SM_CODE,IH.IM_COMP_DESC,IH.ALLOC_TYPE,0 FROM INV_HEADER IH JOIN CDTNOTE_HEADER CH ON IH.CUST_CODE=CH.CUST_CODE WHERE IH.ALLOC_TYPE
    <> 'F' AND (IH.NET_AMOUNT - IH.ALLOC_AMT) > 0 AND ((IH.APPROVED_YN=1 AND IH.ALLOC_CHEQUE_AMT=0) OR (IH.APPROVED_YN=1 AND IH.FROM_ATTAIN ='Y'))
    AND CH.CDTNOTE_NO='MCRH120074' AND IH.SM_CODE='4400' AND IH.DELETED_YN='N' AND IH.MCU='1' order by IH.INV_NO

    /// inv_header has index ([INV_DATE] ASC,
    [MCU] ASC,
    [PREFIX] ASC,
    [INV_NO] ASC,
    [CUST_CODE] ASC,
    [NET_AMOUNT] ASC,
    [ALLOC_AMT] ASC,
    [DELETED_YN] ASC

    an pk on inv_no, im_comp_desc

    select top 1 * from Company_master

    /// company_master has pk on company_code
    select * from cust_region_master where default_YN = 'Y'

    /// cust_region_master has pk on region_code

Share This Page