SQL Server Performance Forum – Threads Archive
identical database with huge performance differenc
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.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.
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.
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.
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
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.
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.
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.
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
]]>