"Chuck P" <Chuck@newsgroup.nospam> wrote in message
news:4099C7AE-C22A-4884-ADDC-0E623E1CEE7F@microsoft.com...
>I have a 4 cpu box with sql05 on it.
> Task manager shows two sqlserver.exe running each at 25% cpu and 5 gig
> memory.  This is at 6 am with NOTHING going on.
>
>
> If I run
> --ActiveSessions.sql
>
> SELECT
>    s.session_id
>  , c.connect_time
>  , s.login_time
>  , s.login_name
>  , c.protocol_type
>  , c.auth_scheme
>  , s.HOST_NAME
>  , s.program_name
> FROM sys.dm_exec_sessions s
>  JOIN sys.dm_exec_connections c
>    ON s.session_id = c.session_id
> I don't get much action.
>
>
> If I look at activity monitor I get 10 sleeping  awaiting command and a
> few
> from me.  The highest cpu is 4000 from my login.
>
>
> Visual Studio Team System is on a separate server
> Team Foundation Server database is installed on this server.
> SqlServerProfiler is showing about every second Report Server is
> running this: "exec GetDBVersion @DBVersion=@p1 output"
>
> Any ideas on why 2 sqlserver.exe processes are running at 25%?
>

"TheSQLGuru" wrote:

> are you patched up to SP2+some CU or another?  I had a client where a server
> would exhibit the exact same issue but this went away with a patch.  Not
> sure which one it was that did the trick though.  Sorry!
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Chuck P" <Chuck@newsgroup.nospam> wrote in message
> news:4099C7AE-C22A-4884-ADDC-0E623E1CEE7F@microsoft.com...
> >I have a 4 cpu box with sql05 on it.
> > Task manager shows two sqlserver.exe running each at 25% cpu and 5 gig
> > memory.  This is at 6 am with NOTHING going on.
> >
> >
> > If I run
> > --ActiveSessions.sql
> >
> > SELECT
> >    s.session_id
> >  , c.connect_time
> >  , s.login_time
> >  , s.login_name
> >  , c.protocol_type
> >  , c.auth_scheme
> >  , s.HOST_NAME
> >  , s.program_name
> > FROM sys.dm_exec_sessions s
> >  JOIN sys.dm_exec_connections c
> >    ON s.session_id = c.session_id
> > I don't get much action.
> >
> >
> > If I look at activity monitor I get 10 sleeping  awaiting command and a
> > few
> > from me.  The highest cpu is 4000 from my login.
> >
> >
> > Visual Studio Team System is on a separate server
> > Team Foundation Server database is installed on this server.
> > SqlServerProfiler is showing about every second Report Server is
> > running this: "exec GetDBVersion @DBVersion=@p1 output"
> >
> > Any ideas on why 2 sqlserver.exe processes are running at 25%?
> >
>
>
>

"Chuck P" <Chuck@newsgroup.nospam> wrote in message
news:C24D5AD7-57E1-4261-A99A-A4E6F559B84B@microsoft.com...
> We're patched.
> Messing around changed the processor affinity to 3 for 1 sql instance and
> 1
> for another.  This dropped the cpu usage on the later box to a few
> percent.
>
>
>
> "TheSQLGuru" wrote:
>
>> are you patched up to SP2+some CU or another?  I had a client where a
>> server
>> would exhibit the exact same issue but this went away with a patch.  Not
>> sure which one it was that did the trick though.  Sorry!
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "Chuck P" <Chuck@newsgroup.nospam> wrote in message
>> news:4099C7AE-C22A-4884-ADDC-0E623E1CEE7F@microsoft.com...
>> >I have a 4 cpu box with sql05 on it.
>> > Task manager shows two sqlserver.exe running each at 25% cpu and 5 gig
>> > memory.  This is at 6 am with NOTHING going on.
>> >
>> >
>> > If I run
>> > --ActiveSessions.sql
>> >
>> > SELECT
>> >    s.session_id
>> >  , c.connect_time
>> >  , s.login_time
>> >  , s.login_name
>> >  , c.protocol_type
>> >  , c.auth_scheme
>> >  , s.HOST_NAME
>> >  , s.program_name
>> > FROM sys.dm_exec_sessions s
>> >  JOIN sys.dm_exec_connections c
>> >    ON s.session_id = c.session_id
>> > I don't get much action.
>> >
>> >
>> > If I look at activity monitor I get 10 sleeping  awaiting command and a
>> > few
>> > from me.  The highest cpu is 4000 from my login.
>> >
>> >
>> > Visual Studio Team System is on a separate server
>> > Team Foundation Server database is installed on this server.
>> > SqlServerProfiler is showing about every second Report Server is
>> > running this: "exec GetDBVersion @DBVersion=@p1 output"
>> >
>> > Any ideas on why 2 sqlserver.exe processes are running at 25%?
>> >
>>
>>
>>

"TheSQLGuru" wrote:

> Messing with proc affinities is pretty significant stuff.  I suggest reading
> up on that if you plan on leaving it that way.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Chuck P" <Chuck@newsgroup.nospam> wrote in message
> news:C24D5AD7-57E1-4261-A99A-A4E6F559B84B@microsoft.com...
> > We're patched.
> > Messing around changed the processor affinity to 3 for 1 sql instance and
> > 1
> > for another.  This dropped the cpu usage on the later box to a few
> > percent.
> >
> >
> >
> > "TheSQLGuru" wrote:
> >
> >> are you patched up to SP2+some CU or another?  I had a client where a
> >> server
> >> would exhibit the exact same issue but this went away with a patch.  Not
> >> sure which one it was that did the trick though.  Sorry!
> >>
> >> --
> >> Kevin G. Boles
> >> Indicium Resources, Inc.
> >> SQL Server MVP
> >> kgboles a earthlink dt net
> >>
> >>
> >> "Chuck P" <Chuck@newsgroup.nospam> wrote in message
> >> news:4099C7AE-C22A-4884-ADDC-0E623E1CEE7F@microsoft.com...
> >> >I have a 4 cpu box with sql05 on it.
> >> > Task manager shows two sqlserver.exe running each at 25% cpu and 5 gig
> >> > memory.  This is at 6 am with NOTHING going on.
> >> >
> >> >
> >> > If I run
> >> > --ActiveSessions.sql
> >> >
> >> > SELECT
> >> >    s.session_id
> >> >  , c.connect_time
> >> >  , s.login_time
> >> >  , s.login_name
> >> >  , c.protocol_type
> >> >  , c.auth_scheme
> >> >  , s.HOST_NAME
> >> >  , s.program_name
> >> > FROM sys.dm_exec_sessions s
> >> >  JOIN sys.dm_exec_connections c
> >> >    ON s.session_id = c.session_id
> >> > I don't get much action.
> >> >
> >> >
> >> > If I look at activity monitor I get 10 sleeping  awaiting command and a
> >> > few
> >> > from me.  The highest cpu is 4000 from my login.
> >> >
> >> >
> >> > Visual Studio Team System is on a separate server
> >> > Team Foundation Server database is installed on this server.
> >> > SqlServerProfiler is showing about every second Report Server is
> >> > running this: "exec GetDBVersion @DBVersion=@p1 output"
> >> >
> >> > Any ideas on why 2 sqlserver.exe processes are running at 25%?
> >> >
> >>
> >>
> >>
>
>
>

"Robbie Meng" wrote:

> Dear Partner,
>
> Thank you for the post.
>
> From your description, I understand that you want to find what has caused
> high CPU usage in your server. If I have misunderstood, please let me know.
>
> Based on your troubleshooting results, it seems there is little chance that
> user activity has consumed high CPU.  I suspect that some system activity
> might consumed more resource such as scheduled job or etc.
>
> To find the root cause of the issue, I would like to collect the following
> information:
> 1.    Please check your trace record and search for any record with high
> duration. (if possible, please list it)
>
> 2.    Is there any scheduled job running in the server?
>
> 3.    Did you configure the server for replication or log shipping?
>
> 4.    Regarding there is two sqlserver.exe, do you mean two instance on you
> server?
>
> Also, I suggest that we monitor the performance counter to check who have
> easing more CPU
>
> Object: Thread
> Counter: Processor Time
> Instance: All SQLServer Worker threads
>
> We can check if any specific sql thread thats eating alarmingly more cpu
> than other threads and we can suspect the special thread to be the problem
> creator. (once we know what thread it is, make a note of thread instance
> number)
>
> These threads are nothing but the worker threads in sqlserver. And these
> threads are eventually associated with a specific sql SPID
>
>
> Please let me know on any updates on the situation.
>
>
> Best Regards,
>
> Robbie Meng
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

> Hi Chuck,
> Could you please let me know why you thought that 25% CPU utilization was
> high? Generally values of 80% processor time per CPU are generally deemed
> to be a bottleneck. I recommend that you refer to this article for more
> information:
> CPU Bottlenecks
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EIE
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msd***@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
>
>