How get some informations about MSSQL (options , memory...)?

Question

How get some informations about MSSQL (options , memory...)?

OracleBoy

Right there with Babe

Points: 743
More actions
July 24, 2013 at 8:41 am

#276239

Hi everybody ,
I need help :
1) I want to know the value of options "Partitioning" and "Bit-Mapped" (In oracle , informations are iin v$option table)
An equivalence to MSSQL ?
2) Information about the memory architecture of the DB ( In Oracle the equivalence is SGA : v$sga ....)
I'm new in sql server administration , so sorry :/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

OracleBoy (7/24/2013)
1) I want to know the value of options "Partitioning" and "Bit-Mapped" (In oracle , informations are iin v$option table)

There are no such options in SQL Server.

2) Information about the memory architecture of the DB ( In Oracle the equivalence is SGA : v$sga ....)

Specifically what are you looking to find out?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OracleBoy Right there with Babe Points: 743 More actions · Answer 2

Hi thk for the reply ,

I want to know the size attribute to pools , memory , cache ...

It's not possible to know if user can create for example partitioned table ? ...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

OracleBoy (7/24/2013)
Hi thk for the reply ,
I want to know the size attribute to pools , memory , cache ...

It's all dynamic. Again, what are you trying to find out? What problem are you trying to solve?

If you want to look at the current allocations, query sys.dm_os_memory_clerks, but it's not very interesting unless you're trying to work something specific out.

It's not possible to know if user can create for example partitioned table ? ...

As long as you're using Enterprise edition, a user who has enough permissions can create a partitioned table, there are no settings that need to be enabled to allow that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OracleBoy Right there with Babe Points: 743 More actions · Answer 4

I want create a script to gather some information about a database:

For now I have just : information about datafile (size , size used , free size , the version of MSSQL , the list of users...)

Some ideas about others "general" informations ?

Thk 🙂

arnipetursson SSCertifiable Points: 6557 More actions · Answer 5

Are you trying to gather info on databases or database instances?

OracleBoy Right there with Babe Points: 743 More actions · Answer 6

Hi i want information about database ( table > 30 Mo ...) and some general information about database instances :

I want create an information sheet for every server

Eric M Russell SSC Guru Points: 125565 More actions · Answer 7

OracleBoy (7/24/2013)
Hi thk for the reply ,
I want to know the size attribute to pools , memory , cache ...
It's not possible to know if user can create for example partitioned table ? ...

The equivalent of Oracle's system virtual tables in SQL Server are data managmenet views (DMV).

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

Glenn Berry maintains some good scripts on his website for querying configuration type information from DMVs.

http://sqlserverperformance.wordpress.com/2013/07/09/sql-server-diagnostic-information-queries-for-july-2013/

There are also some functions for returning information on role based security, object permissions, etc.

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

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho