Monday, January 31, 2011

System Base Tables and Internal Views

System base tables are the underlying tables that actually store the metadata for a specific database.
\These tables contain persisted metadata that has server-wide scope. The Resource database contains the same base table schema as any other non-master database. However, because the Resource database is not visible, users cannot use or connect to it.
To bind to a system base table, a user must connect to the instance of SQL Server by using the dedicated administrator connection (DAC). 

sys.sysschobjs Exists in every database. Each row represents an object in the database.

sys.sysidxstats Exists in every database. Contains a row for each index or statistics for tables and indexed views
NoteNote
Every index (except heap) is associated with a statistic that has the same name as the index.
sys.sysiscols Exists in every database. Contains a row for each persisted index and statistics column.
sys.sysdbreg Exists in the master database only. Contains a row for each registered database.
sys.sysdbfiles Exists in every database. If the column dbid is zero, the row represents a file that belongs to this database. In the master database, the column dbid can be nonzero. When this is the case, the row represents a master file.
sys.sysallocunits Exists in every database. Contains a row for each storage allocation unit.

Some good information about each table is here:
http://msdn.microsoft.com/en-us/library/ms179503.aspx

Information schema views do not contain metadata specific to SQL Server 2008.

There is some information about System Internal Views

System internal views are system views that make available low-level storage metadata for the SQL Server Database Engine
View name Description
sys.system_internals_partitions Contains a combination of rowset and hobt information. There is a one-to-one correspondence between rowset and hobt.
sys.system_internals_partition_columns Contains both rowset and hobt column information. It also contains type and other information redundantly copied from the catalog of the relational engine.
sys.system_internals_allocation_units Contains a row for each allocation unit in the current database.

Got the above from MSDN:
http://msdn.microsoft.com/en-us/library/ms189600.aspx
Connecting to DAC is not a straightforward approach.
After some googling and some frustrated attempts, I found this.

1) Stop sql server
2) in the sql server configuration tool, go to properties of sql server service-> advance tab-> add the startup options "-f" for speedy recovery and "-m" for single mode.
3) start sql server
4) connect to your instance like "Admin:server" in the management studio and now you can run your queries against all the meta data tables and views.

There is however another way to execute commands using DAC connection.
eg:
sqlcmd -Sserver1 -A -E -Qselect * from sys.system_internals_allocation_units
 

sys.data_spaces

I've just discovered the use of sys.data_spaces.
This'll give you the data files' information in your database.

use test
go
select * from sys.data_spaces

output:

namedata_space_idtypetype_descis_default
PRIMARY1FGROWS_FILEGROUP1
SQLIndex_22FGROWS_FILEGROUP0
SQLIndex_13FGROWS_FILEGROUP0
SQLData_14FGROWS_FILEGROUP0
SQLData_25FGROWS_FILEGROUP0
SQLData_36FGROWS_FILEGROUP0
SQLData_47FGROWS_FILEGROUP0



Sunday, January 30, 2011

Encryption

We have a very good inbuilt function to encrypt any text strings in sql server.

select pwdencrypt('yourname')

Here is how to get sql server login passwords in the encrypted format

SELECT name,CONVERT(VARBINARY(256), password) FROM syslogins
But unfortunately Microsoft hasn't provided anything to do the other way, decrypting the encrypted strings.

This is especially useful when you forget your sql server logins' passwords.

Thursday, January 27, 2011

SQL Server 2005 Failover Cluster Setup issues

I was installing 2005 failover cluster y'day and have gone through some troubles.

1) During the setup, we need to enter the sql server virtual name and the virtual ip but I when I was trying to enter the name, it says it already exists.
Then I found that my windowns admin has laready created the "sql cluster name" and "sql server IP" in the sql server resource group in the cluster administrator.

2) So I deleted both of them and then entered those details, what sql server does is that it creates the virtual name and the IP in the domain so the name and ip should be unique and shouldn't be used anywhere else.

3) Then again before the installation was started, I was repeatedly getting the below strange error:
"setup failed to start on the remote machine. check the task scheduler event log on the remote machine"
I checked the sql server setup log as indicated in the path but couldn't find anything.
I've gone mad for some time as tried to install repeatedly for 3 unsuccessful times and later with the help of google found that you shouldn't be logging into the other node when you are installing sql server
I had logged off and then proceeded with the installation and it went fine.

4) There is another option you need to give after you give your sql server virtual name
it says something like this.
"add your domain admin group under which your cluster service account runs".
This name you'll get from your windows admin.
I've mentioned a domain group under which all service accounts in your organization are part of.

5) After installation, I checked the sql server resource group and could see "sql server IP" , "sql server cluster name", sql server, sql server full text search, sql server agent.

6) I've tested the failover and all seems good but there was one more issue coming through when I tried to restore the database

Msg 5184, Level 16, State 2, Line 2
Cannot use file 'g:\Microsoft SQL Server\MSSQL\DATA\test_data.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Then I verified the dependencies for the resource "sql server" in the cluster and found only F drive added.
So I quickly added my remaining two cluster drives G and H and then was able to restore it successfully.

7) One more error you would face if setup fails once and you are trying to re install.
Below is the error:

Product: Microsoft SQL Server Native Client -- Error 1706. An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.

This is because, once the setup fails once and you are trying to do the installation again, it fails as it tries to re install the client components. The solution is that you should remove the sql server client components from add remove programs and then start the setup all over again.

It was overall  a good experience.

Wednesday, January 19, 2011

Dynamic Management Views

I am going through DMVs in 2008 and its astounding, there are too many views and functions that its difficult to remember.

Here are some I picked up seeing the names, its best to left to the readers or to me whenever I get a chance to browse through the list.

select * from sys.dm_db_task_space_usagego
select
* from sys.dm_db_session_space_usagego
select
* from sys.dm_exec_query_memory_grantsgo
select
* from sys.dm_exec_query_optimizer_infogo
select
* from sys.dm_exec_cached_plansgo
select
* from sys.dm_exec_connectionsgo
select
* from sys.dm_exec_query_plango
select
* from sys.dm_exec_query_statsgo
select
* from sys.dm_exec_text_query_plango
select
* from sys.dm_io_pending_io_requestsgo
select
* from sys.dm_os_memory_cache_countersgo
select
* from sys.dm_os_memory_objectsgo
select
* from sys.dm_os_memory_allocationsgo
select
* from sys.dm_os_memory_poolsgo
select
* from sys.dm_os_performance_countersgo
select
* from sys.dm_os_ring_buffersgo
select
* from sys.dm_os_threadsgo
select
* from sys.dm_os_wait_statsgo
select
* from sys.dm_tran_locksgo
select
* from sys.dm_tran_active_transactionsgo

Thursday, January 13, 2011

dbcc pintable

Syntax
DBCC PINTABLE ( database_id , table_id )


This was discontinued from sql server 2005 but can use in 2000.
Here is some info I got from MSDN:

DBCC PINTABLE does not cause the table to be read into memory. As the pages from the table are read into the buffer cache by normal Transact-SQL statements, they are marked as pinned pages. SQL Server does not flush pinned pages when it needs space to read in a new page. SQL Server still logs updates to the page and, if necessary, writes the updated page back to disk. SQL Server does, however, keep a copy of the page available in the buffer cache until the table is unpinned with the DBCC UNPINTABLE statement.
DBCC PINTABLE is best used to keep small, frequently referenced tables in memory. The pages for the small table are read into memory one time, then all future references to their data do not require a disk read

documented/undocumented Trace flags

Trace Flag 3231 (same as 2000): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG do not allow a log backup to run if the database’s recovery model is FULL or BULK_LOGGED.

Trace Flag 3031 (new in 2005): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG run as a CHECKPOINT – regardless of recovery model.

Skip Automatic Recovery (3607)
Trace flag 3607 skips the recovery of databases on the startup of SQL Server and clears the TempDB. Setting this flag lets you get past certain crashes, but there is a chance that some data will be lost.


Skip Automatic Recovery Except Master (3608)
This trace is similar to 3607, but the TempDB in this case is not cleared and only the master database is recovered.


Log Record for Connections (4013)
This trace flag writes an entry to the SQL Server error log when a new connection is established. If you set this option, your error log can fill up quickly. For each connection that occurs, the trace flag writes two entries that look like this:

Login: sa saSQL Query Analyzer(local)ODBCmaster, server
process ID (SPID): 57, kernel process ID (KPID): 57.
Login: sa XANADUsaSQL Query Analyzer(local)ODBCmaster,
server process ID (SPID): 57, kernel process ID (KPID): 57.


Network Database Files (1807)
SQL Server will not allow you to create a database or log file on a networked drive by default. If you attempt to do this, you receive error 5105, which states "Device Activation Error." The 1807 trace flag provides a workaround for this restriction, and you can create database files on a mapped drive or UNC path


Just found two more Trace flags:

3213 - outputs the Backup Buffer configuration information.
3014 - outputs additional information about Backup and File operations
3226 - suppress the backup log entries in the errorlog

For some interesting trace flags which are also hotfixes, check the below link:
http://support.microsoft.com/kb/974006

We all are aware of deadlock trace flags:
here they are from MSDN
1204:
Returns the resources and types of locks participating in a deadlock and also the current command affected

1222
Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

1211:
by enabling this trace flag, lock escalation will be disabled based on memory pressure or the number of locks. So all the page and ro level locks won't be escalated to table level and these could potentially lead to out of locks errors.
1224: 
this will disable lock escalation based on number of locks only. lock escalation can still happen if there is memory pressure.
this will happen only if the amount of memory used by the locks exceed
1) 40% of database engine memory (if locks parameter is set to o in sp_configure)
2) 40% of lock memory

2528
Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE

4616
Makes server-level metadata visible to application roles.

7806
Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express.

There are hundreds of them and I just discovered from the below site:
http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

Tempdb contention

Found a very good article on Tempdb Contention from Paul.
Here is the link to the post:
Storage Engine GAM/SGAM

Bug in sql server 2000 sp4 when using AWE

If Address Windowing Extentions (AWE) support is enabled, a single instance of SQL Server 2000 can only use a maximum of 50 percent of the physical memory that is on the computer.

Note This problem only occurs on 32-bit versions of Microsoft SQL Server 2000 Service Pack 4 that are running on either an x86-based computer or on an x64-based computer

Refer the below article in Microsoft
http://support.microsoft.com/kb/899761

Wednesday, January 12, 2011

In Memory Database

I am going through In Memory Database concepts of Sybase.
Have got the below paper.
Sybase In Memory Database

One more good article on In Memory Database

http://www.mcobject.com/in_memory_database