Systems Engineering and RDBMS

Archive for January, 2009

Cumulative Update #3 for SQL Server 2008

Posted by decipherinfosys on January 31, 2009

Cumulative Update #3 for SQL Server 2008 has been released.  This is build 10.00.1787

You can read more about the fixes in this update over here:

And here are the SQL Server builds that were released post the RTM version.

Posted in SQL Server | 1 Comment »

Forcing Uniform Extent Allocations

Posted by decipherinfosys on January 30, 2009

We had discussed uniform and mixed extents in one our posts before.   A colleague recently pointed out that there is a trace flag ( -T1118 ) which can be used to force uniform extent allocations instead of mixed page allocations.   This was the first time that we used it at a client site when we were facing TempDB issues and upon doing more research, it is actually covered in a MSFT whitepaper:

It states:

“Use TF-1118. Under this trace flag SQL Server allocates full extents to each tempdb object, thereby eliminating the contention on SGAM page. This is done at the expense of some waste of disk space in tempdb. This trace flag has been available since SQL Server 2000. With improvements in tempdb object caching in SQL Server 2005, there should be significantly less contention in allocation structures. If you see contention in SGAM pages, you may want to use this trace flag. Cached tempdb objects may not always be available. For example, cached tempdb objects are destroyed when the query plan with which they are associated is recompiled or removed from the procedure cache.”

The reason we had to use it was because the customer still had a lot of legacy code and was using select … into #temp_1 where 1=0 in order to create the temp definitions.  Since the code is not moving any data into those tables and is only creating those temp objects, it was placing pressure on the allocation and using this flag made it use only uniform extent allocations.  There are some very good pointers in that whitepaper from MSFT so when you get time, do read it thoroughly.

Posted in SQL Server | Leave a Comment »

No Surface Area Configuration Tool in SQL Server 2008

Posted by decipherinfosys on January 29, 2009

So, you thought that you have by now become comfortable with the Surface Area Configuration Tool in SQL Server 2005 and are ready to do the configuration once you install SQL Server 2008.  Well, the tool does not exist anymore in SQL Server 2008 and has been added to the deprecated feature list. As you will recall, we used to use SAC for doing configuration for services & connections as well as for features.

So, how can you go about making configuration changes via the GUI?  It has been divided up into different tools now:

a) Instance and Database Engine Features:

This will now be done through SSMS (SQL Server Management Studio).  Right click on the instance and select Facets as shown in the image below:


Once you select it, the next screen that you will get is this one:


And you can pick and choose what you want to confgure.  Surface Area configuration is one of the facets.  Another thing that you would note in the above image at the bottom right hand side is “Export Current State as Policy” – using this, one can then use that policy across the group in order to have a consistent environment.  We have covered policy based management before in our post here.

And likewise, for the database parameters, you right click on the database in question and select facets and will get a similar screen where you can set the parameters for the database:


b) Connections, Protocols and StartUp options:

These are done via the SQL Server Configuration Manager.  This remains exactly the same as before.  We have covered the configuration manager related work in some of our previous posts.

c) SSAS Features:

This is done via the property settings in SSMS.  Right click and select properties and you will get this screen where you can set the values:


d) SSRS Features:

This is done either by editing the RSReportServer.config file or by using the facets feature again as shown below:


Posted in SQL Server | 20 Comments »

End of the road for Performance Point Server

Posted by decipherinfosys on January 28, 2009

It’s curtains for the performance point server product.  It’s functionality will now be merged into Sharepoint server instead.  MSFT announced it last week – you can read more on this over here.  This BI product was cumbersome to use and many of our customers also were just happy using the feature functionality present within SQL Server, Sharepoint and for the accounting folks – Excel.  Having it’s functionality tightly integrated within Sharepoint Server will be a much better choice and hopefully will see a wider adoption.

Posted in Business Intelligence | 2 Comments »

Back to the basics: Knowing parameters for Procedures, Packages and Functions

Posted by decipherinfosys on January 28, 2009

Database development requires a lot of packages, procedures and functions.  All these stored programs also take arguments as a parameter,  be it an input parameter or an output parameter.  It is almost impossible to remember the parameters or signature of these stored programs (packages, procedures, functions).

