Discover Method Part 1

DISCOVER METHODS
They are very rich source for getting meta data information for the server.
Lets look for more detail on DISCOVER METHODS





Provides information for commands

SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_COMMANDS
)

Provides resource usage information for current running or last executed command.From command_text column we can get query for the session.

You can get more information from here

http://msdn.microsoft.com/en-us/library/bb934103.aspx

Provides information for connections.


SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_CONNECTIONS
)

Provides information related to connection.
Useful column will be connection_host_name which will provide ip of the connection, connection host application.

You can get more information from here

http://msdn.microsoft.com/en-us/library/bb934105.aspx

Provides information for datasources


SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_DATASOURCES
)



But this one will not give results using select command.

You can get the results with using DISCOVER method.



<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Header />
<Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList />
</Restrictions>
<Properties>
<PropertyList />
</Properties>
</Discover>
</Body>
</Envelope>


You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126129.aspx

To get information for current instance

SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_INSTANCES
,INSTANCE_NAME='sseight'
)

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126129.aspx


Provides information for the active jobs running on the server

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_JOBS
)

You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934102.aspx


Provides information for all keywords reserved by provider

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_KEYWORDS
)


You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126063.aspx

Provides information for locks

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_LOCKS
)



SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_LOCKS,
LOCK_STATUS ='1'
)

-- 0 Granted locks
-- 1 Waiting locks
Another useful column in lock_type

You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934104.aspx

Provides information for memory usage by objects


SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
)
ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC

You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934098.aspx

Provides information about the standard and provider-specific properties

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_PROPERTIES
)
You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126045.aspx

2 comments:

Anonymous said...

Thanks for posting this. I'm learning Analysis Services right now and have hunted in vain for months for documentation on doing all the things you've explained here. I was especially interested in the performance counters. Thanks again.

SQLServerSteve

Alex Henry Hayes V. said...

Hi, How could I debug on MSQL 2008 when the Discover Procedure request was made via soap?.

Post a Comment

Popular Posts