|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select performance is one table, but not another
Hi,
I was noticed that at select (Select * from mytable) against one of my table in QueryAnalyzer took forever to return results. In the order of 12 minutes! There are approximately 47,000 records in the table. I did a select * against another table of the same size and it took a few seconds. The table in question has one index on an autoincrementing ID field. A row in the table consist of 20 fields and 500 bytes. Any thought on why this would take so long. Thanks, Jerry One more thing. If you do a "Select * from mytable where somedata="value" "
the query returns in 2 seconds or so. Even if somedata refers to a non-indexed column. The query plan for the qualified query shows 99% of the time spent in the table scan. Show quoteHide quote "JerryK" <jerryk@nospam.com> wrote in message news:%23K5%239IXMFHA.2132@TK2MSFTNGP14.phx.gbl... > Hi, > > I was noticed that at select (Select * from mytable) against one of my > table in QueryAnalyzer took forever to return results. In the order of 12 > minutes! There are approximately 47,000 records in the table. I did a > select * against another table of the same size and it took a few seconds. > The table in question has one index on an autoincrementing ID field. A row > in the table consist of 20 fields and 500 bytes. > > Any thought on why this would take so long. > > Thanks, > > Jerry > > > > Found it.
There is text column in the row and some of the text entries have 2 MB's worth of data! Show quoteHide quote "JerryK" <jerryk@nospam.com> wrote in message news:OmcFbQXMFHA.3660@TK2MSFTNGP12.phx.gbl... > One more thing. If you do a "Select * from mytable where somedata="value" > " the query returns in 2 seconds or so. Even if somedata refers to a > non-indexed column. The query plan for the qualified query shows 99% of > the time spent in the table scan. > > "JerryK" <jerryk@nospam.com> wrote in message > news:%23K5%239IXMFHA.2132@TK2MSFTNGP14.phx.gbl... >> Hi, >> >> I was noticed that at select (Select * from mytable) against one of my >> table in QueryAnalyzer took forever to return results. In the order of >> 12 minutes! There are approximately 47,000 records in the table. I did a >> select * against another table of the same size and it took a few >> seconds. The table in question has one index on an autoincrementing ID >> field. A row in the table consist of 20 fields and 500 bytes. >> >> Any thought on why this would take so long. >> >> Thanks, >> >> Jerry >> >> >> >> > > Someone probably had had you blocked for most of that time.
-- Show quoteHide quoteAndrew J. Kelly SQL MVP "JerryK" <jerryk@nospam.com> wrote in message news:%23K5%239IXMFHA.2132@TK2MSFTNGP14.phx.gbl... > Hi, > > I was noticed that at select (Select * from mytable) against one of my > table in QueryAnalyzer took forever to return results. In the order of 12 > minutes! There are approximately 47,000 records in the table. I did a > select * against another table of the same size and it took a few seconds. > The table in question has one index on an autoincrementing ID field. A row > in the table consist of 20 fields and 500 bytes. > > Any thought on why this would take so long. > > Thanks, > > Jerry > > > > No conflict, I am the only on the database and on the machine
Strangely, SQLServer. exe starts to eat up all available memory on the machine. The memory allocation grows from 120 MBytes to 400 MBytes in abvout 2 minutes. DBCC Checkdatabase comes back clean, as does a DBCC Checktable on the table. jerry Show quoteHide quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23ZkdpeXMFHA.4076@TK2MSFTNGP10.phx.gbl... > Someone probably had had you blocked for most of that time. > > -- > Andrew J. Kelly SQL MVP > > > "JerryK" <jerryk@nospam.com> wrote in message > news:%23K5%239IXMFHA.2132@TK2MSFTNGP14.phx.gbl... >> Hi, >> >> I was noticed that at select (Select * from mytable) against one of my >> table in QueryAnalyzer took forever to return results. In the order of >> 12 minutes! There are approximately 47,000 records in the table. I did a >> select * against another table of the same size and it took a few >> seconds. The table in question has one index on an autoincrementing ID >> field. A row in the table consist of 20 fields and 500 bytes. >> >> Any thought on why this would take so long. >> >> Thanks, >> >> Jerry >> >> >> >> > > Why is that strange? It is the exact behavior that you should expect. SQL
Server will use up all memory available (minus a small amount) if it thinks it can use it. It does not release it once it grabs it unless the OS specifically asks for it. You are selecting all the rows (why who knows<g>) from a table with large blobs in it. That will certainly use up a lot of memory. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "JerryK" <jerryk@nospam.com> wrote in message news:eXhpErXMFHA.1308@TK2MSFTNGP15.phx.gbl... > No conflict, I am the only on the database and on the machine > > Strangely, SQLServer. exe starts to eat up all available memory on the > machine. The memory allocation grows from 120 MBytes to 400 MBytes in > abvout 2 minutes. DBCC Checkdatabase comes back clean, as does a DBCC > Checktable on the table. > > jerry > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:%23ZkdpeXMFHA.4076@TK2MSFTNGP10.phx.gbl... >> Someone probably had had you blocked for most of that time. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "JerryK" <jerryk@nospam.com> wrote in message >> news:%23K5%239IXMFHA.2132@TK2MSFTNGP14.phx.gbl... >>> Hi, >>> >>> I was noticed that at select (Select * from mytable) against one of my >>> table in QueryAnalyzer took forever to return results. In the order of >>> 12 minutes! There are approximately 47,000 records in the table. I did >>> a select * against another table of the same size and it took a few >>> seconds. The table in question has one index on an autoincrementing ID >>> field. A row in the table consist of 20 fields and 500 bytes. >>> >>> Any thought on why this would take so long. >>> >>> Thanks, >>> >>> Jerry >>> >>> >>> >>> >> >> > >  
Other interesting topics
Need help on Effective Reindexing Stragtegy.
Primary File Group Full? SAN issues, please help Determine cause of Timeouts Managing TB size of data TRXN LOG BKP time takes so much longer than Full BKP??? Data Migration Recommendations Needed Debug SP not stopping on Breakpoint Help needed with OpenQuery Backing up a database? |
|||||||||||||||||||||||
