Systems Engineering and RDBMS

Archive for August, 2008

Database Instant File Initialization

Posted by decipherinfosys on August 30, 2008

Not many people know about Database File Initialization that can be done in SQL Server 2005. This MSDN post mentions it pretty clearly what the benefits are by initializing the data and log files. So, what is the advantage of this feature and how can we make use of this feature are the two questions that come to mind immediately.

The advantages are that when performing operations like database creation, file additions to an existing user database, restoring a database or a filegroup and increasing the size of the database, having database instant file initialization can reduce the time of these operations. This feature skips out the zeroing out of the data on the disk and thus does not overwrite the data written on the disk. The OS allocates the disk space and the contents of the file is written down to the disk. This is available on all editions of SQL Server 2005 on Windows Server 2003 and Windows XP on NTFS filesystem.

But in order to make use of this feature, you would need to configure your instance to take advantage of this feature. Here is what you need to do in order to configure the instance to take advantage of this feature:

1) Open up Local Security Settings after going to Start/All Programs/Administrative Tools/Local Security Policy.

2) The screen that will come up will be this:

3) Double click on “Perform volume maintenance tasks” entry and add the SQL Server Service account or the local group SQLServerMSSQLUser$instancename.

Also remember that this is just for the data files so in case you have a large txn log file, the data file will be initialized instantaneously but not the log file. The log file has to be zeroed out before being written to the disk.

Posted in SQL Server, Windows | Leave a Comment »

SQLTXPLAIN and Diagnostic information for single SQL

Posted by decipherinfosys on August 29, 2008

In Oracle, there are various ways, one can diagnose bad or offensive sql statements using tools like tkprof, AWR reports, trace files, event level tracing etc.  We have covered a lot of those tools in our previous blog posts.

There is one another tool called sqltxplain developed by Carlos Sierra. For collecting detailed diagnostic information about specific single sql statement so that it can be tuned correctly. Apart from explain plan, it gives 10046 and 10053 event tracing, init.ora parameter settings, indexes on the objects used in the query and so many other important CBO statistics. More over it writes all these information into log files and trace files that can be shared with Oracle for further investigation and analysis. In this blog, we will briefly discuss how to use SQLTXPLAIN.

SQLTXPLAIN gets installed into separate schema called SQLTXPLAIN. It can be installed on RAC and on any version greater than 9i.  When it is installed, it will ask for application schema so make sure that installation schema has SELECT_CATALOG_ROLE privilege. In order to run the script to create SQLTXPLAIN schema, one need to connect as sysdba.

There are three different ways or methods; we can collect data for a given SQL statement. For executing these methods one has to connect as the application schema against which the sql in question is being run. All the scripts are zipped into sqlt.zip file. Unzip it in your local directory and change the directory in which all sql resides. Let us start with methods.

XPLAIN: This method takes sql as an argument. Script to run is sqltxplain.sql. This method does not run the sql. If sql is using bind variable then don’t change the bind variables with value but keep it as is. We can save the sql in text file i.e. test.txt and pass this file as an input to the scripts. Syntax to execute this method is as follows.

SQL>start sqltxplain.sql test.txt

XTRACT: This method takes id of sql which is executed recently and still in memory. Using v$sql we can get the sql_id or hash_value of the sql statement and pass it as an input. SQL to run in this case is sqltxtract.sql. This method extracts sql from the memory along with runtime execution plan.

SQL>start sqltxtract.sql <hash_value> or
SQL>start sqltxtract.sql <sql_id>

XECUTE: This method is most comprehensive method. Instead of taking sql_id it takes actual sql statement as an input just like in XPLAIN method. It also generates the 10046 and 10053 trace as well. We have to make sure that bind values in the sql are declared and replaced with actual value. Following is the syntax, how we execute it. Replace test.txt with your file name.

SQL>start sqltxecute.sql test.txt

For complete details on how to install, uninstall, how to use SQLTXPLAIN, examples on how to create text files which contains sql statement and bugs affecting this utility, please visit metalink article 215187.1 written by Carlos Sierra. He is also the creator of this most useful script.

Posted in Oracle | Leave a Comment »

Back to the basics: Declaring procedures and functions in anonymous PL/SQL block

Posted by decipherinfosys on August 28, 2008

Last week at client site, one of the developers had a question whether we can use function or procedure in anonymous PL/SQL block or not. And the answer is yes we can very well declare and use the function and procedure in the PL/SQL block. In this blog post, we will show how to do it using small code snippet. But first of all, create the table for our test purpose.

CREATE TABLE TEST
(
ID NUMBER(9),
OBJ_TYPE VARCHAR(5),
OBJ_NAME VARCHAR(30),
NUM_ROWS NUMBER(9),
CREATE_DATE DATE
);

Now let us write small PL/SQL block, which contains both function and stored procedure along with other declarations.

