Embedding HTML in a Report

Does anyone know of a time frame on when SQL 2005 Reporting Services
will be able to handle the embedding of HTML into a report? I saw on
an old post that it is was supposed to be released in a follow on SP.
Does anyone have suggestions in displaying HTML into a field in a
report beyond converting it to an image first?On Apr 18, 4:17 pm, cara...@.gmail.com wrote:
> Does anyone know of a time frame on when SQL 2005 Reporting Services
> will be able to handle the embedding of HTML into a report? I saw on
> an old post that it is was supposed to be released in a follow on SP.
> Does anyone have suggestions in displaying HTML into a field in a
> report beyond converting it to an image first?
In terms of the time frame, I'm not sure; however, you could try a
workaround of using the Jump to URL property of a report control.
Sorry I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Apr 19, 6:50 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Apr 18, 4:17 pm, cara...@.gmail.com wrote:
> > Does anyone know of a time frame on whenSQL2005 Reporting Services
> > will be able to handle theembeddingofHTMLinto areport? I saw on
> > an old post that it is was supposed to be released in a follow on SP.
> > Does anyone have suggestions in displayingHTMLinto a field in a
> >reportbeyond converting it to an image first?
> In terms of the time frame, I'm not sure; however, you could try a
> workaround of using the Jump to URL property of areportcontrol.
> Sorry I could not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Where can I find how to do that "Jump to URL" work around?|||On Apr 19, 8:01 am, Mike512 <mmahon...@.gmail.com> wrote:
> On Apr 19, 6:50 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Apr 18, 4:17 pm, cara...@.gmail.com wrote:
> > > Does anyone know of a time frame on whenSQL2005 Reporting Services
> > > will be able to handle theembeddingofHTMLinto areport? I saw on
> > > an old post that it is was supposed to be released in a follow on SP.
> > > Does anyone have suggestions in displayingHTMLinto a field in a
> > >reportbeyond converting it to an image first?
> > In terms of the time frame, I'm not sure; however, you could try a
> > workaround of using the Jump to URL property of areportcontrol.
> > Sorry I could not be of greater assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Where can I find how to do that "Jump to URL" work around?
First you will want to deploy the HTML file to the webserver/IIS so
that a hyperlink can access it.
Then, depending on how you want to do it, add a report control (table/
image/textbox) to the report in Layout view, select the Properties
(via right-click), select the Navigation tab, and below 'Hyperlink
action:' select Jump to URL and enter in the URL of the newly deployed
HTML file (i.e., http://localhost/SomeVirtualDirectoryName/HTMLFileName.html).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||One other option is to have dotnet code (behind report or an assembly) that
strips the html control characters from the text.
I haven't done this but I know that people have done this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike512" <mmahon512@.gmail.com> wrote in message
news:1176987680.073062.65410@.d57g2000hsg.googlegroups.com...
> On Apr 19, 6:50 am, EMartinez <emartinez...@.gmail.com> wrote:
>> On Apr 18, 4:17 pm, cara...@.gmail.com wrote:
>> > Does anyone know of a time frame on whenSQL2005 Reporting Services
>> > will be able to handle theembeddingofHTMLinto areport? I saw on
>> > an old post that it is was supposed to be released in a follow on SP.
>> > Does anyone have suggestions in displayingHTMLinto a field in a
>> >reportbeyond converting it to an image first?
>> In terms of the time frame, I'm not sure; however, you could try a
>> workaround of using the Jump to URL property of areportcontrol.
>> Sorry I could not be of greater assistance.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
> Where can I find how to do that "Jump to URL" work around?
>|||Thanks for the reply. At first I was trying to retain the HTML in the report
then I just decided to scrap it. So I did that by adding this function:
This was taken from this guy and convert to VB:
http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
To call the function in the Expression Editor of the textbox just use:
=Code.StripHTML(Fields!<whateverfieldyouwanttostriphtmlfrom>.Value)
I hope this helps someone just trying to strip the HTML out since MS hasn't
hacked out a friggin control to render it.
Cheers,
Mike
"EMartinez" wrote:
> On Apr 19, 8:01 am, Mike512 <mmahon...@.gmail.com> wrote:
> > On Apr 19, 6:50 am, EMartinez <emartinez...@.gmail.com> wrote:
> >
> >
> >
> > > On Apr 18, 4:17 pm, cara...@.gmail.com wrote:
> >
> > > > Does anyone know of a time frame on whenSQL2005 Reporting Services
> > > > will be able to handle theembeddingofHTMLinto areport? I saw on
> > > > an old post that it is was supposed to be released in a follow on SP.
> > > > Does anyone have suggestions in displayingHTMLinto a field in a
> > > >reportbeyond converting it to an image first?
> >
> > > In terms of the time frame, I'm not sure; however, you could try a
> > > workaround of using the Jump to URL property of areportcontrol.
> > > Sorry I could not be of greater assistance.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
> >
> > Where can I find how to do that "Jump to URL" work around?
> First you will want to deploy the HTML file to the webserver/IIS so
> that a hyperlink can access it.
> Then, depending on how you want to do it, add a report control (table/
> image/textbox) to the report in Layout view, select the Properties
> (via right-click), select the Navigation tab, and below 'Hyperlink
> action:' select Jump to URL and enter in the URL of the newly deployed
> HTML file (i.e., http://localhost/SomeVirtualDirectoryName/HTMLFileName.html).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||> Does anyone have suggestions in displaying HTML into a field in a
> report beyond converting it to an image first?
Yes, I do. But I'm going to start sounding like a broken record <g>.
You can do this by taking the XML output and applying an XSLT to it! It's
really up to you to decide how the HTML output looks, not what they decided
to provide as an HTML presentation.
The catch here would be how you wanted to display that report in other
formats, such as PDF. You can write XSLT to go to (say) rich text or post
script or use XSL:FO.
But you're still going to have to manage the impedance mismatch between the
HTML formatting you're trying to reproduce and these other formats. That's
why "converting to an image" makes sense to a lot of people -- it's not the
HTML display that's the problem, it's the multiple output targets that have
to render that HTML field.
HTML, almost by definition, mixes formatting and content. That's why it's a
PITA for any reporting engine with multiple output targets. You have to
separate the two and then translate the formatting so you can apply it to
the content as appropriate to the current target.
>L<
<caragav@.gmail.com> wrote in message
news:1176931060.300151.199480@.y5g2000hsa.googlegroups.com...
> Does anyone know of a time frame on when SQL 2005 Reporting Services
> will be able to handle the embedding of HTML into a report? I saw on
> an old post that it is was supposed to be released in a follow on SP.
> Does anyone have suggestions in displaying HTML into a field in a
> report beyond converting it to an image first?
>

Email Subscription

Hi There,
Is it posible to create a subscribers list during run time of the report?
How can I specify that while creating a sbuscription in Report Manager?.
For Example,
I've a report which takes 3 parameters. Date1,Date2,EmpID.
The report has to send to only one user thru depending on the EmpID parameter. How can I specify the query while creating the subscription?.
If this can not be done thru subscription. Can this can be done on DeliveryExtension. Any code samples?.
Any help would be much appreciated!.
Thanks in advance.Data Driven Subscriptions can accomplish this. Look at the following
article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
You can not dynamically change the delivery extension though.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> Hi There,
> Is it posible to create a subscribers list during run time of the report?
> How can I specify that while creating a sbuscription in Report Manager?.
> For Example,
> I've a report which takes 3 parameters. Date1,Date2,EmpID.
> The report has to send to only one user thru depending on the EmpID
parameter. How can I specify the query while creating the subscription?.
> If this can not be done thru subscription. Can this can be done on
DeliveryExtension. Any code samples?.
> Any help would be much appreciated!.
> Thanks in advance.
>|||Hi Daniel,
I've tried this before I put this query on Newsgroup.
I find the difficulty in specifying the query in the subscription:
select email from tbl_subscribers where uid=@.EmpID(Which is Report Parameter).
can I do this, if so how can I specify the query. The above query is not validating & gives an error message "@.EmpID must be declared".
Thnaks.
"Daniel Reib [MSFT]" wrote:
> Data Driven Subscriptions can accomplish this. Look at the following
> article:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> You can not dynamically change the delivery extension though.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > Hi There,
> >
> > Is it posible to create a subscribers list during run time of the report?
> >
> > How can I specify that while creating a sbuscription in Report Manager?.
> >
> > For Example,
> >
> > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> >
> > The report has to send to only one user thru depending on the EmpID
> parameter. How can I specify the query while creating the subscription?.
> >
> > If this can not be done thru subscription. Can this can be done on
> DeliveryExtension. Any code samples?.
> >
> > Any help would be much appreciated!.
> >
> > Thanks in advance.
> >
>
>|||Sorry. I hope I can not use Data Driven Subscription, since I don't have "Enterprise Edition of SQL Server 2000". I've only Standard Edition.
Is there any other way I can accomplish the task & any samples?.
Thanks.
"Daniel Reib [MSFT]" wrote:
> Data Driven Subscriptions can accomplish this. Look at the following
> article:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> You can not dynamically change the delivery extension though.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > Hi There,
> >
> > Is it posible to create a subscribers list during run time of the report?
> >
> > How can I specify that while creating a sbuscription in Report Manager?.
> >
> > For Example,
> >
> > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> >
> > The report has to send to only one user thru depending on the EmpID
> parameter. How can I specify the query while creating the subscription?.
> >
> > If this can not be done thru subscription. Can this can be done on
> DeliveryExtension. Any code samples?.
> >
> > Any help would be much appreciated!.
> >
> > Thanks in advance.
> >
>
>|||Unfortunately there is no real way to accomplish this in the Standard
Edition. You would have to create a subscription for each user.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:1824340C-E14E-470F-835C-FE8DC3904ABF@.microsoft.com...
> Sorry. I hope I can not use Data Driven Subscription, since I don't have
"Enterprise Edition of SQL Server 2000". I've only Standard Edition.
> Is there any other way I can accomplish the task & any samples?.
> Thanks.
> "Daniel Reib [MSFT]" wrote:
> > Data Driven Subscriptions can accomplish this. Look at the following
> > article:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> >
> > You can not dynamically change the delivery extension though.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> > news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > > Hi There,
> > >
> > > Is it posible to create a subscribers list during run time of the
report?
> > >
> > > How can I specify that while creating a sbuscription in Report
Manager?.
> > >
> > > For Example,
> > >
> > > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> > >
> > > The report has to send to only one user thru depending on the EmpID
> > parameter. How can I specify the query while creating the subscription?.
> > >
> > > If this can not be done thru subscription. Can this can be done on
> > DeliveryExtension. Any code samples?.
> > >
> > > Any help would be much appreciated!.
> > >
> > > Thanks in advance.
> > >
> >
> >
> >|||Ohh...Thnaks Daniel.
"Daniel Reib [MSFT]" wrote:
> Unfortunately there is no real way to accomplish this in the Standard
> Edition. You would have to create a subscription for each user.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:1824340C-E14E-470F-835C-FE8DC3904ABF@.microsoft.com...
> > Sorry. I hope I can not use Data Driven Subscription, since I don't have
> "Enterprise Edition of SQL Server 2000". I've only Standard Edition.
> >
> > Is there any other way I can accomplish the task & any samples?.
> >
> > Thanks.
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > Data Driven Subscriptions can accomplish this. Look at the following
> > > article:
> > >
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> > >
> > > You can not dynamically change the delivery extension though.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> > > news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > > > Hi There,
> > > >
> > > > Is it posible to create a subscribers list during run time of the
> report?
> > > >
> > > > How can I specify that while creating a sbuscription in Report
> Manager?.
> > > >
> > > > For Example,
> > > >
> > > > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> > > >
> > > > The report has to send to only one user thru depending on the EmpID
> > > parameter. How can I specify the query while creating the subscription?.
> > > >
> > > > If this can not be done thru subscription. Can this can be done on
> > > DeliveryExtension. Any code samples?.
> > > >
> > > > Any help would be much appreciated!.
> > > >
> > > > Thanks in advance.
> > > >
> > >
> > >
> > >
>
>

Email sent half of the time...

Hi! I have a big problem here...
I use SQL 7.0 SP4 and when the job for backup logs fails,
it send an email, as specified in the job, but only 1/2
times!!! The Net send message is sent correctly, but not
emails... Do someone know why or is experiencing this
problem?
Thanks
not sure what you mean half of the time...
first, make sure you have correctly setup email profile. Can you send a
test message via email?
"David" <david.parenteau@.compuware.com> wrote in message
news:16f1501c41819$fa018870$a301280a@.phx.gbl...
> Hi! I have a big problem here...
> I use SQL 7.0 SP4 and when the job for backup logs fails,
> it send an email, as specified in the job, but only 1/2
> times!!! The Net send message is sent correctly, but not
> emails... Do someone know why or is experiencing this
> problem?
>
> Thanks
>
|||yes, email are sent successfully when sent. By half of the
time I mean this:
I go to the Enterprise Managers, display the list of all
jobs in my SQL server. Right-click on a job and
choose "View Job History..."
The 5 most recent execution are failed. All executions has
sent NET SEND command. The first, third and fifth have
also sent an email notification. But not the second and
fourth!
Is it more clear?
Thanks

>--Original Message--
>not sure what you mean half of the time...
>first, make sure you have correctly setup email profile.
Can you send a
>test message via email?
>"David" <david.parenteau@.compuware.com> wrote in message
>news:16f1501c41819$fa018870$a301280a@.phx.gbl...
fails,
>
>.
>
|||the 5 executions are for the same job? they were executed
by the same user?

>--Original Message--
>yes, email are sent successfully when sent. By half of
the
>time I mean this:
>I go to the Enterprise Managers, display the list of all
>jobs in my SQL server. Right-click on a job and
>choose "View Job History..."
>The 5 most recent execution are failed. All executions
has
>sent NET SEND command. The first, third and fifth have
>also sent an email notification. But not the second and
>fourth!
>Is it more clear?
>Thanks
profile.
>Can you send a
>fails,
1/2
not
>.
>
|||Yes. These jobs were created by the Maintenance Plan
wizard and updated manually to set notification custom.
The first failure (and the others too) was due to a non
logged transactions (SELECT INTO). RIght now the problem
of failing is resolved, but the random problem of sending
email is not: "NOTE: Failed to notify 'teamSQLAdmin' via
email." was the reason of failed where no email was sent.
This job occurs every hours.
All emails marked as sent have been received normall in
the inbox.
David
>--Original Message--
>the 5 executions are for the same job? they were executed
>by the same user?
>the
>has
>profile.
>1/2
>not
>.
>
|||David,
Are you using MS Exchange Server? Perhaps there are exchange server outages periodically? Could you check with your exchange server admin team?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Mark, yes, we use MS Exchange Server to send emails. The
mail server is dedicated on another server on the domain.
No outages are known for the mail server...
What this could involved?

>--Original Message--
>David,
>Are you using MS Exchange Server? Perhaps there are
exchange server outages periodically? Could you check with
your exchange server admin team?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>.
>

Email sent half of the time...

Hi! I have a big problem here...
I use SQL 7.0 SP4 and when the job for backup logs fails,
it send an email, as specified in the job, but only 1/2
times!!! The Net send message is sent correctly, but not
emails... Do someone know why or is experiencing this
problem?
Thanksnot sure what you mean half of the time...
first, make sure you have correctly setup email profile. Can you send a
test message via email?
"David" <david.parenteau@.compuware.com> wrote in message
news:16f1501c41819$fa018870$a301280a@.phx
.gbl...
> Hi! I have a big problem here...
> I use SQL 7.0 SP4 and when the job for backup logs fails,
> it send an email, as specified in the job, but only 1/2
> times!!! The Net send message is sent correctly, but not
> emails... Do someone know why or is experiencing this
> problem?
>
> Thanks
>|||yes, email are sent successfully when sent. By half of the
time I mean this:
I go to the Enterprise Managers, display the list of all
jobs in my SQL server. Right-click on a job and
choose "View Job History..."
The 5 most recent execution are failed. All executions has
sent NET SEND command. The first, third and fifth have
also sent an email notification. But not the second and
fourth!
Is it more clear?
Thanks

>--Original Message--
>not sure what you mean half of the time...
>first, make sure you have correctly setup email profile.
Can you send a
>test message via email?
>"David" <david.parenteau@.compuware.com> wrote in message
> news:16f1501c41819$fa018870$a301280a@.phx
.gbl...
fails,
>
>.
>|||the 5 executions are for the same job? they were executed
by the same user?

>--Original Message--
>yes, email are sent successfully when sent. By half of
the
>time I mean this:
>I go to the Enterprise Managers, display the list of all
>jobs in my SQL server. Right-click on a job and
>choose "View Job History..."
>The 5 most recent execution are failed. All executions
has
>sent NET SEND command. The first, third and fifth have
>also sent an email notification. But not the second and
>fourth!
>Is it more clear?
>Thanks
>
profile.
>Can you send a
>fails,
1/2
not
>.
>|||Yes. These jobs were created by the Maintenance Plan
wizard and updated manually to set notification custom.
The first failure (and the others too) was due to a non
logged transactions (SELECT INTO). RIght now the problem
of failing is resolved, but the random problem of sending
email is not: "NOTE: Failed to notify 'teamSQLAdmin' via
email." was the reason of failed where no email was sent.
This job occurs every hours.
All emails marked as sent have been received normall in
the inbox.
David
>--Original Message--
>the 5 executions are for the same job? they were executed
>by the same user?
>
>the
>has
>profile.
>1/2
>not
>.
>|||David,
Are you using MS Exchange Server? Perhaps there are exchange server outages
periodically? Could you check with your exchange server admin team?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Mark, yes, we use MS Exchange Server to send emails. The
mail server is dedicated on another server on the domain.
No outages are known for the mail server...
What this could involved?

>--Original Message--
>David,
>Are you using MS Exchange Server? Perhaps there are
exchange server outages periodically? Could you check with
your exchange server admin team?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>.
>

email send/receive

How can I have my email download automatically instead of clicking send/receive all the time?

create an activation stored procedure that calls a CLR stored porcedure which calls a webservice which checks your mail.

are you sure you posted this in the correct forum?

Email notifications setup

Hi
Sorry if this seems dumb, i have upgraded to sql 2000 and
am having a great time so far, but I have a little problem.
In my office only 5 people have email so we are using a
simple email server (workgroupmail) is support imap,pop
and all the other usuals.
my question is how do I set up sql server to allow me to
send emails when scheduled tasks run or dont run.
CheersIf you don't want to bother setting up exchange/mapi/outlook/sql mail, see
http://www.aspfaq.com/2403 for some other alternatives.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:07ba01c3b685$e3c7bed0$a301280a@.phx.gbl...
> Hi
> Sorry if this seems dumb, i have upgraded to sql 2000 and
> am having a great time so far, but I have a little problem.
> In my office only 5 people have email so we are using a
> simple email server (workgroupmail) is support imap,pop
> and all the other usuals.
> my question is how do I set up sql server to allow me to
> send emails when scheduled tasks run or dont run.
> Cheers|||Hi
The following show how to set up mail on sqlserver:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
and a whole lot more:
http://www.aspfaq.com/show.asp?id=2403
Without a exchange you may have to have a user logged into the server with
outlook open see (I am not sure if this has changed recently!!) :
http://tinyurl.com/wzx5
An alternative if you have SMTP is XPSMTP
http://www.sqldev.net/xp/xpsmtp.htm
You will have to write your own notification calls.
John
"merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:07ba01c3b685$e3c7bed0$a301280a@.phx.gbl...
> Hi
> Sorry if this seems dumb, i have upgraded to sql 2000 and
> am having a great time so far, but I have a little problem.
> In my office only 5 people have email so we are using a
> simple email server (workgroupmail) is support imap,pop
> and all the other usuals.
> my question is how do I set up sql server to allow me to
> send emails when scheduled tasks run or dont run.
> Cheers

EM losing Server registrations in XP

Hi all
Every time we change a users password in XP Pro, the
client PC looses all of the Server registrations within
SQL 2000 Enterprise Manger.
Anyone else experienced this, and found any solution to it.
CheersFIX: Registered Remote Servers Disappear from SQL Enterprise Manager in
Windows XP When Non-Domain User Password is Changed
http://support.microsoft.com/?id=323280
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:119e201c3f614$b9bc73a0$a101280a@.phx
.gbl...
> Hi all
> Every time we change a users password in XP Pro, the
> client PC looses all of the Server registrations within
> SQL 2000 Enterprise Manger.
> Anyone else experienced this, and found any solution to it.
> Cheers
>|||Thanks
We are running with SP3 already installed.
I will however give the registry tweak a go and see if it
makes a difference
Cheers
>--Original Message--
>FIX: Registered Remote Servers Disappear from SQL
Enterprise Manager in
>Windows XP When Non-Domain User Password is Changed
>http://support.microsoft.com/?id=323280
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:119e201c3f614$b9bc73a0$a101280a@.phx
.gbl...
it.
>
>.
>

elapsed time vs cpu time

Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
read-ahead reads 0.
Table 'vgn_version'. Scan count 8, logical reads 875, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 571 ms, elapsed time = 17560 ms.
When i run statistics io , and statistics time, on a query (query not
mentioned here), i got above mentioned results.
From the info given above, I see there are no physical reads, so all data is
there in the memory. I am fuzzled with the cpu time and elapsed time values.
When cpu has taken half a second to process the data, why elapsed time is
17.5 sec. I was monitoring cpu when the query being executed, cpu
utilization less than 5% of processor time. So can i derive from this that
remaining 17 sec has been caused to move data between dynmic memory and cpu
memory cache. Or are there some thing i am missing here. Can you please
bring your ideas and let me know if you need any more info.
I have run the query in completely quite machine after reboot.
Thanks,
Subbu.subbu wrote:
> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> 0, read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> reads 0, read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> 0, read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 571 ms, elapsed time = 17560 ms.
> When i run statistics io , and statistics time, on a query (query not
> mentioned here), i got above mentioned results.
> From the info given above, I see there are no physical reads, so all
> data is there in the memory. I am fuzzled with the cpu time and
> elapsed time values. When cpu has taken half a second to process the
> data, why elapsed time is
> 17.5 sec. I was monitoring cpu when the query being executed, cpu
> utilization less than 5% of processor time. So can i derive from this
> that remaining 17 sec has been caused to move data between dynmic
> memory and cpu memory cache. Or are there some thing i am missing
> here. Can you please bring your ideas and let me know if you need any
> more info.
> I have run the query in completely quite machine after reboot.
> Thanks,
> Subbu.
How many rows were returned? Where did you run the SQL Statement from?
The total time for a SQL statement includes the time to tech all the
result set pages. Either there were a lot of rows generated and they
took time to fetch or the client application fetched rows slowly.
David Gugick - SQL Server MVP
Quest Software|||... or there was some blocking going on...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> How many rows were returned? Where did you run the SQL Statement from? The
total time for a SQL
> statement includes the time to tech all the result set pages. Either there
were a lot of rows
> generated and they took time to fetch or the client application fetched ro
ws slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||No blocking at all. This is the only query running at that time
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ufcrY1rTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> ... or there was some blocking going on...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
The total time for a SQL[vbcol=seagreen]
there were a lot of rows[vbcol=seagreen]
rows slowly.[vbcol=seagreen]
>|||Two (2) rows returned.
I run sql stmt in query analyser on the machine where sql server installed,
but connected to server machine using Terminal Services (RDP).
I have run same query on another machine, which is in the same place like
previous one did run the query in similar way, it returned results
differently. Elapsed time is almost matching with CPU. Here also 2 rows
returned.
Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 167 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 171 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 173 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Thanks,
Subbu.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> How many rows were returned? Where did you run the SQL Statement from?
> The total time for a SQL statement includes the time to tech all the
> result set pages. Either there were a lot of rows generated and they
> took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Perhaps ODBC tracing is turned on at the slow machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"subbu" <subbaiahd@.hotmail.com> wrote in message news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gb
l...
> Two (2) rows returned.
> I run sql stmt in query analyser on the machine where sql server installed
,
> but connected to server machine using Terminal Services (RDP).
> I have run same query on another machine, which is in the same place like
> previous one did run the query in similar way, it returned results
> differently. Elapsed time is almost matching with CPU. Here also 2 rows
> returned.
> Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
> read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
> read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 167 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 171 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 173 ms.
> SQL Server parse and compile time:
> CPU time = 0 ms, elapsed time = 0 ms.
> SQL Server Execution Times:
> CPU time = 0 ms, elapsed time = 0 ms.
> Thanks,
> Subbu.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
>|||No. ODBC tracing has not started, i have verified just now.
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eO6yBFsTGHA.424@.TK2MSFTNGP12.phx.gbl...
> Perhaps ODBC tracing is turned on at the slow machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
installed,[vbcol=seagreen]
like[vbcol=seagreen]
0,[vbcol=seagreen]
not[vbcol=seagreen]

elapsed time vs cpu time

Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
read-ahead reads 0.
Table 'vgn_version'. Scan count 8, logical reads 875, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 571 ms, elapsed time = 17560 ms.
When i run statistics io , and statistics time, on a query (query not
mentioned here), i got above mentioned results.
From the info given above, I see there are no physical reads, so all data is
there in the memory. I am fuzzled with the cpu time and elapsed time values.
When cpu has taken half a second to process the data, why elapsed time is
17.5 sec. I was monitoring cpu when the query being executed, cpu
utilization less than 5% of processor time. So can i derive from this that
remaining 17 sec has been caused to move data between dynmic memory and cpu
memory cache. Or are there some thing i am missing here. Can you please
bring your ideas and let me know if you need any more info.
I have run the query in completely quite machine after reboot.
Thanks,
Subbu.
subbu wrote:
> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> 0, read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> reads 0, read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> 0, read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 571 ms, elapsed time = 17560 ms.
> When i run statistics io , and statistics time, on a query (query not
> mentioned here), i got above mentioned results.
> From the info given above, I see there are no physical reads, so all
> data is there in the memory. I am fuzzled with the cpu time and
> elapsed time values. When cpu has taken half a second to process the
> data, why elapsed time is
> 17.5 sec. I was monitoring cpu when the query being executed, cpu
> utilization less than 5% of processor time. So can i derive from this
> that remaining 17 sec has been caused to move data between dynmic
> memory and cpu memory cache. Or are there some thing i am missing
> here. Can you please bring your ideas and let me know if you need any
> more info.
> I have run the query in completely quite machine after reboot.
> Thanks,
> Subbu.
How many rows were returned? Where did you run the SQL Statement from?
The total time for a SQL statement includes the time to tech all the
result set pages. Either there were a lot of rows generated and they
took time to fetch or the client application fetched rows slowly.
David Gugick - SQL Server MVP
Quest Software
|||... or there was some blocking going on...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> How many rows were returned? Where did you run the SQL Statement from? The total time for a SQL
> statement includes the time to tech all the result set pages. Either there were a lot of rows
> generated and they took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||No blocking at all. This is the only query running at that time
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ufcrY1rTGHA.5500@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> ... or there was some blocking going on...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
The total time for a SQL[vbcol=seagreen]
there were a lot of rows[vbcol=seagreen]
rows slowly.
>
|||Two (2) rows returned.
I run sql stmt in query analyser on the machine where sql server installed,
but connected to server machine using Terminal Services (RDP).
I have run same query on another machine, which is in the same place like
previous one did run the query in similar way, it returned results
differently. Elapsed time is almost matching with CPU. Here also 2 rows
returned.
Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 167 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 171 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 173 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Thanks,
Subbu.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> How many rows were returned? Where did you run the SQL Statement from?
> The total time for a SQL statement includes the time to tech all the
> result set pages. Either there were a lot of rows generated and they
> took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||Perhaps ODBC tracing is turned on at the slow machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"subbu" <subbaiahd@.hotmail.com> wrote in message news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> Two (2) rows returned.
> I run sql stmt in query analyser on the machine where sql server installed,
> but connected to server machine using Terminal Services (RDP).
> I have run same query on another machine, which is in the same place like
> previous one did run the query in similar way, it returned results
> differently. Elapsed time is almost matching with CPU. Here also 2 rows
> returned.
> Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
> read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
> read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 167 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 171 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 173 ms.
> SQL Server parse and compile time:
> CPU time = 0 ms, elapsed time = 0 ms.
> SQL Server Execution Times:
> CPU time = 0 ms, elapsed time = 0 ms.
> Thanks,
> Subbu.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
>
|||No. ODBC tracing has not started, i have verified just now.
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eO6yBFsTGHA.424@.TK2MSFTNGP12.phx.gbl...
> Perhaps ODBC tracing is turned on at the slow machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
installed,[vbcol=seagreen]
like[vbcol=seagreen]
0,[vbcol=seagreen]
not[vbcol=seagreen]

elapsed time vs cpu time

Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
read-ahead reads 0.
Table 'vgn_version'. Scan count 8, logical reads 875, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 571 ms, elapsed time = 17560 ms.
When i run statistics io , and statistics time, on a query (query not
mentioned here), i got above mentioned results.
From the info given above, I see there are no physical reads, so all data is
there in the memory. I am fuzzled with the cpu time and elapsed time values.
When cpu has taken half a second to process the data, why elapsed time is
17.5 sec. I was monitoring cpu when the query being executed, cpu
utilization less than 5% of processor time. So can i derive from this that
remaining 17 sec has been caused to move data between dynmic memory and cpu
memory cache. Or are there some thing i am missing here. Can you please
bring your ideas and let me know if you need any more info.
I have run the query in completely quite machine after reboot.
Thanks,
Subbu.subbu wrote:
> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> 0, read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> reads 0, read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> 0, read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 571 ms, elapsed time = 17560 ms.
> When i run statistics io , and statistics time, on a query (query not
> mentioned here), i got above mentioned results.
> From the info given above, I see there are no physical reads, so all
> data is there in the memory. I am fuzzled with the cpu time and
> elapsed time values. When cpu has taken half a second to process the
> data, why elapsed time is
> 17.5 sec. I was monitoring cpu when the query being executed, cpu
> utilization less than 5% of processor time. So can i derive from this
> that remaining 17 sec has been caused to move data between dynmic
> memory and cpu memory cache. Or are there some thing i am missing
> here. Can you please bring your ideas and let me know if you need any
> more info.
> I have run the query in completely quite machine after reboot.
> Thanks,
> Subbu.
How many rows were returned? Where did you run the SQL Statement from?
The total time for a SQL statement includes the time to tech all the
result set pages. Either there were a lot of rows generated and they
took time to fetch or the client application fetched rows slowly.
--
David Gugick - SQL Server MVP
Quest Software|||... or there was some blocking going on...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
>> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
>> read-ahead reads 0.
>> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
>> 0, read-ahead reads 0.
>> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
>> reads 0, read-ahead reads 0.
>> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
>> 0, read-ahead reads 0.
>> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
>> read-ahead reads 0.
>> SQL Server Execution Times:
>> CPU time = 571 ms, elapsed time = 17560 ms.
>> When i run statistics io , and statistics time, on a query (query not
>> mentioned here), i got above mentioned results.
>> From the info given above, I see there are no physical reads, so all
>> data is there in the memory. I am fuzzled with the cpu time and
>> elapsed time values. When cpu has taken half a second to process the
>> data, why elapsed time is
>> 17.5 sec. I was monitoring cpu when the query being executed, cpu
>> utilization less than 5% of processor time. So can i derive from this
>> that remaining 17 sec has been caused to move data between dynmic
>> memory and cpu memory cache. Or are there some thing i am missing
>> here. Can you please bring your ideas and let me know if you need any
>> more info.
>> I have run the query in completely quite machine after reboot.
>> Thanks,
>> Subbu.
> How many rows were returned? Where did you run the SQL Statement from? The total time for a SQL
> statement includes the time to tech all the result set pages. Either there were a lot of rows
> generated and they took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||No blocking at all. This is the only query running at that time
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ufcrY1rTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> ... or there was some blocking going on...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> > subbu wrote:
> >> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> >> read-ahead reads 0.
> >> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> >> 0, read-ahead reads 0.
> >> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> >> reads 0, read-ahead reads 0.
> >> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> >> 0, read-ahead reads 0.
> >> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> >> read-ahead reads 0.
> >>
> >> SQL Server Execution Times:
> >> CPU time = 571 ms, elapsed time = 17560 ms.
> >>
> >> When i run statistics io , and statistics time, on a query (query not
> >> mentioned here), i got above mentioned results.
> >>
> >> From the info given above, I see there are no physical reads, so all
> >> data is there in the memory. I am fuzzled with the cpu time and
> >> elapsed time values. When cpu has taken half a second to process the
> >> data, why elapsed time is
> >> 17.5 sec. I was monitoring cpu when the query being executed, cpu
> >> utilization less than 5% of processor time. So can i derive from this
> >> that remaining 17 sec has been caused to move data between dynmic
> >> memory and cpu memory cache. Or are there some thing i am missing
> >> here. Can you please bring your ideas and let me know if you need any
> >> more info.
> >>
> >> I have run the query in completely quite machine after reboot.
> >>
> >> Thanks,
> >> Subbu.
> >
> > How many rows were returned? Where did you run the SQL Statement from?
The total time for a SQL
> > statement includes the time to tech all the result set pages. Either
there were a lot of rows
> > generated and they took time to fetch or the client application fetched
rows slowly.
> >
> > --
> > David Gugick - SQL Server MVP
> > Quest Software
> >
>|||Two (2) rows returned.
I run sql stmt in query analyser on the machine where sql server installed,
but connected to server machine using Terminal Services (RDP).
I have run same query on another machine, which is in the same place like
previous one did run the query in similar way, it returned results
differently. Elapsed time is almost matching with CPU. Here also 2 rows
returned.
Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 167 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 171 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 173 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Thanks,
Subbu.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> > Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> > read-ahead reads 0.
> > Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> > 0, read-ahead reads 0.
> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> > reads 0, read-ahead reads 0.
> > Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> > 0, read-ahead reads 0.
> > Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> > read-ahead reads 0.
> >
> > SQL Server Execution Times:
> > CPU time = 571 ms, elapsed time = 17560 ms.
> >
> > When i run statistics io , and statistics time, on a query (query not
> > mentioned here), i got above mentioned results.
> >
> > From the info given above, I see there are no physical reads, so all
> > data is there in the memory. I am fuzzled with the cpu time and
> > elapsed time values. When cpu has taken half a second to process the
> > data, why elapsed time is
> > 17.5 sec. I was monitoring cpu when the query being executed, cpu
> > utilization less than 5% of processor time. So can i derive from this
> > that remaining 17 sec has been caused to move data between dynmic
> > memory and cpu memory cache. Or are there some thing i am missing
> > here. Can you please bring your ideas and let me know if you need any
> > more info.
> >
> > I have run the query in completely quite machine after reboot.
> >
> > Thanks,
> > Subbu.
> How many rows were returned? Where did you run the SQL Statement from?
> The total time for a SQL statement includes the time to tech all the
> result set pages. Either there were a lot of rows generated and they
> took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Perhaps ODBC tracing is turned on at the slow machine?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"subbu" <subbaiahd@.hotmail.com> wrote in message news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> Two (2) rows returned.
> I run sql stmt in query analyser on the machine where sql server installed,
> but connected to server machine using Terminal Services (RDP).
> I have run same query on another machine, which is in the same place like
> previous one did run the query in similar way, it returned results
> differently. Elapsed time is almost matching with CPU. Here also 2 rows
> returned.
> Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
> read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
> read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 167 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 171 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 173 ms.
> SQL Server parse and compile time:
> CPU time = 0 ms, elapsed time = 0 ms.
> SQL Server Execution Times:
> CPU time = 0 ms, elapsed time = 0 ms.
> Thanks,
> Subbu.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
>> subbu wrote:
>> > Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
>> > read-ahead reads 0.
>> > Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
>> > 0, read-ahead reads 0.
>> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
>> > reads 0, read-ahead reads 0.
>> > Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
>> > 0, read-ahead reads 0.
>> > Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
>> > read-ahead reads 0.
>> >
>> > SQL Server Execution Times:
>> > CPU time = 571 ms, elapsed time = 17560 ms.
>> >
>> > When i run statistics io , and statistics time, on a query (query not
>> > mentioned here), i got above mentioned results.
>> >
>> > From the info given above, I see there are no physical reads, so all
>> > data is there in the memory. I am fuzzled with the cpu time and
>> > elapsed time values. When cpu has taken half a second to process the
>> > data, why elapsed time is
>> > 17.5 sec. I was monitoring cpu when the query being executed, cpu
>> > utilization less than 5% of processor time. So can i derive from this
>> > that remaining 17 sec has been caused to move data between dynmic
>> > memory and cpu memory cache. Or are there some thing i am missing
>> > here. Can you please bring your ideas and let me know if you need any
>> > more info.
>> >
>> > I have run the query in completely quite machine after reboot.
>> >
>> > Thanks,
>> > Subbu.
>> How many rows were returned? Where did you run the SQL Statement from?
>> The total time for a SQL statement includes the time to tech all the
>> result set pages. Either there were a lot of rows generated and they
>> took time to fetch or the client application fetched rows slowly.
>> --
>> David Gugick - SQL Server MVP
>> Quest Software
>|||No. ODBC tracing has not started, i have verified just now.
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eO6yBFsTGHA.424@.TK2MSFTNGP12.phx.gbl...
> Perhaps ODBC tracing is turned on at the slow machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> > Two (2) rows returned.
> >
> > I run sql stmt in query analyser on the machine where sql server
installed,
> > but connected to server machine using Terminal Services (RDP).
> >
> > I have run same query on another machine, which is in the same place
like
> > previous one did run the query in similar way, it returned results
> > differently. Elapsed time is almost matching with CPU. Here also 2 rows
> > returned.
> >
> > Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
> > read-ahead reads 0.
> >
> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads
0,
> > read-ahead reads 0.
> >
> > Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
> > read-ahead reads 0.
> >
> > Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
> > read-ahead reads 0.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 156 ms, elapsed time = 167 ms.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 156 ms, elapsed time = 171 ms.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 156 ms, elapsed time = 173 ms.
> >
> > SQL Server parse and compile time:
> >
> > CPU time = 0 ms, elapsed time = 0 ms.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 0 ms, elapsed time = 0 ms.
> >
> > Thanks,
> >
> > Subbu.
> >
> > "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> > news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> >> subbu wrote:
> >> > Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> >> > read-ahead reads 0.
> >> > Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> >> > 0, read-ahead reads 0.
> >> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> >> > reads 0, read-ahead reads 0.
> >> > Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> >> > 0, read-ahead reads 0.
> >> > Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> >> > read-ahead reads 0.
> >> >
> >> > SQL Server Execution Times:
> >> > CPU time = 571 ms, elapsed time = 17560 ms.
> >> >
> >> > When i run statistics io , and statistics time, on a query (query
not
> >> > mentioned here), i got above mentioned results.
> >> >
> >> > From the info given above, I see there are no physical reads, so all
> >> > data is there in the memory. I am fuzzled with the cpu time and
> >> > elapsed time values. When cpu has taken half a second to process the
> >> > data, why elapsed time is
> >> > 17.5 sec. I was monitoring cpu when the query being executed, cpu
> >> > utilization less than 5% of processor time. So can i derive from this
> >> > that remaining 17 sec has been caused to move data between dynmic
> >> > memory and cpu memory cache. Or are there some thing i am missing
> >> > here. Can you please bring your ideas and let me know if you need any
> >> > more info.
> >> >
> >> > I have run the query in completely quite machine after reboot.
> >> >
> >> > Thanks,
> >> > Subbu.
> >>
> >> How many rows were returned? Where did you run the SQL Statement from?
> >> The total time for a SQL statement includes the time to tech all the
> >> result set pages. Either there were a lot of rows generated and they
> >> took time to fetch or the client application fetched rows slowly.
> >>
> >> --
> >> David Gugick - SQL Server MVP
> >> Quest Software
> >>
> >
> >

Either SQL or VB is automatically applying time offsets on my data

I have a windows app which is used in australia and uses web services to communicate with the database located in new zealand. I have a problem where dates that are returned from SQL are automatically modified to suit the timezone and i dont know how to turn it off.

For example, i insert the value '10/Jan/2006 20:00' into the database from a PC located in australia. When that data is selected back out, a 4 hour time offset is automatically applied to it and the time is returned as '10/Jan/2006 16:00'.

My current workaround is to modify my queries to say;

"Select convert(varchar, tb_date) tb_date from tablename"

Which returns the date as a string rather than a datetime which means the time offset is not applied.

This is a really poor solution and i'd be really keen to find out a better way...
Thanks

This is not problem with VB or SQL. This is because of the way DateTimes are serialized in XML when sent over Web Services. For information read this excellent article on working with DateTimes in TimeZone involved applications.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/datetimecode.asp

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
James
Size of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.

> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
David Portas
SQL Server MVP
|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as[vbcol=seagreen]
> indexing) that are at least as important.
or
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>
|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
JamesSize of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.
> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
--
David Portas
SQL Server MVP
--|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as
> indexing) that are at least as important.
> > Ultimately, I'm trying to figure out if speed needs to be a
> > consideration in determining whether to orient a new table horizontally
or
> > vertically.
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
JamesSize of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.

> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
David Portas
SQL Server MVP
--|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as
> indexing) that are at least as important.
>
or[vbcol=seagreen]
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency of SmallInt vs. SmallDatetime

I have a scheduling db (sql2000) that stores a lot of 'time' data and does
a lot of operations on this data. I'm storing event dates in a smalldatetime
field. I'm storing a 'starttime' in another smalldatetime field (eg. as
'1/1/1900 9:30') and 'endtime' in a third smalldatetime field ( eg. as
'1/1/1900 11:30').
Does anyone have any information/ideas on what performance improvements I
might see if was to instead store the 2 'time' fields as smallints (storing
number of minutes since midnight) rather than as smalldatetimes? I
understand I would halve the storage required, what about performance per
se?
Thanks,
Paul.
Hi
Every row in a Table has an overhead of about 20 bytes. Saving a byte here
or there does not help much. You have to do a lot more processing to convert
your time to smallint and back (in your code).
Have you tested the difference? Functions like dateadd and datediff and not
avilable to you if you don't use DateTime datatypes.
Regards
Mike
"Paul W" wrote:

> I have a scheduling db (sql2000) that stores a lot of 'time' data and does
> a lot of operations on this data. I'm storing event dates in a smalldatetime
> field. I'm storing a 'starttime' in another smalldatetime field (eg. as
> '1/1/1900 9:30') and 'endtime' in a third smalldatetime field ( eg. as
> '1/1/1900 11:30').
> Does anyone have any information/ideas on what performance improvements I
> might see if was to instead store the 2 'time' fields as smallints (storing
> number of minutes since midnight) rather than as smalldatetimes? I
> understand I would halve the storage required, what about performance per
> se?
> Thanks,
> Paul.
>
>

