I have the following setup -
Database Server having (Report Server Database + Application Database)
Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75 gb
disk space)
Software - Windows 2K Advance Server SP4, SQL 2000 sp3
IIS Server ( Report Server, Application Website)
Hardware config - 2CPU (1130 mhz), 1 gb RAM
Software - Windows 2K Server SP4, Reporting Services with SP1
Flow of application - User visits the application web site, queries (data
size 2.3 million records for a month) the and internally application passes
this query to Report Server through URL and data comes back to the User.
This thing works fine as long user queries for 2-3 days of data, but it
starts throwing "Server not available..." if it goes beyond that. (I saw
that on the web server aspnet_wp.exe starts crashing)
Any suggestion or solutions are welcome. I'm thinking of putting Report
Server web site on the Database Server, but I want to avoid that as I don't
want to run IIS with SQL 2000 (may be I'm wrong).
Thanks in advance,
SuhaibDo you have the /3GB flag set in c:\boot.ini? Take a look at for some common
things
http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx
Another thing to do would be to monitor the Process | Privates bytes
performance counter for the aspnet_wp.exe process.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Suhaib Khan" <skhan@.faicorp.com> wrote in message
news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> I have the following setup -
> Database Server having (Report Server Database + Application Database)
> Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
gb
> disk space)
> Software - Windows 2K Advance Server SP4, SQL 2000 sp3
>
> IIS Server ( Report Server, Application Website)
> Hardware config - 2CPU (1130 mhz), 1 gb RAM
> Software - Windows 2K Server SP4, Reporting Services with SP1
> Flow of application - User visits the application web site, queries (data
> size 2.3 million records for a month) the and internally application
passes
> this query to Report Server through URL and data comes back to the User.
> This thing works fine as long user queries for 2-3 days of data, but it
> starts throwing "Server not available..." if it goes beyond that. (I saw
> that on the web server aspnet_wp.exe starts crashing)
> Any suggestion or solutions are welcome. I'm thinking of putting Report
> Server web site on the Database Server, but I want to avoid that as I
don't
> want to run IIS with SQL 2000 (may be I'm wrong).
>
> Thanks in advance,
> Suhaib
>|||how many rows are returned to the report?
have you some aggregated data? does it SQL Server standard or enterprise
edition?
Have you try to use OLAP Cubes?
"Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> I have the following setup -
> Database Server having (Report Server Database + Application Database)
> Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
gb
> disk space)
> Software - Windows 2K Advance Server SP4, SQL 2000 sp3
>
> IIS Server ( Report Server, Application Website)
> Hardware config - 2CPU (1130 mhz), 1 gb RAM
> Software - Windows 2K Server SP4, Reporting Services with SP1
> Flow of application - User visits the application web site, queries (data
> size 2.3 million records for a month) the and internally application
passes
> this query to Report Server through URL and data comes back to the User.
> This thing works fine as long user queries for 2-3 days of data, but it
> starts throwing "Server not available..." if it goes beyond that. (I saw
> that on the web server aspnet_wp.exe starts crashing)
> Any suggestion or solutions are welcome. I'm thinking of putting Report
> Server web site on the Database Server, but I want to avoid that as I
don't
> want to run IIS with SQL 2000 (may be I'm wrong).
>
> Thanks in advance,
> Suhaib
>|||What is the size of the result set? How many pages? Also, what are you
rending it as (html, pdf, etc?). How long does it take from query analyzer?
Are you sending lots of data to RS and letting it filter or letting the
query itself filter out the data before it arrives at RS?
Bruce L-C
"Suhaib Khan" <skhan@.faicorp.com> wrote in message
news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> I have the following setup -
> Database Server having (Report Server Database + Application Database)
> Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
gb
> disk space)
> Software - Windows 2K Advance Server SP4, SQL 2000 sp3
>
> IIS Server ( Report Server, Application Website)
> Hardware config - 2CPU (1130 mhz), 1 gb RAM
> Software - Windows 2K Server SP4, Reporting Services with SP1
> Flow of application - User visits the application web site, queries (data
> size 2.3 million records for a month) the and internally application
passes
> this query to Report Server through URL and data comes back to the User.
> This thing works fine as long user queries for 2-3 days of data, but it
> starts throwing "Server not available..." if it goes beyond that. (I saw
> that on the web server aspnet_wp.exe starts crashing)
> Any suggestion or solutions are welcome. I'm thinking of putting Report
> Server web site on the Database Server, but I want to avoid that as I
don't
> want to run IIS with SQL 2000 (may be I'm wrong).
>
> Thanks in advance,
> Suhaib
>|||how many rows are returned to the report?
for 5 days - 430,000.
have you some aggregated data?
Yes, but we are not using any filter option on Report Server side
does it SQL Server standard or enterprise edition?
enterprise
Have you try to use OLAP Cubes?
Not now.
We are upgrading our current Reports from pure html using ASP to ASP.NET
with Reporting Services. We used to allow user's to query data for 3
months, again these months were not prefined, user's were allowed to select
Jan to March or Feb to April, with Reporting Services we are not able cross
more than 3 days forget about going across 3 months.
Database is partitioned based on Month, Vendor, and Year ID (Currently we
just have one vendor and one month of data, so I can't balme my partiton)
Suhaib
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:%23MZgb%23gXEHA.1048@.tk2msftngp13.phx.gbl...
> how many rows are returned to the report?
> have you some aggregated data? does it SQL Server standard or enterprise
> edition?
> Have you try to use OLAP Cubes?
> "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > I have the following setup -
> >
> > Database Server having (Report Server Database + Application Database)
> > Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
> gb
> > disk space)
> > Software - Windows 2K Advance Server SP4, SQL 2000 sp3
> >
> >
> > IIS Server ( Report Server, Application Website)
> > Hardware config - 2CPU (1130 mhz), 1 gb RAM
> > Software - Windows 2K Server SP4, Reporting Services with SP1
> >
> > Flow of application - User visits the application web site, queries
(data
> > size 2.3 million records for a month) the and internally application
> passes
> > this query to Report Server through URL and data comes back to the User.
> >
> > This thing works fine as long user queries for 2-3 days of data, but it
> > starts throwing "Server not available..." if it goes beyond that. (I saw
> > that on the web server aspnet_wp.exe starts crashing)
> >
> > Any suggestion or solutions are welcome. I'm thinking of putting Report
> > Server web site on the Database Server, but I want to avoid that as I
> don't
> > want to run IIS with SQL 2000 (may be I'm wrong).
> >
> >
> > Thanks in advance,
> >
> > Suhaib
> >
> >
>|||430 000 rows'
wow!!!!
its very very big!!!
why do you return so many details in your report?
If you use aggregated function in RS, then the time to process the report
will be huge!!! in this case, I recommend to create multiple datasets (1 for
the detail, 1 for aggregated data with an SQL statement which do the
calculations (sums, group by...))
Maybe you can schedule the report and save it as a snapshot report. (or save
it as PDF in a network folder)
"Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
news:%23hIXORiXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> how many rows are returned to the report?
> for 5 days - 430,000.
> have you some aggregated data?
> Yes, but we are not using any filter option on Report Server side
> does it SQL Server standard or enterprise edition?
> enterprise
> Have you try to use OLAP Cubes?
> Not now.
> We are upgrading our current Reports from pure html using ASP to ASP.NET
> with Reporting Services. We used to allow user's to query data for 3
> months, again these months were not prefined, user's were allowed to
select
> Jan to March or Feb to April, with Reporting Services we are not able
cross
> more than 3 days forget about going across 3 months.
>
> Database is partitioned based on Month, Vendor, and Year ID (Currently we
> just have one vendor and one month of data, so I can't balme my partiton)
>
> Suhaib
>
> "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
> news:%23MZgb%23gXEHA.1048@.tk2msftngp13.phx.gbl...
> > how many rows are returned to the report?
> > have you some aggregated data? does it SQL Server standard or enterprise
> > edition?
> > Have you try to use OLAP Cubes?
> >
> > "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> > news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > I have the following setup -
> > >
> > > Database Server having (Report Server Database + Application Database)
> > > Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive
(75
> > gb
> > > disk space)
> > > Software - Windows 2K Advance Server SP4, SQL 2000 sp3
> > >
> > >
> > > IIS Server ( Report Server, Application Website)
> > > Hardware config - 2CPU (1130 mhz), 1 gb RAM
> > > Software - Windows 2K Server SP4, Reporting Services with SP1
> > >
> > > Flow of application - User visits the application web site, queries
> (data
> > > size 2.3 million records for a month) the and internally application
> > passes
> > > this query to Report Server through URL and data comes back to the
User.
> > >
> > > This thing works fine as long user queries for 2-3 days of data, but
it
> > > starts throwing "Server not available..." if it goes beyond that. (I
saw
> > > that on the web server aspnet_wp.exe starts crashing)
> > >
> > > Any suggestion or solutions are welcome. I'm thinking of putting
Report
> > > Server web site on the Database Server, but I want to avoid that as I
> > don't
> > > want to run IIS with SQL 2000 (may be I'm wrong).
> > >
> > >
> > > Thanks in advance,
> > >
> > > Suhaib
> > >
> > >
> >
> >
>|||Do a report in Query Analyzer and return 430,000 rows. Now wait and wait
somemore and wait somemore. If your customer needs a data extract (for
instance to load their own datamart) then you should use DTS. Otherwise, you
should have reports that provide drill through that allows the customer to
get to the data they are interested in. No human is going to browse 430,000
rows. I suggest you look at how to do drill through. RS supports drill
through reports very cleanly and it is the way to go.
As far as scheduling a PDF. This would be a 5,000 page PDF report. This is
not reasonable to expect a system to do. I strongly suggest re-analyzing
your requirements and solutions. Nobody will have a solution for a 5,000
page report that is speedy. Again, if you are extracting data for someone's
datamart then use DTS.
Bruce L-C
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:umOPgHrXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> 430 000 rows'
> wow!!!!
> its very very big!!!
> why do you return so many details in your report?
> If you use aggregated function in RS, then the time to process the report
> will be huge!!! in this case, I recommend to create multiple datasets (1
for
> the detail, 1 for aggregated data with an SQL statement which do the
> calculations (sums, group by...))
> Maybe you can schedule the report and save it as a snapshot report. (or
save
> it as PDF in a network folder)
> "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> news:%23hIXORiXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > how many rows are returned to the report?
> > for 5 days - 430,000.
> >
> > have you some aggregated data?
> > Yes, but we are not using any filter option on Report Server side
> >
> > does it SQL Server standard or enterprise edition?
> > enterprise
> >
> > Have you try to use OLAP Cubes?
> > Not now.
> >
> > We are upgrading our current Reports from pure html using ASP to ASP.NET
> > with Reporting Services. We used to allow user's to query data for 3
> > months, again these months were not prefined, user's were allowed to
> select
> > Jan to March or Feb to April, with Reporting Services we are not able
> cross
> > more than 3 days forget about going across 3 months.
> >
> >
> > Database is partitioned based on Month, Vendor, and Year ID (Currently
we
> > just have one vendor and one month of data, so I can't balme my
partiton)
> >
> >
> > Suhaib
> >
> >
> > "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
> > news:%23MZgb%23gXEHA.1048@.tk2msftngp13.phx.gbl...
> > > how many rows are returned to the report?
> > > have you some aggregated data? does it SQL Server standard or
enterprise
> > > edition?
> > > Have you try to use OLAP Cubes?
> > >
> > > "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> > > news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > > I have the following setup -
> > > >
> > > > Database Server having (Report Server Database + Application
Database)
> > > > Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive
> (75
> > > gb
> > > > disk space)
> > > > Software - Windows 2K Advance Server SP4, SQL 2000 sp3
> > > >
> > > >
> > > > IIS Server ( Report Server, Application Website)
> > > > Hardware config - 2CPU (1130 mhz), 1 gb RAM
> > > > Software - Windows 2K Server SP4, Reporting Services with SP1
> > > >
> > > > Flow of application - User visits the application web site, queries
> > (data
> > > > size 2.3 million records for a month) the and internally application
> > > passes
> > > > this query to Report Server through URL and data comes back to the
> User.
> > > >
> > > > This thing works fine as long user queries for 2-3 days of data, but
> it
> > > > starts throwing "Server not available..." if it goes beyond that. (I
> saw
> > > > that on the web server aspnet_wp.exe starts crashing)
> > > >
> > > > Any suggestion or solutions are welcome. I'm thinking of putting
> Report
> > > > Server web site on the Database Server, but I want to avoid that as
I
> > > don't
> > > > want to run IIS with SQL 2000 (may be I'm wrong).
> > > >
> > > >
> > > > Thanks in advance,
> > > >
> > > > Suhaib
> > > >
> > > >
> > >
> > >
> >
> >
>
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Performance dramatically down on Analysis Manager after switching to simple recovery model
Please, any one can give me some suggestion about this subject ?
The facts are following: SQL Server 2000 Standard Edition (SP4)
One week ago I switched the recovery model of my database (50 GB size) to simple. I had my reasons. Since then, the procesing time of my OLAP cubes has dramatically raised, in procent of 500-800%.
For exemple, one cube with a 30 minutes processing time before, now is 8 hours.
And the biggest one, with a time of 1.5 hours nor is at 20 hours. I'm destroyed.
After I have notices that, I switched back to full mode, but the situations remains the same. I don't know what to do any more.
Thanks in advance to all willing to give me some sugestions.
Hi Simona,
Your transaction log is bigger now!Switch to simple recovery model and shrink the transaction log.
sqlFriday, March 23, 2012
Performance Dashboard Errors
After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :
The 'version_string' parameter is missing a value.
Anyone else seen this, and how did you fix the problem?
What is the service pack level on SQL Server?|||I am getting the same error message
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
|||How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.
-Sue
|||I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)
I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.
The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.
Many thanks for any suggestions on this one.
John
|||Any resolution on this?
Thanks
|||Still nothing and even Google doesnt seem to have much of any use.
Its a real pity as I can see it would be useful to have the performance dashboard working.
|||I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.
The performance_dashboard_main.rdl is a XML file so I searched for and found
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
which I changed to several different values (as a new filename) until I just removed it as below
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.
Performance Dashboard Errors
After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :
The 'version_string' parameter is missing a value.
Anyone else seen this, and how did you fix the problem?
What is the service pack level on SQL Server?|||I am getting the same error message
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
|||How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.
-Sue
|||I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)
I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.
The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.
Many thanks for any suggestions on this one.
John
|||Any resolution on this?
Thanks
|||Still nothing and even Google doesnt seem to have much of any use.
Its a real pity as I can see it would be useful to have the performance dashboard working.
|||I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.
The performance_dashboard_main.rdl is a XML file so I searched for and found
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
which I changed to several different values (as a new filename) until I just removed it as below
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.
Performance Dashboard Errors
After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :
The 'version_string' parameter is missing a value.
Anyone else seen this, and how did you fix the problem?
What is the service pack level on SQL Server?|||I am getting the same error message
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
|||How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.
-Sue
|||I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)
I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.
The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.
Many thanks for any suggestions on this one.
John
|||Any resolution on this?
Thanks
|||Still nothing and even Google doesnt seem to have much of any use.
Its a real pity as I can see it would be useful to have the performance dashboard working.
|||I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.
The performance_dashboard_main.rdl is a XML file so I searched for and found
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
which I changed to several different values (as a new filename) until I just removed it as below
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.
Performance Dashboard Errors
After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :
The 'version_string' parameter is missing a value.
Anyone else seen this, and how did you fix the problem?
What is the service pack level on SQL Server?|||I am getting the same error message
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
|||How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.
-Sue
|||I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)
I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.
The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.
Many thanks for any suggestions on this one.
John
|||Any resolution on this?
Thanks
|||Still nothing and even Google doesnt seem to have much of any use.
Its a real pity as I can see it would be useful to have the performance dashboard working.
|||I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.
The performance_dashboard_main.rdl is a XML file so I searched for and found
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
which I changed to several different values (as a new filename) until I just removed it as below
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.
Performance Dashboard Errors
After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :
The 'version_string' parameter is missing a value.
Anyone else seen this, and how did you fix the problem?
What is the service pack level on SQL Server?|||I am getting the same error message
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
|||How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.
-Sue
|||I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)
I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.
The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.
Many thanks for any suggestions on this one.
John
|||Any resolution on this?
Thanks
|||Still nothing and even Google doesnt seem to have much of any use.
Its a real pity as I can see it would be useful to have the performance dashboard working.
|||I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.
The performance_dashboard_main.rdl is a XML file so I searched for and found
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
which I changed to several different values (as a new filename) until I just removed it as below
<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.
sqlPerformance counters on 64 bit SSIS
On 32 bit SSIS installations, both of the following performance counter objects are visible in perfmon.
SQLServer:SSIS Service
SQLServer:SSIS Pipeline
On 64 bit SSIS installations, only the following is available.
SQLServer:SSIS Service
The SQlServer:SSIS Pipeline counters are nowhere to be found.
Should I re-install? Is this a known issue with 64 bit SSIS?
P.S. Remote or local access administrative access with perfmon makes no difference, the "SQLServer:SSIS Pipeline" performance counters don't appear in the listbox when connecting to Windows 2003 x64 server.
Try running perfmon w/ 32-bit switch like so:
mmc.exe /32 perfmon.msc
Counters should show up.
|||Halfway answered.
Local perfmon access to x64 "SQlServer:SSIS Pipeline" counters: yes
Remote perfmon access to x64 "SQlServer:SSIS Pipeline" counters: no
As to the local SSIS performance counter access on x64, using the SysWOW64 version of perfmon.exe does pick up the"SQlServer:SSIS Pipeline" counters.(e.g. C\WINDOWS\SysWOW64\perfmon.exe), so thanks for that.
Tuesday, March 20, 2012
performance bottleneck
I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.184
1) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly'?
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>
|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly'?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>
|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.184
1) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly'?
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>
|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly'?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>
|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John
performance bottleneck
I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/downl...aits_Queues.doc
John
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/downl...aits_Queues.doc
John
performance bottleneck
I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
--
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >I am looking at the performance of one of our production servers and have
> >done Perfmon. I am seeing a definite bottleneck on the following drives
> >
> > I, L, H and J
> >
> > What do you thing and is there anything else I am missing here.
> >
> > Thanks for your help.
> >
> >
> >
> > PhysicalDisk
> > E
> > T
> > O
> > I
> > L
> > H
> > J
> >
> > % Disk Time
> > 0.693
> > 56.483
> > 1.917
> > 379.04
> > 326.234
> > 470.186
> > 149.234
> >
> > Avg.Disk Queue Length
> > 0.007
> > 0.565
> > 0.019
> > 3.79
> > 3.262
> > 4.702
> > 1.492
> >
> > Current Disk Queue Length
> > 0.01
> > 0.181
> > 0.014
> > 27.186
> > 2.664
> > 3.443
> > 2.935
> >
> > Disk Writes/sec
> > 1.234
> > 112.324
> > 22.064
> > 5.734
> > 5.535
> > 8.989
> > 4.184
> >
> >
> >
>
>|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
--
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >I am looking at the performance of one of our production servers and have
> >done Perfmon. I am seeing a definite bottleneck on the following drives
> >
> > I, L, H and J
> >
> > What do you thing and is there anything else I am missing here.
> >
> > Thanks for your help.
> >
> >
> >
> > PhysicalDisk
> > E
> > T
> > O
> > I
> > L
> > H
> > J
> >
> > % Disk Time
> > 0.693
> > 56.483
> > 1.917
> > 379.04
> > 326.234
> > 470.186
> > 149.234
> >
> > Avg.Disk Queue Length
> > 0.007
> > 0.565
> > 0.019
> > 3.79
> > 3.262
> > 4.702
> > 1.492
> >
> > Current Disk Queue Length
> > 0.01
> > 0.181
> > 0.014
> > 27.186
> > 2.664
> > 3.443
> > 2.935
> >
> > Disk Writes/sec
> > 1.234
> > 112.324
> > 22.064
> > 5.734
> > 5.535
> > 8.989
> > 4.184
> >
> >
> >
>
>|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John
Performance between SP3 and SP4
We have the following select statement
SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
WHERE W.TRANSACTIONID = 53364
AND D.ID = W.RECORDID
AND D.APPROVAL IS NOT NULL
AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
There are approx 350000 records in DAT_SAMPLES with a clustered index
on ID
and 1 record in WRK_SELECT
Executing the above query in SQLServer 2000 with SP3 takes < 1 second
Executing the same query against the same database connected to
SQLServer 2000 SP4 take approx 30 seconds
Looking at the execution plans SP3 performs a Nested Loop/inner join,
but SP4 performs a Merge Join/Inner Loop
I have attempted to rewrite the query using EXISTS and IN with the same
results.
Has anybody else come across similar performance issues with SP4Having you made sure that statistics are up to date on both servers/database
s?
I do recall having read about different algorithms being used between SPs,
but can't remember which SPs (might have been 2 & 3.)
Also, might a FROM...WITH help?
"Warren" wrote:
> We have the following select statement
> SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
> WHERE W.TRANSACTIONID = 53364
> AND D.ID = W.RECORDID
> AND D.APPROVAL IS NOT NULL
> AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
> There are approx 350000 records in DAT_SAMPLES with a clustered index
> on ID
> and 1 record in WRK_SELECT
> Executing the above query in SQLServer 2000 with SP3 takes < 1 second
> Executing the same query against the same database connected to
> SQLServer 2000 SP4 take approx 30 seconds
> Looking at the execution plans SP3 performs a Nested Loop/inner join,
> but SP4 performs a Merge Join/Inner Loop
> I have attempted to rewrite the query using EXISTS and IN with the same
> results.
> Has anybody else come across similar performance issues with SP4
>|||I had updated all statistics and also tried a FROM... WITH, neither had
any effect.
I have identified the problem being that the WRK_SELECT.RECORDID is a
DECIMAL(10,0) and the DAT_SAMPLES.ID is a DECIMAL(9,0). This did not
cause any problem with SP3, but with SP4 it only works if I change
RECORDID to DECIMAL(9,0), or use CONVERT in the query.
SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
WHERE W.TRANSACTIONID = 53364
AND D.ID = W.RECORDID
AND D.APPROVAL IS NOT NULL
AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
There are approx 350000 records in DAT_SAMPLES with a clustered index
on ID
and 1 record in WRK_SELECT
Executing the above query in SQLServer 2000 with SP3 takes < 1 second
Executing the same query against the same database connected to
SQLServer 2000 SP4 take approx 30 seconds
Looking at the execution plans SP3 performs a Nested Loop/inner join,
but SP4 performs a Merge Join/Inner Loop
I have attempted to rewrite the query using EXISTS and IN with the same
results.
Has anybody else come across similar performance issues with SP4Having you made sure that statistics are up to date on both servers/database
s?
I do recall having read about different algorithms being used between SPs,
but can't remember which SPs (might have been 2 & 3.)
Also, might a FROM...WITH help?
"Warren" wrote:
> We have the following select statement
> SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
> WHERE W.TRANSACTIONID = 53364
> AND D.ID = W.RECORDID
> AND D.APPROVAL IS NOT NULL
> AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
> There are approx 350000 records in DAT_SAMPLES with a clustered index
> on ID
> and 1 record in WRK_SELECT
> Executing the above query in SQLServer 2000 with SP3 takes < 1 second
> Executing the same query against the same database connected to
> SQLServer 2000 SP4 take approx 30 seconds
> Looking at the execution plans SP3 performs a Nested Loop/inner join,
> but SP4 performs a Merge Join/Inner Loop
> I have attempted to rewrite the query using EXISTS and IN with the same
> results.
> Has anybody else come across similar performance issues with SP4
>|||I had updated all statistics and also tried a FROM... WITH, neither had
any effect.
I have identified the problem being that the WRK_SELECT.RECORDID is a
DECIMAL(10,0) and the DAT_SAMPLES.ID is a DECIMAL(9,0). This did not
cause any problem with SP3, but with SP4 it only works if I change
RECORDID to DECIMAL(9,0), or use CONVERT in the query.
Monday, March 12, 2012
performance and locking problems - urget
Dear Gurus,
A Client has the following problems/requests for their Production
databases, what is your professional/practical advises to tackle and
resolve these issues:
1)Number of Transactions per day The current database can currently
handle about 5000 order transactions per day
2)Table locking When doing row updates SQL Server will lock the
whole table. For example after a few instances of a driver updating an
order record it will lock the entire orders table
3)Reports When a user runs a report involving big quantities of
order-related data other processes will suffer occasional timeouts
4)Archiving There is no efficient way of archiving historical system
dataDavid McGeorge (soalvajavab1@.yahoo.com) writes:
> A Client has the following problems/requests for their Production
> databases, what is your professional/practical advises to tackle and
> resolve these issues:
I'm afraid that your questions are far too open-ended for it to be
possible to give an exhaustive answer in a newsgroup post. Had I been
a consultant, I would have been tempted to refer you the web site for
my business. Judging from your mail address you're a web programmer,
and from your questions it appears that you client is a need of someone
with SQL expertise.
> 1)Number of Transactions per day ? The current database can currently
> handle about 5000 order transactions per day
What do you mean with "can"? Is 5000 transactions/day the actual load?
Or is the database at the maximum of its capacity with that rate? I
would assume the former, since 5000 transactions/day is a low number,
unless the transactions are extremely complex.
> 2)Table locking ? When doing row updates SQL Server will lock the
> whole table. For example after a few instances of a driver updating an
> order record it will lock the entire orders table
This sounds like the indexing of the database needs to be improved.
If you say:
UPDATE tbl
SET col = 23
WHERE othercol = 12
And there is no index on othercol, SQL Server will have no choice but
to lock the entire table.
One tool to improve indexing in the database is the Index Tuning Wizard.
You give it a day's workload, and it will suggest suitable indexes to add.
> 3)Reports ? When a user runs a report involving big quantities of
> order-related data other processes will suffer occasional timeouts
Reports are often problem in OLTP databases, because they need to scan
lots of data. Indexing can help, but if user can build their own reports,
or there are report functions with lots of selection criteria, this
may not be feasible. Not the least, if the users can accept that an
odd report takes 20-30 minutes to run.
Therefore many sites sets up a report database, which is a copy of the
live database. Depending on the requirements, the report database can
be loaded from yesterday's backup, or you could keep it updated with
log shipping or replication. The latter is required if users must have
today's data in their reports, but it takes more effort to get there.
> 4)Archiving ? There is no efficient way of archiving historical system
> data
This point is completely impossible to address without knowing the
business requirements.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Performance : Queries taking very long time (Urgent)
The production_table has ~20 million records and has identity key which is
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.
Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. You
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:
> The production_table has ~20 million records and has identity key which is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>
|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
David Portas
SQL Server MVP
|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.
Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. You
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:
> The production_table has ~20 million records and has identity key which is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>
|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
David Portas
SQL Server MVP
|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
Performance : Queries taking very long time (Urgent)
The production_table has ~20 million records and has identity key which is
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. You
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:
> The production_table has ~20 million records and has identity key which is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
--
David Portas
SQL Server MVP
--|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. You
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:
> The production_table has ~20 million records and has identity key which is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
--
David Portas
SQL Server MVP
--|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
Performance : Queries taking very long time (Urgent)
The production_table has ~20 million records and has identity key which is
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. Yo
u
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million record
s
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
David Portas
SQL Server MVP
--|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. Yo
u
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million record
s
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
David Portas
SQL Server MVP
--|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
Wednesday, March 7, 2012
performance
I have been looking at the performance of my SQL Server 2000.
For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
MIN MAX AVERAGE
0.494 39244.5 113.686
If I take out the busiest 45 minutes and look at the Statistics for 23 hours
and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
MIN MAX AVERAGE
0.912 4860.8 42.423
Microsoft suggests a threshold value of 90% for this counter. Do these
values look OK? I am not having any performance problems on this machine.
Even the average %Processor Time is less than 3%. Any insight will be
appreciated. Thanks.%Disk Time is almost a useless counter and should be used very sparingly or
even not at all. If it is at 100 or less you can be pretty sure the disks
are not being taxed too much. Anything over that and it becomes pretty
unreliable and hard to correlate. The disk Q's will give a much better
overall indication of how busy they are but there are other disk counters
that will give more detailed insights to how efficient they are. The
fn_virtual_filestats are also a good place to start for disk related info.
Here are some other places:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/28/don-t-forget-the-business-transaction-throughput-and-response-time.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
IO Basics
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:159A4413-BD9B-4123-A669-4D4140603888@.microsoft.com...
>I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next
> day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23
> hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.|||In addition to what Andy said, you should also look at the I/O response
counters (Avg Disk sec/read and Avg disk sec/write). These counters tell you
how long in average an I/O read or write take to complete. Together with Avg
Disk bytes/read and Avg Disk bytes/write--which tell you the sizes of your
I/O, you can get a pretty good picture of how your disks are doing. For
instance, if you are doing small I/Os and your avg disk response time is long
(e.g. > 20ms or a smaller threshold), something is up with your disk and you
should take a closer look.
Linchi
"sharman" wrote:
> I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23 hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.
For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
MIN MAX AVERAGE
0.494 39244.5 113.686
If I take out the busiest 45 minutes and look at the Statistics for 23 hours
and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
MIN MAX AVERAGE
0.912 4860.8 42.423
Microsoft suggests a threshold value of 90% for this counter. Do these
values look OK? I am not having any performance problems on this machine.
Even the average %Processor Time is less than 3%. Any insight will be
appreciated. Thanks.%Disk Time is almost a useless counter and should be used very sparingly or
even not at all. If it is at 100 or less you can be pretty sure the disks
are not being taxed too much. Anything over that and it becomes pretty
unreliable and hard to correlate. The disk Q's will give a much better
overall indication of how busy they are but there are other disk counters
that will give more detailed insights to how efficient they are. The
fn_virtual_filestats are also a good place to start for disk related info.
Here are some other places:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/28/don-t-forget-the-business-transaction-throughput-and-response-time.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
IO Basics
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:159A4413-BD9B-4123-A669-4D4140603888@.microsoft.com...
>I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next
> day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23
> hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.|||In addition to what Andy said, you should also look at the I/O response
counters (Avg Disk sec/read and Avg disk sec/write). These counters tell you
how long in average an I/O read or write take to complete. Together with Avg
Disk bytes/read and Avg Disk bytes/write--which tell you the sizes of your
I/O, you can get a pretty good picture of how your disks are doing. For
instance, if you are doing small I/Os and your avg disk response time is long
(e.g. > 20ms or a smaller threshold), something is up with your disk and you
should take a closer look.
Linchi
"sharman" wrote:
> I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23 hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.
Monday, February 20, 2012
Percentage of Total
Hi Friends,
I have a Cube wherein i have dimensions with following Heirachy:-
Activity
PartNumber
Now if the person selects Activity and Quantity, like as below i want output as
Activity Part Quantity %of Quantity
Activity1 Part1 75 37.5%
Part2 125 62.5%
Activity1 Total 200 40%
Activity2 Part3 90 90%
Part4 10 10%
Activity2 Total 100 20%
Activity3 Part5 100 50%
Part6 100 50%
Activity3 Total 200 40%
This is really urgent...
Regards,
Kaushal
Here's a sample Adventure Works query which may help:
With
Member [Measures].[OrderPercentOfParent] as
[Measures].[Order Quantity] /
([Measures].[Order Quantity],
[Promotion].[Promotions].Parent),
FORMAT_STRING = 'Percent'
select
{[Measures].[Order Quantity],
[Measures].[OrderPercentOfParent]} on 0,
NON EMPTY DrillDownLevel([Promotion].[Promotions].[All Promotions].Children) on 1
from [Adventure Works]
Order Quantity OrderPercentOfParent
No Discount 238,806 86.91%
Reseller 35,970 13.09%
Discontinued Product 838 2.33%
Excess Inventory 304 0.85%
New Product 2,356 6.55%
Seasonal Discount 1,172 3.26%
Volume Discount 31,300 87.02%
Deepak thanks for the help, but i have used following formula...
IIF([Activity].CurrentMember.Parent is null, 100,(Measures.[Net OI EUR - 2006] / (Measures.[Net OI EUR - 2006],[Activity].CurrentMember.Parent))*100)
Activity Value - 2006 Value - 2007 Var% Delta Mix(Value2007 - Value2006) * Var%
Act1 100 75 5% -0.125
Act2 50 80 10% 3
Act3 50 80 3% 0.9
Grand Total 200 235 4% 1.4(But i want 3.775 Sum of Act1+Act2+Act3)
How can i achieve this, really urgent...
Regards,
Kaushal
Percentage of Total
Hi Friends,
I have a Cube wherein i have dimensions with following Heirachy:-
Activity
PartNumber
Now if the person selects Activity and Quantity, like as below i want output as
Activity Part Quantity %of Quantity
Activity1 Part1 75 37.5%
Part2 125 62.5%
Activity1 Total 200 40%
Activity2 Part3 90 90%
Part4 10 10%
Activity2 Total 100 20%
Activity3 Part5 100 50%
Part6 100 50%
Activity3 Total 200 40%
This is really urgent...
Regards,
Kaushal
Here's a sample Adventure Works query which may help:
With
Member [Measures].[OrderPercentOfParent] as
[Measures].[Order Quantity] /
([Measures].[Order Quantity],
[Promotion].[Promotions].Parent),
FORMAT_STRING = 'Percent'
select
{[Measures].[Order Quantity],
[Measures].[OrderPercentOfParent]} on 0,
NONEMPTYDrillDownLevel([Promotion].[Promotions].[All Promotions].Children) on 1
from [Adventure Works]
Order Quantity OrderPercentOfParent
No Discount 238,806 86.91%
Reseller 35,970 13.09%
Discontinued Product 838 2.33%
Excess Inventory 304 0.85%
New Product 2,356 6.55%
Seasonal Discount 1,172 3.26%
Volume Discount 31,300 87.02%
Deepak thanks for the help, but i have used following formula...
IIF([Activity].CurrentMember.Parentisnull, 100,(Measures.[Net OI EUR - 2006] / (Measures.[Net OI EUR - 2006],[Activity].CurrentMember.Parent))*100)
Activity Value - 2006 Value - 2007 Var% Delta Mix(Value2007 - Value2006) * Var%
Act1 100 75 5% -0.125
Act2 50 80 10% 3
Act3 50 80 3% 0.9
Grand Total 200 235 4% 1.4(But i want 3.775 Sum of Act1+Act2+Act3)
How can i achieve this, really urgent...
Regards,
Kaushal
percentage in matrix table
Hello Chris,
I have a data set with the following fileds:
plan
month
leads
orders
I have made the following matrix table: month for column, plan for row, and
leads and orders for the details, and subtotals.
--plan--Jan--Feb--Total
--leads orders-leads orders-leads orders
GOLD --9--8--17--12--26--20
GOLDP --9--7--38--21--47--28
PLATINUM --6--3--8--6--14--9
PLATINUMP --550--343--954--572--1504--915
GrandTotal --574--361--1017--611--1591--972
i want to add another column that calculates the percentage of
total orders / total leads, so it should look like this
--plan--Jan--Feb--Total--% ordered
-- leads orders leads orders leads orders
GOLD --9--8--17--12--26--20--76.92 %
GOLDP --9--7--38--21--47--28--59.57 %
PLATINUM --6--3--8--6--14--9--64.29 %
PLATINUMP --550--343--954--572--1504--915--60.84 %
GrandTotal --574--361--1017--611--1591--972
I have spent a many hours working on it. i ended up adding another table
just for the % ordered column, but it's impossible to align it with the
matrix table.
Is there anyway i can integrate it into the matrix table?
thanks
aaronHi, Roni
It is very strange how you ended with such % calculations they are not
correlate to the numbers (spatially in gold, platinum and so on it is
probably means something),
In any case, you can use calculated field with formula expression in the
same matrix.
Regards, ProJester,
MSN Programmer.
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron|||% calculations are done by dividing total orders/total leads. why is it
strange?
and how do i do "use calculated field with formula expression in the
same matrix"? i can't figure out how to add it.
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||the % is calculated by total orders / total leads. why is it strange?
and how exactly do you do "you can use calculated field with formula
expression in the same matrix"? i can't figure it out.
also, is there anyway to anchor the column heading when they scroll down
like it in ms excel?
thanks
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||Hi, Aaron,
do you figure out how to add that last percent column? If Yes, please let
me know how. Thanks!
Henry
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron
I have a data set with the following fileds:
plan
month
leads
orders
I have made the following matrix table: month for column, plan for row, and
leads and orders for the details, and subtotals.
--plan--Jan--Feb--Total
--leads orders-leads orders-leads orders
GOLD --9--8--17--12--26--20
GOLDP --9--7--38--21--47--28
PLATINUM --6--3--8--6--14--9
PLATINUMP --550--343--954--572--1504--915
GrandTotal --574--361--1017--611--1591--972
i want to add another column that calculates the percentage of
total orders / total leads, so it should look like this
--plan--Jan--Feb--Total--% ordered
-- leads orders leads orders leads orders
GOLD --9--8--17--12--26--20--76.92 %
GOLDP --9--7--38--21--47--28--59.57 %
PLATINUM --6--3--8--6--14--9--64.29 %
PLATINUMP --550--343--954--572--1504--915--60.84 %
GrandTotal --574--361--1017--611--1591--972
I have spent a many hours working on it. i ended up adding another table
just for the % ordered column, but it's impossible to align it with the
matrix table.
Is there anyway i can integrate it into the matrix table?
thanks
aaronHi, Roni
It is very strange how you ended with such % calculations they are not
correlate to the numbers (spatially in gold, platinum and so on it is
probably means something),
In any case, you can use calculated field with formula expression in the
same matrix.
Regards, ProJester,
MSN Programmer.
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron|||% calculations are done by dividing total orders/total leads. why is it
strange?
and how do i do "use calculated field with formula expression in the
same matrix"? i can't figure out how to add it.
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||the % is calculated by total orders / total leads. why is it strange?
and how exactly do you do "you can use calculated field with formula
expression in the same matrix"? i can't figure it out.
also, is there anyway to anchor the column heading when they scroll down
like it in ms excel?
thanks
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||Hi, Aaron,
do you figure out how to add that last percent column? If Yes, please let
me know how. Thanks!
Henry
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron
Subscribe to:
Posts (Atom)