Analyze CPU Time per SQL Session

You have already found sys.dm_exec_sessions. Have you looked at the
column cpu_time it returns?
Roy Harvey
Beacon Falls, CT
On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
wrote:

>Hello
>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
>possible to analyze which SQL Server Session is consuming how much Processor
>Ressource in Time or Percent? With the DMV Views i see that there are 11000
>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
>Taskmanager). This server is dedicated for SQL Server.
>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
>must now, how many CPU Consumption per Session, so i can identify the
>Sessions who are responsable for the high CPU Workload
>Somebody knows how i can use the DMV Views or are there tools to retrieve
>the information?
>regards
>Paolo
>
It's not an ideal approach, but you could always take snapshots of the DMV
and find the cpu time differences between snapshots, and that should give you
some rough idea.
Linchi
"Paolo Taverna" wrote:

> Hi Roy
> yes i had a look at the column cpu_time. So far i understand is the column
> cpu_time the sum of the total cpu_time of the request since this Session is
> alive. So if the column last_request_end_time is a lot behind the time when
> the CPU's have high workload, the column total_cpu_time is not helpful.
> Regards Paolo
> "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
> news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
>
>

Analyze CPU Time per SQL Session

Hello
Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
possible to analyze which SQL Server Session is consuming how much Processor
Ressource in Time or Percent? With the DMV Views i see that there are 11000
open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
Taskmanager). This server is dedicated for SQL Server.
With the View sys.dm_exec_sessions i see how many Sessions are open, but i
must now, how many CPU Consumption per Session, so i can identify the
Sessions who are responsable for the high CPU Workload
Somebody knows how i can use the DMV Views or are there tools to retrieve
the information?
regards
PaoloYou have already found sys.dm_exec_sessions. Have you looked at the
column cpu_time it returns?
Roy Harvey
Beacon Falls, CT
On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
wrote:
>Hello
>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
>possible to analyze which SQL Server Session is consuming how much Processor
>Ressource in Time or Percent? With the DMV Views i see that there are 11000
>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
>Taskmanager). This server is dedicated for SQL Server.
>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
>must now, how many CPU Consumption per Session, so i can identify the
>Sessions who are responsable for the high CPU Workload
>Somebody knows how i can use the DMV Views or are there tools to retrieve
>the information?
>regards
>Paolo
>|||Hi Roy
yes i had a look at the column cpu_time. So far i understand is the column
cpu_time the sum of the total cpu_time of the request since this Session is
alive. So if the column last_request_end_time is a lot behind the time when
the CPU's have high workload, the column total_cpu_time is not helpful.
Regards Paolo
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
> You have already found sys.dm_exec_sessions. Have you looked at the
> column cpu_time it returns?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
> wrote:
>>Hello
>>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
>>possible to analyze which SQL Server Session is consuming how much
>>Processor
>>Ressource in Time or Percent? With the DMV Views i see that there are
>>11000
>>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
>>Taskmanager). This server is dedicated for SQL Server.
>>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
>>must now, how many CPU Consumption per Session, so i can identify the
>>Sessions who are responsable for the high CPU Workload
>>Somebody knows how i can use the DMV Views or are there tools to retrieve
>>the information?
>>regards
>>Paolo|||It's not an ideal approach, but you could always take snapshots of the DMV
and find the cpu time differences between snapshots, and that should give you
some rough idea.
Linchi
"Paolo Taverna" wrote:
> Hi Roy
> yes i had a look at the column cpu_time. So far i understand is the column
> cpu_time the sum of the total cpu_time of the request since this Session is
> alive. So if the column last_request_end_time is a lot behind the time when
> the CPU's have high workload, the column total_cpu_time is not helpful.
> Regards Paolo
> "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
> news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
> > You have already found sys.dm_exec_sessions. Have you looked at the
> > column cpu_time it returns?
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
> > wrote:
> >
> >>Hello
> >>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
> >>possible to analyze which SQL Server Session is consuming how much
> >>Processor
> >>Ressource in Time or Percent? With the DMV Views i see that there are
> >>11000
> >>open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
> >>Taskmanager). This server is dedicated for SQL Server.
> >>With the View sys.dm_exec_sessions i see how many Sessions are open, but i
> >>must now, how many CPU Consumption per Session, so i can identify the
> >>Sessions who are responsable for the high CPU Workload
> >>
> >>Somebody knows how i can use the DMV Views or are there tools to retrieve
> >>the information?
> >>
> >>regards
> >>Paolo
> >>
>
>

Analysis Services 2005 using only one CPU

Hi,

I am facing issue with our client related to Analysis Services 2005 using only 13% of CPU.

Our client is running on server with 8 CPUs, 15GB of RAM. When request being made to execute MDX, only 13% of CPU is utilized. It is like using only one CPU. When additional concurrent user is added, then CPU usage increases.

The Analaysis Services being used is Evaluation Copy at this point.

I checked all possible settings in Analysis Services, like to increase concurrent threads, and other settings, but it brings no result. CPU is stuck at 13% for single user.

So, is it like limitation of SQL Server Evaluation copy that it does not utilize full CPU, or Analysis Services is not meant to use more that 1 CPU (which is unlikely)?

Can you help me to troubleshoot this issue.

Thank you

Analysis Services OLAP engine is roughly divided into two major components: 1) query processor and calculation engine 2) storage engine. Storage engine is multithreaded to support parallel access to multiple partitions. The calculation engine is mainly singlethreaded. If your query is calculation intensive instead of data intensive, you would only see a single CPU maxed out.|||

thanks for clarifying the internals of Analysis Services.

At the moment i have only one partition in the OLAP Database. The performance drop when several crossjoin statements used in the MDX. I will try to optimize my mdx statements, but is there anyway to optimize calculation engine?

Will it help calculation engine if i divide my cube into several partitions? Our client data is pretty big, 1GB for 8 days data. Eventually, our client wants to keep 60 days data in the cube, and historical data being purged or archived. My intention was also eventually create partitions either monthly, or weekly as it is implemented in Project REAL.

So, what are the way you can suggest to optimize calculating engine?

Thank you.

Analysis Services 2005 Lock-up on Calculated Measures

AS 2005/VS 2005 is locking-up when I try to go to the calculated measures tab. It uses some CPU and I think a lot of I/O. My only way out is to close VS.

Everything else is working. I looked at the XML and it looked OK to me (naive me).

I have written out the xml and created another db/cube and the problem came along with the xml.

I'm stuck.

Tom

The calculation tab in BIDS (VS) has to do a parse of the script to display it in the UI, so this would suggest that there is something in the script that is giving it issues.

Try creating another database/cube using the xmla as you did before, but also do the following:

search for the <MDXScript></MDXScript> tags and copy the contents into another file for safe keeping|||

I tried that - no luck.

Also tried a transplant from a OK cube. I'm guessing it is not the mdx scripts.

It happens when I try to go to the translate tab and the perspective tab too (in addition to the calculated tab).

I also re-installed SS/AS/RP/IS 2005; (that was a train wreck)

I am still stuck; I'll try other tabs and maybe get a clue. Any assistance would be appreciated.

|||

OK, I did the previous post off the top of my head and as you must have figured out, you actually need to remove the data under the <MDXScript><Commands><Command><Text> Node to remove the calculation script.

The other structure under the MDXScript node is the <CalculationProperties> - this is where translations and display folders for the calculated members are stored so it might be this data that is causing your issue. This is the only data that I can think of at the moment that would appear in all three tabs that are causing you issues.

If you have not already, you could try the same approach as before, except you can actually remove all the calculation property nodes leaving just empty <CalculationProperties></CalculationProperties> tags.

|||

Got it!

Was behind on SQL server/AS updates and VS updates.

When I updated to cummulative SQL server updates and VS latest update the problem was resolved.

Thank you for your time and your input.

Tom