Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A query on a table of 1812 rows is taking over 3 minutes to execute. Is there a reason this should be happening?

If I execute a

SELECT * 

statement on any other table the results pop back immediately. Not sure what other information I would need to post.

Thanks

Table Def:

CREATE TABLE [dbo].[tbl_search_results]
(
    [krid] [bigint] IDENTITY(1, 1) NOT NULL,
    [kwid] [bigint] NOT NULL,           
    [peer] [varchar] (100) NOT NULL,
    [totalUPC] [int] NOT NULL DEFAULT 0,
    [totalTXT] [int] NOT NULL DEFAULT 0,

    CONSTRAINT pk_search_seed_group PRIMARY KEY (krid),
    CONSTRAINT fk_search_seed_group_keywords FOREIGN KEY (kwid) REFERENCES dbo.tbl_search_keywords(kwid)    
)

Show plan:

        <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2550.0">
          <BatchSequence>
            <Batch>
              <Statements>
                <StmtSimple StatementText="SELECT * FROM dbo.tbl_search_results&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0119419" StatementEstRows="1812" StatementOptmLevel="TRIVIAL" QueryHash="0x29FE6246B43A91F9" QueryPlanHash="0x681FEA667FB1F44D">
                  <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
                  <QueryPlan CachedPlanSize="8" CompileTime="1" CompileCPU="1" CompileMemory="56">
                    <RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1812" EstimateIO="0.00979167" EstimateCPU="0.0021502" AvgRowSize="85" EstimatedTotalSubtreeCost="0.0119419" TableCardinality="1812" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="krid" />
                        <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="kwid" />
                        <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="peer" />
                        <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="totalUPC" />
                        <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="totalTXT" />
                      </OutputList>
                      <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="krid" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="kwid" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="peer" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="totalUPC" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Column="totalTXT" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[searchTest]" Schema="[dbo]" Table="[tbl_search_results]" Index="[pk_search_seed_group]" IndexKind="Clustered" />
                      </IndexScan>
                    </RelOp>
                  </QueryPlan>
                </StmtSimple>
              </Statements>
            </Batch>
          </BatchSequence>
        </ShowPlanXML>
share|improve this question
2  
During this operation what do you see this waiting on? Use sys.dm_os_waiting_tasks to get this information. Also, can you give us the actual execution plan? I believe you have posted the estimated plan. –  Thomas Stringer Feb 1 '13 at 16:01
    
@a_horse_with_no_name I am using SSMS. I think may be due to a locking situation. I see in the dm_os_waiting_tasks a keylock is being held on the table. –  Alex Erwin Feb 1 '13 at 16:17
    
The table target is included in a transaction that I am running, should I set an isolation level so that I can still read the contents? –  Alex Erwin Feb 1 '13 at 16:22
1  
@AlexErwin There you go, now you just need to drill down into what session is holding that lock. –  Thomas Stringer Feb 1 '13 at 16:38
    
HI, sometimes when we do any select operation in any table which used by some other process can lead this issue. Try "nolock" once along with your select query. SELECT * FROM tbl_search_results WITH (NOLOCK) –  Manoj Sep 9 '13 at 12:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.