Control/Limit Memory usage

Hello,

I was wondering if there was a setting or a way to limit or restrict the amount of Memory an SSIS package can use? I know that by default the windows OS limits a process (a package in this case) to 2GB and up to 3GB with AWE enable but what if I wanted to say Limit it to 1GB of memory is there anyway to do that? Is there an SSIS Engine setting or Package property somewhere?

Thanks!

See if this helps you. Check out the Buffer size section in this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Containstable variable usage

I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....

Can you not use a variable as a column list? I have a variable for search criteria and it works fine...

Here is my syntax

containstable([tablename],@.columnlist,@.srch)

I have been looking online and can't seem to find anything that says I can or cannot use a variable.

Column list cannot be replaced by variable. You have to use dynamic SQL to form and execute the SELECT statement if you want to parameterize CONTAINS/CONTAINSTABLE column list.

Consuming all Memory

Hi
How to adjust memory usage by using configuration options in SQL Server:
http://support.microsoft.com/defaul...b;en-us;Q321363
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message news:OnAVm8KlGHA
.836@.TK2MSFTNGP02.phx.gbl...
We have an application running in MSSQL 2000 with SP 4 and C#.NET 2003.
This server has 8 GB of memory and enough hard drive.
The MSSQL server keep consuming more and more memory and never release it.
It gets to the point that we have to reboot or the server crash.
The MSSQL memory start with 278432 kb of ram until it take all the memory av
ailable in the server.
My question is..
What happend If I specify a minimum and maximun amount of memory. Will MSSQL
stop running when it get to the maximun or what will happned?
I will appreciate your help,
RafaelWe have an application running in MSSQL 2000 with SP 4 and C#.NET 2003.
This server has 8 GB of memory and enough hard drive.
The MSSQL server keep consuming more and more memory and never release it.
It gets to the point that we have to reboot or the server crash.
The MSSQL memory start with 278432 kb of ram until it take all the memory av
ailable in the server.
My question is..
What happend If I specify a minimum and maximun amount of memory. Will MSSQL
stop running when it get to the maximun or what will happned?
I will appreciate your help,
Rafael|||Hi
How to adjust memory usage by using configuration options in SQL Server:
http://support.microsoft.com/defaul...b;en-us;Q321363
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message news:OnAVm8KlGHA
.836@.TK2MSFTNGP02.phx.gbl...
We have an application running in MSSQL 2000 with SP 4 and C#.NET 2003.
This server has 8 GB of memory and enough hard drive.
The MSSQL server keep consuming more and more memory and never release it.
It gets to the point that we have to reboot or the server crash.
The MSSQL memory start with 278432 kb of ram until it take all the memory av
ailable in the server.
My question is..
What happend If I specify a minimum and maximun amount of memory. Will MSSQL
stop running when it get to the maximun or what will happned?
I will appreciate your help,
Rafael

Constant locking issue

My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
SQL Server loves memory. It will give back to the OS only when necessary.
My workstation and laptop each have 2GB and I suggest that you give your
server more RAM.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
|||The problem is, this morning I went back to check the SQL server memory
usage has gone up to 850M
over all memory usage by the whole server was very close to 1G
Even though there were less users in the morning
(Yesterday peak of usage, SQL server memory usage was around 798M)
This tells me something in SQL server holding on to memory that doesn't
release
SQL server is set to Dynamically configure SQL memory, minimum memory query
is set to 1024Kb
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#rTA1fOOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> SQL Server loves memory. It will give back to the OS only when necessary.
> My workstation and laptop each have 2GB and I suggest that you give your
> server more RAM.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
> My SQL server has 1G Ram, and I noticed that it was using about 700MB of
it[vbcol=seagreen]
> constant. Next day I looked and usage was gone up to 798 constant.
> Is it possible that SQL server is not releasing the memory it is using,
> thus causing slow downs, and lock finally.
> My guess is that SQL server doesn't realy make use for windows page file.
> MC
>
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in
> offset
>

Constant locking issue

My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
>
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
>
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
>
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
>
> SQL log
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
>SQL Server loves memory. It will give back to the OS only when necessary.
My workstation and laptop each have 2GB and I suggest that you give your
server more RAM.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||The problem is, this morning I went back to check the SQL server memory
usage has gone up to 850M
over all memory usage by the whole server was very close to 1G
Even though there were less users in the morning
(Yesterday peak of usage, SQL server memory usage was around 798M)
This tells me something in SQL server holding on to memory that doesn't
release
SQL server is set to Dynamically configure SQL memory, minimum memory query
is set to 1024Kb
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#rTA1fOOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> SQL Server loves memory. It will give back to the OS only when necessary.
> My workstation and laptop each have 2GB and I suggest that you give your
> server more RAM.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
> My SQL server has 1G Ram, and I noticed that it was using about 700MB of
it
> constant. Next day I looked and usage was gone up to 798 constant.
> Is it possible that SQL server is not releasing the memory it is using,
> thus causing slow downs, and lock finally.
> My guess is that SQL server doesn't realy make use for windows page file.
> MC
>
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> >
> > I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
> 2G
> > RAM)
> > We use this for Maximizer ECRM application, but lately (past 2-3month)
we
> > seem to get more SQL locking issues while running application.
> > When I try to kill the proccess that causing the lock, it still shows
the
> > proccess in Enterprise manager, but this leads to more locks somehow.
> > If I try to stop SQL server, at this stage, SQL server engine hangs then
> > times out (doesn't properly stop)
> > If I try to re-start the whole server, server itself hangs on the blue
> > screen trying to shut down
> > (I can re-start server, no problem, as long as these locks doesn't
occur)
> >
> > No CRM application changes.
> > Only changes on the server is constant windows patches, Symantec
> > Anti-Virus updates.
> > I don't know if any of those windows security update is the cause.
> > I donwloaded latest SQL 2000 updates and installed still seem to happen.
> > Nothing in the windows event log that tells me that I am having software
> or
> > hardware issues
> >
> > I noticed this error in SQL event log though, but I don't remember
> getting
> > this error on previous SQL crashes, due to locks
> > Any idea appreciated
> > MC
> >
> > SQL log
> >
> > SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> > 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf]
in
> > database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
> offset
> > of the latest long IO is: 0x000000004a9c00
> >
> >
>

Constant locking issue

My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf
] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>SQL Server loves memory. It will give back to the OS only when necessary.
My workstation and laptop each have 2GB and I suggest that you give your
server more RAM.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf
] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||The problem is, this morning I went back to check the SQL server memory
usage has gone up to 850M
over all memory usage by the whole server was very close to 1G
Even though there were less users in the morning
(Yesterday peak of usage, SQL server memory usage was around 798M)
This tells me something in SQL server holding on to memory that doesn't
release
SQL server is set to Dynamically configure SQL memory, minimum memory query
is set to 1024Kb
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#rTA1fOOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> SQL Server loves memory. It will give back to the OS only when necessary.
> My workstation and laptop each have 2GB and I suggest that you give your
> server more RAM.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
> My SQL server has 1G Ram, and I noticed that it was using about 700MB of
it
> constant. Next day I looked and usage was gone up to 798 constant.
> Is it possible that SQL server is not releasing the memory it is using,
> thus causing slow downs, and lock finally.
> My guess is that SQL server doesn't realy make use for windows page file.
> MC
>
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in[vbcol=seagreen]
> offset
>

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

tpenrose:

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

I'm not sure how Management Studio cause the CPU issue, on my machine it works fine. But the Management Studio process is sqlwb.exe, you can use SQL Server Performance Tool Profiler to trace what's SQL doing when you connect via Management Studio

|||

Thanks, but I've tried Profiler and no joy.

A basic trace just shows the standard "Existing Connections" and nothing more. There's no apparant activity going on as far as SQL Server is concerned.

I should just clarify that it's not sqlwb.exe (which is on the client) that's showing the CPU usage, but services.exe and wmiprvse.exe on the server that seem to be causing the CPU spikes.

Hope someone can help otherwise I guess it back to good old 2000. Shame though 'cause 2005 "looked" really promising. Don't think I'll be able to wait another 6-12 months for Service Pack 2.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend thewmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.