:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Nov 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Server roles for SQL Server

A login may be member of a Server role or database role. Member of server roles have permissions for SQL Server instance level tasks.Following script will show that which SQL Server login is member of a particular server role.

select p.name, p.type_desc, pp.name, pp.type_desc  
 from sys.server_role_members roles  
 join sys.server_principals p on roles.member_principal_id = p.principal_id  
 join sys.server_principals pp on roles.role_principal_id = pp.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Who has access to your database

Once a user is logged in to your SQL Server instance, he may or may not have access to your databases. In order to access a database, SQL Server login of that user must be mapped to database user created inside the database. Use following command to list the logins who have access to your databases. I have used sys.database_principals system view for this information.

 
use DBName  
 GO  
 SELECT UserName = dp.name, UserType = dp.type_desc,  
 LoginName = sp.name, LoginType = sp.type_desc  
 FROM sys.database_principals dp  
 JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Who can logIn to your SQL Server instance

Being a DBA you would like to have information related to access at various levels in SQL Server. Primarily the question is that who can login to your SQL Server instance. Use following command to get list of all log ins who are able/disable to log in to your SQL Server instance. Command will query sys.server_principals system view and will get information about windows logins and SQL Server logins.

SELECT name, type_desc, is_disabled
FROM sys.server_principals

Result will be in following form

Access to SQL Server instance

Also you may apply filter on ‘isdisabled‘ and ‘type_desc‘. In above result set login name sa is disabled so it can not be used for login to SQL Server instance.

  • Nov 05 / 2008
  • 1
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis

System Views in SQL Server 2005 for metadata

System views are new for SQL Server 2005. System views in SQL Server 2005 are designed to expose instance and database related metadata in an organized and useful method. Some system tables from SQL Server 2000 are now implemented in SQL Server 2005 as system views for backward compatibility. Although the SQL Server 2000 system tables can still be queried by name, SQL Server 2005 features and related metadata will not be seen. Thus, the results may be different from those when querying the corresponding new system view.
There are many SQL Server 2005 system tables that do not have system views for accessing data from them, such as the backup and restore history tables. In those cases, the data must be accessed by querying the system table itself.
System base tables are the underlying tables that actually store metadata for a specific database. System base tables are used within SQL Server 2005 Database Engine and are not intended for customer user. Therefore, the system views are provided for accessing that metadata without accessing the base tables.
All of the system objects referenced by the system views are physically persisted in the system base tables stored within the read only system database called Resource. This database is not visible to users and does not appear in SQL Server Management Studio. Users cannot use or connect to it, unless in single-user mode.
All system views are contained in either the INFORMATION_SCHEMA or the sys schemas. Both schemas logically appear in every database.
There are six collections of system views:
  • catalog
  • compatibility
  • dynamic management
  • information schema
  • replication
  • notification services
There are numerous categories of system views within each collection. Following are descriptions of each of the collections:

Catalog views
Catalog views return information that is used by the Database Engine, such as information on objects, databases, files, security, and more. They do not contain information about backups, replication, database maintenance plans, or SQL Server Agent catalog data.

Compatibility views
Compatibility views are provided for backward compatibility only with SQL Server 2000 system tables. They do not expose any SQL Server 2005 new feature metadata, such as partitioning. Use the new catalog views instead.

Dynamic management views (DMVs)
Dynamic management views (DMVs) return information on server state or database state that can be used for monitoring the health of a server instance and databases, and diagnose performance problems. Dynamic management views (DMVs) can be identified by their name, which begins with ”dm_,” plus an abbreviation of what category the DMV is a part, then a description of what the view returns. For example, dm_db_file_space_usage is part of the database category of DMVs and returns information on file space usage.

Information schema views
Information schema views are system views that are part of a separate schema, called INFORMATION_SCHEMA. Returns metadata for database objects in a particular database. All other system views are part of the sys schema.

Replication views
Replication views return information about replication. They are created when a database is configured as a publisher or subscriber, and different views are created in the different databases: msdb, distribution, publisher database, and subscriber database. Otherwise, these views will not exist. Using replication stored procedures is still a good way to access replication metadata.

Notification services views
Notification services views return instance and application data specifically related to Notification Services; designed to help with debugging, tracking, or troubleshooting.

  • Oct 27 / 2008
  • 0
dbDigger, Monitoring and Analysis, SQL Server IO, Storage

Characteristics for I/O performance monitoring for SQL Server Databases

Several factors are involved in performance of disk drive having your data files. Along with typical characteristics of disk, OS , Drivers and network over head also contribute to performance. I have list the major factors here.

Click here to read all posts related to SQL Server storage systems and I/O operations

  • Oct 17 / 2008
  • 0
dbDigger, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Clustered Indexes creates memory bottlenecks

Mostly indexes are encouraged to boost the performance. But in case of very large tables we should consider another dimension also. Incorrect use of indexing is most common root cause of memory pressure. Along with growing size of database, DBA should look forward to remove clustered indexes from large tables.The negative result is that by changing the clustered primary keys to nonclustered primary keys, you’ll increase the amount of load on the disk subsystem. But main achievement will be in case of memory.
As clustered index contains the entire table, when the clustered index is loaded into memory, much more memory is used than loading a non-clustered version of the index. This will dramatically reduce your memory requirements for this table object.
Consider a very wide table (average of 250 bytes per row) that has a total of 65 GB of data. It means that the clustered index is also 65 GB in size. By changing the primary key from a clustered index to a nonclustered index, the index space requirements drop from 65 GB to approx 1 GB (including padding). This greatly reduces the amount of storage required, and when doing index scans against the primary key of the table, only 1 GB of data has to be loaded into memory – which means the data can be left in memory much longer.In common case if a production server has only 16 GB of RAM installed, so having a 63 GB index in memory is not an effective use of memory. By making these changes to the primary key, disk load increased a small percentage. But memory load will decrease dramatically allowing SQL Server to keep data in cache much longer, while also allowing SQL Server to cache other objects into memory as well.

SQL Server Memory Bottleneck

Consult us to explore the Databases. Contact us