INSERTED table performance

I have a table with one UPDATE trigger. When I execute a one row update
command
to the table, Graphical Query Plan reports very slow select from INSERTED
table (900ms).
However, if I check the same command using Profiler, everything goes quickly
(duration 0 ms). Why is that? Which one should I trust, profiler or query
plan?I trust Profiler more than the Graphical Query Plan. I have seen some quite
strange costs and percentages in the Graphical Query Plan, specially when
objects are involved that don't exist at the beginning of the query, like
the inserted and deleted tables, temporary tables and table variables
--
Jacco Schalkwijk
SQL Server MVP
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Pexi,
Something that surprise me when I first found out. Inserted and deleted do
not exist, but are virtual tables that are populated each time you query
them by scanning the transaction log to extract the before and after images.
This is why the suggestion is to fill temp tables #inserted and #deleted if
you need to make repeated use of these tables.
Regarding the difference in the timings, the best way to measure is to
create a test. Do a loop calling your UPDATE repeatedly and logging the
milliseconds in a table.
SET @.BeginTime = GetDate()
EXEC YourTestStatement
INSERT INTO TrackingTable Values(@.BeginTime, GetDate())
Afterward you can analyze the results (and publish an article).
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Thanks for the replies! You kind of confirm my thinking:
never trust the query plan - it just tells fairy tales sometimes :)
pexi
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>

Insert text field problem