DECLARE
/*– variable*/
v_date DATE;
v_tableName VARCHAR(30);
v_objectID NUMBER(9);
v_numRows NUMBER(9);
/*– Cursor*/
CURSOR C1 IS
SELECT object_name, object_id
FROM user_objects
WHERE object_Type = ‘TABLE’;
/*– Function declaration*/
FUNCTION getDate(num_days NUMBER)
RETURN DATE IS
BEGIN
RETURN(SYSDATE – num_days);
END getDate;
/*– Procedure declaration*/
PROCEDURE testproc(p_name IN VARCHAR, p_num_rows OUT NUMBER) IS
BEGIN
SELECT num_rows INTO p_num_rows FROM user_tables
WHERE table_name = p_name;
END testproc;

BEGIN
OPEN C1;
FETCH C1 INTO v_tablename,v_objectID;
LOOP
EXIT WHEN C1%NOTFOUND;
/*– Call to function*/
v_date := getDate(1);
/*– Call to procedure*/
testproc(v_tableName,v_numRows);

INSERT INTO TEST(ID,obj_Type,obj_name,num_rows,create_date)
VALUES(v_objectID,’TABLE’,v_tableName,v_numrows,v_Date);
/*– Fetch new record*/
FETCH C1 INTO v_tablename,v_objectID;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END;
/

If we run this block as is, it will run perfectly fine. Scope of declared subprograms will remain until the execution of the anonymous block. If we query the USER_OBJECTS table, you will not find subprograms used in this PL/SQL block.

SELECT object_id, object_Type, object_name
FROM user_objects
WHERE object_name IN (‘GETDATE’,’TESTPROC’)

no rows selected

SQL>

One very important thing to remember when dealing with subprograms in anonymous PL/SQL block is that, declaration of subprograms should be always in the end, after all the variable, cursor and other declarations are done. If subprograms are not declared in the end, it will result into an error. Let us assume that in our example above, if cursor is declared in the last after function and procedure declaration, it will result into following error.

ERROR at line 20:
ORA-06550: line 20, column 4:
PLS-00103: Encountered the symbol “CURSOR” when expecting one of the following:
begin function package pragma procedure form

Posted in Oracle | 6 Comments »

MaxTokenSize and Windows Authentication

Posted by decipherinfosys on August 27, 2008

As you know already, there are two modes of connecting to SQL Server – Windows Authentication and SQL Server Authentication. When using Windows Authentication, if you have a large company with a lot of users and groups in the AD (Active Directory), at times you can see connectivity errors related to kerberos which look like this in the event log:

The kerberos SSPI package generated an output token of size 23C9 bytes, which was too large to fit in the 2349 buffer provided by the process id 0. If the condition persists, please contact your system administrator.

Similar error is logged into the SQL Server Error Log as well:

The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.

These kind of errors typically occur when users have memberships in many AD groups (this includes explicit as well as inherited memberships). There is a registry setting called MaxTokenSize which has a default value of 12000 decimal. In larger organizations, this default value is not adequate and the user tokens can be larger than this value. Since kerberos does not accept broken tokens, the authentication will fail because the value for that registry entry is not high enough.

So, how can you see what value you have in your environment for a given user? MSFT has a utility called TokenSZ which can be used to ascertain that. When you run it, you can see the default size for the MaxToken parameter. If you want to change the value on the server, you will need to modify (or add if it does not exist already) this registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters

Under it, you will either see the entry MaxTokenSize or if you do not see it in there, you can add one by right clicking in the right pane and selecting New/DWORD Value. Do that as shown in the image below and put in a higher value:

You will need to reboot in order for the entry change to take effect. Any server or a workstation that interacts with SQL Server will need this change. Some more good reading materials on this topic can be seen here and here.

Posted in SQL Server, Windows | Leave a Comment »

Hosting Environment – Database Servers

Posted by decipherinfosys on August 26, 2008

For one of our clients, we had helped them with their implementation in the production environment. The hosting provider had asked for a clear understanding of who will do what and what kind of responsibilities will be shared between the client production team and the hosting provider. We thought our readers might benefit from this list as well. In this case, the client production team had a good team of DBAs/Database Developers and so the entire monitoring and parts of the maintenance work was done by that team rather than the hosting provider team. Here is the spreadsheet.

Topic Hosting Provider Client
Database Server Set-up
SAN/RAID array configuration and hardware set-up X Instructions/Requirements from Client
Windows 2003 64-bit Enterprise Edition + SP2 X
Set up of the logical volumes X Instructions/Requirements from Client
SQL Server 2005 Enterprise Edition 64-bit install + SP2 install + Cumulative Update #3 X Instructions/Requirements from Client
Cluster set up for local failover X
Log Shipping for site failover X Instructions/Requirements from Client
Disaster Recovery Tests and runbook for disaster recovery X
Instance and DB Configuration
Instance and DB configuration parameters X
Login accounts and permissions X
DB Creation X
Schema object creation X
Startup parameters X
Trace Flag configuration X
Maintenance
Back-ups (Configuring and verifying via restores from time to time) X Instructions/Requirements from Client
Stats collection X
Fragmentation monitoring and corrective action scripts X
Integrity Checks X
Proper Filesizes configuration X
System/Services re-cycling (if at all necessary) X After confirmation from Client
Monitoring
Wait Events and Queues Troubleshooting Scripts X
Perfmon templates X
Profiler templates X
User defined Alert scripts configuration(severity errors, disk space, cpu/memory/io thresholds etc.) X
Baseline performance analysis and configuration (Trend Analysis) X
Event logs and SQL Error Logs monitoring X
Auditing – System and Instance X
SSIS and other packages configuration X
Cluster monitoring, mirroring and hardware issues monitoring X
Upgrades
Application DB Version Upgrades/Migrations X
Application DB patches X
Critical DB/application fixes X
Patches
OS patches X After confirmation from Client
Security Patches (OS or SQL) X After confirmation from Client
SQL Server patches X After confirmation from Client
Remote Logins
Administrator account password X X
Another user with admin privs. X X
sa password X X

Posted in Performance Tuning, SQL Server, Windows | 1 Comment »

VMWare now has MSFT validation in SVVP

Posted by decipherinfosys on August 22, 2008

Read on one of the blog posts here that VMWare has now joined MSFT’s Server Virtualization Validation Program (SVVP). VMWare is added to the SVVP web site as well. So, what does it mean to us? It means that unlike before, MSFT will now provide support for a number of it’s server operating systems when they are running in a VMWare environment. This is the list of the supported operating Systems: Windows Server 2008, Windows Server 2003 + SP2 and above, Windows 2000 Server + SP4.

Posted in VMWare, Windows | Leave a Comment »

SQL Server 2008: Discontinued & Deprecated Features

Posted by decipherinfosys on August 21, 2008

We have been blogging about SQL Server 2008 right from the early CTP days – we have covered a lot of new features as well as enhancements to the existing features from SQL Server 2005. And now that it is released, it is also important to know which features have been deprecated. You can read more on that in the technet post here and here.  Discontinued means that those features are not available in SQL Server 2008 and Deprecated means that even though those features are still available in SQL Server 2008, they are slated to be removed in the future release(s).

One thing that might surprise some people is the removal of the Surface Area Configuration Tool.  We, though, welcome the change since it is good to have that baked into other management tools and instrumentation which are much more robust than SAC.

Posted in SQL Server | Leave a Comment »

More goodies from Sysinternals

Posted by decipherinfosys on August 21, 2008

AccessChk v 4.2 and AutoRuns for Windows v9.32 are the latest utilities from Sysinternals.  Both are geared towards sysadmins and are a good addition to the toolset for a SE.

Posted in Windows | Leave a Comment »

Copying Databases from one Instance to Another – II

Posted by decipherinfosys on August 21, 2008

In one of our previous blog posts, we had covered the different methods available to a Developer/DBA to copy/move the user databases from one instance to another in SQL Server. You can read that post over here. In that post, we had also covered the different things to look out for when performing the copy/move from one instance to another. In this post, we will cover the very first method – Attach and Detach for the user databases.

The steps involved are these:

1) Detach the user database(s) from the source instance.

2) Copy over the files from the source machine to the target machine. If using the same machine but different instances, you can copy over the files to the location that you want on the same machine.

3) Attach the user database(s) to the destination instance.

4) Collect 100% statistics on the user database(s).

5) Resolve the issues pertaining to the orphaned users (and other issues that we had mentioned in our previous post pertaining to the dependencies on the master and the msdb databases etc.).

For #1, detaching is a very simple and straight forward process. You need to first make sure that you kill the connections to the database in question (read this post to understand why) and then being in the master database, detach the database in question using the sp_detach_db system stored procedure.

We had blogged about the attach and detach process before and you can access that post here. However, in that post, we had not covered attaching a single file database which sometimes becomes a need when you get a file from someone else. At times, you might just get a data file and you are required to attach it to an instance. You can do that by using the system stored procedure: sp_attach_single_file_db. The syntax of that command is pretty much self explanatory and you can look that up in BOL. When that command is run, it automatically creates a transaction log file automatically upon a successful attach process of the data file.

Posted in SQL Server | Leave a Comment »

SQL Server 2005 P2P Replication

Posted by decipherinfosys on August 20, 2008

We had covered P2P replication before in one of our posts over here.  Here is a link to the technet post which talks about how MSFT has implemented it in house:

http://technet.microsoft.com/en-us/magazine/cc160974.aspx

Posted in Load Balancing, SQL Server, Windows | Leave a Comment »