Monday, March 26, 2012
mssdmn consuming cpu and memory
cpu cycles (25% or more on each processor). The proces that is consuming
the cpu is mssdmn. The server hosts three major databases; MS Sharepoint
team services, Lyris list manager, and Macromedia Breeze. Over the weekend
I shutdown all three of these major servers and the cpu continued to be
consumed. I have run Symantec AV and found no viruses. I have restarted
the SQL server several times and the problem continues. I find that if I
work in SQL Enterprise Manager on the sql server the cpu decreases, which I
guess points to a background process.
The server O/S is Windows 2003 server with the latest service packs and
updates applied.
Any suggestions on how to solve the problem would be grealy appreciated.
ThanksThis is SQL FTS - actually MSSearch. You might want to configure it to be
less invasive or perhaps more invasive. Use sp_fulltext_server
'resource_usage' to do this. Configure it for 1 to get less invasive, 5 to
get more invasive.
The question you should be asking yourself is why is it indexing all the
time. What sorts of docs are you storing, and are any of them in Far Eastern
languages or in German. These tend to be resource intensive.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim Boyer" <boyerj@.wsu.edu> wrote in message
news:92bbe140b9d046ddb63a62ee9eef93a8@.ureader.com...
> I have a sql 2000 SP4 server with two processors that has started to
consume
> cpu cycles (25% or more on each processor). The proces that is consuming
> the cpu is mssdmn. The server hosts three major databases; MS Sharepoint
> team services, Lyris list manager, and Macromedia Breeze. Over the
weekend
> I shutdown all three of these major servers and the cpu continued to be
> consumed. I have run Symantec AV and found no viruses. I have restarted
> the SQL server several times and the problem continues. I find that if I
> work in SQL Enterprise Manager on the sql server the cpu decreases, which
I
> guess points to a background process.
> The server O/S is Windows 2003 server with the latest service packs and
> updates applied.
> Any suggestions on how to solve the problem would be grealy appreciated.
> Thanks
mssdmn consuming cpu and memory
cpu cycles (25% or more on each processor). The proces that is consuming
the cpu is mssdmn. The server hosts three major databases; MS Sharepoint
team services, Lyris list manager, and Macromedia Breeze. Over the weekend
I shutdown all three of these major servers and the cpu continued to be
consumed. I have run Symantec AV and found no viruses. I have restarted
the SQL server several times and the problem continues. I find that if I
work in SQL Enterprise Manager on the sql server the cpu decreases, which I
guess points to a background process.
The server O/S is Windows 2003 server with the latest service packs and
updates applied.
Any suggestions on how to solve the problem would be grealy appreciated.
Thanks
This is SQL FTS - actually MSSearch. You might want to configure it to be
less invasive or perhaps more invasive. Use sp_fulltext_server
'resource_usage' to do this. Configure it for 1 to get less invasive, 5 to
get more invasive.
The question you should be asking yourself is why is it indexing all the
time. What sorts of docs are you storing, and are any of them in Far Eastern
languages or in German. These tend to be resource intensive.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim Boyer" <boyerj@.wsu.edu> wrote in message
news:92bbe140b9d046ddb63a62ee9eef93a8@.ureader.com. ..
> I have a sql 2000 SP4 server with two processors that has started to
consume
> cpu cycles (25% or more on each processor). The proces that is consuming
> the cpu is mssdmn. The server hosts three major databases; MS Sharepoint
> team services, Lyris list manager, and Macromedia Breeze. Over the
weekend
> I shutdown all three of these major servers and the cpu continued to be
> consumed. I have run Symantec AV and found no viruses. I have restarted
> the SQL server several times and the problem continues. I find that if I
> work in SQL Enterprise Manager on the sql server the cpu decreases, which
I
> guess points to a background process.
> The server O/S is Windows 2003 server with the latest service packs and
> updates applied.
> Any suggestions on how to solve the problem would be grealy appreciated.
> Thanks
sql
mssdmn consuming cpu and memory
cpu cycles (25% or more on each processor). The proces that is consuming
the cpu is mssdmn. The server hosts three major databases; MS Sharepoint
team services, Lyris list manager, and Macromedia Breeze. Over the weekend
I shutdown all three of these major servers and the cpu continued to be
consumed. I have run Symantec AV and found no viruses. I have restarted
the SQL server several times and the problem continues. I find that if I
work in SQL Enterprise Manager on the sql server the cpu decreases, which I
guess points to a background process.
The server O/S is Windows 2003 server with the latest service packs and
updates applied.
Any suggestions on how to solve the problem would be grealy appreciated.
ThanksThis is SQL FTS - actually MSSearch. You might want to configure it to be
less invasive or perhaps more invasive. Use sp_fulltext_server
'resource_usage' to do this. Configure it for 1 to get less invasive, 5 to
get more invasive.
The question you should be asking yourself is why is it indexing all the
time. What sorts of docs are you storing, and are any of them in Far Eastern
languages or in German. These tend to be resource intensive.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim Boyer" <boyerj@.wsu.edu> wrote in message
news:92bbe140b9d046ddb63a62ee9eef93a8@.ur
eader.com...
> I have a sql 2000 SP4 server with two processors that has started to
consume
> cpu cycles (25% or more on each processor). The proces that is consuming
> the cpu is mssdmn. The server hosts three major databases; MS Sharepoint
> team services, Lyris list manager, and Macromedia Breeze. Over the
weekend
> I shutdown all three of these major servers and the cpu continued to be
> consumed. I have run Symantec AV and found no viruses. I have restarted
> the SQL server several times and the problem continues. I find that if I
> work in SQL Enterprise Manager on the sql server the cpu decreases, which
I
> guess points to a background process.
> The server O/S is Windows 2003 server with the latest service packs and
> updates applied.
> Any suggestions on how to solve the problem would be grealy appreciated.
> Thanks
Monday, March 19, 2012
Msg 8621: The query processor ran out of stack space during query optimization
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Jamie Thomson wrote:
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
Umachandar Jayachandran - MS wrote:
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
Msg 8621: The query processor ran out of stack space during query optimization
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Jamie Thomson wrote:
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
Umachandar Jayachandran - MS wrote:
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
Monday, February 20, 2012
MSDN Version of SQL Server
I have SQL Server 2000 standard edition from MSDN installed on what will now
become a production server. I have since purchased a 1 processor licence. My
question is there any reason not to continue using the MSDN version in
production? Or should I uninstall it and use the purchased version.
Thanks
NIsnt there a limit to the number of CPUs and RAM that MSDN copy can handle.
We had this problem once in SQLServer 7 and we eventually had to Backup
DB/uninstall MSDN version/Install Licensed Version and
Restore the Databases.
Whatever reason, you should always install a Licensed version in a
Production Version. What if you have to call PSS for some
reason ?
Gopi
"NH" <NH@.discussions.microsoft.com> wrote in message
news:4C998CC4-C4E9-41A2-BE48-197A5A4DDE8F@.microsoft.com...
> Hi,
> I have SQL Server 2000 standard edition from MSDN installed on what will
> now
> become a production server. I have since purchased a 1 processor licence.
> My
> question is there any reason not to continue using the MSDN version in
> production? Or should I uninstall it and use the purchased version.
> Thanks
> N|||For SQL7, MSDN came with Desktop Edition (not to be
w call Desktop Engine,
AKA MSDE). Desktop Edition has such restrictions.
For 2000, MSDN comes with Developer Edition which is identical to Enterprise
Edition, but you are
now allowed to run in production environment.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gopinath Rajee" <gopinathr@.healthasyst.com> wrote in message
news:urNSfT2DFHA.1396@.tk2msftngp13.phx.gbl...
> Isnt there a limit to the number of CPUs and RAM that MSDN copy can handle
.
> We had this problem once in SQLServer 7 and we eventually had to Backup DB
/uninstall MSDN
> version/Install Licensed Version and
> Restore the Databases.
> Whatever reason, you should always install a Licensed version in a Product
ion Version. What if you
> have to call PSS for some
> reason ?
> Gopi
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:4C998CC4-C4E9-41A2-BE48-197A5A4DDE8F@.microsoft.com...
>|||Also...
> Whatever reason, you should always install a Licensed version in a Product
ion Version. What if you
> have to call PSS for some
> reason ?
I couldn't agree more. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gopinath Rajee" <gopinathr@.healthasyst.com> wrote in message
news:urNSfT2DFHA.1396@.tk2msftngp13.phx.gbl...
> Isnt there a limit to the number of CPUs and RAM that MSDN copy can handle
.
> We had this problem once in SQLServer 7 and we eventually had to Backup DB
/uninstall MSDN
> version/Install Licensed Version and
> Restore the Databases.
> Whatever reason, you should always install a Licensed version in a Product
ion Version. What if you
> have to call PSS for some
> reason ?
> Gopi
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:4C998CC4-C4E9-41A2-BE48-197A5A4DDE8F@.microsoft.com...
>|||Thanks for the replies.
However, do you know if the MSDN version of SQL Standard Edition differs
from the one you purchase?
"Tibor Karaszi" wrote:
> Also...
>
> I couldn't agree more. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gopinath Rajee" <gopinathr@.healthasyst.com> wrote in message
> news:urNSfT2DFHA.1396@.tk2msftngp13.phx.gbl...
>
>|||MSDN doesn't come with production licenses. It might some with SE as well as
EE and Dev Ed. But you
are still not allowed to run in production environment. See
http://msdn.microsoft.com/subscript...ng/default.aspx
A quote from above:
"MSDN Universal, MSDN Enterprise, MSDN Professional and MSDN Operating Syste
ms are licensed for
single users, and can only be used by licensed subscribers. All products tha
t ship in MSDN
Subscriptions are for development and test purposes only, and cannot be used
in a live production
environment."
You also might find something valuable at :
http://msdn.microsoft.com/subscript...lt.aspx#support
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"NH" <NH@.discussions.microsoft.com> wrote in message
news:19D4DE6A-4093-48EB-B900-7606C7F42A16@.microsoft.com...
> Thanks for the replies.
> However, do you know if the MSDN version of SQL Standard Edition differs
> from the one you purchase?
> "Tibor Karaszi" wrote:
>|||Hi,
I realise MSDN versions of SQL Standard are "not allowed to run in
production environment". But what I am asking is does MSDN version of SQL
Standard differ from the version you would buy (OEM type). Are there
restrictions on RAM, connections etc etc?
"Tibor Karaszi" wrote:
> MSDN doesn't come with production licenses. It might some with SE as well
as EE and Dev Ed. But you
> are still not allowed to run in production environment. See
> http://msdn.microsoft.com/subscript...ng/default.aspx
> A quote from above:
> "MSDN Universal, MSDN Enterprise, MSDN Professional and MSDN Operating Sys
tems are licensed for
> single users, and can only be used by licensed subscribers. All products t
hat ship in MSDN
> Subscriptions are for development and test purposes only, and cannot be us
ed in a live production
> environment."
>
> You also might find something valuable at :
> http://msdn.microsoft.com/subscript...lt.aspx#support
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:19D4DE6A-4093-48EB-B900-7606C7F42A16@.microsoft.com...
>
>|||> Are there
> restrictions on RAM, connections etc etc?
No.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"NH" <NH@.discussions.microsoft.com> wrote in message
news:D3D585A4-1C12-4203-9640-8ED39685B371@.microsoft.com...
> Hi,
> I realise MSDN versions of SQL Standard are "not allowed to run in
> production environment". But what I am asking is does MSDN version of SQL
> Standard differ from the version you would buy (OEM type). Are there
> restrictions on RAM, connections etc etc?
> "Tibor Karaszi" wrote:
>|||Thanks for your help.
N
"Tibor Karaszi" wrote:
> No.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:D3D585A4-1C12-4203-9640-8ED39685B371@.microsoft.com...
>
>