Performance Related Question

I am doing a performance counter for the CPU usage and
recording the output to a log file.
What i want to know is a way where I can say that the max
CPU usage say 80% occured for x amount of time, the
perfmon does not tell me how long was the maximum time
was for.
so i want something like CPU usage max value 80% was for
9 seconds.
Any ideasapok
In the Performance Monitor you can set up a range of intervals , i mean from
9am to 11am and see how CPU behaved since.
Also, If I rememeber well there are some classes in .NET framework you can
use to get the info from PM.
"apok" <anonymous@.discussions.microsoft.com> wrote in message
news:15aa01c535b7$e327f0a0$a501280a@.phx.gbl...
> I am doing a performance counter for the CPU usage and
> recording the output to a log file.
> What i want to know is a way where I can say that the max
> CPU usage say 80% occured for x amount of time, the
> perfmon does not tell me how long was the maximum time
> was for.
> so i want something like CPU usage max value 80% was for
> 9 seconds.
> Any ideas

Performance Related Question

I am doing a performance counter for the CPU usage and
recording the output to a log file.
What i want to know is a way where I can say that the max
CPU usage say 80% occured for x amount of time, the
perfmon does not tell me how long was the maximum time
was for.
so i want something like CPU usage max value 80% was for
9 seconds.
Any ideasapok
In the Performance Monitor you can set up a range of intervals , i mean from
9am to 11am and see how CPU behaved since.
Also, If I rememeber well there are some classes in .NET framework you can
use to get the info from PM.
"apok" <anonymous@.discussions.microsoft.com> wrote in message
news:15aa01c535b7$e327f0a0$a501280a@.phx.gbl...
> I am doing a performance counter for the CPU usage and
> recording the output to a log file.
> What i want to know is a way where I can say that the max
> CPU usage say 80% occured for x amount of time, the
> perfmon does not tell me how long was the maximum time
> was for.
> so i want something like CPU usage max value 80% was for
> 9 seconds.
> Any ideassql

Performance Related Question

I am doing a performance counter for the CPU usage and
recording the output to a log file.
What i want to know is a way where I can say that the max
CPU usage say 80% occured for x amount of time, the
perfmon does not tell me how long was the maximum time
was for.
so i want something like CPU usage max value 80% was for
9 seconds.
Any ideas
apok
In the Performance Monitor you can set up a range of intervals , i mean from
9am to 11am and see how CPU behaved since.
Also, If I rememeber well there are some classes in .NET framework you can
use to get the info from PM.
"apok" <anonymous@.discussions.microsoft.com> wrote in message
news:15aa01c535b7$e327f0a0$a501280a@.phx.gbl...
> I am doing a performance counter for the CPU usage and
> recording the output to a log file.
> What i want to know is a way where I can say that the max
> CPU usage say 80% occured for x amount of time, the
> perfmon does not tell me how long was the maximum time
> was for.
> so i want something like CPU usage max value 80% was for
> 9 seconds.
> Any ideas

Performance Recommendations for SQL Server - Paging Related

