SQL Server Performance

Indexing question

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by cwnichols, Sep 11, 2009.

  1. cwnichols New Member

    Hi All, I have an indexing question. I have table with the following layout that contains 100MM records: CREATE TABLE [dbo].[Cell88]( [firstname] [varchar](50) NULL, [middleinit] [varchar](50) NULL, [lastname] [varchar](50) NULL, [streetnumber] [varchar](50) NULL, [streetname] [varchar](50) NULL, [streetaddress] [varchar](50) NULL, [city] [varchar](50) NULL, [state] [varchar](50) NULL, [zip] [varchar](50) NULL, [telephonenumber] [varchar](50) NULL) ON [PRIMARY] I’m using Microsoft Linq to query. My query looks like this: SELECT TOP (1) [t0].[firstname], [t0].[middleinit], [t0].[lastname], [t0].[streetnumber], [t0].[streetname], [t0].[streetaddress], [t0].[city], [t0].[state], [t0].[zip], [t0].[telephonenumber]FROM [Cell88].[dbo].[Cell88] AS [t0]WHERE ([t0].[streetnumber] = '1000') AND ([t0].[streetname] = 'main') AND ([t0].[city] = 'anytown') AND ([t0].[state] = 'wa') AND ([t0].[lastname] = 'smith')
    The client stats says I'm doing a clustered index seek. Can anybody tell me what the proper index should be?
    Thanks,
    Chris
  2. moh_hassan20 New Member

    welcome to the forum
    [quote user="cwnichols"]The client stats says I'm doing a clustered index seek.[/quote]
    what is wrong for the clustered index seek? (or you mean clustered index scan)
    that means that your clustered index contain all columns in the where condition, which is good
    i notice that all columns are NULL and no primary key !! which mean that the clustered index is not unique!!.
  3. Adriaan New Member

    An index seek is what you want in a query - it means the index is covering for your query.
    If the execution plan mentions a table scan, that's when you may have a problem. If it mentions an index scan, there is room for improvement.
  4. cwnichols New Member

    You are both right. I was doing an index seek but my index wasn't covering so a lookup was happening too. I rebuilt the index and everything is good now. Thanks.
  5. moh_hassan20 New Member

    i am happy to hear everything is good

Share This Page