Easiest way to check the signature is to run the desc (DESCRIBE) command at the sql prompt.  Following command shows parameters of the test_proc stored procedure.

SQL> desc test_proc
PROCEDURE test_proc
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
P_COL1                         VARCHAR2                IN
P_COL2                         DATE                    IN
P_COL3                         NUMBER                  IN

Other way to get to the same information is by querying the USER_ARGUMENTS view.  This view contains information about all the arguments for all the packages, procedures, and functions.  One can look at the oracle documentation for more details on the columns in USER_ARGUMENTS view.  In the following sql, we are showing the most relevant information we need to know about any stored programs like data type, default values, position etc.

COLUMN object_name format A10
COLUMN argument_name format A10
COLUMN data_Type format A10
COLUMN position format 99

SELECT object_name,argument_name,data_Type,in_out,position
FROM user_arguments ua
WHERE object_name IN (‘TEST_PROC’,’TEST_FUNC’)
ORDER BY object_name,position;

---------- ---------- ---------- --- --------
TEST_FUNC             VARCHAR2   OUT        0
TEST_FUNC  SKUID      VARCHAR2   IN         1

TEST_PROC  P_COL1     VARCHAR2   IN         1
TEST_PROC  P_COL2     DATE       IN         2
TEST_PROC  P_COL3     NUMBER     IN         3

One thing to note is that for functions, position 0 is always return value of the function. So it shows the data type, length etc for returning value of the function. All the actual arguments start with position 1. If we describe the function, it will be shown like this.
SQL> desc test_func

Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
P_ITEM                         VARCHAR2                IN

Other than information shown above, we can also check for “TYPE” object parameter, default values of the arguments, default length, character set of the argument etc.

Posted in Oracle | Leave a Comment »

Back to the basics: WHERE CURRENT OF

Posted by decipherinfosys on January 27, 2009

This clause is normally used in the DELETE and UPDATE statements in order to work on the latest row fetched by a cursor.

Why is it advantageous to use this clause? Normally delete and update statements are followed by a select statement.  We give a where criteria in the select clause to fetch interested records into the cursor and then work on those records in order to either update it or delete it.  WHERE CURRENT OF clause saves us from defining this filter criteria or where clause during the update and/or delete statement.

Here’s an example.  First we will create table and populate it with some data.

col3 NUMBER(9),
col4 DATE,

INSERT INTO TEST(col1,col2,col3) VALUES(‘ORACLE’,’11G’,1111);
INSERT INTO TEST(col1,col2,col3) VALUES(‘ORACLE’,’10G’,2222);
INSERT INTO TEST(col1,col2,col3) VALUES(‘SQLSERVER’,’2000′,3333);
INSERT INTO TEST(col1,col2,col3) VALUES(‘SQLSERVER’,’2005′,4444);
INSERT INTO TEST(col1,col2,col3) VALUES(‘SQLSERVER’,’2008′,5555);

Following anonymous PL/SQL block demonstrates the use of the WHERE CURRENT OF clause:

test_rec c1%ROWTYPE;
OPEN c1;
FETCH c1 INTO test_rec;
IF test_rec.col1 = ‘ORACLE’ THEN
UPDATE TEST SET col3 = col3 + 1,col4 = sysdate + 1 WHERE CURRENT OF c1;

PL/SQL procedure successfully completed.

Though in our example we haven’t used the where clause, the main idea here is to avoid duplicating where clause in each update/delete statement after cursor is opened to update the same record.  This will help synchronize code only in one place without worrying about changing it everywhere specially when the where clause is changed.

Posted in Oracle | Leave a Comment »

SQL Server 2008 install on Windows Server 2008

Posted by decipherinfosys on January 26, 2009

Recently, we got a chance to play with Windows 2008 Server and one of the things that we needed to do was to install SQL Server 2008 on it.  The steps are pretty much the same as we had on Windows 2003 with the only difference being that the firewall is enabled by default in Windows 2008 Server and thus we got the warning message during the install about it.  We had covered the SQL Server 2008 install on Windows 2003 Server in the past in our document here.

Here is another document which gives screenshots for the step by step basic install of SQL Server 2008 on Windows 2008 Server.

