How to disabel the Microsoft Search Service

Hi everybody,

I work with SQL Server 2000 SP2 in an Active/active Cluster.
Two days ago, one the sql server fulltext resource of one node just failed suddenly.
Now I am realizing that Search Engine Utility has not been used by any of my databases, so I would like do disable this service...
Does anybody know how can I get rid of the Microsoft Search Service.
I don't need this service active!
In Service Manager the Microsoft Search is in an unkown state.
In cluster Administrator the sql server full text resource is in a failed state.
Do I need to delete the full text resource or is there any other way to disable the Search Service?
Any help or advice will be very important to me...
Thanks,Search service is a shared service by all SQL Server instances.

Can you take off-line on SQL Server FullText resources from all SQL Server instances from cluster administrator?

You shouldn't remove the search service even though you currently do not use it. Try take it off-line.

Becuase your search service is in unknow status, I would move all cluster groups to the good node and restart the machine that search service is not function properly.|||Hi Allen_Cui

Thank you for your reply...
I have already tried to move all the groups to the good node and the result was:
Microsoft Search is in "stopped" state in Service Manager... -> that's ok!
SQL Server Full Text resource was in "failed" state in cluster Administrator... -> that's not ok!
But before I try the manual failover I selected the option "Do not restart" in the properties of the failed Full Text Resource...
My problem now is: I want to convert the state of the failed Full Text Resource to an offline state the is more pleasant to see...
Do you have any idea?
I will be waiting for your help...|||I would check the machine application/system logs to see any errors related FullText resource and search service.

How to differentiate process instances in perfmon

In a multi-instance cluster, how can you match a named sql server instance
with an instance of sqlservr.exe? When setting up counter collection in
perfmon, I choose the "process" performance object, then the "% processor
time" counter". In list of instances, I can see sqlservr#1, sqlservr#2, etc.
How do I know which named instance each of those represents?
TIA.
Hello
One way (I dont know if is the best way) is to match the ProcessID on
Process Counter from Performance Monitor to the ProcessID that is on the
ErrorLog from the Instance.
Something like:
PerfMon -> Process -> ProcessID -> Select SQLServer# on the instances.
Now go to SQL Server Enterprise Manager for each instance and look on the
ErrorLog for the line:
Server Process ID is 3636.
That should be easy if you need to do just on time.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
"Denise" wrote:

> In a multi-instance cluster, how can you match a named sql server instance
> with an instance of sqlservr.exe? When setting up counter collection in
> perfmon, I choose the "process" performance object, then the "% processor
> time" counter". In list of instances, I can see sqlservr#1, sqlservr#2, etc.
> How do I know which named instance each of those represents?
> TIA.
|||That's a good idea, thanks for the suggestion.
I don't have access to the error log on all the servers, but I can use
SELECT SERVERPROPERTY ('processid')
"Carlos Eduardo Selonke de Souza" wrote:
[vbcol=seagreen]
> Hello
> One way (I dont know if is the best way) is to match the ProcessID on
> Process Counter from Performance Monitor to the ProcessID that is on the
> ErrorLog from the Instance.
> Something like:
> PerfMon -> Process -> ProcessID -> Select SQLServer# on the instances.
> Now go to SQL Server Enterprise Manager for each instance and look on the
> ErrorLog for the line:
> Server Process ID is 3636.
> That should be easy if you need to do just on time.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
>
> "Denise" wrote:

How to determine the number of cluster for the my clustering models?

Hi, all here,

Thank you very much for your kind attention.

Is there any good way to determine the number of clusters for the clustering models?

Really looking forward to hearing from you for your guidance.

Thanks a lot.

With best regards,

Yours sincerely,

If you set the CLUSTER_COUNT parameter to 0, then the algorithm will try to determine automatically the number of clusters.

Also, you could try to set CLUSTER_COUNT to a large value (say, 30). The algorithm will start looking for 30 clusters, then merge those that are really close, so the result will be less or equal to 30, and will give you an idea of how many actual clusters seem to exist in your data. You can also use the MINIMUM_SUPPORT parameter to add further restrictions to the clusters detected by the algorithm

|||

Hi, Bogdan, thank you very much for your guidance.

Best regards,

How to determine the best setting for Max Worker Threads