effects of inserting while selecting

We have a situation where a burst of inserts occurs while a select is being
done. This causes both clients to time out. As a whole does selecting from a
table during the time that inserts are being down slow down the inserts? or
do we have some indexing problems?
thanks
mlblastvm wrote:
> We have a situation where a burst of inserts occurs while a select is being
> done. This causes both clients to time out. As a whole does selecting from a
> table during the time that inserts are being down slow down the inserts? or
> do we have some indexing problems?
> thanks
My money is on an indexing problem... Review the execution plan for
some of your SELECT statements - any scans, particularly table scans,
are likely sources of contention and opportunities for improved indexing.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||the execution plans look fine. One application is performing at least 50,000
inserts in succession. The other application is reading around 22000 rows.
Actually to be more specific here is what happens. Application A reads out
20k records from Table A and then inserts around that many to the database
(different table though). Application B at the same time inserts records into
Table A. Do the inserts from one application slow down the inserts from the
other application because the heads have to move around?
"Tracy McKibben" wrote:

> mlblastvm wrote:
> My money is on an indexing problem... Review the execution plan for
> some of your SELECT statements - any scans, particularly table scans,
> are likely sources of contention and opportunities for improved indexing.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mlblastvm wrote:
> the execution plans look fine. One application is performing at least 50,000
> inserts in succession. The other application is reading around 22000 rows.
> Actually to be more specific here is what happens. Application A reads out
> 20k records from Table A and then inserts around that many to the database
> (different table though). Application B at the same time inserts records into
> Table A. Do the inserts from one application slow down the inserts from the
> other application because the heads have to move around?
>
Well, inserting data into a database certainly does produce disk I/O,
particularly against the transaction log. The rule of thumb is that the
transaction log and database files are place on seperate volumes, with
the transaction log being on an I/O channel that provides the fastest
possible write throughput.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||In our case the logs are on separate volumes. I assume based on what you are
saying that doing a select while inserts are being done will have an impact
on both the select and the insert?
"Tracy McKibben" wrote:

