Dear All,
I developed an application in VC++ 6.0 with Crystal Reports 9.0.
Now i want to deploy my application on client machine but i don't now what components (files, dll) of CR are required to register or deploy with my application.
Would some body please help me, its really very urgent.Hi! All!
I'm aslo having the same problem in CR9 Runtime Components in Client machine.
I'm using VB 6 & Crystal Reports 9.
What are all the CR9 componenets or dll files need to be installed in Client machine?
Please if anyone help me to solve this problem.
Thanks.
-Raja|||Visit the next urls -->
http://support.businessobjects.com/communityCS/TechnicalPapers/rdc9_deployment.pdf.asp
http://support.businessobjects.com/documentation/installation_resources/crystal_reports/cr9.asp
Showing posts with label machine. Show all posts
Showing posts with label machine. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
CR10 ActiveX print orientation
Does anyone know how to set the page orientation of the print dialog on the users machine at runtime? Is it possible?
i.e. if you create a CR with landscape layout and the users default print setup is portrait the report will not print correctly.You need to define something called :
crep.PaperOrientation = 2 ; //crDefaultPaperOrientation
Where
crDefaultPaperOrientation= 0
crLandscape= 2
crPortrait = 1
Goto Crystal reports'd developer help and search for PaperOrientation or CRPaperOrientation :
Printer Settings
This application (some demo application) demonstrates how to change the report printer settings at runtime using code.
There are four new report properties that allow you to easily retrieve and set PaperSize, PaperOrientation, PaperSource, and PrinterDuplex for the report printer options. All of these are demonstrated.
There is also a new method called PrinterSetup that provides a Windows standard printer setup window to allow the user to change the printer properties directly at runtime. This is demonstrated as well.
The two methods are independent of each other. For example, changing the Windows standard printer setup will not alter the report printer settings and vice-versa. These new properties and the new method give you much more control over how the report is printed.
Thanks
:wave:
i.e. if you create a CR with landscape layout and the users default print setup is portrait the report will not print correctly.You need to define something called :
crep.PaperOrientation = 2 ; //crDefaultPaperOrientation
Where
crDefaultPaperOrientation= 0
crLandscape= 2
crPortrait = 1
Goto Crystal reports'd developer help and search for PaperOrientation or CRPaperOrientation :
Printer Settings
This application (some demo application) demonstrates how to change the report printer settings at runtime using code.
There are four new report properties that allow you to easily retrieve and set PaperSize, PaperOrientation, PaperSource, and PrinterDuplex for the report printer options. All of these are demonstrated.
There is also a new method called PrinterSetup that provides a Windows standard printer setup window to allow the user to change the printer properties directly at runtime. This is demonstrated as well.
The two methods are independent of each other. For example, changing the Windows standard printer setup will not alter the report printer settings and vice-versa. These new properties and the new method give you much more control over how the report is printed.
Thanks
:wave:
Thursday, March 8, 2012
CPU Usage
Hi All,
I have a question about CPU usage. Actually, I believe that SQL server
isn't using as much as it could!
I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
HyperThreading)
No matter what queries I run, the CPU usage never goes above 25%. In
the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
that's not the issue. The number of processors is the same since I
installed SQL server.
I'm pretty sure that SQL Server is confused about something, as running
updates on a million rows or doing selects on calculated fields never
pushes it above 25%. I'm thinking SQL server for some reason thinks
there's only one CPU. The odd thing in the Windows Task Manager is that
it shows activity (low amounts) on each CPU.
Am I not understanding something, or is this a real problem?
If your COU is not going beyond 25% then you should be happy about it :-)
But you aren't. So, is something running slow? What makes you worry about
your CPU utilization?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?
|||You really should use Perfmon to monitor activity and not task manager but
as Vyas stated this may be fine. It really depends on what your doing and
how. Inserts / Updates and Deletes are always single threaded operations
for the actual data mod part. They may use parallelism for the WHERE clause
but it does depend. Another possibility is that you have bottlenecks else
where. Have you looked at the memory and disk counters in perfmon? If you
have a high disk queue your procs may be waiting on them and thus are not
able to do as much work as you think they should.
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?
|||Some selects could be faster... we're running some selects that take
about 30 seconds (which may be OK, but I want my money's worth from
those CPUs!
). Having a limit that it *NEVER* goes over just seems
suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
and let you know if anything shows up. The discs don't seem to be
thrashing around so I'm not sure if it's a I/O problem, but you never
know...
What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
server is somehow working with only really using 1 of the 4, that seems
like something to look into. I'll check the more obvious stuff first,
though.
Narayana Vyas Kondreddi wrote:
> If your COU is not going beyond 25% then you should be happy about it :-)
> But you aren't. So, is something running slow? What makes you worry about
> your CPU utilization?
|||Following up on the perfmon stuff...
Disk Write Time percentage is about 3% during a insertion run, total
disk time is about 9% during selects. CPU Max is always 25.00 %
BTW, the select run that never goes over 25% is in a stored procedure.
Stored Procedure compilations and recompilations aren't even
registering. The only thing I can see is a issue would be the number of
Lock Requests in SQL server per second. The max I hit is 90,000 when
it's really busy.
Chris Kuske wrote:
[vbcol=seagreen]
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs! ). Having a limit that it *NEVER* goes over just seems
> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
|||Disk percentage is not the best counter to watch. Better to look at avg and
current disk queues. When you say CPU is 25% are you looking at the perfmon
counter for total cpu or theindividual ones? Make sure you have each cpu's
counter individually listed in perfmon so you can see if all processors are
being used or not. What about blocking? Does sp_who2 show anything with a
value other than 0 in the "blocked" column during this operation? Here are
some links that may help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138d387$1@.newsfeed.netlojix.com...[vbcol=seagreen]
> Following up on the perfmon stuff...
> Disk Write Time percentage is about 3% during a insertion run, total
> disk time is about 9% during selects. CPU Max is always 25.00 %
> BTW, the select run that never goes over 25% is in a stored procedure.
> Stored Procedure compilations and recompilations aren't even
> registering. The only thing I can see is a issue would be the number of
> Lock Requests in SQL server per second. The max I hit is 90,000 when
> it's really busy.
> Chris Kuske wrote:
:-)[vbcol=seagreen]
about[vbcol=seagreen]
|||Chris
I would recommend working on the individual query that you feel is not
running fast enough. Here are some pointers on how to work out if the query
is running optimally :
1. Update statistics on the underlying tables.
2. Run the query through Index Tuning Wizard to see if it provides any
index recommendations.
As mentioned by Andrew, its possible that you may have a bottleneck
somewhere else on the system and having fast cpu(s) may not be able to
address that bottleneck.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Chris,
As Narayana mentioned, it can hardly be your goal to max out your CPU's.
Anyway, if you want to check your system, you can run the following
batch. A few seconds after the main SELECT query starts all your CPU's
will max out for 20 seconds or longer (depending on the number of CPU's
and their speed).
set quoted_identifier on
go
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD1.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable1
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD2.Quantity NOT BETWEEN 10 AND 15
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD2.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable2
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD1.Quantity < 40
go
SET SHOWPLAN_TEXT ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET SHOWPLAN_TEXT OFF
go
SET STATISTICS TIME ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET STATISTICS TIME OFF
go
-- On my machine with 2 Hyperthreaded CPU's
-- CPU time = 112471 ms, elapsed time = 31835 ms.
DROP TABLE LargeTable1
DROP TABLE LargeTable2
Hope this helps,
Gert-jan
Chris Kuske wrote:[vbcol=seagreen]
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs! ). Having a limit that it *NEVER* goes over just seems
> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
(Please reply only to the newsgroup)
I have a question about CPU usage. Actually, I believe that SQL server
isn't using as much as it could!
I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
HyperThreading)
No matter what queries I run, the CPU usage never goes above 25%. In
the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
that's not the issue. The number of processors is the same since I
installed SQL server.
I'm pretty sure that SQL Server is confused about something, as running
updates on a million rows or doing selects on calculated fields never
pushes it above 25%. I'm thinking SQL server for some reason thinks
there's only one CPU. The odd thing in the Windows Task Manager is that
it shows activity (low amounts) on each CPU.
Am I not understanding something, or is this a real problem?
If your COU is not going beyond 25% then you should be happy about it :-)
But you aren't. So, is something running slow? What makes you worry about
your CPU utilization?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?
|||You really should use Perfmon to monitor activity and not task manager but
as Vyas stated this may be fine. It really depends on what your doing and
how. Inserts / Updates and Deletes are always single threaded operations
for the actual data mod part. They may use parallelism for the WHERE clause
but it does depend. Another possibility is that you have bottlenecks else
where. Have you looked at the memory and disk counters in perfmon? If you
have a high disk queue your procs may be waiting on them and thus are not
able to do as much work as you think they should.
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?
|||Some selects could be faster... we're running some selects that take
about 30 seconds (which may be OK, but I want my money's worth from
those CPUs!
). Having a limit that it *NEVER* goes over just seemssuspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
and let you know if anything shows up. The discs don't seem to be
thrashing around so I'm not sure if it's a I/O problem, but you never
know...
What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
server is somehow working with only really using 1 of the 4, that seems
like something to look into. I'll check the more obvious stuff first,
though.
Narayana Vyas Kondreddi wrote:
> If your COU is not going beyond 25% then you should be happy about it :-)
> But you aren't. So, is something running slow? What makes you worry about
> your CPU utilization?
|||Following up on the perfmon stuff...
Disk Write Time percentage is about 3% during a insertion run, total
disk time is about 9% during selects. CPU Max is always 25.00 %
BTW, the select run that never goes over 25% is in a stored procedure.
Stored Procedure compilations and recompilations aren't even
registering. The only thing I can see is a issue would be the number of
Lock Requests in SQL server per second. The max I hit is 90,000 when
it's really busy.
Chris Kuske wrote:
[vbcol=seagreen]
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs!
). Having a limit that it *NEVER* goes over just seems> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
|||Disk percentage is not the best counter to watch. Better to look at avg and
current disk queues. When you say CPU is 25% are you looking at the perfmon
counter for total cpu or theindividual ones? Make sure you have each cpu's
counter individually listed in perfmon so you can see if all processors are
being used or not. What about blocking? Does sp_who2 show anything with a
value other than 0 in the "blocked" column during this operation? Here are
some links that may help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138d387$1@.newsfeed.netlojix.com...[vbcol=seagreen]
> Following up on the perfmon stuff...
> Disk Write Time percentage is about 3% during a insertion run, total
> disk time is about 9% during selects. CPU Max is always 25.00 %
> BTW, the select run that never goes over 25% is in a stored procedure.
> Stored Procedure compilations and recompilations aren't even
> registering. The only thing I can see is a issue would be the number of
> Lock Requests in SQL server per second. The max I hit is 90,000 when
> it's really busy.
> Chris Kuske wrote:
:-)[vbcol=seagreen]
about[vbcol=seagreen]
|||Chris
I would recommend working on the individual query that you feel is not
running fast enough. Here are some pointers on how to work out if the query
is running optimally :
1. Update statistics on the underlying tables.
2. Run the query through Index Tuning Wizard to see if it provides any
index recommendations.
As mentioned by Andrew, its possible that you may have a bottleneck
somewhere else on the system and having fast cpu(s) may not be able to
address that bottleneck.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Chris,
As Narayana mentioned, it can hardly be your goal to max out your CPU's.
Anyway, if you want to check your system, you can run the following
batch. A few seconds after the main SELECT query starts all your CPU's
will max out for 20 seconds or longer (depending on the number of CPU's
and their speed).
set quoted_identifier on
go
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD1.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable1
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD2.Quantity NOT BETWEEN 10 AND 15
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD2.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable2
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD1.Quantity < 40
go
SET SHOWPLAN_TEXT ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET SHOWPLAN_TEXT OFF
go
SET STATISTICS TIME ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET STATISTICS TIME OFF
go
-- On my machine with 2 Hyperthreaded CPU's
-- CPU time = 112471 ms, elapsed time = 31835 ms.
DROP TABLE LargeTable1
DROP TABLE LargeTable2
Hope this helps,
Gert-jan
Chris Kuske wrote:[vbcol=seagreen]
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs!
). Having a limit that it *NEVER* goes over just seems> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
(Please reply only to the newsgroup)
Wednesday, March 7, 2012
CPU Usage
Hi All,
I have a question about CPU usage. Actually, I believe that SQL server
isn't using as much as it could!
I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
HyperThreading)
No matter what queries I run, the CPU usage never goes above 25%. In
the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
that's not the issue. The number of processors is the same since I
installed SQL server.
I'm pretty sure that SQL Server is confused about something, as running
updates on a million rows or doing selects on calculated fields never
pushes it above 25%. I'm thinking SQL server for some reason thinks
there's only one CPU. The odd thing in the Windows Task Manager is that
it shows activity (low amounts) on each CPU.
Am I not understanding something, or is this a real problem?If your COU is not going beyond 25% then you should be happy about it :-)
But you aren't. So, is something running slow? What makes you worry about
your CPU utilization?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?|||You really should use Perfmon to monitor activity and not task manager but
as Vyas stated this may be fine. It really depends on what your doing and
how. Inserts / Updates and Deletes are always single threaded operations
for the actual data mod part. They may use parallelism for the WHERE clause
but it does depend. Another possibility is that you have bottlenecks else
where. Have you looked at the memory and disk counters in perfmon? If you
have a high disk queue your procs may be waiting on them and thus are not
able to do as much work as you think they should.
--
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?|||Some selects could be faster... we're running some selects that take
about 30 seconds (which may be OK, but I want my money's worth from
those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
and let you know if anything shows up. The discs don't seem to be
thrashing around so I'm not sure if it's a I/O problem, but you never
know...
What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
server is somehow working with only really using 1 of the 4, that seems
like something to look into. I'll check the more obvious stuff first,
though.
Narayana Vyas Kondreddi wrote:
> If your COU is not going beyond 25% then you should be happy about it :-)
> But you aren't. So, is something running slow? What makes you worry about
> your CPU utilization?|||Following up on the perfmon stuff...
Disk Write Time percentage is about 3% during a insertion run, total
disk time is about 9% during selects. CPU Max is always 25.00 %
BTW, the select run that never goes over 25% is in a stored procedure.
Stored Procedure compilations and recompilations aren't even
registering. The only thing I can see is a issue would be the number of
Lock Requests in SQL server per second. The max I hit is 90,000 when
it's really busy.
Chris Kuske wrote:
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
>> If your COU is not going beyond 25% then you should be happy about it :-)
>> But you aren't. So, is something running slow? What makes you worry about
>> your CPU utilization?|||Disk percentage is not the best counter to watch. Better to look at avg and
current disk queues. When you say CPU is 25% are you looking at the perfmon
counter for total cpu or theindividual ones? Make sure you have each cpu's
counter individually listed in perfmon so you can see if all processors are
being used or not. What about blocking? Does sp_who2 show anything with a
value other than 0 in the "blocked" column during this operation? Here are
some links that may help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138d387$1@.newsfeed.netlojix.com...
> Following up on the perfmon stuff...
> Disk Write Time percentage is about 3% during a insertion run, total
> disk time is about 9% during selects. CPU Max is always 25.00 %
> BTW, the select run that never goes over 25% is in a stored procedure.
> Stored Procedure compilations and recompilations aren't even
> registering. The only thing I can see is a issue would be the number of
> Lock Requests in SQL server per second. The max I hit is 90,000 when
> it's really busy.
> Chris Kuske wrote:
> > Some selects could be faster... we're running some selects that take
> > about 30 seconds (which may be OK, but I want my money's worth from
> > those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
> > suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> > and let you know if anything shows up. The discs don't seem to be
> > thrashing around so I'm not sure if it's a I/O problem, but you never
> > know...
> >
> > What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> > server is somehow working with only really using 1 of the 4, that seems
> > like something to look into. I'll check the more obvious stuff first,
> > though.
> >
> > Narayana Vyas Kondreddi wrote:
> >
> >> If your COU is not going beyond 25% then you should be happy about it
:-)
> >> But you aren't. So, is something running slow? What makes you worry
about
> >> your CPU utilization?|||Chris
I would recommend working on the individual query that you feel is not
running fast enough. Here are some pointers on how to work out if the query
is running optimally :
1. Update statistics on the underlying tables.
2. Run the query through Index Tuning Wizard to see if it provides any
index recommendations.
As mentioned by Andrew, its possible that you may have a bottleneck
somewhere else on the system and having fast cpu(s) may not be able to
address that bottleneck.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Chris,
As Narayana mentioned, it can hardly be your goal to max out your CPU's.
Anyway, if you want to check your system, you can run the following
batch. A few seconds after the main SELECT query starts all your CPU's
will max out for 20 seconds or longer (depending on the number of CPU's
and their speed).
set quoted_identifier on
go
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD1.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable1
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD2.Quantity NOT BETWEEN 10 AND 15
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD2.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable2
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD1.Quantity < 40
go
SET SHOWPLAN_TEXT ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET SHOWPLAN_TEXT OFF
go
SET STATISTICS TIME ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET STATISTICS TIME OFF
go
-- On my machine with 2 Hyperthreaded CPU's
-- CPU time = 112471 ms, elapsed time = 31835 ms.
DROP TABLE LargeTable1
DROP TABLE LargeTable2
Hope this helps,
Gert-jan
Chris Kuske wrote:
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
> > If your COU is not going beyond 25% then you should be happy about it :-)
> > But you aren't. So, is something running slow? What makes you worry about
> > your CPU utilization?
--
(Please reply only to the newsgroup)
I have a question about CPU usage. Actually, I believe that SQL server
isn't using as much as it could!
I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
HyperThreading)
No matter what queries I run, the CPU usage never goes above 25%. In
the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
that's not the issue. The number of processors is the same since I
installed SQL server.
I'm pretty sure that SQL Server is confused about something, as running
updates on a million rows or doing selects on calculated fields never
pushes it above 25%. I'm thinking SQL server for some reason thinks
there's only one CPU. The odd thing in the Windows Task Manager is that
it shows activity (low amounts) on each CPU.
Am I not understanding something, or is this a real problem?If your COU is not going beyond 25% then you should be happy about it :-)
But you aren't. So, is something running slow? What makes you worry about
your CPU utilization?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?|||You really should use Perfmon to monitor activity and not task manager but
as Vyas stated this may be fine. It really depends on what your doing and
how. Inserts / Updates and Deletes are always single threaded operations
for the actual data mod part. They may use parallelism for the WHERE clause
but it does depend. Another possibility is that you have bottlenecks else
where. Have you looked at the memory and disk counters in perfmon? If you
have a high disk queue your procs may be waiting on them and thus are not
able to do as much work as you think they should.
--
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138a5f6$1@.newsfeed.netlojix.com...
> Hi All,
> I have a question about CPU usage. Actually, I believe that SQL server
> isn't using as much as it could!
> I have dual Xeon machine (shows up as 4 CPUs to SQL server due to
> HyperThreading)
> No matter what queries I run, the CPU usage never goes above 25%. In
> the Enterprise Mangager, it's set to use CPUs 0 through 3, so I know
> that's not the issue. The number of processors is the same since I
> installed SQL server.
> I'm pretty sure that SQL Server is confused about something, as running
> updates on a million rows or doing selects on calculated fields never
> pushes it above 25%. I'm thinking SQL server for some reason thinks
> there's only one CPU. The odd thing in the Windows Task Manager is that
> it shows activity (low amounts) on each CPU.
> Am I not understanding something, or is this a real problem?|||Some selects could be faster... we're running some selects that take
about 30 seconds (which may be OK, but I want my money's worth from
those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
and let you know if anything shows up. The discs don't seem to be
thrashing around so I'm not sure if it's a I/O problem, but you never
know...
What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
server is somehow working with only really using 1 of the 4, that seems
like something to look into. I'll check the more obvious stuff first,
though.
Narayana Vyas Kondreddi wrote:
> If your COU is not going beyond 25% then you should be happy about it :-)
> But you aren't. So, is something running slow? What makes you worry about
> your CPU utilization?|||Following up on the perfmon stuff...
Disk Write Time percentage is about 3% during a insertion run, total
disk time is about 9% during selects. CPU Max is always 25.00 %
BTW, the select run that never goes over 25% is in a stored procedure.
Stored Procedure compilations and recompilations aren't even
registering. The only thing I can see is a issue would be the number of
Lock Requests in SQL server per second. The max I hit is 90,000 when
it's really busy.
Chris Kuske wrote:
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
>> If your COU is not going beyond 25% then you should be happy about it :-)
>> But you aren't. So, is something running slow? What makes you worry about
>> your CPU utilization?|||Disk percentage is not the best counter to watch. Better to look at avg and
current disk queues. When you say CPU is 25% are you looking at the perfmon
counter for total cpu or theindividual ones? Make sure you have each cpu's
counter individually listed in perfmon so you can see if all processors are
being used or not. What about blocking? Does sp_who2 show anything with a
value other than 0 in the "blocked" column during this operation? Here are
some links that may help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Chris Kuske" <chris@.solidoak.com> wrote in message
news:4138d387$1@.newsfeed.netlojix.com...
> Following up on the perfmon stuff...
> Disk Write Time percentage is about 3% during a insertion run, total
> disk time is about 9% during selects. CPU Max is always 25.00 %
> BTW, the select run that never goes over 25% is in a stored procedure.
> Stored Procedure compilations and recompilations aren't even
> registering. The only thing I can see is a issue would be the number of
> Lock Requests in SQL server per second. The max I hit is 90,000 when
> it's really busy.
> Chris Kuske wrote:
> > Some selects could be faster... we're running some selects that take
> > about 30 seconds (which may be OK, but I want my money's worth from
> > those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
> > suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> > and let you know if anything shows up. The discs don't seem to be
> > thrashing around so I'm not sure if it's a I/O problem, but you never
> > know...
> >
> > What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> > server is somehow working with only really using 1 of the 4, that seems
> > like something to look into. I'll check the more obvious stuff first,
> > though.
> >
> > Narayana Vyas Kondreddi wrote:
> >
> >> If your COU is not going beyond 25% then you should be happy about it
:-)
> >> But you aren't. So, is something running slow? What makes you worry
about
> >> your CPU utilization?|||Chris
I would recommend working on the individual query that you feel is not
running fast enough. Here are some pointers on how to work out if the query
is running optimally :
1. Update statistics on the underlying tables.
2. Run the query through Index Tuning Wizard to see if it provides any
index recommendations.
As mentioned by Andrew, its possible that you may have a bottleneck
somewhere else on the system and having fast cpu(s) may not be able to
address that bottleneck.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Chris,
As Narayana mentioned, it can hardly be your goal to max out your CPU's.
Anyway, if you want to check your system, you can run the following
batch. A few seconds after the main SELECT query starts all your CPU's
will max out for 20 seconds or longer (depending on the number of CPU's
and their speed).
set quoted_identifier on
go
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD1.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable1
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD2.Quantity NOT BETWEEN 10 AND 15
SELECT TOP 100000 OD1.ProductID+OD2.ProductID+OD2.Quantity AS
ProductID,REPLICATE('a',200) as Filler
INTO LargeTable2
FROM northwind.dbo."order details" OD1
CROSS JOIN northwind.dbo."order details" OD2
WHERE OD1.Quantity < 40
go
SET SHOWPLAN_TEXT ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET SHOWPLAN_TEXT OFF
go
SET STATISTICS TIME ON
go
SELECT LT1.ProductID,COUNT(*)
FROM LargeTable1 LT1
INNER JOIN LargeTable2 LT2
ON LT1.ProductID=LT2.ProductID
GROUP BY LT1.ProductID
go
SET STATISTICS TIME OFF
go
-- On my machine with 2 Hyperthreaded CPU's
-- CPU time = 112471 ms, elapsed time = 31835 ms.
DROP TABLE LargeTable1
DROP TABLE LargeTable2
Hope this helps,
Gert-jan
Chris Kuske wrote:
> Some selects could be faster... we're running some selects that take
> about 30 seconds (which may be OK, but I want my money's worth from
> those CPUs! :) ). Having a limit that it *NEVER* goes over just seems
> suspicious to me. I'll monitor Perfmon for awhile as Andrew suggested,
> and let you know if anything shows up. The discs don't seem to be
> thrashing around so I'm not sure if it's a I/O problem, but you never
> know...
> What I keep thinking is that (100% one 1 cpu / 4 cpus) = 25%. If SQL
> server is somehow working with only really using 1 of the 4, that seems
> like something to look into. I'll check the more obvious stuff first,
> though.
> Narayana Vyas Kondreddi wrote:
> > If your COU is not going beyond 25% then you should be happy about it :-)
> > But you aren't. So, is something running slow? What makes you worry about
> > your CPU utilization?
--
(Please reply only to the newsgroup)
CPU Distribution between Sql Server and OS
Hi,
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and database
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.
Hi
"Krish" wrote:
> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
> and the senario is
> the dataserver has to function for some big computations and data processing.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and database
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and database
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.
Hi
"Krish" wrote:
> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
> and the senario is
> the dataserver has to function for some big computations and data processing.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and database
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John
CPU Distribution between Sql Server and OS
Hi,
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing
.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and databas
e
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.Hi
"Krish" wrote:
> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU
's
> and the senario is
> the dataserver has to function for some big computations and data processi
ng.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and datab
ase
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS (
OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing
.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and databas
e
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.Hi
"Krish" wrote:
> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU
's
> and the senario is
> the dataserver has to function for some big computations and data processi
ng.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and datab
ase
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS (
OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John
CPU Distribution between Sql Server and OS
Hi,
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and database
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.Hi
"Krish" wrote:
> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
> and the senario is
> the dataserver has to function for some big computations and data processing.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and database
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and database
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.Hi
"Krish" wrote:
> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
> and the senario is
> the dataserver has to function for some big computations and data processing.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and database
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John
Saturday, February 25, 2012
CPU dedicated to one Stored Procedure
Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
CPU 100%
Hi,
I have sql server on a web server.
The machine grinds almost to a halt at peak times, as the asp pages are
causing sql server to use most of the cpu.
Looking at the graph it is at 100% for about 20 seconds at a time, then
drops, then goes back up.
Is there anything I can do to make sql server use less of the cpu? (Without
causing the asp pages to hand.)
Thanks
JJJJ (nospam) writes:
> I have sql server on a web server.
> The machine grinds almost to a halt at peak times, as the asp pages are
> causing sql server to use most of the cpu.
> Looking at the graph it is at 100% for about 20 seconds at a time, then
> drops, then goes back up.
> Is there anything I can do to make sql server use less of the cpu?
Probably. But exactly what I cannot tell. I can however give some
suggestions of what you can do to track down the problem. The best
approach is to use the Profiler. With this tool you can track commands
send to SQL Server, and you can get information about them. Assuming
that the source of the problem is a resource-intensive query, you
can use the Profiler to narrow down which query/ies it may be. Once
you have located one or more queries, you can look into how to improve
them, for instance by adding indexes.
Depending on your budget, you may also consider putting SQL Server on a
separate box, so that the web server and SQL Server does not have to
compete for resources.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
Many Thanks for the reply.
I am on the verge of investing in a new server anyway, as it seems easier to
throw more hardware at it, although I appreciate this is not necessarily the
best solution.
However, if I can get sql server on a separate box, hopefully my problems
will be solved!
JJ
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94ECF2D31D7D6Yazorman@.127.0.0.1...
> JJ (nospam) writes:
> > I have sql server on a web server.
> > The machine grinds almost to a halt at peak times, as the asp pages are
> > causing sql server to use most of the cpu.
> > Looking at the graph it is at 100% for about 20 seconds at a time, then
> > drops, then goes back up.
> > Is there anything I can do to make sql server use less of the cpu?
> Probably. But exactly what I cannot tell. I can however give some
> suggestions of what you can do to track down the problem. The best
> approach is to use the Profiler. With this tool you can track commands
> send to SQL Server, and you can get information about them. Assuming
> that the source of the problem is a resource-intensive query, you
> can use the Profiler to narrow down which query/ies it may be. Once
> you have located one or more queries, you can look into how to improve
> them, for instance by adding indexes.
> Depending on your budget, you may also consider putting SQL Server on a
> separate box, so that the web server and SQL Server does not have to
> compete for resources.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JJ (nospam) writes:
> I am on the verge of investing in a new server anyway, as it seems
> easier to throw more hardware at it, although I appreciate this is not
> necessarily the best solution.
Hey, if a new server costs 30000 crowns (a modest and cheap one), and
a consultant costs 1000 crowns an hour, and it takes him 40 hours to
track down the problem and fix it, throwing hardware on it may even
be a better solution.
Although judging from your description it sounds like you will still
have performance problems even with a new box, but hopefully less
severe - and maybe you can live with them.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Friday, February 24, 2012
Couple of questions = )
1.
Does anyone know the max number of Databases a single MSSQL 2000 enterprise machine can handle ??
2.
Does anyone have an idea on how you could use the DTS in MSSQL server to kick a script off on a UNIX machine ??
Chris1. 32,767
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
Does anyone know the max number of Databases a single MSSQL 2000 enterprise machine can handle ??
2.
Does anyone have an idea on how you could use the DTS in MSSQL server to kick a script off on a UNIX machine ??
Chris1. 32,767
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
Subscribe to:
Posts (Atom)