I just became in charge of a server running SQL Server 2000 Enterprise on a
Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB RAM
of the 32 GB RAM in use. The database on the server is 120GB. Approximately
60GB is actual data, 49GB of indexes and the rest is empty space. For
reference, this database is for our ClarifyCRM application.
I am starting to do performance monitoring and I am seeing a high amount of
system paging - 1,000-2000 or more pages/sec.
Even though RAM utilization is low with plenty to spare, I see that the
paging file on the server is only 2GB in size (40.8 MB of it is actually in
use). Other than increasing the size of the paging file (which I will be
doing soon) while is extremely small, are there any other recommendations to
decrease the amount of system paging that is occurring?What are the memory settings set to, especially the MAX Memory one? It
sounds odd that only 17.8 out of 32GB is being used. I assume this is 32 bit
so with AWE the memory should not be dynamic and I would expect it to be
much higher. What other apps are running on this server?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
of the actual memory you can get at.
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
> What are the memory settings set to, especially the MAX Memory one? It
> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
> bit so with AWE the memory should not be dynamic and I would expect it to
> be much higher. What other apps are running on this server?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
>> RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other recommendations
>> to
>> decrease the amount of system paging that is occurring?
>|||You may not actually have a problem.
Microsoft has an excellent (and short) article on paging and monitorint it,
I suggect you spend a few minutes reading it.
http://support.microsoft.com/kb/555223
Good luck,
Jay
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||Yes Tony probably hit the nail on the head here. You are using SQL2000 so
you most likely have SP4 without the Hotfix and it will only use approx half
the memory.
http://support.microsoft.com/kb/899761
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>of the actual memory you can get at.
> Tony.
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson
> [Ramblings from the field from a SQL consultant]
> http://sqlserverfaq.com
> [UK SQL User Community]
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it to
>> be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>|||Thats the one.
Thanks Andrew - I was being lazy, well, time constrained :(
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OhLBUbbDIHA.5976@.TK2MSFTNGP02.phx.gbl...
> Yes Tony probably hit the nail on the head here. You are using SQL2000 so
> you most likely have SP4 without the Hotfix and it will only use approx
> half the memory.
> http://support.microsoft.com/kb/899761
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
> news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>>of the actual memory you can get at.
>> Tony.
>> --
>> Tony Rogerson, SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson
>> [Ramblings from the field from a SQL consultant]
>> http://sqlserverfaq.com
>> [UK SQL User Community]
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it
>> to be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise
>>on a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high
>> amount of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is
>> actually in
>> use). Other than increasing the size of the paging file (which I will
>> be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>>
>

Performance Problems On Recursive Table

I have a table that has a parent-child relation to itself. (see SQL at end)
Basically, records of type 0 are related to type 1, and type 1 to type 2.
This table has about 2 million records right now. When I do this:
INNER JOIN
ExpSrvLog I ON E.ExpSrvID = I.ExpSrvID AND I.ItemType = 2
LEFT OUTER JOIN
ExpSrvLog B ON I.LogID = B.ParentID AND B.ItemType = 1
It seems to consume an inordinate amount of time. Any way to optimize this
behavior? Thanks.
CREATE TABLE [ExpSrvLog] (
[LogID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExpSrvID] [int] NOT NULL ,
[CalendarDate] [smalldatetime] NOT NULL ,
[ItemType] [tinyint] NOT NULL ,
[VendorInvoiceID] [int] NULL ,
[ClientInvoiceID] [int] NULL ,
[PaymentID] [int] NULL ,
[BillingAmount] [money] NULL ,
[ActualQty] [decimal](8, 2) NULL ,
[ActualRate] [money] NULL ,
[ParentID] [bigint] NULL ,
CONSTRAINT [PK_ExpSrvLog] PRIMARY KEY CLUSTERED
(
[LogID] DESC
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_ExpSrvLog_ClientInvoice] FOREIGN KEY
(
[ClientInvoiceID]
) REFERENCES [ClientInvoice] (
[InvoiceID]
),
CONSTRAINT [FK_ExpSrvLog_ExpSrvLog] FOREIGN KEY
(
[ParentID]
) REFERENCES [ExpSrvLog] (
[LogID]
),
CONSTRAINT [FK_ExpSrvLog_VendorInvoice] FOREIGN KEY
(
[VendorInvoiceID]
) REFERENCES [VendorInvoice] (
[InvoiceID]
)
) ON [PRIMARY]
ENDApart from the primary key, are there any other indexes on that table?
Foreign key columns should at least be indexed.
ML|||"ML" wrote:
> Apart from the primary key, are there any other indexes on that table?
Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FKs.
)
I've also tried an index over ParentID and LogID, to no help.
- alphadog|||Paul, While recursive joins are elegant, they are notoriously slow on
recursive tables with > 1 million rows. You might want try to use mutiples
queries and(or) tables to accomplish your task instead of the recursive join
s.
"Paul Tiseo" wrote:

> "ML" wrote:
> Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
> VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FK
s.)
> I've also tried an index over ParentID and LogID, to no help.
> - alphadog|||Really? Damn. It works so well that way. Oh well, thanks for the info, Frank
.
So, what are all my options? Physically splitting the table is something I
have planned, but at this point in developement is not an option. I guess
it'll have to be temp tables.
"frank chang" wrote:
> Paul, While recursive joins are elegant, they are notoriously slow on
> recursive tables with > 1 million rows. You might want try to use mutiples
> queries and(or) tables to accomplish your task instead of the recursive jo
ins.
> "Paul Tiseo" wrote:
>|||Maybe this example can help you find a way to navigate the
ancestor/descendant axes in your hierarchy:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||>> I have a table that has a parent-child relation to itself. (see SQL at end) Basical
ly, records [sic] of type 0 are related to type 1, and type 1 to type 2. This table h
as about 2 million records [sic] right now. When I do this: <<
No relational key, IDENTITY, too many NULL-able columns, money and
bigint proprierary datatypes and you don't know that rows and records
are not the same. .And there are not specs.
behavior? <<
Change the DDL. Does this hierarchy only go down three levels? Can
level 0 have more than one level 1 subordinate? Can a level 1 have
more than one level 2 subordinate? If I assume not, then:
CREATE TABLE ExpsrvLog
(expsrv_id INTEGER NOT NULL,
hierarchy_level INTEGER DEFAULT 0 NOT NULL
CHECK (hierarchy_level IN (0, 1, 2)),
payment_date DATETIME NOT NULL,
vendor_invoice_nbr INTEGER NOT NULL,
REFERENCES VendorInvoices (invoice_nbr)
ON UPDATE CASCADE,
client_invoice_nbr INTEGER NOT NULL
REFERENCES ClientInvoices (invoice_nbr)
ON UPDATE CASCADE,
payment_nbr INTEGER NOT NULL,
billing_amount DECIMAL(8,2) NOT NULL,
actual_qty DECIMAL(8,2) NOT NULL,
actual_rate DECIMAL(8,2) NULL,
PRIMARY KEY (expsrv_id, hierarchy_level));
If the asumption was wrong, we can move onto the nested sets model.
You might want to get a copy of TREES & HIERARCHIES IN SQL along with a
basic data modeling book.|||"--CELKO--" wrote:
> No relational key,
Of what type? I have a primary key (see first post), an alternate key (not
in the original DDL, but basically CREATE UNIQUE INDEX AK_ExpSrvLog ON
dbo.ExpSrvLog([ExpSrvID], [CalendarDate] DESC , [ItemType]) ) and some
primary-foreign key relationships. What exactly is missing? Do you mean a
natural key? If so, it's the AK that I did not include.

> IDENTITY
Why? How does IDENTITY affect my performance question?

> too many NULL-able columns,
The Real World intrudes into the World of Relational Model Puritans
sometimes. <shrug> Again, how do the NULLable columns affect my performance
problem?

> money and bigint proprierary datatypes
Not a problem for this project.

> and you don't know that rows and records are not the same.
Do you know what they say about "ASS-U-ME"? I use common idioms. Forgive me
for not abiding by your stricter one.

> And there are not specs.
I'm sorry, but I don't understand this comment. What specs do you need?

> Change the DDL. Does this hierarchy only go down three levels? Can
> level 0 have more than one level 1 subordinate? Can a level 1 have
> more than one level 2 subordinate? If I assume not, then:
Yes (three and only three levels), yes and yes.

> CREATE TABLE ExpsrvLog
> (expsrv_id INTEGER NOT NULL,
> hierarchy_level INTEGER DEFAULT 0 NOT NULL
> CHECK (hierarchy_level IN (0, 1, 2)),
> payment_date DATETIME NOT NULL,
> vendor_invoice_nbr INTEGER NOT NULL,
> REFERENCES VendorInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> client_invoice_nbr INTEGER NOT NULL
> REFERENCES ClientInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> payment_nbr INTEGER NOT NULL,
> billing_amount DECIMAL(8,2) NOT NULL,
> actual_qty DECIMAL(8,2) NOT NULL,
> actual_rate DECIMAL(8,2) NULL,
> PRIMARY KEY (expsrv_id, hierarchy_level));
> If the asumption was wrong, we can move onto the nested sets model.
So, you changed the structure of the table in a way that doesn't address my
original problem (in fact you seem to have removed the recursive
relationship) but makes it more purist-friendly in terms of datatypes and
nullability. Your "SQL standardization" and "Relational Model" efforts are
appreciated (although your zeal blinded you to my actual question) but it's
not a solution to my immediate problem.
I don't need to know *how* to model or query the hierarchy. I need to know
*why* it isn't performing properly when I use the self-join.
Thanks.sql

Performance problem... again :(

Hi there.
This is not my 1st post related with performance. I am experiencing serious
performance problems with a VB6 application running over a SQL Server 2000
database. I have already used SQL Analyser, SQL Profiler and Performance
Monitor to collect data that might help resolve this problem. However, my
dificulty is to analyze this data. Is there any tool that it helps to it? I
have downloaded from Microsofts site a tool that collects, at the same time,
data from Profiler, Perfmon and SQL Analyser. However, I have the same
problem... I can't interprete it. This tool is called PSSDIAG. There must be
some powerful tools that analyse the database and provides some hypothetical
solutions. Tools that tell us what is wrong. I do not know none . I would
appreciate some help... some 'freeware' help .
Thanks in advance.
Regards,
Marco
On Thu, 31 Mar 2005 19:42:50 +0100, Marco Pais wrote:

>Hi there.
>This is not my 1st post related with performance.
(snip)
Hi Marco,
I've seen your posts, but I'm afraid I can't give much advise - except
the advice to post in another group.
This group (microsoft.public.sqlserver.mseq) is devoted to Microsoft
English Query, an add-on to SQL Server. Your questions would probably
fit better in either microsoft.public.sqlserver.programming (for writing
and tuning Transact-SQL queries, stored procedures, triggers, etc) or in
microsoft.public.sqlserver.server (for general questions related to SQL
Server - I guess your current questions would fit best in that group).
Oh, and for your first question (hundred connections from VB frontend
application), you might wish to search for VB groups as well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Performance problem...

Hi there.
This is not my 1st post related with performance. I am experiencing serious
performance problems with a VB6 application running over a SQL Server 2000
database. I have already used SQL Analyser, SQL Profiler and Performance
Monitor to collect data that might help resolve this problem. However, my
dificulty is to analyze this data. Is there any tool that it helps to it? I
have downloaded from Microsofts site a tool that collects, at the same time,
data from Profiler, Perfmon and SQL Analyser. However, I have the same
problem... I can't interprete it. This tool is called PSSDIAG. There must be
some powerful tools that analyse the database and provides some hypothetical
solutions. Tools that tell us what is wrong. I do not know none . I would
appreciate some help... some 'freeware' help .
Thanks in advance.
Regards,
Marco
There are some tools, but they're not really better than what you can do
yourself.
A) Get to know your application. Review the architecture. Have someone
else review the architecture. Most performance problems are the result of
poorly thought-out architecture (for instance, a part of the app that needs
to call back to the database 100 or 1000 times to fill a page, instead of
doing just a single query to get all of the data at once).
B) If you determine it's a database problem, start by profiling for
SQL:BatchCompleted and RPC:Completed events and look at the duration column.
Which stored procedures are taking the longest? Those are your easiest
targets. Start tuning them. Look at the execution plans, look at what
indexes exist, and start making things better.
If you need help with that second step, start by reading everything at
http://www.sql-server-performance.com .
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
> Hi there.
> This is not my 1st post related with performance. I am experiencing
serious
> performance problems with a VB6 application running over a SQL Server 2000
> database. I have already used SQL Analyser, SQL Profiler and Performance
> Monitor to collect data that might help resolve this problem. However, my
> dificulty is to analyze this data. Is there any tool that it helps to it?
I
> have downloaded from Microsofts site a tool that collects, at the same
time,
> data from Profiler, Perfmon and SQL Analyser. However, I have the same
> problem... I can't interprete it. This tool is called PSSDIAG. There must
be
> some powerful tools that analyse the database and provides some
hypothetical
> solutions. Tools that tell us what is wrong. I do not know none . I
would
> appreciate some help... some 'freeware' help .
> Thanks in advance.
> Regards,
> Marco
>
|||There are so many factors involved with performance tuning... everything
from optimizing your queries to hardware to indexes to statistics... Do you
have more specific information about your problem?
Google "Performance Monitor SQL Server" and you'll find pages devoted to
interpreting commonly used counters. You can also use QA to view graphic
representations of your estimated query execution plans. Check them out -
if performance used to be good, and just recently became an issue it might
be as simple as missing or out of date statistics...
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
> Hi there.
> This is not my 1st post related with performance. I am experiencing
> serious performance problems with a VB6 application running over a SQL
> Server 2000 database. I have already used SQL Analyser, SQL Profiler and
> Performance Monitor to collect data that might help resolve this problem.
> However, my dificulty is to analyze this data. Is there any tool that it
> helps to it? I have downloaded from Microsofts site a tool that collects,
> at the same time, data from Profiler, Perfmon and SQL Analyser. However, I
> have the same problem... I can't interprete it. This tool is called
> PSSDIAG. There must be some powerful tools that analyse the database and
> provides some hypothetical solutions. Tools that tell us what is wrong. I
> do not know none . I would appreciate some help... some 'freeware' help
> .
> Thanks in advance.
> Regards,
> Marco
>
|||I can get a good conclusion... it is not a hardware problem. I have already
made many changings to the hardware structure. It can't get much better. I
have a powerful server and very good workstations. The problem must be in
application design and/or database architecture. The application has also
suffered many changes. I have created many SPs instead using dynamic SQL
queries inlaid in aplication.
The last analysis I made tells me that the aplication works fine (or
reasonable) when 3/4 users are connected. In hard periods (10-15 users
connected), the aplication gets very slow. I am realist... 10, 15, 20 users
connected should not be a problem. The database is 1 GB large. I am very
concerned. In some years, or even months, it will get worst... and I do not
know what else must I do...
"Michael C#" <xyz@.abcdef.com> wrote in message
news:NS23e.18750$nE1.5330@.fe09.lga...
> There are so many factors involved with performance tuning... everything
> from optimizing your queries to hardware to indexes to statistics... Do
> you have more specific information about your problem?
> Google "Performance Monitor SQL Server" and you'll find pages devoted to
> interpreting commonly used counters. You can also use QA to view graphic
> representations of your estimated query execution plans. Check them out -
> if performance used to be good, and just recently became an issue it might
> be as simple as missing or out of date statistics...
> "Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
> news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
>
|||Just one thing more... how can I work 'missing or out of date statistics'?
"Michael C#" <xyz@.abcdef.com> wrote in message
news:NS23e.18750$nE1.5330@.fe09.lga...
> There are so many factors involved with performance tuning... everything
> from optimizing your queries to hardware to indexes to statistics... Do
> you have more specific information about your problem?
> Google "Performance Monitor SQL Server" and you'll find pages devoted to
> interpreting commonly used counters. You can also use QA to view graphic
> representations of your estimated query execution plans. Check them out -
> if performance used to be good, and just recently became an issue it might
> be as simple as missing or out of date statistics...
> "Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
> news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
>
|||I just saw your other post. It sounds like your statistics and/or indexes
might be out of whack a little. Look up CREATE STATISTICS and UPDATE
STATISTICS in BOL. You'll want to make sure you have current statistics for
your tables, and you might want to look for columns that are commonly
queried to ensure you have appropriate indexes.
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:Oh3Y$mqNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Just one thing more... how can I work 'missing or out of date
> statistics'?
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:NS23e.18750$nE1.5330@.fe09.lga...
>

Performance of XML EXPLICIT vs XML AUTO and XML transform

I need to retrieve data, as XML, from 8 tables that are related by a common
primary key. Ultimately the data should be in a format similar to:
<Table1 field1="blah">
<Table2 field1="blah"/>
<Table2 field1="blah"/>
<Table3 field1="blah"/>
<Table3 field1="blah"/>
<Table4 field1="blah"/>
<Table5 field1="blah"/>
<Table6 field1="blah"/>
<Table7/>
<Table8 field1="blah"/>
</Table1>
For a given successful query, some of the tables will have 1 or more
matching records, others will have 0 or more, and Table 1 will have 1
record. I am currently using joins and XML AUTO to retrieve the data, and
then passing it through an XSL transform to get it into the format I need.
An alternative would be to use a UNION and XML EXPLICT to accomplish the
same thing.
Since ultimately performance will be an issue, it there anyway, short of a
perormance test, to tell which method will be faster? Are there any other
ways to accomplish this that I should consider?
Thanks in advance
-Andy
"Andy Walldorff" <andyw@.REMOVEsuperior.net> wrote in message
news:eWJm5okoEHA.2784@.TK2MSFTNGP14.phx.gbl...
[snip]
> Since ultimately performance will be an issue, it there anyway, short of a
> perormance test, to tell which method will be faster? Are there any other
> ways to accomplish this that I should consider?
You will have to run a test since there are quite a few factors. I think the
XSL method is probably the fastest and it seems the cleanest.
http://www.sqlxml.org/faqs.aspx?faq=55
Bryant
|||"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:%23sSRGlnoEHA.516@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "Andy Walldorff" <andyw@.REMOVEsuperior.net> wrote in message
> news:eWJm5okoEHA.2784@.TK2MSFTNGP14.phx.gbl...
> [snip]
a[vbcol=seagreen]
other
> You will have to run a test since there are quite a few factors. I think
the
> XSL method is probably the fastest and it seems the cleanest.
> http://www.sqlxml.org/faqs.aspx?faq=55
> --
> Bryant
>
Thanks

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related
. The third party system we have is archaic and code is written for SQL Serv
er 6.5. It has indices for multiple fields and all the data and index are in
one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The
system created numerous table locks and one part of the system totally locks
out if a user is deleting from a table and other users are trying to access
the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we h
ave more than 1100 connections. Since its a third party tool we have limited
control over code change and they never do any support. The management is a
sking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices al
so into a file. Move the tempdb into another file and keep in a different ra
id array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
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
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
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
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almost 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Searches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
--
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
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.