> mlblastvm wrote:
> Well, inserting data into a database certainly does produce disk I/O,
> particularly against the transaction log. The rule of thumb is that the
> transaction log and database files are place on seperate volumes, with
> the transaction log being on an I/O channel that provides the fastest
> possible write throughput.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mlblastvm wrote:
> In our case the logs are on separate volumes. I assume based on what you are
> saying that doing a select while inserts are being done will have an impact
> on both the select and the insert?
>
I was simply responding to your question about the "heads moving around".
An INSERT can block a SELECT if that insert has locked data pages (or
even the entire table) that the SELECT is attempting to read. The
SELECT will be forced to wait until the lock has been released.
Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
is currently reading, the INSERT will potentially have to wait. It all
depends on the lock types being used. Having good indexes in place to
support the SELECT statements will help to alleviate some of this.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I guess the real question I have is this. In a case where locking is not
occuring is it reasonable to assume that selecting from a database while
doing inserts to it will cause both the select and insert to take longer than
if they were to be done independantly. If that is the case what exactly
accounts for this?
thanks
"Tracy McKibben" wrote:

> mlblastvm wrote:
> I was simply responding to your question about the "heads moving around".
> An INSERT can block a SELECT if that insert has locked data pages (or
> even the entire table) that the SELECT is attempting to read. The
> SELECT will be forced to wait until the lock has been released.
> Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
> is currently reading, the INSERT will potentially have to wait. It all
> depends on the lock types being used. Having good indexes in place to
> support the SELECT statements will help to alleviate some of this.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mlblastvm wrote:
> I guess the real question I have is this. In a case where locking is not
> occuring is it reasonable to assume that selecting from a database while
> doing inserts to it will cause both the select and insert to take longer than
> if they were to be done independantly. If that is the case what exactly
> accounts for this?
> thanks
You will ALWAYS have locking - that locking may or may not cause
contention, which will appear to slow things down.
If things slow down during these large inserts, the only reasons that I
can think of are:
1. Locks being created by the INSERT are blocking other processes
2. The disks are not capable of keeping up with the I/O demands
produced by the influx of new data from the INSERT
3. The new data from the INSERT is requiring the database and/or
transaction log file to grow, resulting in a delay while that growth
takes place.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I would add:
4) Not enough memory to hold all the pages required for the insert and all
the pages required for the select in memory at once causing extra IO and
processor activity swapping pages in and out.
5) Enough data being transferred in and out to cause network contention on
the TDS connections
6) Becoming processor bound - especially if the select requires sorting of
results
7) Checkpoint interval low enough so inserts are causing frequent
checkpoints saturating the mdf disk drive
In general, if you're running that close to the margins, you probably need
to bump up your timeouts.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45537D29.2060702@.realsqlguy.com...
> mlblastvm wrote:
> You will ALWAYS have locking - that locking may or may not cause
> contention, which will appear to slow things down.
> If things slow down during these large inserts, the only reasons that I
> can think of are:
> 1. Locks being created by the INSERT are blocking other processes
> 2. The disks are not capable of keeping up with the I/O demands produced
> by the influx of new data from the INSERT
> 3. The new data from the INSERT is requiring the database and/or
> transaction log file to grow, resulting in a delay while that growth takes
> place.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

