CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your help
And if you are using SQL2005 SP2, you would find the newly released dashboard
reports rather useful in finding queries that may be consuming excessive CPU.
Linchi
"tabarnak@.gmail.com" wrote:

> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>
|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your helpAre you on SQL 2000.
Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
approach I take is normally, to analyse these figures , ascertain whether
it's an IO or network problem . Then drill down to the db level
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178200041.682518.327170@.n59g2000hsh.googlegroups.com...
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||And if you are using SQL2005 SP2, you would find the newly released dashboar
d
reports rather useful in finding queries that may be consuming excessive CPU
.
Linchi
"tabarnak@.gmail.com" wrote:

> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards|||I went to the link but got a 404
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/sql
<a href="http://links.10026.com/?link=http://www.itjobfeed.com/sql"> uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178703761.778779.133060@.e51g2000hsg.googlegroups.com...
> On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> (being away for some days)
> Hi Jack,
> I uploaded the output in http://rafb.net/p/6rXNqc96.html.
> CXPACKET, LATCH_EX and PAGEIOLATCH_SH
> Am I right, could you give some explanatations and hints.
> is it possible to clear these stats, in order to have them on a short
> sessions, along-side of the profiler for instance.
> Thanks
> Regards
>

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your helpAre you on SQL 2000.
Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
approach I take is normally, to analyse these figures , ascertain whether
it's an IO or network problem . Then drill down to the db level
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178200041.682518.327170@.n59g2000hsh.googlegroups.com...
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||And if you are using SQL2005 SP2, you would find the newly released dashboard
reports rather useful in finding queries that may be consuming excessive CPU.
Linchi
"tabarnak@.gmail.com" wrote:
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards|||I went to the link but got a 404
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/sql
<a href="http://links.10026.com/?link=">http://www.itjobfeed.com/sql"> uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178703761.778779.133060@.e51g2000hsg.googlegroups.com...
> On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
>> Are you on SQL 2000.
>> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site.
>> The
>> approach I take is normally, to analyse these figures , ascertain whether
>> it's an IO or network problem . Then drill down to the db level
>> --
>> Jack Vamvas
> (being away for some days)
> Hi Jack,
> I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
> CXPACKET, LATCH_EX and PAGEIOLATCH_SH
> Am I right, could you give some explanatations and hints.
> is it possible to clear these stats, in order to have them on a short
> sessions, along-side of the profiler for instance.
> Thanks
> Regards
>

Counting Unique Values?

I have a database that holds information about the location of our members. Their location is held as an ID that it gets from another table in the databse.

Is there an SQL SELECT query I can write that will return a result listing the ID and the number of times it appears? I am sure this is rather simple but I just am unaware of the code?

thanks.Assuming that you are using SQL, you could use the GROUP BY clause to do what you've described.

What tool(s) are you using, and what is your table design?

-PatP|||I am using MS Access. The table has fields holding member data. All items that will potentially be repeated ie gender, location, member package, member category, etc are populated with a unique ID referencing another table containing the data that is pertinent to those. All the IDs are held in number fields.

I assume that is the info you were after?|||yes, Access supports COUNT(*) and GROUP BYselect ID,count(*) as rows from sometable group by ID

Counting members within timerange

Hi,

I want to analyse support tickets... They are opened at a specific time and closed at a specific time... The question now is: How many support tickets are open at a specific date... Or better: I want to see a statistic for each day how many tickets were open on that day...

Besides thinking about some very fancy MDX stuff I only can imagine to build up a special fact table in my ETL process which builds a record per day per open ticket... That's also not nice...

Any other ideas?

Thanks,

Hi Thomas,

It's one of those problems that's easier in SQL than MDX. You could take an approach similar to the one I describe here:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!113.entry

...although in this case it's not just a simple sum. You would have a Start Date and an End Date dimension, then sum the set of all tickets which have a) start dates between the currentmembers on Start Date and End Date, or b) end dates between the currentmembers on Start Date and End Date, or c) start dates before the currentmember on Start Date and end dates after the currentmember on End Date (I think). Performance might be a problem, but I'd still prefer to do this than blow up the fact table in the ETL.

Regards,

Chris

|||

You might find this entry in Mosha's blog interesting as well:

Counting 'in-flight' events in MDX

Time dimension has special meaning in OLAP and DW. The classic problems involve looking at previous period, parallel period, computing moving averages and running sums. Today we will look into less common, but nevertheless interesting problem of 'in-flight' events. This scenario arises when there is more than one date/time attribute associated with the fact.

...

|||

...interesting read... Thanks for the link!

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle
my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of custome
rs and number of different resources.
When the end user is creating a pivot table with Region en time on the axis,
the count of resources should reflect on the crossings. And I really don't
know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please helpF.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/d...r />
inct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tack
le my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of custo
mers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axi
s, the count of resources should reflect on the crossings. And I really don'
t know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of customers and number of different resources.
When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please help
F.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/de.../distinct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tackle my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of customers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>