Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Unanswered: SQL Server - View timeout

    Hi,

    I'm trying to access some views that I could previously browse and am now getting a timeout message.

    I shutdown SQL Server and reconnected, this time specifying 30 seconds instead of the default 15 for the connection timeout. No luck wiht that but after repeating and specifying 60 seconds I'm still getting the same message.

    Is there another timeout setting that can be changed?

    (SQL Server 2005)

    Thank you,

    Rod
    Last edited by RodKane; 08-08-06 at 15:55.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the query behind the view is likely not written efficiently. please post your code, ddl and where your indices are placed.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2006
    Posts
    11
    Quote Originally Posted by Thrasymachus
    the query behind the view is likely not written efficiently. please post your code, ddl and where your indices are placed.
    Sean,

    Here's the code, it's accessing Perfmon related tables created by Relog, plus a couple of other tables used to add information, the indexes are included in the DDL:

    Thank you,

    Rod


    SELECT DISTINCT TOP (100) PERCENT dbo.MapSyncTimes.SyncTime
    FROM dbo.DisplayToID INNER JOIN
    dbo.CounterData ON dbo.DisplayToID.GUID = dbo.CounterData.GUID INNER JOIN
    dbo.PerfMonTestCases INNER JOIN
    dbo.MapTestCases ON dbo.PerfMonTestCases.PerfMonID = dbo.MapTestCases.PerfMonID COLLATE Latin1_General_CI_AS ON
    dbo.DisplayToID.DisplayString = dbo.MapTestCases.DisplayString INNER JOIN
    dbo.MapSyncTimes ON dbo.DisplayToID.GUID = dbo.MapSyncTimes.GUID AND dbo.CounterData.GUID = dbo.MapSyncTimes.GUID AND
    dbo.CounterData.CounterID = dbo.MapSyncTimes.CounterID AND dbo.CounterData.RecordIndex = dbo.MapSyncTimes.RecordIndex
    WHERE (dbo.PerfMonTestCases.Export = 1)


    table DDL:

    CREATE TABLE [dbo].[PerfMonTestCases](
    [PerfMonID] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Export] [bit] NULL,
    [OLAP_Server_Type] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CPM_ServerID] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OLAP_ServerID] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Relational_ServerID] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [COM_PID_CPM_App] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [COM_PID_CPM_Queued] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TestCase_TemplateName] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TestCase_UserCount] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TestCase_Program] [char](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Comments] [char](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]



    CREATE TABLE [dbo].[MapTestCases](
    [PerfMonID] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DisplayString] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]




    CREATE TABLE [dbo].[DisplayToID](
    [GUID] [uniqueidentifier] NOT NULL,
    [RunID] [int] NULL,
    [DisplayString] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LogStartTime] [char](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LogStopTime] [char](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NumberOfRecords] [int] NULL,
    [MinutesToUTC] [int] NULL,
    [TimeZoneName] [char](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRIMARY KEY CLUSTERED
    (
    [GUID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
    UNIQUE NONCLUSTERED
    (
    [DisplayString] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]




    CREATE TABLE [dbo].[CounterData](
    [GUID] [uniqueidentifier] NOT NULL,
    [CounterID] [int] NOT NULL,
    [RecordIndex] [int] NOT NULL,
    [CounterDateTime] [char](24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CounterValue] [float] NOT NULL,
    [FirstValueA] [int] NULL,
    [FirstValueB] [int] NULL,
    [SecondValueA] [int] NULL,
    [SecondValueB] [int] NULL,
    [MultiCount] [int] NULL,
    PRIMARY KEY CLUSTERED
    (
    [GUID] ASC,
    [CounterID] ASC,
    [RecordIndex] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]




    CREATE TABLE [dbo].[MapSyncTimes](
    [GUID] [uniqueidentifier] NOT NULL,
    [CounterID] [int] NOT NULL,
    [RecordIndex] [int] NOT NULL,
    [CounterDateTime] [char](24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [SyncTime] [varchar](29) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [PK__MapSyncTimes__440B1D61] PRIMARY KEY CLUSTERED
    (
    [GUID] ASC,
    [CounterID] ASC,
    [RecordIndex] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Distinct causes read aheads.

    Do you have indexes on your joins and search conditions? Do you have any index or table scans in your execution plan?

    Yesterday one of our legacy apps was timing out because some developer had 3 NOT IN (SELECT DISTINCT)s in uncorrelated subqueries in the where clause. They awed when the query went to under a second after using some left outer joins.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2006
    Posts
    11
    Thank you.

    I'll add some indexes to the columns that are joined.

    The above query is located in a view and was mostly produced by the wizard.

    I've done basic SQL before but haven't really focussed on optimizing and writing statements they way they should be written.

    In terms of <<...any index or table scans in your execution plan?>> , I'm not sure what that means, I think there's a feature in SQL Server to generate output for that which can be viewed, but if it means doing something specifically then I'll have to read up on that.


    Thanks again,

    Rod

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •