Useful queries tips for All – SQL user to SQL DBA (SQL Server 2005)

 Useful queries  tips for All – SQL user to SQL DBA (SQL Server 2005)

You might be aware with SQL-Server query. You might even know simple T-SQL query but sometime in order to get some specific results, we need to use some special query. Here are some of them. Use it and enjoy the power of Microsoft SQL Server

 

This one is for getting list of all available objects in current database with schema name.

select s.name As ‘Schema’ , so.name as ‘Object’

from

sys.objects AS so

JOIN

sys.schemas as s

ON

s.schema_id=so.schema_id

where so.type<>‘S’

Order by s.name ASC

 

If you wish to get see how much space have been used by which object in your current database, you can use following query:

select 

      OBJECT_NAME(object_id) as ObjectName,

      SUM (reserved_page_count) * 8192/ 1024 as Reserved_In_KB,

      SUM(used_page_count) * 8192 / 1024 as Used_In_kb

from

      sys.dm_db_partition_stats

group by

      OBJECT_NAME(object_id)

 

Sometime it is useful to see what kind of rights you have on server wide. You can use following query for same. It will give you list of permission you have on SQL-Server based on user account by which you have logged in.

USE AdventureWorks

GO

SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

GO

 

Sometime it is useful to see what kind of rights you have in database. You can use following query for same. It will give you list of permission you have in database based on user account by which you have logged in.

USE AdventureWorks

GO

SELECT * FROM fn_my_permissions(NULL, ‘DATABASE’);

GO

 

Following query will be used to see buffer cache hit ratio. It is something like how efficiently your pages are residing in buffer.  More you are closure to 100%, good you are utilizing your buffer cache.

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BCHitRatio]

FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters 

        WHERE counter_name = ‘Buffer cache hit ratio’

          AND object_name = ‘SQLServer:Buffer Manager’) a 

     JOIN

     (SELECT *, 1 x FROM sys.dm_os_performance_counters 

        WHERE counter_name = ‘Buffer cache hit ratio base’

          and object_name = ‘SQLServer:Buffer Manager’) b

      ON

      a.object_name=b.object_name

 

 

 

If you wish to see the see all network instance of SQL-Server 2005. You can use following command from command prompt.

Osql –L

 

Reference: Ritesh Shah