Feature request: Show login name at home page

in order to see under which account one is currently logged in. This would
be usefull in development state.
rolandTry
User!UserID
"Roland" <nomail@.spamprotect.com> wrote in message
news:u6NCJ7VXEHA.3016@.tk2msftngp13.phx.gbl...
> in order to see under which account one is currently logged in. This would
> be usefull in development state.
> roland
>|||Cool. Thanks. But I wanted to see this on the homepage, not in a report
roland

Fatal errors with SQL (asserts, invalid page reads etc)

Suddenly in one database we have a lot of errors, it seams some things are
corrupted. I tried to start maintanance / database repair, but this fails
too.

When selecting in Query Analyzer a range of records from a table I get the
following message:

Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 68
Process ID: 1208

When I select the record that causes this error, the following error is
reported in Query Analyzer:

Could not find the index entry for RID '163748993200' in index page
(3:373352), index ID 0, database 'sal'.

In the log I see a lot of these messages:

Stack Signature for the dump is 0x&D179C48
Could not open FCD for invalid file ID 21761 in database'sal'
I/O error (bad page ID) detected during read at offset 0x00000b64d2000

How can this be fixed?
How can I rebuild the index for one table / check integrity of one table?
What kind of actions may caused this corruption (if it is corruption) ?
How can it be prevented?

I hope someone can help.

Regards,

ReneTo check a single table, use DBCC CHECKTABLE. If there is only a
non-clustered index problem, rebuild the index using DBCC DBREINDEX. Check
for integrity of the whole database with DBCC CHECKDB. If ou have major
problems, it could be that your HDD has errors.
To prevent errors, use RAID 1, RAID 5 or RAID 10 arrays of disks for your
database.
Always have backup strategy.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rene" <no@.spam.nl> wrote in message
news:vg7lujcbogfg06@.corp.supernews.com...
> Suddenly in one database we have a lot of errors, it seams some things are
> corrupted. I tried to start maintanance / database repair, but this fails
> too.
> When selecting in Query Analyzer a range of records from a table I get the
> following message:
> Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
> Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> SPID: 68
> Process ID: 1208
> When I select the record that causes this error, the following error is
> reported in Query Analyzer:
> Could not find the index entry for RID '163748993200' in index page
> (3:373352), index ID 0, database 'sal'.
> In the log I see a lot of these messages:
> Stack Signature for the dump is 0x&D179C48
> Could not open FCD for invalid file ID 21761 in database'sal'
> I/O error (bad page ID) detected during read at offset 0x00000b64d2000
>
> How can this be fixed?
> How can I rebuild the index for one table / check integrity of one table?
> What kind of actions may caused this corruption (if it is corruption) ?
> How can it be prevented?
> I hope someone can help.
> Regards,
> Rene|||Thanks for you answer. We have a raid 5 and daily backup, but this record
was insert in may but we discovered it yesterday. I don't think a backup
strategy can handle this.

It seems that it is small problem since the server was stil operational for
more then a month, it just caused some extra heartbeats since this is the
first time I've got such problem.

Thanks,

Rene

"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> schreef in
bericht news:OmW$sjUQDHA.560@.TK2MSFTNGP10.phx.gbl...
> To check a single table, use DBCC CHECKTABLE. If there is only a
> non-clustered index problem, rebuild the index using DBCC DBREINDEX. Check
> for integrity of the whole database with DBCC CHECKDB. If ou have major
> problems, it could be that your HDD has errors.
> To prevent errors, use RAID 1, RAID 5 or RAID 10 arrays of disks for your
> database.
> Always have backup strategy.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Rene" <no@.spam.nl> wrote in message
> news:vg7lujcbogfg06@.corp.supernews.com...
> > Suddenly in one database we have a lot of errors, it seams some things
are
> > corrupted. I tried to start maintanance / database repair, but this
fails
> > too.
> > When selecting in Query Analyzer a range of records from a table I get
the
> > following message:
> > Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
> > Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> > SPID: 68
> > Process ID: 1208
> > When I select the record that causes this error, the following error is
> > reported in Query Analyzer:
> > Could not find the index entry for RID '163748993200' in index page
> > (3:373352), index ID 0, database 'sal'.
> > In the log I see a lot of these messages:
> > Stack Signature for the dump is 0x&D179C48
> > Could not open FCD for invalid file ID 21761 in database'sal'
> > I/O error (bad page ID) detected during read at offset 0x00000b64d2000
> > How can this be fixed?
> > How can I rebuild the index for one table / check integrity of one
table?
> > What kind of actions may caused this corruption (if it is corruption) ?
> > How can it be prevented?
> > I hope someone can help.
> > Regards,
> > Rene|||Rene (no@.spam.nl) writes:
> Thanks for you answer. We have a raid 5 and daily backup, but this record
> was insert in may but we discovered it yesterday. I don't think a backup
> strategy can handle this.

Nope. But a good backup strategy may make it possible to easy recover
from a problem like this.

The error you get has number 644. Search for 644 in Books Online, and
you will find an article that describes you should handle this problem.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> schreef in bericht
news:Xns93AE94888CCCYazorman@.127.0.0.1...
> Nope. But a good backup strategy may make it possible to easy recover
> from a problem like this.

I've read some article on the net where the corruption was undetected for a
long time, even the oldest backup was corrupted. Should be hard to recover,
not?

Rene|||Rene (no@.spam.nl) writes:
> I've read some article on the net where the corruption was undetected
> for a long time, even the oldest backup was corrupted. Should be hard to
> recover, not?

Yes, I have run into a case where a DBCC CHECKDB did not show any errors,
but when you restroed the backup, there were errors. I should add that
this was with SQL Server 6.0.

But it is definitely a good idea to every now and then restore a backup
and run a DBCC on the restored copy. As it is to every now and then to
restore a database, and then apply transaction log dumps to a certain
point in time. That is also part of a backup strategy: make certain
that if the disaster strikes that you actually know that the restore
procedures are working.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Faster select count(*)

About 50% of the search time is spent just counting the number of rows in
the resultset.
We page the resultset and show only one page at a time. Is there any way we
can count
the number of rows in the resultset faster?
CasperMore information, please -- counting what result set? Are you doing this on
the client side? Can you show the code you're currently using?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OcflLTjLFHA.3000@.TK2MSFTNGP10.phx.gbl...
> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way
we
> can count
> the number of rows in the resultset faster?
> Casper
>|||select rowcnt from sysindexes
where id = object_id(N'[table]') and indid = 1
(indid = 1) is PK constraints I believe, so may not work for heaps; other
indexes don't appear to track rowcnt. Also the rwcnt field I don't think is
kept 100% current, so probably not accurate enough for your needs. But it's
there anyways ;)
- KH
"Casper Hornstrup" wrote:

> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way w
e
> can count
> the number of rows in the resultset faster?
> Casper
>
>|||http://www.aspfaq.com/2120
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OcflLTjLFHA.3000@.TK2MSFTNGP10.phx.gbl...
> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way
we
> can count
> the number of rows in the resultset faster?
> Casper
>|||> select rowcnt from sysindexes
> where id = object_id(N'[table]') and indid = 1
That will give you a rough count of what's in the entire table. However,
the OP said they wanted the count of the resultset... which is likely a
combination of a join between multiple tables and a filtered where clause.|||Yea, the original question is pretty ambiguious.
In the scenario you suggest using @.@.ROWCOUNT after the select might work for
this application.
It all depends eh? ;)
- KH
"Aaron [SQL Server MVP]" wrote:

> That will give you a rough count of what's in the entire table. However,
> the OP said they wanted the count of the resultset... which is likely a
> combination of a join between multiple tables and a filtered where clause.
>
>|||"examnotes" <KH@.discussions.microsoft.com> wrote in message news:<5EC86F57-C7F7-4AC0
-A197-B1C677605F8A@.microsoft.com>...
> Yea, the original question is pretty ambiguious.
> In the scenario you suggest using @.@.ROWCOUNT after the select might work f
or
> this application.
> It all depends eh? ;)
> - KH
>
> "Aaron [SQL Server MVP]" wrote:
>
respond

faster page update using SQL Server data

Hi,

What is the fastest way to get informations from a SQL BD (using stored procedure) in a WEB Page ?

This web page need to be updated EVERY SECONDE !
Javacript / OleDB / SQLConnection / ... ?

I plan to use a a usercontrol containing the informations, am I right ?

thank you for your help,You can get HTML code directly using the Web Tasks functionality in SQL Server ... I think this is the easiest and the fastest way to get web pages directly from SQL Server ...

Refer to SQL Server BOL for further info ...

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Timnot entirely sure why my browser posted my original message again!
sorry.

thanks guys for your help so far.
yes I have indexes on the columns

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

Tim|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

The DROP at the end is just cleanup. The script was meant to show how
you create an indexed view and how to use it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.

Thanks

Tim|||"C10B" <tswalton@.gmail.comwrote in message
news:1172494748.828877.126110@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.
>
Thanks
>
Tim
>

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||

Quote:

Originally Posted by

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

The requirement is simply this...

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"

with unlimited segmenting of the data really.

So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.

Any other tips?

Thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?

No. An indexed view is materialised, and this particular view has one
row per webpage with the count of visitors. An alternative would be have
a separate table with the counts, and update that table through a trigger.
But why roll our own when SQL Server can do the job for us?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"C10B" <tswalton@.gmail.comwrote in message
news:1172502298.291068.60420@.s48g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>

Quote:

Originally Posted by

>If you want more help, I suggest you give us a complete DDL of your
>database, some real example data and perhaps we can do better.

>
>
The requirement is simply this...
>
I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>

Yes, we understand the requirements. That doesn't eliminate the value of a
DDL and sample data.

Quote:

Originally Posted by

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.
>

Define slow?

(my former employer did queries similar to this in subsecond times.)

Quote:

Originally Posted by

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.
>
Any other tips?

Yes, post a full DDL.

Quote:

Originally Posted by

>
Thanks
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>
"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

You did not say anything about wanting to get the counts for a certain
day, so I interpreted your question that you wanted total counts of
the entire table. Whence my suggestion of an indexed view.

If you are not able to relate clearly what you want to do, then the
answers you get will be equally imprecise.

Quote:

Originally Posted by

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.

There is no need for that. If you always want to run these count
queries for a certain period, make sure that there is a clustered
index on the datetime column in your table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Tim"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

First question, do you have an index on the table.

That should help.

Quote:

Originally Posted by

>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?

A non-clustered index on place would help, as SQL Server then only
would have to scan that index, and not the entire difference. But it
seems that the table has two columns. In such case, the index would not
reduce execution time that much.

A better alternative may be to define an indexed view that maintains
the count:

CREATE TABLE pagehits (pageid varchar(20) NOT NULL,
viewtime datetime NOT NULL,
PRIMARY KEY (pageid, viewtime)
)
go
CREATE VIEW pagecount WITH SCHEMABINDING AS
SELECT pageid, cnt = COUNT_BIG(*)
FROM dbo.pagehits
GROUP BY pageid
go
CREATE UNIQUE CLUSTERED INDEX pagecount_ix ON pagecount(pageid)
go
SELECT TOP 10 pageid, cnt
FROM pagecount WITH (NOEXPAND)
ORDER BY cnt
go
DROP VIEW pagecount
DROP TABLE pagehits

I added the NOEXPAND hint to the query, since it's only on Enterprise
Edition, the optimizer considers indexed views.

Note that this could have effect on performance when writing to the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Fastest returning query would be on a separate table that maintains the
count to the granularity you would require in your output (week, day, or
hour). A query that requires more than hourly granularity can still be done
on the source table.

For each new week|day|hour add a row for each page to be tracked. This can
be done dynamically during monitoring but it is better to do it ahead of
time.

Then put an insert trigger on your million row table that will increment the
count on the proper row of the table.

An alternative would be a temp table that contains a subset copy of the
source data for the time range most likely to queried. For example a table
that contains the last 31 days of data, the 32nd day of data
deleted/archived every night.

"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

|||erland - you are quite right, I could have worded my first post a
little better

greg - I don't know what a DDL is

russ - thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

greg - I don't know what a DDL is

DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.

If I had had a penny for every time someone says DDL in answer, without the
person asking having no idea what it means, I would be a rich man now.

Quote:

Originally Posted by

russ - thanks

What Russ proposed is the same idea that I proposed, but rather than relying
SQL Server updating an indexed view, he suggested a separate table that you
update through a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98E480B95D736Yazorman@.127.0.0.1...

Quote:

Originally Posted by

C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

>greg - I don't know what a DDL is

>
DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.
>
If I had had a penny for every time someone says DDL in answer, without
the
person asking having no idea what it means, I would be a rich man now.
>

Quote:

Originally Posted by

>russ - thanks

>
What Russ proposed is the same idea that I proposed, but rather than
relying
SQL Server updating an indexed view, he suggested a separate table that
you
update through a trigger.
>

Indexed view is probably best, I just like having more control.

Quote:

Originally Posted by

>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText)
See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText)
>
|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest S
P
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying proble
m
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter&
#91;]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[
]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris