SQL Server Performance

Cannot understand where is the problem

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by waqar, Dec 19, 2006.

  1. waqar Member

    Hi Guys,

    I am using SQL 2005 Enterprise Edition with 8GB or RAM and Dual core DELL SERVER.
    I am writing a simple query to extract some data but i cannot understand why query is slow, here is my code

    ***********************************************************
    CREATE TABLE [dbo].[#TMP]
    (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [E1] [char](13),
    [E2] [varchar](25),
    [E3] [varchar](50),
    [E4] [datetime],
    [E5] [int],
    [E6] [int],
    [E7] [int],
    [E8] [Money],
    [E9] [char](10),
    [E10] [varchar](100),
    [E11] [varchar](50),
    [E12] [varchar](20),
    [E13] [char](2),
    [E14] [varchar](10),
    CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
    )


    INSERT INTO #TMP (E1,E2,E3,E4,E5,E6,E7,E8,E9,E11,E12,E13,E14)
    SELECT E1,E2,E3,E4,E5,E6,E7,E8,E9,E11,E12,E13,E14 FROM TABLE2

    --TABLE2 have only 100 record



    SELECT ID FROM #TMP WHERE ID BETWEEN 1 AND 40 --RESULT IS INSTANT

    SELECT E1,E2,E3,E4,E5,E6,E7,E8,E9,E11,E12,E13,E14 FROM #TMP WHERE ID BETWEEN 1 AND 40 --RESULT TAKING 4-5 SECONDS

    DROP TABLE #TMP
    ***********************************************************

    Is there thing missing?
    Please assist.

    ________________________________________________
    Be great in act, as you have been in thought.
  2. Adriaan New Member

    When you select the ID column only, SQL Server can read all the data it needs from the Primary Key index.

    When you select other columns, SQL Server has to read data from the table as well, which will take more time than reading from an index.

    Other than that, the server may be busy doing other things.

    When you repeat the two queries, you will notice that especially the second query will finish quicker. This is because the data from the table will have been cached after the first run, and does not need to be read from disk.
  3. waqar Member

    Adriaan,

    thanks for your quick reply.
    Server is idle no process is running, I/O queue is almost zero % utlilize. It is wired, that if i use
    SELECT ID,E1 * FROM #TMP result is still fast but as i keep adding E2,E3... speed to retrieve records seems to be slower and slower.

    Is there any thing like indexing or Statistics i can define to fasten speed while using more columns?

    ________________________________________________
    Be great in act, as you have been in thought.
  4. Adriaan New Member

    No, it just take time to read the data from the columns.

    You have a dual-core processor, so you might try to add the following phrase at the very end of your query:

    OPTION (MAXDOP 1)

    This suppresses one of SQL Server's longstanding bugs: on a multi-processor computer, it may use too many processors to execute a simple query.
  5. waqar Member

    Adriaan,

    Thanks i will try to use this Clause tomorrow.
    One more question,

    When i execute query it return result immediately (with 1 or more columns) but keep on executing and finish job like in 3-5 seconds (although all records were returned in <1 sec).

    Any idea what process is doing even after returning all records?

    ________________________________________________
    Be great in act, as you have been in thought.
  6. Adriaan New Member

    Setup a trace in Profiler to see the exact activity.

    As you are using a temp table, I guess this might be activity to drop the temp table from tempdb, and after that perhaps also the Auto-Shrink for tempdb (one of the options that you should unmark on any database).
  7. MichaelB Member

    one thought is that you could do a covering index which means you create a non-clustered index that uses every field of output that way it just has to go to the index rather than the actual table. Your table is not too wide so this should work.

    Cheers!

    Michael B
    Sr. DBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7
  8. joechang New Member

    the above table has a clustered index, so the arguments re index is not valid

    run
    SET STATISTICS TIME ON
    before the 2 queries

    if the cpu is not high, then think the problem is on the client side, not the server side
  9. HankS New Member

    quote:Originally posted by Adriaan

    When you select the ID column only, SQL Server can read all the data it needs from the Primary Key index.

    The primary key is the clustered index in this case so it is reading the data rows anyway.

    I don't understand why it would be slow though.

    Hank
  10. HankS New Member

    quote:Originally posted by MikeEBS

    one thought is that you could do a covering index which means you create a non-clustered index that uses every field of output that way it just has to go to the index rather than the actual table. Your table is not too wide so this should work.

    Cheers!

    Michael B
    Sr. DBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7

    Think about that for a minute. Create an index, in this case, that includes all the columns in the table. Essentially duplicate the table, not sure how that would improve performance.

    Hank
  11. mmarovic Active Member

    It is clear that query including bookmark lookups on top of reading index is slower but 4-5 seconds for reading 100 rows table is still too slow. It can't be explained by bookmark lookups.
  12. alzdba Member

    - what volume (mb) does this query produce ? 100 rows is how many MB ?
    - are there lobs int (varchar(max) or oldstyle ?)
    - what's the space usage of your starting table ? (npages)[?]

Share This Page