We have a database consisting of one table that is used to store application
reports. The reports are stored in a text field in the table. The size of the
database is around 17 Gig and is set to auto-grow. The logical drive(part of
a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
table. We were able to insert reports that were 5K in size, but unable to
insert reports that were 6K. We deleted some of the reports from the table
and now things seem to be fine.
What can cause this?
Gary,
Have you recently ran a DBCC CHECKDB on the database containing the table or
a DBCC CHECKTABLE on the individual table itself?
As a side note, you might check out Reporting Services for the future of
report generation.
Reporting Services Home
http://www.microsoft.com/sql/reporting/default.mspx
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> We have a database consisting of one table that is used to store
> application
> reports. The reports are stored in a text field in the table. The size of
> the
> database is around 17 Gig and is set to auto-grow. The logical drive(part
> of
> a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> table. We were able to insert reports that were 5K in size, but unable to
> insert reports that were 6K. We deleted some of the reports from the table
> and now things seem to be fine.
> What can cause this?
|||We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
CHECKTABLE. We are moving toward Reporting Services. What should I look for
in the DBCC CHECKTABLE?
"Jerry Spivey" wrote:

> Gary,
> Have you recently ran a DBCC CHECKDB on the database containing the table or
> a DBCC CHECKTABLE on the individual table itself?
> As a side note, you might check out Reporting Services for the future of
> report generation.
> Reporting Services Home
> http://www.microsoft.com/sql/reporting/default.mspx
> HTH
> Jerry
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
>
>
|||Gary,
CHECKDB is a superset of CHECKTABLE so if you're already run CHECKDB there
is no need to run CHECKTABLE.
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:1B00B83D-AC11-46D4-831F-E6A7BC5DEA5F@.microsoft.com...[vbcol=seagreen]
> We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
> CHECKTABLE. We are moving toward Reporting Services. What should I look
> for
> in the DBCC CHECKTABLE?
> "Jerry Spivey" wrote:

Insert text field problem

We have a database consisting of one table that is used to store application
reports. The reports are stored in a text field in the table. The size of the
database is around 17 Gig and is set to auto-grow. The logical drive(part of
a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
table. We were able to insert reports that were 5K in size, but unable to
insert reports that were 6K. We deleted some of the reports from the table
and now things seem to be fine.
What can cause this?Gary,
Have you recently ran a DBCC CHECKDB on the database containing the table or
a DBCC CHECKTABLE on the individual table itself?
As a side note, you might check out Reporting Services for the future of
report generation.
Reporting Services Home
http://www.microsoft.com/sql/reporting/default.mspx
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> We have a database consisting of one table that is used to store
> application
> reports. The reports are stored in a text field in the table. The size of
> the
> database is around 17 Gig and is set to auto-grow. The logical drive(part
> of
> a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> table. We were able to insert reports that were 5K in size, but unable to
> insert reports that were 6K. We deleted some of the reports from the table
> and now things seem to be fine.
> What can cause this?|||We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
CHECKTABLE. We are moving toward Reporting Services. What should I look for
in the DBCC CHECKTABLE?
"Jerry Spivey" wrote:
> Gary,
> Have you recently ran a DBCC CHECKDB on the database containing the table or
> a DBCC CHECKTABLE on the individual table itself?
> As a side note, you might check out Reporting Services for the future of
> report generation.
> Reporting Services Home
> http://www.microsoft.com/sql/reporting/default.mspx
> HTH
> Jerry
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> > We have a database consisting of one table that is used to store
> > application
> > reports. The reports are stored in a text field in the table. The size of
> > the
> > database is around 17 Gig and is set to auto-grow. The logical drive(part
> > of
> > a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> > table. We were able to insert reports that were 5K in size, but unable to
> > insert reports that were 6K. We deleted some of the reports from the table
> > and now things seem to be fine.
> > What can cause this?
>
>|||Gary,
CHECKDB is a superset of CHECKTABLE so if you're already run CHECKDB there
is no need to run CHECKTABLE.
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:1B00B83D-AC11-46D4-831F-E6A7BC5DEA5F@.microsoft.com...
> We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
> CHECKTABLE. We are moving toward Reporting Services. What should I look
> for
> in the DBCC CHECKTABLE?
> "Jerry Spivey" wrote:
>> Gary,
>> Have you recently ran a DBCC CHECKDB on the database containing the table
>> or
>> a DBCC CHECKTABLE on the individual table itself?
>> As a side note, you might check out Reporting Services for the future of
>> report generation.
>> Reporting Services Home
>> http://www.microsoft.com/sql/reporting/default.mspx
>> HTH
>> Jerry
>> "Gary" <Gary@.discussions.microsoft.com> wrote in message
>> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
>> > We have a database consisting of one table that is used to store
>> > application
>> > reports. The reports are stored in a text field in the table. The size
>> > of
>> > the
>> > database is around 17 Gig and is set to auto-grow. The logical
>> > drive(part
>> > of
>> > a SAN) has 25 Gig free. Last week, we has trouble inserting rows into
>> > the
>> > table. We were able to insert reports that were 5K in size, but unable
>> > to
>> > insert reports that were 6K. We deleted some of the reports from the
>> > table
>> > and now things seem to be fine.
>> > What can cause this?
>>

Insert text field problem

We have a database consisting of one table that is used to store application
reports. The reports are stored in a text field in the table. The size of th
e
database is around 17 Gig and is set to auto-grow. The logical drive(part of
a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
table. We were able to insert reports that were 5K in size, but unable to
insert reports that were 6K. We deleted some of the reports from the table
and now things seem to be fine.
What can cause this?Gary,
Have you recently ran a DBCC CHECKDB on the database containing the table or
a DBCC CHECKTABLE on the individual table itself?
As a side note, you might check out Reporting Services for the future of
report generation.
Reporting Services Home
http://www.microsoft.com/sql/reporting/default.mspx
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> We have a database consisting of one table that is used to store
> application
> reports. The reports are stored in a text field in the table. The size of
> the
> database is around 17 Gig and is set to auto-grow. The logical drive(part
> of
> a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> table. We were able to insert reports that were 5K in size, but unable to
> insert reports that were 6K. We deleted some of the reports from the table
> and now things seem to be fine.
> What can cause this?|||We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
CHECKTABLE. We are moving toward Reporting Services. What should I look for
in the DBCC CHECKTABLE?
"Jerry Spivey" wrote:

> Gary,
> Have you recently ran a DBCC CHECKDB on the database containing the table
or
> a DBCC CHECKTABLE on the individual table itself?
> As a side note, you might check out Reporting Services for the future of
> report generation.
> Reporting Services Home
> http://www.microsoft.com/sql/reporting/default.mspx
> HTH
> Jerry
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
>
>|||Gary,
CHECKDB is a superset of CHECKTABLE so if you're already run CHECKDB there
is no need to run CHECKTABLE.
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:1B00B83D-AC11-46D4-831F-E6A7BC5DEA5F@.microsoft.com...[vbcol=seagreen]
> We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
> CHECKTABLE. We are moving toward Reporting Services. What should I look
> for
> in the DBCC CHECKTABLE?
> "Jerry Spivey" wrote:
>