SQL Server Performance

Slow running query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SQLWorld, Aug 10, 2009.

  1. SQLWorld New Member

    <P mce_keep="true">I have a large table with 80,000,00 records. My query is very slow it takes more then 45 minutes to return the result. let me know how can i tune it.</P><P mce_keep="true">&nbsp;<FONT face="Courier New"><FONT color=#0000ff>SELECT</FONT><FONT color=#000000> col1</FONT><FONT color=#808080><FONT color=#808080>,</FONT></FONT><FONT color=#000000> col2</FONT><FONT color=#808080><FONT color=#808080>,</FONT></FONT><FONT color=#0000ff> col3</FONT><FONT color=#808080><FONT color=#808080>,</FONT></FONT><FONT color=#0000ff> col4</FONT><FONT color=#808080><FONT color=#808080>,</FONT></FONT><FONT color=#0000ff> col5</FONT><FONT color=#808080><FONT color=#808080>,</FONT></FONT><FONT color=#0000ff> col6</FONT></FONT></P><DIV dir=ltr align=left><P><FONT size=2><FONT face="Courier New"><SPAN class=309143007-10082009>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>FROM</FONT><FONT face="Courier New"><FONT color=#000000> dbo</FONT><FONT color=#808080><FONT color=#808080>.table</FONT></FONT><FONT color=#000000> </FONT><FONT color=#0000ff><FONT color=#0000ff>AS</FONT></FONT><FONT color=#000000> br</FONT></P></FONT></FONT><P><FONT size=2><FONT face="Courier New"><SPAN class=309143007-10082009>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>ORDER</FONT><FONT face="Courier New"><FONT color=#000000> </FONT><FONT color=#0000ff><FONT color=#0000ff>BY</FONT></FONT><FONT color=#000000> br</FONT><FONT color=#808080><FONT color=#808080>.col1</FONT></FONT><FONT color=#808080><FONT color=#808080>,</FONT></FONT><FONT color=#000000> </FONT>br<FONT color=#808080><FONT color=#808080>.</FONT></FONT>col2</FONT></FONT></P><P><FONT face="Courier New">have clustred index on the table. </FONT></P><P><SPAN class=309143007-10082009><FONT face=Arial size=2><STRONG>Execution Plan:</STRONG></FONT></SPAN></P><P><SPAN class=309143007-10082009><FONT face=Arial size=2></FONT></SPAN>&nbsp;</P><P><SPAN class=309143007-10082009><IMG src="cid:309143007@10082009-3069" mce_src="cid:309143007@10082009-3069"></SPAN></P></DIV><P mce_keep="true">&nbsp;</P>
  2. madhuottapalam New Member

    You need to start from de-fragmentation of data(if needed) , check the indexes available if required create covering index, there is no WHERE condition, check what do you do with all the 8 Million record, generally, you dont really required the whole data like this from a huge table. If you can have filter get only the required data.
  3. preethi Member

    I am unable to see the execution plan. Is it possible to place the text version of it?
  4. SQLWorld New Member

    I clould have posted that. After researching for sometime it is clear the my query is a pure table scan it does not doa cluster index seek as there is no filter condition.
    I will not be able to add a filter clause as this is an ETL env. and this query is used to compare both the tables. In other tips to tune it.
    I am planning to add a covering index for the column used in the order by clause what say? check if that helps. Yes I have 64 bit sql server memeory will not be an issue as AWE is enabled with max & min memory config to 28 GB & 16 GB.
    Any forther tips to improve the performance?
  5. preethi Member

    I used to track the changes manually and upload only them. I have a transactional replication, with slight modification to handle it. You can think of using triggers or different mechanism to track the changes. Then you can bypass the compare step.
    If you table has a clustered index on Col1 & Col2you may not need another index for this operation.
    Another thing you can think for improving perfomance is using WITH (NOLOCK) option. It will reduce the lock.
    Note: I am not sure how you compare both tables where this query plays its part..
  6. SajidAttar New Member

    In addition to apply With Nolock query hint (if your business logic allows you to read uncommitted records) ...how much time your query takes if you remove the Order By Clause? and what are the columns selected for clustered index? If you face better performance by removing order by clause and you dont have clustered index on Col1 & Col2, you can think of having clustered index on these columns..
    Just my 2 cents..

Share This Page