Posted in SQL Server, Windows | Leave a Comment »

Mistakes of a start up

Posted by decipherinfosys on January 26, 2009

While talking to one of our advisors today, he pointed out an article on the top 17 mistakes that start ups make.  These were written by John Osher and are an eye opener for any start up.  You can read them over here:

Posted in General | Leave a Comment »

Start Up Options in SQL Server 2005

Posted by decipherinfosys on January 25, 2009

A reader recently asked how to access the start up options in SQL Server 2005.  In SQL Server 2000, they were able to access it through Enterprise Manager.  In SQL Server 2005, you can do so from the SQL Server Configuration Manager.  As you can see from the image below, you can right click on the service, select properties and then on the Advanced tab, you can get to the startup parameters for that instance – the ones shown in the image below are the default ones:


Besides the default ones, the most commonly used ones are the “-g” or “-T” options.  You can read more about all the   startup options that are available in SQL Server 2005 at this MSDN article here.

Posted in SQL Server | Leave a Comment »

SQL Server Profiler – Basic Concepts

Posted by decipherinfosys on January 24, 2009

We have used SQL Server Profiler in several of our posts before and have included the enhancements done to this wonderful utility in SQL Server 2005.  A recent question that was asked by one of our clients was to put together 2-3 training lunch and learns for them to help them understand how to make use of this utility.  In this series of posts on SQL Server profiler, we will cover some of those concepts that we covered in the training for our client.

First thing to understand is that SQL Server Profiler is comprised of two parts –

a) Client GUI Application.  This is what you see and most of the folks have experience with this part of the profiler only.

b) Server application.  This is also called as SQL trace and all of the actual work is done by it.  What you do using the GUI on the profiler client utility actually uses this code.  And one does not just need to use the profiler GUI to use SQL Trace – it can be initiated by using system stored procedures or by custom code which uses SMO (Server Management Objects).

So, think of SQL Trace as a trace utility that captures the communication between the client applications and SQL Server instance and it’s databases.  One can put filters and select specific events and data columns that one wants to use to restrict the output and get exactly what one needs.  And once the trace data has been collected, that can be saved to a database table so that one can use T-SQL to query against that data or save it as a XML file or as a profiler file (proprietary format).

As you might have already seen in the GUI application of profiler, there are event classes & events, data columns and filters – these are the three main pieces of terminology to understand before we get cranking out profiler templates.  Otherwise, you will be getting so much data by making poor choices of events and data columns that the analysis will be very cumbersome and sometimes not even useful.  Information (Data) is useful but only when you can separate the noise from actual meaningful data and can do proper analysis on it.  Before we start, get yourself familiarized with all the profiler terminology – it is succinctly mentioned here.

Since wading through the GUI or BOL for finding out which event classes contain which events and which columns are then available within those events is a cumbersome process, one can simply use T-SQL to get to that information.

Example: To see which trace event classes contain what events, you can simply use this T-SQL:

select as Event_Class, as event_name
sys.trace_events as v1
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
order by

If we want to now, take it one step further and want to see which data columns are available within those events as well as a list of named column values, we can do this:

select as Event_Class
, as event_name
, as data_column
, case v4.is_filterable when 0 then ‘No’ else ‘Yes’ end as Is_Column_Filterable
, v5.subclass_name
, v5.subclass_value
sys.trace_events as v1
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
inner join sys.trace_event_bindings as v3
on v1.trace_event_id = v3.trace_event_id
inner join sys.trace_columns as v4
on v3.trace_column_id = v4.trace_column_id
inner join sys.trace_subclass_values as v5
on v3.trace_event_id = v5.trace_event_id
and v3.trace_column_id = v5.trace_column_id
where like ‘%DeadLock%’
order by,

Over here, we filtered the events based on the event names containing the word “Deadlock”.   Now that we know what event classes we have available, what events we have within those classes and what data columns are available for us to filter by – this SQL serves as a quick way to form a plan to set a profiler template in place.  In subsequent posts in this series, we will see how we can use the GUI to capture information for the different scenarios, how we can do the same using just system stored procedures, how we can perform stress tests using profiler and much more.  Stay tuned…

Posted in SQL Server | 2 Comments »