Showing posts with label reading. Show all posts
Showing posts with label reading. Show all posts

Friday, March 30, 2012

Profiler [SQL Timeout using perfmon.msc]

After reading the information on the Poor man's monitor
http://www.sqlmag.com/Articles/ArticleID/37468/pg/2/2.html I began to believe
I could write an application that would immediately tell me when a timeout
was happening. Being that the server is a production server, I don't dare
test on it and the testing I did indicated that only the Server 2003 was able
to pull the data I was looking for (as the article indicated).
The problem is that when I set up the profmon.msc on the 2003 server to look
at the SQL timeouts on the 2000 server, it brings back nulls to the three
monitoring tables Counterdata, CounterDataDetails, and DisplayToID.
Is there a way to coax the actual SQL Lock Timeout data back from the 2000
server to the 2003 server through PerfMon.msc?
Should I post this to a different user group?
--
Regards,
JamieAnswered my own question. The counterid will increment each time the table
is created. I believe my smallest counterid in the counterdetails was 191.
When I reset the query to point to the right counterid, it immediately
produced results.
--
Regards,
Jamie
"thejamie" wrote:
> After reading the information on the Poor man's monitor
> http://www.sqlmag.com/Articles/ArticleID/37468/pg/2/2.html I began to believe
> I could write an application that would immediately tell me when a timeout
> was happening. Being that the server is a production server, I don't dare
> test on it and the testing I did indicated that only the Server 2003 was able
> to pull the data I was looking for (as the article indicated).
> The problem is that when I set up the profmon.msc on the 2003 server to look
> at the SQL timeouts on the 2000 server, it brings back nulls to the three
> monitoring tables Counterdata, CounterDataDetails, and DisplayToID.
> Is there a way to coax the actual SQL Lock Timeout data back from the 2000
> server to the 2003 server through PerfMon.msc?
> Should I post this to a different user group?
> --
> Regards,
> Jamie

Monday, March 26, 2012

Producing reports on a web page

Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
reading up on MS SQL Reporting Services and from what I can see they offer a
report repository as well as a a web based portal for downloading the reports.
The company I work for is currently using the following:
â?¢ Webserver running IIS
â?¢ SQL Server 2000
â?¢ Coldfusion MX 7.0 Server
â?¢ Crystal Enterprise Server 10.0
The current set-up we have is to produce the report in a web page (not
necessarily through a proprietary portal or anything like that).
Crystal Reports no longer supports this functionality in version 11. Does
anyone here know if it's possible for me to be able to create and produce a
report and then display it on a web page using Cold Fusion? This would
include being able to create a subreport and passing it parameters with
stored procedures.
Thanks!
ToddNot sure how Cold Fusion works but there are two ways you can integrate with
Report Services (assuming you are not using the Portal it ships with). One
is to use URL integration and the other is web services. With VS 2005 there
are two new controls (webform and winform) that integrate with RS 2005 using
webservices. Very good and very tight integration. They require 2.0
framework.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> reading up on MS SQL Reporting Services and from what I can see they offer
> a
> report repository as well as a a web based portal for downloading the
> reports.
> The company I work for is currently using the following:
> . Webserver running IIS
> . SQL Server 2000
> . Coldfusion MX 7.0 Server
> . Crystal Enterprise Server 10.0
> The current set-up we have is to produce the report in a web page (not
> necessarily through a proprietary portal or anything like that).
> Crystal Reports no longer supports this functionality in version 11. Does
> anyone here know if it's possible for me to be able to create and produce
> a
> report and then display it on a web page using Cold Fusion? This would
> include being able to create a subreport and passing it parameters with
> stored procedures.
>
> Thanks!
> Todd|||Thanks Bruce, I really appreciate the response.
This is how they currently do it:
Can you tell me if it is at all possible to do this with Microsoft Reporting
services? They are asking me to do the research and find out if MS SQL is a
viable solution.
-----
This document has been created demonstrating how we use Crystal Reports in
real life for reporting. It would be great if Cold Fusion reports could
produce the same result in the near future. Not only would it relive us of
many yearly fees from Business Objects, but it would rededicate our future to
ColdFusion and Macromedia/Adobe.
We currently use Crystal Reports Developer 10 and XI to develop reports that
get generated and displayed in our software on the fly. This software is
driven by ColdFusion MX 7.0. The reports are built upon stored procedures
that accept variables and produce different results.
Crystal Developer lets you browse the available stored procedures and select
the one you would like to use:
After you select which stored procedure you would like to use, the next
screen automatically asks you for some sample data to pass to the SP
variables:
Once the report has been attached to the stored procedure, the rest is just
creating the report. The field explorer on the shows which fields are
available from the stored procedure:
Fields are dragged on and the report is saved. Displaying the report can be
done by using some simple javascript. So we have the flexibility to have
whatever we want on the screen, and the report to be â'embeddedâ' in our
application.
We created a header and a footer to wrap around the parameters because those
donâ't change.
In our attempts to move over to the early version of ColdFusion reports,
this process has been real clumsy. The stored procedures donâ't show up on
the database browser and the only way we can get them to execute is using the
exec <stored procedure> in the query window. You can forget about passing
variables to it. It would be great if a similar process could be duplicated
with ColdFusion reports.
Thanks!!!
Todd
"Bruce L-C [MVP]" wrote:
> Not sure how Cold Fusion works but there are two ways you can integrate with
> Report Services (assuming you are not using the Portal it ships with). One
> is to use URL integration and the other is web services. With VS 2005 there
> are two new controls (webform and winform) that integrate with RS 2005 using
> webservices. Very good and very tight integration. They require 2.0
> framework.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
> news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> > reading up on MS SQL Reporting Services and from what I can see they offer
> > a
> > report repository as well as a a web based portal for downloading the
> > reports.
> >
> > The company I work for is currently using the following:
> >
> > . Webserver running IIS
> > . SQL Server 2000
> > . Coldfusion MX 7.0 Server
> > . Crystal Enterprise Server 10.0
> >
> > The current set-up we have is to produce the report in a web page (not
> > necessarily through a proprietary portal or anything like that).
> >
> > Crystal Reports no longer supports this functionality in version 11. Does
> > anyone here know if it's possible for me to be able to create and produce
> > a
> > report and then display it on a web page using Cold Fusion? This would
> > include being able to create a subreport and passing it parameters with
> > stored procedures.
> >
> >
> > Thanks!
> >
> > Todd
>
>|||The process here is just how the report is created by the developer? I.e.
this is not happening by an end user is it?
What you would do with RS is to create the report using your stored
procedures. SP are fully supported as long as the SP returns a single
resultset. Multiple resultsets are not supported. Once you have the report
working then you use either URL integration or webservices and integrate it
with your web pages.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Todd Jaspers" <ToddJaspers@.discussions.microsoft.com> wrote in message
news:8E94C9CC-0C74-4F60-AA08-C02CA08C71DB@.microsoft.com...
> Thanks Bruce, I really appreciate the response.
> This is how they currently do it:
> Can you tell me if it is at all possible to do this with Microsoft
> Reporting
> services? They are asking me to do the research and find out if MS SQL is
> a
> viable solution.
>
> -----
> This document has been created demonstrating how we use Crystal Reports in
> real life for reporting. It would be great if Cold Fusion reports could
> produce the same result in the near future. Not only would it relive us
> of
> many yearly fees from Business Objects, but it would rededicate our future
> to
> ColdFusion and Macromedia/Adobe.
> We currently use Crystal Reports Developer 10 and XI to develop reports
> that
> get generated and displayed in our software on the fly. This software is
> driven by ColdFusion MX 7.0. The reports are built upon stored procedures
> that accept variables and produce different results.
> Crystal Developer lets you browse the available stored procedures and
> select
> the one you would like to use:
> After you select which stored procedure you would like to use, the next
> screen automatically asks you for some sample data to pass to the SP
> variables:
> Once the report has been attached to the stored procedure, the rest is
> just
> creating the report. The field explorer on the shows which fields are
> available from the stored procedure:
> Fields are dragged on and the report is saved. Displaying the report can
> be
> done by using some simple javascript. So we have the flexibility to have
> whatever we want on the screen, and the report to be "embedded" in our
> application.
> We created a header and a footer to wrap around the parameters because
> those
> don't change.
> In our attempts to move over to the early version of ColdFusion reports,
> this process has been real clumsy. The stored procedures don't show up on
> the database browser and the only way we can get them to execute is using
> the
> exec <stored procedure> in the query window. You can forget about passing
> variables to it. It would be great if a similar process could be
> duplicated
> with ColdFusion reports.
>
> Thanks!!!
> Todd
>
> "Bruce L-C [MVP]" wrote:
>> Not sure how Cold Fusion works but there are two ways you can integrate
>> with
>> Report Services (assuming you are not using the Portal it ships with).
>> One
>> is to use URL integration and the other is web services. With VS 2005
>> there
>> are two new controls (webform and winform) that integrate with RS 2005
>> using
>> webservices. Very good and very tight integration. They require 2.0
>> framework.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
>> news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
>> > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've
>> > been
>> > reading up on MS SQL Reporting Services and from what I can see they
>> > offer
>> > a
>> > report repository as well as a a web based portal for downloading the
>> > reports.
>> >
>> > The company I work for is currently using the following:
>> >
>> > . Webserver running IIS
>> > . SQL Server 2000
>> > . Coldfusion MX 7.0 Server
>> > . Crystal Enterprise Server 10.0
>> >
>> > The current set-up we have is to produce the report in a web page (not
>> > necessarily through a proprietary portal or anything like that).
>> >
>> > Crystal Reports no longer supports this functionality in version 11.
>> > Does
>> > anyone here know if it's possible for me to be able to create and
>> > produce
>> > a
>> > report and then display it on a web page using Cold Fusion? This would
>> > include being able to create a subreport and passing it parameters with
>> > stored procedures.
>> >
>> >
>> > Thanks!
>> >
>> > Todd
>>|||Hi Todd,
I assume you know Cold fussion ? Does cold fusion, uses web services,
if yes, you can easily..integrate the way of reporting services or crystal
report
to be see in ur portal.
Why crystal report is not supported in the web in Version 11'who said this
regards
"Todd Jaspers" wrote:
> Thanks Bruce, I really appreciate the response.
> This is how they currently do it:
> Can you tell me if it is at all possible to do this with Microsoft Reporting
> services? They are asking me to do the research and find out if MS SQL is a
> viable solution.
>
> -----
> This document has been created demonstrating how we use Crystal Reports in
> real life for reporting. It would be great if Cold Fusion reports could
> produce the same result in the near future. Not only would it relive us of
> many yearly fees from Business Objects, but it would rededicate our future to
> ColdFusion and Macromedia/Adobe.
> We currently use Crystal Reports Developer 10 and XI to develop reports that
> get generated and displayed in our software on the fly. This software is
> driven by ColdFusion MX 7.0. The reports are built upon stored procedures
> that accept variables and produce different results.
> Crystal Developer lets you browse the available stored procedures and select
> the one you would like to use:
> After you select which stored procedure you would like to use, the next
> screen automatically asks you for some sample data to pass to the SP
> variables:
> Once the report has been attached to the stored procedure, the rest is just
> creating the report. The field explorer on the shows which fields are
> available from the stored procedure:
> Fields are dragged on and the report is saved. Displaying the report can be
> done by using some simple javascript. So we have the flexibility to have
> whatever we want on the screen, and the report to be â'embeddedâ' in our
> application.
> We created a header and a footer to wrap around the parameters because those
> donâ't change.
> In our attempts to move over to the early version of ColdFusion reports,
> this process has been real clumsy. The stored procedures donâ't show up on
> the database browser and the only way we can get them to execute is using the
> exec <stored procedure> in the query window. You can forget about passing
> variables to it. It would be great if a similar process could be duplicated
> with ColdFusion reports.
>
> Thanks!!!
> Todd
>
> "Bruce L-C [MVP]" wrote:
> > Not sure how Cold Fusion works but there are two ways you can integrate with
> > Report Services (assuming you are not using the Portal it ships with). One
> > is to use URL integration and the other is web services. With VS 2005 there
> > are two new controls (webform and winform) that integrate with RS 2005 using
> > webservices. Very good and very tight integration. They require 2.0
> > framework.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
> > news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> > > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> > > reading up on MS SQL Reporting Services and from what I can see they offer
> > > a
> > > report repository as well as a a web based portal for downloading the
> > > reports.
> > >
> > > The company I work for is currently using the following:
> > >
> > > . Webserver running IIS
> > > . SQL Server 2000
> > > . Coldfusion MX 7.0 Server
> > > . Crystal Enterprise Server 10.0
> > >
> > > The current set-up we have is to produce the report in a web page (not
> > > necessarily through a proprietary portal or anything like that).
> > >
> > > Crystal Reports no longer supports this functionality in version 11. Does
> > > anyone here know if it's possible for me to be able to create and produce
> > > a
> > > report and then display it on a web page using Cold Fusion? This would
> > > include being able to create a subreport and passing it parameters with
> > > stored procedures.
> > >
> > >
> > > Thanks!
> > >
> > > Todd
> >
> >
> >

Monday, February 20, 2012

Procedure takes more I/O on machine than on other

Hi,
I have a procedure for which I get different phsical_io reading from
sysprocesses on two different servers.
And this difference is not twice or thrice, but it is 10 times more I/O on
other machine.
What could be the possible cause?
I have verified both have exactly same tables design and server settings.
Thanks
Pushkarwhat about data volume?
"Pushkar" wrote:

> Hi,
> I have a procedure for which I get different phsical_io reading from
> sysprocesses on two different servers.
> And this difference is not twice or thrice, but it is 10 times more I/O on
> other machine.
> What could be the possible cause?
> I have verified both have exactly same tables design and server settings.
> Thanks
> Pushkar
>
>|||do a DBCC SHOWCONTIG on the tables (with the index that is used in the query
)
you select in the query on both servers
and check the logical and extent fragmentation.
And see if they are nearly equal.|||"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OljdnhBeGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a procedure for which I get different phsical_io reading from
> sysprocesses on two different servers.
> And this difference is not twice or thrice, but it is 10 times more I/O on
> other machine.
> What could be the possible cause?
> I have verified both have exactly same tables design and server settings.
>
Is the execution plan the same on both servers?
Is the logical IO the same?
Physical IO is affected by a whole lot of factors, but logical io is a
function of the data volume and the query plan.
David|||Thanks David
I have checked that logical I/O is same both machines.
How should I verify whether physical I/O is same or not.
Problem is simple on one machine I am using 70-80% cpu, since less I/O is
done on this machine and sp completed very fast, but on another machine I am
using 10-12% cpu, because I/O is more, so sp takes very long time to
complete.
I don't think there is any differnce SQL Server settings and query plan on
both machines, stiill different behavior.
Thanks
Pushkar
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OsYz4CeGHA.3632@.TK2MSFTNGP02.phx.gbl...
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:OljdnhBeGHA.2188@.TK2MSFTNGP04.phx.gbl...
>
> Is the execution plan the same on both servers?
> Is the logical IO the same?
> Physical IO is affected by a whole lot of factors, but logical io is a
> function of the data volume and the query plan.
> David
>|||Check your overall disk usage on both machines. Does one of the machines
have something going on that uses a lot of the CPU, so that SQL server is
competing with it?
You think the query plans are the same, but have you confirmed this? Please
post the query plans from each server, to be certain if they are the same or
not.
Have you regenerated statisitics on both servers to confirm they are up to
date?
Do you have the same data on both servers?
Do you have virus scan, or some performance monitors running on either
server. If so, are they configured identically?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:uZlc1CEeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> Thanks David
> I have checked that logical I/O is same both machines.
> How should I verify whether physical I/O is same or not.
> Problem is simple on one machine I am using 70-80% cpu, since less I/O is
> done on this machine and sp completed very fast, but on another machine I
am
> using 10-12% cpu, because I/O is more, so sp takes very long time to
> complete.
> I don't think there is any differnce SQL Server settings and query plan on
> both machines, stiill different behavior.
> Thanks
> Pushkar
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OsYz4CeGHA.3632@.TK2MSFTNGP02.phx.gbl...
settings.
>|||underprocessable|||Sorry, by disk utilization I did not mean free disk space, but disk IO.
Poor choice of words on my part.
Both machines have the same processor and disk hardware, with the same
amount of memory?
Something must be different on each machine, but there are so many things to
look at, I'm not really sure where to start.
Hopefully someone else knows better what to look at.
Please let us know what the issue turns out to be.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OPLenwEeGHA.3932@.TK2MSFTNGP05.phx.gbl...
> Hi,
machines
is
> I have checked disk usage on both machines, lot of free disk space is
there
> on all drives.
> There is no other process running on both server, so no issue of
competing.
>
same
> Query plans are exactly same, I have attached the same.
>
to
> Statistics are uptodate
>
> Exactly same data on both servers.
>
> I am using windows performance monitor and is configured properly.
> One thing I want to your notice that I have installed a new fresh instance
> on both machine and created same database and procedure on both machine.
> I have not changed any server/database settings. then also I am getting
same
> result.
> I don't still this is SQL Server issue or some other hardware/software
> issue.
> Please help me out.
> Thanks,
> Pushkar
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eDIg5JEeGHA.4900@.TK2MSFTNGP02.phx.gbl...
machines
is
same
to
is
I
a
>
>|||How shall I figure out about DISK I/O?
CPU and Memory are exactly the same on both machines. But do u agree that
this is some hardware issue?
Thanks
Pushkar
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%230$OL2EeGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Sorry, by disk utilization I did not mean free disk space, but disk IO.
> Poor choice of words on my part.
> Both machines have the same processor and disk hardware, with the same
> amount of memory?
> Something must be different on each machine, but there are so many things
> to
> look at, I'm not really sure where to start.
> Hopefully someone else knows better what to look at.
> Please let us know what the issue turns out to be.
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:OPLenwEeGHA.3932@.TK2MSFTNGP05.phx.gbl...
> machines
> is
> there
> competing.
> same
> to
> same
> machines
> is
> same
> to
> is
> I
> a
>|||Honestly, I don't know what the issue is. There are so many different
configuration parameters for the OS, DB, and hardware that it is impossible
to say without checking them one by one. And I myself only know a handful
of them, so I can't provide any real guidance. You can use performance
monitor to check your IO, but even there you will find many different things
to watch. At the same time, either memory or processor could make a
difference. If one is running low, then it will affect everything else.
I would run perfmon and compare memory, CPU, and IO on both boxes for a few
hours and see if you can find a discrepency there. Try getting the above
stats with and without the database running. If you get significantly
different results (between the boxes) while the database is down, then you
know something else is going on, and you can exclude the database and focus
on the hardware/OS. If both boxes are the same with the database down, but
different with the database running, then I would revisit your DB
configuration and look for discrepencies.
Please confirm two things first...
1. Is your disk using the same hardware for both databases?
2. Do both databases have the same amount of data?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OkSKHIFeGHA.380@.TK2MSFTNGP04.phx.gbl...
> How shall I figure out about DISK I/O?
> CPU and Memory are exactly the same on both machines. But do u agree that
> this is some hardware issue?
> Thanks
> Pushkar
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%230$OL2EeGHA.1320@.TK2MSFTNGP04.phx.gbl...
things
server
up
either
machine.
server
up
either
I/O
in
more
is
>