Loading ...
Sorry, an error occurred while loading the content.

A DBA question regarding SQL Server 2K

Expand Messages
  • John Warner
    Still dealing with the query that worked fine and now times out. One thing the query does is create several temp tables. Would this action be recorded in the
    Message 1 of 24 , Oct 6, 2008
    • 0 Attachment
      Still dealing with the query that worked fine and now times out.

      One thing the query does is create several temp tables. Would this action
      be recorded in the transaction log in SQL Server? Would this recording
      impact performance significantly?


      Thanks.

      John Warner
    • Paul Livengood
      Yes, all transactions in the TEMPDB are logged, but this should not impact performance so much that your query becomes unusuable. However, if you are using
      Message 2 of 24 , Oct 6, 2008
      • 0 Attachment
        Yes, all transactions in the TEMPDB are logged, but this should not
        impact performance so much that your query becomes unusuable.

        However, if you are using temp tables that you load with lots of data
        and then using any links or Where clauses against the temp table, you
        may see some performance hits if you do not index your temp tables.
        With a few exceptions you can apply an index against a temp table just
        like you do a normal table. Keep in mind this will make the loading of
        the temp table longer, but it will increase read times.

        I know that you already mentioned that you are rebuilding the indexes,
        but are you sure the indexes are being used and that they are optimal?
        Have you run an execution plan against the query? If so, do you have
        one to compare it against from when the query was running well? Can
        you post them here?


        HTH
        Paul




        --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
        >
        > Still dealing with the query that worked fine and now times out.
        >
        > One thing the query does is create several temp tables. Would this
        action
        > be recorded in the transaction log in SQL Server? Would this recording
        > impact performance significantly?
        >
        >
        > Thanks.
        >
        > John Warner
        >
      • John Warner
        Thanks for the information on the TempDB and the log. Is there by chance away to disable logging while the query runs? No data is written so there is no risk
        Message 3 of 24 , Oct 6, 2008
        • 0 Attachment
          Thanks for the information on the TempDB and the log. Is there by chance
          away to disable logging while the query runs? No data is written so there
          is no risk of data loss but if logging is turned off it must be just for
          the process running the query other users still need that working as
          normal.

          Re the execution plan it looks like it is making good use of indexes, but
          alas I do not have one from before. It just wasn't an issue.

          Thanks again!

          John Warner




          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
          > Sent: Monday, October 06, 2008 11:56 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] Re: A DBA question regarding SQL Server 2K
          >
          >
          > Yes, all transactions in the TEMPDB are logged, but this should not
          > impact performance so much that your query becomes unusuable.
          >
          > However, if you are using temp tables that you load with lots
          > of data
          > and then using any links or Where clauses against the temp table, you
          > may see some performance hits if you do not index your temp tables.
          > With a few exceptions you can apply an index against a temp
          > table just
          > like you do a normal table. Keep in mind this will make the
          > loading of
          > the temp table longer, but it will increase read times.
          >
          > I know that you already mentioned that you are rebuilding the
          > indexes,
          > but are you sure the indexes are being used and that they are
          > optimal?
          > Have you run an execution plan against the query? If so, do you have
          > one to compare it against from when the query was running well? Can
          > you post them here?
          >
          >
          > HTH
          > Paul
          >
          >
          >
          >
          > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
          > <john@...> wrote:
          > >
          > > Still dealing with the query that worked fine and now times out.
          > >
          > > One thing the query does is create several temp tables. Would this
          > action
          > > be recorded in the transaction log in SQL Server? Would
          > this recording
          > > impact performance significantly?
          > >
          > >
          > > Thanks.
          > >
          > > John Warner
          > >
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • Paul Livengood
          Nope.  Logging is automatic and non-negotiable in SQL.  However, you can make sure you have as little impact as possible by doing a few things.  * Make sure
          Message 4 of 24 , Oct 6, 2008
          • 0 Attachment
            Nope.  Logging is automatic and non-negotiable in SQL.  However, you can make sure you have as little impact as possible by doing a few things. 

            * Make sure your tempdb is set to SIMPLE backup mode.
            * On your tempdb you should have Truncate on Checkpoint set to TRUE.
            * If you are dealing with large amounts of data (say loading in 10 million rows, you can try using a loop with batches only loading in 100k at a time).  This will lesson the imapct on the log. 

            Can you post the actual exection plan (Text Mode) from the query?



             


            ----- Original Message ----
            From: John Warner <john@...>
            To: SQLQueriesNoCode@yahoogroups.com
            Sent: Monday, October 6, 2008 10:02:07 AM
            Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding SQL Server 2K


            Thanks for the information on the TempDB and the log. Is there by chance
            away to disable logging while the query runs? No data is written so there
            is no risk of data loss but if logging is turned off it must be just for
            the process running the query other users still need that working as
            normal.

            Re the execution plan it looks like it is making good use of indexes, but
            alas I do not have one from before. It just wasn't an issue.

            Thanks again!

            John Warner

            > -----Original Message-----
            > From: SQLQueriesNoCode@ yahoogroups. com
            > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Paul Livengood
            > Sent: Monday, October 06, 2008 11:56 AM
            > To: SQLQueriesNoCode@ yahoogroups. com
            > Subject: [SQLQueriesNoCode] Re: A DBA question regarding SQL Server 2K
            >
            >
            > Yes, all transactions in the TEMPDB are logged, but this should not
            > impact performance so much that your query becomes unusuable.
            >
            > However, if you are using temp tables that you load with lots
            > of data
            > and then using any links or Where clauses against the temp table, you
            > may see some performance hits if you do not index your temp tables.
            > With a few exceptions you can apply an index against a temp
            > table just
            > like you do a normal table. Keep in mind this will make the
            > loading of
            > the temp table longer, but it will increase read times.
            >
            > I know that you already mentioned that you are rebuilding the
            > indexes,
            > but are you sure the indexes are being used and that they are
            > optimal?
            > Have you run an execution plan against the query? If so, do you have
            > one to compare it against from when the query was running well? Can
            > you post them here?
            >
            >
            > HTH
            > Paul
            >
            >
            >
            >
            > --- In SQLQueriesNoCode@ yahoogroups. com, "John Warner"
            > <john@...> wrote:
            > >
            > > Still dealing with the query that worked fine and now times out.
            > >
            > > One thing the query does is create several temp tables. Would this
            > action
            > > be recorded in the transaction log in SQL Server? Would
            > this recording
            > > impact performance significantly?
            > >
            > >
            > > Thanks.
            > >
            > > John Warner
            > >
            >
            >
            >
            > ------------ --------- --------- ------
            >
            > Yahoo! Groups Links
            >
            >
            >



            [Non-text portions of this message have been removed]
          • Arnie Rowland
            You can t selectively turn off logging. If you have any indexes on the #Temp tables, completely load the #Temp tables BEFORE indexing -it will be faster.
            Message 5 of 24 , Oct 6, 2008
            • 0 Attachment
              You can't selectively 'turn off' logging.

              If you have any indexes on the #Temp tables, completely load the #Temp
              tables BEFORE indexing -it will be faster.

              Have you considered derived tables instead of #Temp tables?


              Regards,

              Arnie Rowland, MVP (SQL Server)

              "Fortune favors the prepared mind." Louis Pasteur


              -----Original Message-----
              From: SQLQueriesNoCode@yahoogroups.com
              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
              Sent: Monday, October 06, 2008 9:05 AM
              To: Arnie
              Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding SQL Server
              2K

              Thanks for the information on the TempDB and the log. Is there by chance
              away to disable logging while the query runs? No data is written so
              there
              is no risk of data loss but if logging is turned off it must be just for
              the process running the query other users still need that working as
              normal.

              Re the execution plan it looks like it is making good use of indexes,
              but
              alas I do not have one from before. It just wasn't an issue.

              Thanks again!

              John Warner




              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
              > Sent: Monday, October 06, 2008 11:56 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] Re: A DBA question regarding SQL Server 2K
              >
              >
              > Yes, all transactions in the TEMPDB are logged, but this should not
              > impact performance so much that your query becomes unusuable.
              >
              > However, if you are using temp tables that you load with lots
              > of data
              > and then using any links or Where clauses against the temp table, you
              > may see some performance hits if you do not index your temp tables.
              > With a few exceptions you can apply an index against a temp
              > table just
              > like you do a normal table. Keep in mind this will make the
              > loading of
              > the temp table longer, but it will increase read times.
              >
              > I know that you already mentioned that you are rebuilding the
              > indexes,
              > but are you sure the indexes are being used and that they are
              > optimal?
              > Have you run an execution plan against the query? If so, do you have
              > one to compare it against from when the query was running well? Can
              > you post them here?
              >
              >
              > HTH
              > Paul
              >
              >
              >
              >
              > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
              > <john@...> wrote:
              > >
              > > Still dealing with the query that worked fine and now times out.
              > >
              > > One thing the query does is create several temp tables. Would this
              > action
              > > be recorded in the transaction log in SQL Server? Would
              > this recording
              > > impact performance significantly?
              > >
              > >
              > > Thanks.
              > >
              > > John Warner
              > >
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >



              ------------------------------------

              Yahoo! Groups Links







              Disclaimer - October 6, 2008
              This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
              This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
            • John Warner
              What s a derived table? What is happening is I have some arrays that I have to pass to the query (sort of long IN clauses) that I put into temp tables and
              Message 6 of 24 , Oct 6, 2008
              • 0 Attachment
                What's a derived table? What is happening is I have some 'arrays' that I
                have to pass to the query (sort of long IN clauses) that I put into temp
                tables and then Join the column of the table(s) I querying. These tables
                are really not that large, at most maybe a couple thousand rows for the
                biggest one and fewer then 50 or so for the smallest. About 5 fields in
                all.
                I'm also noticing performance hits on other queries from this database
                thought these are much simpler queries. I just keep looking for what has
                impacted the database in general and fixes for this one query in
                particular.

                Paul at the moment I can't fetch an execution plan at all. I'm not in a
                position to run the query until tomorrow or Thursday. Function of tasking
                and creating the 'arrays' to pass in.

                Thank you both for the ideas and advice.

                John Warner




                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                > Sent: Monday, October 06, 2008 12:15 PM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                > SQL Server 2K
                >
                >
                > You can't selectively 'turn off' logging.
                >
                > If you have any indexes on the #Temp tables, completely load
                > the #Temp tables BEFORE indexing -it will be faster.
                >
                > Have you considered derived tables instead of #Temp tables?
                >
                >
                > Regards,
                >
                > Arnie Rowland, MVP (SQL Server)
                >
                > "Fortune favors the prepared mind." Louis Pasteur
                >
                >
                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                > Sent: Monday, October 06, 2008 9:05 AM
                > To: Arnie
                > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                > SQL Server 2K
                >
                > Thanks for the information on the TempDB and the log. Is
                > there by chance away to disable logging while the query runs?
                > No data is written so there is no risk of data loss but if
                > logging is turned off it must be just for the process running
                > the query other users still need that working as normal.
                >
                > Re the execution plan it looks like it is making good use of
                > indexes, but alas I do not have one from before. It just
                > wasn't an issue.
                >
                > Thanks again!
                >
                > John Warner
                >
                >
                >
                >
                > > -----Original Message-----
                > > From: SQLQueriesNoCode@yahoogroups.com
                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul
                > Livengood
                > > Sent: Monday, October 06, 2008 11:56 AM
                > > To: SQLQueriesNoCode@yahoogroups.com
                > > Subject: [SQLQueriesNoCode] Re: A DBA question regarding
                > SQL Server 2K
                > >
                > >
                > > Yes, all transactions in the TEMPDB are logged, but this should not
                > > impact performance so much that your query becomes unusuable.
                > >
                > > However, if you are using temp tables that you load with lots
                > > of data
                > > and then using any links or Where clauses against the temp
                > table, you
                > > may see some performance hits if you do not index your temp
                > tables.
                > > With a few exceptions you can apply an index against a temp
                > > table just
                > > like you do a normal table. Keep in mind this will make the
                > > loading of
                > > the temp table longer, but it will increase read times.
                > >
                > > I know that you already mentioned that you are rebuilding the
                > > indexes,
                > > but are you sure the indexes are being used and that they are
                > > optimal?
                > > Have you run an execution plan against the query? If so,
                > do you have
                > > one to compare it against from when the query was running
                > well? Can
                > > you post them here?
                > >
                > >
                > > HTH
                > > Paul
                > >
                > >
                > >
                > >
                > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                > > <john@...> wrote:
                > > >
                > > > Still dealing with the query that worked fine and now times out.
                > > >
                > > > One thing the query does is create several temp tables. Would this
                > > action
                > > > be recorded in the transaction log in SQL Server? Would
                > > this recording
                > > > impact performance significantly?
                > > >
                > > >
                > > > Thanks.
                > > >
                > > > John Warner
                > > >
                > >
                > >
                > >
                > > ------------------------------------
                > >
                > > Yahoo! Groups Links
                > >
                > >
                > >
                >
                >
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >
                >
                >
                >
                >
                > Disclaimer - October 6, 2008
                > This email and any files transmitted with it are confidential
                > and intended solely for SQLQueriesNoCode@yahoogroups.com. If
                > you are not the named addressee you should not disseminate,
                > distribute, copy or alter this email. Any views or opinions
                > presented in this email are solely those of the author and
                > might not represent those of Westwood Consulting, Inc.
                > Warning: Although Westwood Consulting, Inc has taken
                > reasonable precautions to ensure no viruses are present in
                > this email, the company cannot accept responsibility for any
                > loss or damage arising from the use of this email or attachments.
                > This disclaimer was added by Policy Patrol:
                > http://www.policypatrol.com/
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >
              • Dave May
                John, When optimizing a query I usually start by gathering IO statistics with SET STATISTICS IO ON, followed by analysis of the execution plan. For the
                Message 7 of 24 , Oct 6, 2008
                • 0 Attachment
                  John,

                  When optimizing a query I usually start by gathering IO statistics with
                  SET STATISTICS IO ON, followed by analysis of the execution plan. For
                  the mailing list the short version of the plan will probably work best,
                  but you can also use SHOWPLAN_ALL for much more detailed information.

                  I've included a snippet below that shows how to use these two techniques
                  in the AdventureWorks database -- when you get a chance can you run your
                  query with these options set and post the results?

                  set statistics io on

                  select top 10 *
                  from person.address
                  where addressline1 like '%1970%'

                  go
                  set showplan_text on
                  go

                  select top 10 *
                  from person.address
                  where addressline1 like '%1970%'

                  go
                  set showplan_text off
                  go

                  Dave.

                  -----Original Message-----
                  From: SQLQueriesNoCode@yahoogroups.com
                  [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  Sent: Monday, October 06, 2008 12:28 PM
                  To: SQLQueriesNoCode@yahoogroups.com
                  Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding SQL Server
                  2K

                  What's a derived table? What is happening is I have some 'arrays' that I
                  have to pass to the query (sort of long IN clauses) that I put into temp
                  tables and then Join the column of the table(s) I querying. These tables
                  are really not that large, at most maybe a couple thousand rows for the
                  biggest one and fewer then 50 or so for the smallest. About 5 fields in
                  all.
                  I'm also noticing performance hits on other queries from this database
                  thought these are much simpler queries. I just keep looking for what has
                  impacted the database in general and fixes for this one query in
                  particular.

                  Paul at the moment I can't fetch an execution plan at all. I'm not in a
                  position to run the query until tomorrow or Thursday. Function of
                  tasking
                  and creating the 'arrays' to pass in.

                  Thank you both for the ideas and advice.

                  John Warner




                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                  > Sent: Monday, October 06, 2008 12:15 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                  > SQL Server 2K
                  >
                  >
                  > You can't selectively 'turn off' logging.
                  >
                  > If you have any indexes on the #Temp tables, completely load
                  > the #Temp tables BEFORE indexing -it will be faster.
                  >
                  > Have you considered derived tables instead of #Temp tables?
                  >
                  >
                  > Regards,
                  >
                  > Arnie Rowland, MVP (SQL Server)
                  >
                  > "Fortune favors the prepared mind." Louis Pasteur
                  >
                  >
                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  > Sent: Monday, October 06, 2008 9:05 AM
                  > To: Arnie
                  > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                  > SQL Server 2K
                  >
                  > Thanks for the information on the TempDB and the log. Is
                  > there by chance away to disable logging while the query runs?
                  > No data is written so there is no risk of data loss but if
                  > logging is turned off it must be just for the process running
                  > the query other users still need that working as normal.
                  >
                  > Re the execution plan it looks like it is making good use of
                  > indexes, but alas I do not have one from before. It just
                  > wasn't an issue.
                  >
                  > Thanks again!
                  >
                  > John Warner
                  >
                  >
                  >
                  >
                  > > -----Original Message-----
                  > > From: SQLQueriesNoCode@yahoogroups.com
                  > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul
                  > Livengood
                  > > Sent: Monday, October 06, 2008 11:56 AM
                  > > To: SQLQueriesNoCode@yahoogroups.com
                  > > Subject: [SQLQueriesNoCode] Re: A DBA question regarding
                  > SQL Server 2K
                  > >
                  > >
                  > > Yes, all transactions in the TEMPDB are logged, but this should not
                  > > impact performance so much that your query becomes unusuable.
                  > >
                  > > However, if you are using temp tables that you load with lots
                  > > of data
                  > > and then using any links or Where clauses against the temp
                  > table, you
                  > > may see some performance hits if you do not index your temp
                  > tables.
                  > > With a few exceptions you can apply an index against a temp
                  > > table just
                  > > like you do a normal table. Keep in mind this will make the
                  > > loading of
                  > > the temp table longer, but it will increase read times.
                  > >
                  > > I know that you already mentioned that you are rebuilding the
                  > > indexes,
                  > > but are you sure the indexes are being used and that they are
                  > > optimal?
                  > > Have you run an execution plan against the query? If so,
                  > do you have
                  > > one to compare it against from when the query was running
                  > well? Can
                  > > you post them here?
                  > >
                  > >
                  > > HTH
                  > > Paul
                  > >
                  > >
                  > >
                  > >
                  > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                  > > <john@...> wrote:
                  > > >
                  > > > Still dealing with the query that worked fine and now times out.
                  > > >
                  > > > One thing the query does is create several temp tables. Would this
                  > > action
                  > > > be recorded in the transaction log in SQL Server? Would
                  > > this recording
                  > > > impact performance significantly?
                  > > >
                  > > >
                  > > > Thanks.
                  > > >
                  > > > John Warner
                  > > >
                  > >
                  > >
                  > >
                  > > ------------------------------------
                  > >
                  > > Yahoo! Groups Links
                  > >
                  > >
                  > >
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                  >
                  > Disclaimer - October 6, 2008
                  > This email and any files transmitted with it are confidential
                  > and intended solely for SQLQueriesNoCode@yahoogroups.com. If
                  > you are not the named addressee you should not disseminate,
                  > distribute, copy or alter this email. Any views or opinions
                  > presented in this email are solely those of the author and
                  > might not represent those of Westwood Consulting, Inc.
                  > Warning: Although Westwood Consulting, Inc has taken
                  > reasonable precautions to ensure no viruses are present in
                  > this email, the company cannot accept responsibility for any
                  > loss or damage arising from the use of this email or attachments.
                  > This disclaimer was added by Policy Patrol:
                  > http://www.policypatrol.com/
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >



                  ------------------------------------

                  Yahoo! Groups Links
                • John Warner
                  Thanks and will do. All these wonderful ideas and there is nothing I can do about it today. I should have waited to ask but I was looking at help on another
                  Message 8 of 24 , Oct 6, 2008
                  • 0 Attachment
                    Thanks and will do. All these wonderful ideas and there is nothing I can do
                    about it today. I should have waited to ask but I was looking at help on
                    another issue had had the idea about the transaction log.

                    Thank you.

                    John Warner




                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Dave May
                    > Sent: Monday, October 06, 2008 12:31 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                    > SQL Server 2K
                    >
                    >
                    > John,
                    >
                    > When optimizing a query I usually start by gathering IO
                    > statistics with SET STATISTICS IO ON, followed by analysis of
                    > the execution plan. For the mailing list the short version
                    > of the plan will probably work best, but you can also use
                    > SHOWPLAN_ALL for much more detailed information.
                    >
                    > I've included a snippet below that shows how to use these two
                    > techniques in the AdventureWorks database -- when you get a
                    > chance can you run your query with these options set and post
                    > the results?
                    >
                    > set statistics io on
                    >
                    > select top 10 *
                    > from person.address
                    > where addressline1 like '%1970%'
                    >
                    > go
                    > set showplan_text on
                    > go
                    >
                    > select top 10 *
                    > from person.address
                    > where addressline1 like '%1970%'
                    >
                    > go
                    > set showplan_text off
                    > go
                    >
                    > Dave.
                    >
                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    > Sent: Monday, October 06, 2008 12:28 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                    > SQL Server 2K
                    >
                    > What's a derived table? What is happening is I have some
                    > 'arrays' that I have to pass to the query (sort of long IN
                    > clauses) that I put into temp tables and then Join the column
                    > of the table(s) I querying. These tables are really not that
                    > large, at most maybe a couple thousand rows for the biggest
                    > one and fewer then 50 or so for the smallest. About 5 fields
                    > in all. I'm also noticing performance hits on other queries
                    > from this database thought these are much simpler queries. I
                    > just keep looking for what has impacted the database in
                    > general and fixes for this one query in particular.
                    >
                    > Paul at the moment I can't fetch an execution plan at all.
                    > I'm not in a position to run the query until tomorrow or
                    > Thursday. Function of tasking and creating the 'arrays' to pass in.
                    >
                    > Thank you both for the ideas and advice.
                    >
                    > John Warner
                    >
                    >
                    >
                    >
                    > > -----Original Message-----
                    > > From: SQLQueriesNoCode@yahoogroups.com
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                    > > Sent: Monday, October 06, 2008 12:15 PM
                    > > To: SQLQueriesNoCode@yahoogroups.com
                    > > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                    > > SQL Server 2K
                    > >
                    > >
                    > > You can't selectively 'turn off' logging.
                    > >
                    > > If you have any indexes on the #Temp tables, completely load
                    > > the #Temp tables BEFORE indexing -it will be faster.
                    > >
                    > > Have you considered derived tables instead of #Temp tables?
                    > >
                    > >
                    > > Regards,
                    > >
                    > > Arnie Rowland, MVP (SQL Server)
                    > >
                    > > "Fortune favors the prepared mind." Louis Pasteur
                    > >
                    > >
                    > > -----Original Message-----
                    > > From: SQLQueriesNoCode@yahoogroups.com
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    > > Sent: Monday, October 06, 2008 9:05 AM
                    > > To: Arnie
                    > > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                    > > SQL Server 2K
                    > >
                    > > Thanks for the information on the TempDB and the log. Is
                    > > there by chance away to disable logging while the query runs?
                    > > No data is written so there is no risk of data loss but if
                    > > logging is turned off it must be just for the process running
                    > > the query other users still need that working as normal.
                    > >
                    > > Re the execution plan it looks like it is making good use of
                    > > indexes, but alas I do not have one from before. It just
                    > > wasn't an issue.
                    > >
                    > > Thanks again!
                    > >
                    > > John Warner
                    > >
                    > >
                    > >
                    > >
                    > > > -----Original Message-----
                    > > > From: SQLQueriesNoCode@yahoogroups.com
                    > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul
                    > > Livengood
                    > > > Sent: Monday, October 06, 2008 11:56 AM
                    > > > To: SQLQueriesNoCode@yahoogroups.com
                    > > > Subject: [SQLQueriesNoCode] Re: A DBA question regarding
                    > > SQL Server 2K
                    > > >
                    > > >
                    > > > Yes, all transactions in the TEMPDB are logged, but this
                    > should not
                    > > > impact performance so much that your query becomes unusuable.
                    > > >
                    > > > However, if you are using temp tables that you load with lots of
                    > > > data
                    > > > and then using any links or Where clauses against the temp
                    > > table, you
                    > > > may see some performance hits if you do not index your temp
                    > > tables.
                    > > > With a few exceptions you can apply an index against a temp
                    > > > table just
                    > > > like you do a normal table. Keep in mind this will make the
                    > > > loading of
                    > > > the temp table longer, but it will increase read times.
                    > > >
                    > > > I know that you already mentioned that you are rebuilding the
                    > > > indexes, but are you sure the indexes are being used and
                    > that they
                    > > > are optimal?
                    > > > Have you run an execution plan against the query? If so,
                    > > do you have
                    > > > one to compare it against from when the query was running
                    > > well? Can
                    > > > you post them here?
                    > > >
                    > > >
                    > > > HTH
                    > > > Paul
                    > > >
                    > > >
                    > > >
                    > > >
                    > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
                    > > > wrote:
                    > > > >
                    > > > > Still dealing with the query that worked fine and now times out.
                    > > > >
                    > > > > One thing the query does is create several temp tables.
                    > Would this
                    > > > action
                    > > > > be recorded in the transaction log in SQL Server? Would
                    > > > this recording
                    > > > > impact performance significantly?
                    > > > >
                    > > > >
                    > > > > Thanks.
                    > > > >
                    > > > > John Warner
                    > > > >
                    > > >
                    > > >
                    > > >
                    > > > ------------------------------------
                    > > >
                    > > > Yahoo! Groups Links
                    > > >
                    > > >
                    > > >
                    > >
                    > >
                    > >
                    > > ------------------------------------
                    > >
                    > > Yahoo! Groups Links
                    > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > > Disclaimer - October 6, 2008
                    > > This email and any files transmitted with it are confidential
                    > > and intended solely for SQLQueriesNoCode@yahoogroups.com. If
                    > > you are not the named addressee you should not disseminate,
                    > > distribute, copy or alter this email. Any views or opinions
                    > > presented in this email are solely those of the author and
                    > > might not represent those of Westwood Consulting, Inc.
                    > > Warning: Although Westwood Consulting, Inc has taken
                    > > reasonable precautions to ensure no viruses are present in
                    > > this email, the company cannot accept responsibility for any
                    > > loss or damage arising from the use of this email or attachments.
                    > > This disclaimer was added by Policy Patrol:
                    > > http://www.policypatrol.com/
                    > >
                    > > ------------------------------------
                    > >
                    > > Yahoo! Groups Links
                    > >
                    > >
                    > >
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                  • Paul Livengood
                    A derived table is basically an instant temp table.   SELECT * from (select name from employees) DerTabA   The system will derive the Select name from
                    Message 9 of 24 , Oct 6, 2008
                    • 0 Attachment
                      A derived table is basically an instant temp table.
                       
                      SELECT *
                      from (select name from employees) DerTabA
                       
                      The system will derive the "Select name from employee" results into a temp table and then run a select against that derived table. 
                       
                      There could be lots of items getting in the way of your queries running well.  Blocking, bad disk in your array, memory issues, etc. 
                       
                      I would start with the basics and make sure you are not paging too much memory and that all your disks are functioning normally.   
                      After that I would recommend checking for blocking in general on your server.   These may help you get some general information about slowness in your system, but not about that one query.
                       
                      Paul
                       



                      ----- Original Message ----
                      From: John Warner <john@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Monday, October 6, 2008 10:27:50 AM
                      Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding SQL Server 2K


                      What's a derived table? What is happening is I have some 'arrays' that I
                      have to pass to the query (sort of long IN clauses) that I put into temp
                      tables and then Join the column of the table(s) I querying. These tables
                      are really not that large, at most maybe a couple thousand rows for the
                      biggest one and fewer then 50 or so for the smallest. About 5 fields in
                      all.
                      I'm also noticing performance hits on other queries from this database
                      thought these are much simpler queries. I just keep looking for what has
                      impacted the database in general and fixes for this one query in
                      particular.

                      Paul at the moment I can't fetch an execution plan at all. I'm not in a
                      position to run the query until tomorrow or Thursday. Function of tasking
                      and creating the 'arrays' to pass in.

                      Thank you both for the ideas and advice.

                      John Warner

                      > -----Original Message-----
                      > From: SQLQueriesNoCode@ yahoogroups. com
                      > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Arnie Rowland
                      > Sent: Monday, October 06, 2008 12:15 PM
                      > To: SQLQueriesNoCode@ yahoogroups. com
                      > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                      > SQL Server 2K
                      >
                      >
                      > You can't selectively 'turn off' logging.
                      >
                      > If you have any indexes on the #Temp tables, completely load
                      > the #Temp tables BEFORE indexing -it will be faster.
                      >
                      > Have you considered derived tables instead of #Temp tables?
                      >
                      >
                      > Regards,
                      >
                      > Arnie Rowland, MVP (SQL Server)
                      >
                      > "Fortune favors the prepared mind." Louis Pasteur
                      >
                      >
                      > -----Original Message-----
                      > From: SQLQueriesNoCode@ yahoogroups. com
                      > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of John Warner
                      > Sent: Monday, October 06, 2008 9:05 AM
                      > To: Arnie
                      > Subject: RE: [SQLQueriesNoCode] Re: A DBA question regarding
                      > SQL Server 2K
                      >
                      > Thanks for the information on the TempDB and the log. Is
                      > there by chance away to disable logging while the query runs?
                      > No data is written so there is no risk of data loss but if
                      > logging is turned off it must be just for the process running
                      > the query other users still need that working as normal.
                      >
                      > Re the execution plan it looks like it is making good use of
                      > indexes, but alas I do not have one from before. It just
                      > wasn't an issue.
                      >
                      > Thanks again!
                      >
                      > John Warner
                      >
                      >
                      >
                      >
                      > > -----Original Message-----
                      > > From: SQLQueriesNoCode@ yahoogroups. com
                      > > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Paul
                      > Livengood
                      > > Sent: Monday, October 06, 2008 11:56 AM
                      > > To: SQLQueriesNoCode@ yahoogroups. com
                      > > Subject: [SQLQueriesNoCode] Re: A DBA question regarding
                      > SQL Server 2K
                      > >
                      > >
                      > > Yes, all transactions in the TEMPDB are logged, but this should not
                      > > impact performance so much that your query becomes unusuable.
                      > >
                      > > However, if you are using temp tables that you load with lots
                      > > of data
                      > > and then using any links or Where clauses against the temp
                      > table, you
                      > > may see some performance hits if you do not index your temp
                      > tables.
                      > > With a few exceptions you can apply an index against a temp
                      > > table just
                      > > like you do a normal table. Keep in mind this will make the
                      > > loading of
                      > > the temp table longer, but it will increase read times.
                      > >
                      > > I know that you already mentioned that you are rebuilding the
                      > > indexes,
                      > > but are you sure the indexes are being used and that they are
                      > > optimal?
                      > > Have you run an execution plan against the query? If so,
                      > do you have
                      > > one to compare it against from when the query was running
                      > well? Can
                      > > you post them here?
                      > >
                      > >
                      > > HTH
                      > > Paul
                      > >
                      > >
                      > >
                      > >
                      > > --- In SQLQueriesNoCode@ yahoogroups. com, "John Warner"
                      > > <john@...> wrote:
                      > > >
                      > > > Still dealing with the query that worked fine and now times out.
                      > > >
                      > > > One thing the query does is create several temp tables. Would this
                      > > action
                      > > > be recorded in the transaction log in SQL Server? Would
                      > > this recording
                      > > > impact performance significantly?
                      > > >
                      > > >
                      > > > Thanks.
                      > > >
                      > > > John Warner
                      > > >
                      > >
                      > >
                      > >
                      > > ------------ --------- --------- ------
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      >
                      >
                      >
                      > ------------ --------- --------- ------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > Disclaimer - October 6, 2008
                      > This email and any files transmitted with it are confidential
                      > and intended solely for SQLQueriesNoCode@ yahoogroups. com. If
                      > you are not the named addressee you should not disseminate,
                      > distribute, copy or alter this email. Any views or opinions
                      > presented in this email are solely those of the author and
                      > might not represent those of Westwood Consulting, Inc.
                      > Warning: Although Westwood Consulting, Inc has taken
                      > reasonable precautions to ensure no viruses are present in
                      > this email, the company cannot accept responsibility for any
                      > loss or damage arising from the use of this email or attachments.
                      > This disclaimer was added by Policy Patrol:
                      > http://www.policypa trol.com/
                      >
                      > ------------ --------- --------- ------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >



                      [Non-text portions of this message have been removed]
                    Your message has been successfully submitted and would be delivered to recipients shortly.