SQL Server useful hints for Oracle and Sybase DBAs

1. Some System Catalog tables to get familiar with:

Sysdatabases - Contains one row for each database on Microsoft® SQL Server™. When SQL Server is initially installed, sysdatabases contains entries for the master, model, msdb, mssqlweb, and tempdb databases. This table is stored only in the master database.

Sysdevices - Contains one row for each disk backup file, tape backup file, and database file. This table is stored only in the master database.

Sysfiles - Contains one row for each file in a database. This system table is a virtual table; it cannot b updated or modified directly.

Sysindexes - Contains one row for each index and table in the database. This table is stored in each database.

Syslogins - Contains one row for each login account.

Sysobjects - Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

Sysprocesses - The sysprocesses table holds information about processes running on Microsoft® SQL Server™. These processes can be client processes or system processes. sysprocesses is stored only in the master database.

2. Filegroups:

Database files can be grouped together in filegroups for allocation and administration purposes. Some systems can improve their performance by controlling the placement of data and indexes onto specific disk drives. Filegroups can aid this process. The system administrator can create filegroups for each disk drive, then assign specific tables, indexes, or the text, ntext, or image data from a table, to specific filegroups.

These are what Sybase calls database segments. In MS SQL Server you can backup, restore and run DBCC’s on individual filegroups of a database.

3. Indexes

Clustered indexes have one row in sysindexes with indid = 1. The pages in the data
chain and the rows in them are ordered on the value of the clustered index key.
No more than one clustered index per table.

When the clustered index is rebuild, all non clustered index get automatically rebuild.

4. xp_cmdshell command:

Xp_cmdshell - Executes a given command string as an operating-system command i.e. 1>xp_cmdshell dir

*** This is considered a security risk. When allowed. ***

5. Statistics

statistics - Statistical information can be used by the query processor to determine the optimal strategy for evaluating a query. When you create an index, SQL Server automatically stores statistical information regarding the distribution of values in the indexed column(s). The query optimizer in SQL Server uses these statistics to estimate the cost of using the index for a query. Additionally, when the AUTO_CREATE_STATISTICS database option is set to ON (default), SQL Server automatically creates statistics for columns without indexes that are used in a predicate.

sp_autostats - Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.

The following commands can help you determine if a index needs to be rebuild.

DBCC show statistics - Displays the current distribution statistics for the specified target on the specified table. The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not an index is useful to the query optimizer. The results returned are based on distribution steps of the index.

sp_spaceused – If the unused column goes negitave, it is a candidate to check the index.

** When you do a sp_helpindex and see an indexed name _WA_sys_xxx, where xxx is a column name, These are statistics that are generated by SQL server. You can look at these as possible candidates to put and index on.

6. Terms

MSDB database - This is essentially where SQL server stores backup and restore
history. Must have it, one of the four necessary DB’s along with model, tempdb
and master.

SQL Server Agent – Essentially is the process that allows you to schedule periodic activities and notifications on the SQL Servers jobs scheduler.

1433 – MS SQL Server default listening port. On Sybase it was 2025. We usually change this to another port for security purposes.

Tran log - Unlike Sybase, on MS SQL Server 2000 you must have a separate file for the transaction log. It must be separate from the data files

Detach and attach a database- In SQL Server 2000, the data and transaction log files of a database can be detached and then reattached to another server, or even to the same server. Detaching a database removes the database from SQL Server but leaves the database intact within the data and transaction log files that compose the database. These data and transaction log files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached. This makes the database available in exactly the same state it was in when it was detached.

Log Shipping - In SQL Serve 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. You must have Enterprise Edition, otherwise, you can do this yourself manually or with scheduled jobs.

Microsoft Clustering - A cluster is a group of independent computers that work together to run a common set of applications and provide the image of a single system to the client and application. The computers are physically connected by cables and programmatically connected by cluster software. These connections allow computers to use failover and load balancing, which is not possible with a stand-alone computer.

7. Database Placement

A general rule of thumb is to place the data, logs and backups on separate devices or disk drives. In case a drive should go bad.

Oracle Hints:

1. Analyze command. Analyze command serves several purposes in Oracle.

The two features that are most important to remember are:

A. Analyze allows you to update statistics on tables. (i.e. like update statistics

B. Analyze allows you to validate objects. (i.e. like dbcc checktable)
2. If you in log mode, Oracle writes the archive logs to disk when they become full.
If the disk where the archive logs are written is full, Oracle will stop working until
Space is made to right the archive logs.