effects of inserting while selecting

We have a situation where a burst of inserts occurs while a select is being
done. This causes both clients to time out. As a whole does selecting from a
table during the time that inserts are being down slow down the inserts? or
do we have some indexing problems?
thanksmlblastvm wrote:
> We have a situation where a burst of inserts occurs while a select is being
> done. This causes both clients to time out. As a whole does selecting from a
> table during the time that inserts are being down slow down the inserts? or
> do we have some indexing problems?
> thanks
My money is on an indexing problem... Review the execution plan for
some of your SELECT statements - any scans, particularly table scans,
are likely sources of contention and opportunities for improved indexing.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the execution plans look fine. One application is performing at least 50,000
inserts in succession. The other application is reading around 22000 rows.
Actually to be more specific here is what happens. Application A reads out
20k records from Table A and then inserts around that many to the database
(different table though). Application B at the same time inserts records into
Table A. Do the inserts from one application slow down the inserts from the
other application because the heads have to move around?
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > We have a situation where a burst of inserts occurs while a select is being
> > done. This causes both clients to time out. As a whole does selecting from a
> > table during the time that inserts are being down slow down the inserts? or
> > do we have some indexing problems?
> > thanks
> My money is on an indexing problem... Review the execution plan for
> some of your SELECT statements - any scans, particularly table scans,
> are likely sources of contention and opportunities for improved indexing.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> the execution plans look fine. One application is performing at least 50,000
> inserts in succession. The other application is reading around 22000 rows.
> Actually to be more specific here is what happens. Application A reads out
> 20k records from Table A and then inserts around that many to the database
> (different table though). Application B at the same time inserts records into
> Table A. Do the inserts from one application slow down the inserts from the
> other application because the heads have to move around?
>
Well, inserting data into a database certainly does produce disk I/O,
particularly against the transaction log. The rule of thumb is that the
transaction log and database files are place on seperate volumes, with
the transaction log being on an I/O channel that provides the fastest
possible write throughput.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In our case the logs are on separate volumes. I assume based on what you are
saying that doing a select while inserts are being done will have an impact
on both the select and the insert?
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > the execution plans look fine. One application is performing at least 50,000
> > inserts in succession. The other application is reading around 22000 rows.
> > Actually to be more specific here is what happens. Application A reads out
> > 20k records from Table A and then inserts around that many to the database
> > (different table though). Application B at the same time inserts records into
> > Table A. Do the inserts from one application slow down the inserts from the
> > other application because the heads have to move around?
> >
> Well, inserting data into a database certainly does produce disk I/O,
> particularly against the transaction log. The rule of thumb is that the
> transaction log and database files are place on seperate volumes, with
> the transaction log being on an I/O channel that provides the fastest
> possible write throughput.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> In our case the logs are on separate volumes. I assume based on what you are
> saying that doing a select while inserts are being done will have an impact
> on both the select and the insert?
>
I was simply responding to your question about the "heads moving around".
An INSERT can block a SELECT if that insert has locked data pages (or
even the entire table) that the SELECT is attempting to read. The
SELECT will be forced to wait until the lock has been released.
Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
is currently reading, the INSERT will potentially have to wait. It all
depends on the lock types being used. Having good indexes in place to
support the SELECT statements will help to alleviate some of this.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess the real question I have is this. In a case where locking is not
occuring is it reasonable to assume that selecting from a database while
doing inserts to it will cause both the select and insert to take longer than
if they were to be done independantly. If that is the case what exactly
accounts for this?
thanks
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > In our case the logs are on separate volumes. I assume based on what you are
> > saying that doing a select while inserts are being done will have an impact
> > on both the select and the insert?
> >
> I was simply responding to your question about the "heads moving around".
> An INSERT can block a SELECT if that insert has locked data pages (or
> even the entire table) that the SELECT is attempting to read. The
> SELECT will be forced to wait until the lock has been released.
> Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
> is currently reading, the INSERT will potentially have to wait. It all
> depends on the lock types being used. Having good indexes in place to
> support the SELECT statements will help to alleviate some of this.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> I guess the real question I have is this. In a case where locking is not
> occuring is it reasonable to assume that selecting from a database while
> doing inserts to it will cause both the select and insert to take longer than
> if they were to be done independantly. If that is the case what exactly
> accounts for this?
> thanks
You will ALWAYS have locking - that locking may or may not cause
contention, which will appear to slow things down.
If things slow down during these large inserts, the only reasons that I
can think of are:
1. Locks being created by the INSERT are blocking other processes
2. The disks are not capable of keeping up with the I/O demands
produced by the influx of new data from the INSERT
3. The new data from the INSERT is requiring the database and/or
transaction log file to grow, resulting in a delay while that growth
takes place.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I would add:
4) Not enough memory to hold all the pages required for the insert and all
the pages required for the select in memory at once causing extra IO and
processor activity swapping pages in and out.
5) Enough data being transferred in and out to cause network contention on
the TDS connections
6) Becoming processor bound - especially if the select requires sorting of
results
7) Checkpoint interval low enough so inserts are causing frequent
checkpoints saturating the mdf disk drive
In general, if you're running that close to the margins, you probably need
to bump up your timeouts.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45537D29.2060702@.realsqlguy.com...
> mlblastvm wrote:
>> I guess the real question I have is this. In a case where locking is not
>> occuring is it reasonable to assume that selecting from a database while
>> doing inserts to it will cause both the select and insert to take longer
>> than if they were to be done independantly. If that is the case what
>> exactly accounts for this?
>> thanks
> You will ALWAYS have locking - that locking may or may not cause
> contention, which will appear to slow things down.
> If things slow down during these large inserts, the only reasons that I
> can think of are:
> 1. Locks being created by the INSERT are blocking other processes
> 2. The disks are not capable of keeping up with the I/O demands produced
> by the influx of new data from the INSERT
> 3. The new data from the INSERT is requiring the database and/or
> transaction log file to grow, resulting in a delay while that growth takes
> place.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com