Dear All,
I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
environment. Right now, I am using the default 255 on Max Worker Threads, but
based on my analysis, during peak hour, the number of connection is around
300, is that better to set the max worker threads to a higher value e.g. 350?
What will happen with I set it to 510 instead? Many thanks!
From,
HenryEach thread takes a small amount of memory, even if it is not being used. I
don'tremember how much ( maybe 1k). The best practice is NOT to set it
arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
max connections occasionally. or alert on max connections > 310 so you will
know if you need to bump it again...
If these are not heads-down data entry people, it may even be OK for them to
thread share...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> Dear All,
> I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> environment. Right now, I am using the default 255 on Max Worker Threads,
but
> based on my analysis, during peak hour, the number of connection is around
> 300, is that better to set the max worker threads to a higher value e.g.
350?
> What will happen with I set it to 510 instead? Many thanks!
> From,
> Henry|||IIRC, each worker thread uses closer to 384k of RAM.
I agree with Wayne. Use the 255 right now and use System Monitor and/or
Profiler and get a baseline value. Then bump it up to around 300 and check
the new values against your baseline. With a little tweaking here and
there, you should be able to come up with a reasonable value.
Rick Sawtell
MCT, MCSD, MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OGiSzYesEHA.3580@.TK2MSFTNGP10.phx.gbl...
> Each thread takes a small amount of memory, even if it is not being used.
I
> don'tremember how much ( maybe 1k). The best practice is NOT to set it
> arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
> max connections occasionally. or alert on max connections > 310 so you
will
> know if you need to bump it again...
> If these are not heads-down data entry people, it may even be OK for them
to
> thread share...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker
Threads,
> but
> > based on my analysis, during peak hour, the number of connection is
around
> > 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>|||Just because ht enumber of connection is over 255 does not in any way mean
you need more worker threads. Take a look at
dbcc sqlperf(UMSSTATS) and see how they are being used now.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> Dear All,
> I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> environment. Right now, I am using the default 255 on Max Worker Threads,
> but
> based on my analysis, during peak hour, the number of connection is around
> 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> What will happen with I set it to 510 instead? Many thanks!
> From,
> Henry|||Oops...
Thanks Andrew... I meant connections, not worker threads.
In fact, if I remember correctly, we demo'd a 4 processor SQL 7.0 instance a
number of years ago and found that tuning the worker threads down to
somewhere around 12 gave us the best performance...
Rick
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OLaR4thsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker
Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is
around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>|||Dear Andrew,
Thanks for your advise. Would you please kindly explain how to use dbcc
sqlperf(UMSSTATS) to determine the best setting? Coz as far as I know from
web article, we can use the "num runnable" to determine whether there is
bottleneck on CPU, if always 0, then is healthy, otherwise, may have
bottleneck. For my case, the current default worker thread setting 255, give
me 0 or 1 most of the time. Is that my goal is to adjust until the "num
runnable" always on 0? Please advise, many thanks!
From,
Henry :)
"Andrew J. Kelly" wrote:
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>|||This is a sample output for the UMSSTATS. Your question is whether you
should bump up the max worker threads. The default is a maximum of 255 and
they get evenly divided between the processors assigned to SQL Server. In
your case that is 4 processors and that means the number of workers for each
processor can be over 60. But it will rarely get even close to that on a
system that is not extremely busy. The key is to look at the "idle workers"
size in realation to the "num workers". In your case if the value is always
0 or 1 you are hardly pushing the systems ability to work with what it has.
You are seeing normal behavior.
Scheduler ID 0.0
num users 18.0
num runnable 0.0
num workers 13.0
idle workers 11.0
work queued 0.0
cntxt switches 2.2994396E+7
cntxt switches(idle) 1.7793976E+7
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:E13223AE-6F93-4D5E-8082-472E73FA6585@.microsoft.com...
> Dear Andrew,
> Thanks for your advise. Would you please kindly explain how to use dbcc
> sqlperf(UMSSTATS) to determine the best setting? Coz as far as I know from
> web article, we can use the "num runnable" to determine whether there is
> bottleneck on CPU, if always 0, then is healthy, otherwise, may have
> bottleneck. For my case, the current default worker thread setting 255,
> give
> me 0 or 1 most of the time. Is that my goal is to adjust until the "num
> runnable" always on 0? Please advise, many thanks!
> From,
> Henry :)
> "Andrew J. Kelly" wrote:
>> Just because ht enumber of connection is over 255 does not in any way
>> mean
>> you need more worker threads. Take a look at
>> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
>> > Dear All,
>> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
>> > environment. Right now, I am using the default 255 on Max Worker
>> > Threads,
>> > but
>> > based on my analysis, during peak hour, the number of connection is
>> > around
>> > 300, is that better to set the max worker threads to a higher value
>> > e.g.
>> > 350?
>> > What will happen with I set it to 510 instead? Many thanks!
>> > From,
>> > Henry
>>|||How do you know how many connections so that i can monitor this too.
thanks
"Wayne Snyder" wrote:
> Each thread takes a small amount of memory, even if it is not being used. I
> don'tremember how much ( maybe 1k). The best practice is NOT to set it
> arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
> max connections occasionally. or alert on max connections > 310 so you will
> know if you need to bump it again...
> If these are not heads-down data entry people, it may even be OK for them to
> thread share...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>|||> How do you know how many connections so that i can monitor this too.
> thanks
You can monitor User Connections using Performance Monitor. You can also
get the current number of user connections with the following query:
SELECT cntr_value
FROM master..sysperfinfo
WHERE
object_name = 'SQLServer:General Statistics' AND
counter_name = 'User Connections'
Keep in mind that threads are pooled so you can have thousands of database
connections without the need to increase max worker threads. Connections
are often waiting for work and are not using a thread. Personally, I
wouldn't consider increasing the default unless the 'working thread limit of
255 has been reached' in appears in the error log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:AFDCBDA1-9559-40E6-97A6-B0F52053920C@.microsoft.com...
> How do you know how many connections so that i can monitor this too.
> thanks
> "Wayne Snyder" wrote:
>> Each thread takes a small amount of memory, even if it is not being used.
>> I
>> don'tremember how much ( maybe 1k). The best practice is NOT to set it
>> arbitrarily high... In your case, I'd set it to 300 or 310. and watch
>> for
>> max connections occasionally. or alert on max connections > 310 so you
>> will
>> know if you need to bump it again...
>> If these are not heads-down data entry people, it may even be OK for them
>> to
>> thread share...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
>> > Dear All,
>> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
>> > environment. Right now, I am using the default 255 on Max Worker
>> > Threads,
>> but
>> > based on my analysis, during peak hour, the number of connection is
>> > around
>> > 300, is that better to set the max worker threads to a higher value
>> > e.g.
>> 350?
>> > What will happen with I set it to 510 instead? Many thanks!
>> > From,
>> > Henry
>>|||So if i run the dbcc sqlperf(umsstats) command say every hour i see how many
threads are in progress at any time......if reaching 255 at any one time
then i look at whether or not to change it
Thanks i have a look
"Andrew J. Kelly" wrote:
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>sql

How to determine a physical node name with a sql cluster?

I am trying to determine what physical node my sql server cluster is running
on from within SQL Server. How can I do that?
For example. In query analyzer
select @.@.servername
- returns the virtual name.
Select CONVERT(Varchar(25), SERVERPROPERTY('machinename'))
- returns the virtual name.
"Charles" wrote:

> I am trying to determine what physical node my sql server cluster is running
> on from within SQL Server. How can I do that?

How to determine a physical node name with a sql cluster?

I am trying to determine what physical node my sql server cluster is running
on from within SQL Server. How can I do that?For example. In query analyzer
select @.@.servername
- returns the virtual name.
Select CONVERT(Varchar(25), SERVERPROPERTY('machinename'))
- returns the virtual name.
"Charles" wrote:
> I am trying to determine what physical node my sql server cluster is running
> on from within SQL Server. How can I do that?

How to determine a physical node name with a sql cluster?

I am trying to determine what physical node my sql server cluster is running
on from within SQL Server. How can I do that?For example. In query analyzer
select @.@.servername
- returns the virtual name.
Select CONVERT(Varchar(25), SERVERPROPERTY('machinename'))
- returns the virtual name.
"Charles" wrote:

> I am trying to determine what physical node my sql server cluster is runni
ng
> on from within SQL Server. How can I do that?