Dear all,
I want to know all the possible values for the status field bring up for
sysprocesses table. Values such 'running' or 'sleeping' seems very
evident but there is one so-called 'DEF-WK...' or something like that which
I haven't idea.
In this occasion I am not be able to find it inside the BOL
Does anyone know how do I figure out such values?
Thanks in advance,
EnricHi
Look at the code from the system SP sp_who2
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:C9B83081-19A4-4A33-AD4C-F75AE450B394@.microsoft.com...
> Dear all,
> I want to know all the possible values for the status field bring up for
> sysprocesses table. Values such 'running' or 'sleeping' seems very
> evident but there is one so-called 'DEF-WK...' or something like that
> which
> I haven't idea.
> In this occasion I am not be able to find it inside the BOL
> Does anyone know how do I figure out such values?
> Thanks in advance,
> Enric
Showing posts with label status. Show all posts
Showing posts with label status. Show all posts
Monday, March 26, 2012
Info about a running job step
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
QuentinYou can use sp_help_job e.g.
exec msdb..sp_help_job
@.job_name = 'jobname ,
@.job_aspect = 'JOB'
Look at the current_execution_status and current_execution_step columns
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message
news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
Quentin|||Thanks Mr. Dentist.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efTLfj4VDHA.1816@.TK2MSFTNGP09.phx.gbl...
> You can use sp_help_job e.g.
> exec msdb..sp_help_job
> @.job_name = 'jobname ,
> @.job_aspect = 'JOB'
> Look at the current_execution_status and current_execution_step columns
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi group,
> I need to retrieve information about a running step, specifically its
> status. When a step is being executed, the EM always knows and shows so
> (under status showing Executing Job Step 'n (step name)'). However, I can
> not find such information in the system tables. where does EM get the
info
> from? How can I access this info with a query, or better yet, within a
> stored proc? Help is appreciated.
> Quentin
>
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
QuentinYou can use sp_help_job e.g.
exec msdb..sp_help_job
@.job_name = 'jobname ,
@.job_aspect = 'JOB'
Look at the current_execution_status and current_execution_step columns
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message
news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
Quentin|||Thanks Mr. Dentist.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efTLfj4VDHA.1816@.TK2MSFTNGP09.phx.gbl...
> You can use sp_help_job e.g.
> exec msdb..sp_help_job
> @.job_name = 'jobname ,
> @.job_aspect = 'JOB'
> Look at the current_execution_status and current_execution_step columns
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi group,
> I need to retrieve information about a running step, specifically its
> status. When a step is being executed, the EM always knows and shows so
> (under status showing Executing Job Step 'n (step name)'). However, I can
> not find such information in the system tables. where does EM get the
info
> from? How can I access this info with a query, or better yet, within a
> stored proc? Help is appreciated.
> Quentin
>
Wednesday, March 21, 2012
Indexing Question
Hi Gurus,
I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.
Now, the question is should I create an index for this Status column? Will it improve the performance?
Thanks.I would say NO. One of the criteria for creating a good index is selectivity. So your index on a booleon column would not help the performance. In addition, it is just an overhead on the inserts.
- CB|||Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||I agree with Brett. In the situation that he described (covered indexes), it could be helpful to tag that column at the end of the composite index to avoid another trip to get the data.
- CB
Originally posted by Brett Kaiser
Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||In this situation, adding status to the index will not help, unless Brett thinks otherwise.
- CB
Originally posted by shekarnarayanan
Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards...|||Agreed, just try it. Set up a test/dev environment. Run query before index added, look at query execution plan, apply index and look once again at query execution plan. It will help.|||Well, I tried as suggested and the execution plan does not seem to use the new index at all! It just uses the clustered PK index. So I guess the answer is NO to the new index.
Thanks for all the suggestions.|||SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?|||You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.
blindman|||Originally posted by Brett Kaiser
SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?
Hi Brett,
Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.|||On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.
I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.
Now, the question is should I create an index for this Status column? Will it improve the performance?
Thanks.I would say NO. One of the criteria for creating a good index is selectivity. So your index on a booleon column would not help the performance. In addition, it is just an overhead on the inserts.
- CB|||Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||I agree with Brett. In the situation that he described (covered indexes), it could be helpful to tag that column at the end of the composite index to avoid another trip to get the data.
- CB
Originally posted by Brett Kaiser
Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||In this situation, adding status to the index will not help, unless Brett thinks otherwise.
- CB
Originally posted by shekarnarayanan
Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards...|||Agreed, just try it. Set up a test/dev environment. Run query before index added, look at query execution plan, apply index and look once again at query execution plan. It will help.|||Well, I tried as suggested and the execution plan does not seem to use the new index at all! It just uses the clustered PK index. So I guess the answer is NO to the new index.
Thanks for all the suggestions.|||SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?|||You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.
blindman|||Originally posted by Brett Kaiser
SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?
Hi Brett,
Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.|||On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.
Friday, March 9, 2012
Indexes question
Hi Gurus,
When I query a table using sp_MShelpindex in one of my databases I see
following output:-
Name Status Indid
pk_ACTIONS 18450 1
_WA_COMPLETE_6FB49575 8388704 2
Please tell me what are these %_WA_% objects. Are these statistics or
automatically generated indexes created by index tuning wizard?
Thanks in advance
ManuThe "indexes" with names in that format are not indexes, just
statistics. They are not created by the tuning wizard, but they SQL
Server in normal operation. Simply ignore them and you will be fine.
Roy Harvey
Beacon Falls, CT
On Wed, 19 Sep 2007 13:04:04 -0700, manu
<manu@.discussions.microsoft.com> wrote:
>Hi Gurus,
>When I query a table using sp_MShelpindex in one of my databases I see
>following output:-
>Name Status Indid
>pk_ACTIONS 18450 1
>_WA_COMPLETE_6FB49575 8388704 2
>Please tell me what are these %_WA_% objects. Are these statistics or
>automatically generated indexes created by index tuning wizard?
>Thanks in advance
>Manu
When I query a table using sp_MShelpindex in one of my databases I see
following output:-
Name Status Indid
pk_ACTIONS 18450 1
_WA_COMPLETE_6FB49575 8388704 2
Please tell me what are these %_WA_% objects. Are these statistics or
automatically generated indexes created by index tuning wizard?
Thanks in advance
ManuThe "indexes" with names in that format are not indexes, just
statistics. They are not created by the tuning wizard, but they SQL
Server in normal operation. Simply ignore them and you will be fine.
Roy Harvey
Beacon Falls, CT
On Wed, 19 Sep 2007 13:04:04 -0700, manu
<manu@.discussions.microsoft.com> wrote:
>Hi Gurus,
>When I query a table using sp_MShelpindex in one of my databases I see
>following output:-
>Name Status Indid
>pk_ACTIONS 18450 1
>_WA_COMPLETE_6FB49575 8388704 2
>Please tell me what are these %_WA_% objects. Are these statistics or
>automatically generated indexes created by index tuning wizard?
>Thanks in advance
>Manu
Subscribe to:
Posts (Atom)