Database Centre

SQL Server 2008 Performance Data Collector

2011-07-14T16:19:00.002+05:30

Performance Tuning in SQL Server is still tricky. SQL Server had provided various tools to monitor performance like Profiler, PerfMon, Management Studio’s, DMVs (Dynamic Management Views) and so on. Some of these tools collects database logs and generate report in order to help DBA’s to identify problems areas themselves and take necessary steps. But still getting a consolidated report which helps to identify which process is taking lot of time is a problem and not very straight forward.

SQL Server 2008 introduced Performance Data collector to overcome some of these limitations. Here we will see what exactly performance Data collector is and what it does.

Performance Data Collector is performance tools introduced in SQL Server 2008. It can collect data from single or multiple SQL Server instances and diagnostically analyze this information to generate reports. The activities of Performance Data Collected can be broadly classified into three categories.

Collects Data:
SQL Server 2008 comes with three built-in Data Collection Sets: one is to collect Disk Usage information; another is used to collect Query Statistics, and the third is used to collect a wide variety of Server Activities. According to Microsoft, these three Data Collection Sets collect the essential data needed to identify and troubleshoot most common SQL Server performance problems. If you don’t think the data that is collected is enough, you can create your own custom Data Collection Sets.

Central Repository:
All data collected by Data Collection Sets are stored in a centralized location called Management Data Warehouse (MDW). MDW can store data from single or multiple SQL Server Instances. While currently the focus of MDW is to store only performance related data, but in future version of SQL Server this will store virtually any data you want to collect from SQL Server, such as Extended Events, Audit data etc. The MDW is extensible, so if you are up to the task, you can store your own data in the MDW.

Performance Reports:
Data collected in MDW are diagnostically analyzed and reports are generated. SQL Server 2008 includes three built-in reports; Disk Usage Summary, Query Statistics History, and Server Activity History.

Sorting version numbers

2010-06-18T17:02:00.003+05:30

We already know that every product has some version numbers usually in combination of dots eg. 1.1 or 1.2.1 etc . If we have these number stored in a table, then we can not simply sort them by issuing ORDER BY Clause. Here we will see how to sort them in ascending or descending order.

CREATE TABLE version_list
(
ID NUMBER(10),
VERSION VARCHAR2(30)
);

INSERT INTO version_list VALUES
(1, ‘10.2.3’);

INSERT INTO version_list VALUES
(2, ‘9.1.2’);

INSERT INTO version_list VALUES
(3, ‘10.1.2’);

SELECT *
FROM version_list
ORDER BY version;
10.2.3
9.1.2
10.1.2
You can see that the output is not sorted.

SELECT *
FROM version_list
ORDER BY TO_NUMBER(REPLACE(version, '', ''));
9.1.2
10.1.2
10.2.3
Now you can see that the output is sorted. We had strip all dots from the version_list and then issued ORDER BY and it worked.

Introduction to Data warehousing

2010-06-13T17:48:00.018+05:30

Summary
In this article we will learn what is a Data warehouse? Why do we need a data warehouse? What are the characteristics of a Data warehouse? And finally go through some of the popular approaches for designing a Data warehouse.

What is Data Warehousing?
Ralph Kimball states that a data warehouse is a “Copy of transaction data specifically structured for query and analysis”

In other words; data is extracted periodically from production databases and copied onto special repository database. There it is validated, reformatted, reorganized, summarized, restructured, and merged with data from other sources. The resulted database is used to generate various summarized and ad-hoc reports, which in turn help in identifying future trends and decision making.

Why do we need a Data warehouse?
In today’s challenging times and cut-throat competition, there is a need to identify current trends, predict future trends and takes appropriate decision accordingly. Good decision can be taken only when we have all the relevant data available. Here the need of a good Data warehouse comes. A Data warehouse collects data from disparate sources and integrates them into a single source. Analysis is done using this centralized source.

Advantages and Disadvantages of a Data warehouse
Advantages:
- Large quantify of information consolidate into one location from disparate sources is available for analysis.
- Data warehouses facilitate decision support system applications such as trend reports (e.g., the items with the most sales in a particular area within the last two years), exception reports, and reports that show actual performance versus goals.
- Prior to loading data into data warehouse all data inconsistencies are identified and removed. This greatly simplifies reporting and analysis.
- Since Data warehouse is separate from operational system, they do not affect or slow down the operational system.

Disadvantages:
- Data warehouse system is complex in nature and they require skilled technical peoples and project managers.
- Setting up a Data warehouse is very expensive and time consuming.

Characteristics of a Data warehouse:
According to Bill Inmon "A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process"

Characteristics of a Data warehouse are explained below:
Subject-oriented: Information is present based on specific subject. Data is organized in such a way that data related to all real word elements are linked together. E.g. in operational database data will be grouped based on Loans, Savings, Demat etc. where as in warehouse data will be grouped based on subject like Customer, Vendor, Product etc.

Non-volatile: Data once entered is never updated or deleted. It is remains static and read-only for future reference.

Integrated: Data warehouse contains data from different operational data sources (OLTP) into a single integrated database.

Time Variant: Data warehouses are time variant in the sense that they maintain both historical and (nearly) current data.

Accessible: The primary purpose of a data warehouse is to provide readily available information to the en-users.

Implementation Methods
These are broadly two main approaches for designing a warehouse.

Top-down Approach
This is a Bill Inmon’s approach. In this approach data is extracted from operational system and loaded into staging area. Here the data is cleansed, consolidated and validated to ensure its accuracy and then transferred to Enterprise Data warehouse (EDW). The Data in EDW is usually in normalized form to avoid redundancy and have a detailed and true version of data. After EDW is in place, subject area specific data-marts are created which have data in de-normalized form and in a summary format.

Top-down approach should be used when:
We have complete clarity of Business requirements related to all verticals.
We are ready to invest considerable time and money.

Bottom-up Approach:
This is a Ralph Kimball’s approach. In this approach data marts are build first and then gradually the final Enterprise Data Warehouse (EDW) is build by integrating different data marts. Here separate data marts are combined based on Conformed dimensions and Conformed Facts. A Conformed dimensions and Conformed Facts can be shared across data marts.

Bottom-up approach should be used when:
We have time and money constraint.
We do not complete clarity of all verticals of Business and have clarity of only one or two business vertical.

Data warehousing Terms

Fact Table: A Fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema or a snowflake schema, surrounded by dimension tables. E.g. In a banking data warehouse accounting and balances will be stored in a Fact table.

Dimension Table: A dimension table consists of textual representation of the business process. E.g. In a banking data warehouse accounting and balances will be stored in a Fact table where as customer name, address, mobile numbers will be stored in the dimension table.

Cube: Cube is a logical schema which contains facts and dimensions

Star Schema: Star Schema is simplest data warehouse schema which consists of a few fact tables and multiple dimension tables connected to fact table.

Snowflake Schema: Snowflake schema is a variation of Star schema where dimension table are normalized into multiple related tables. Fact table remains same similar to Star schema.

Data Mart: Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization. A single Data warehouse may have multiple data marts.

Extract Transform and Load (ETL): It is a process through which data is extracted from disparate sources, transformed as per the requirement and loaded to the target database.

Operational Data Store (ODS): It comes between Staging Area and Data warehouse. Data is extracted from OLTP system to stating area and then after cleansing and validation, data is loaded into ODS. Later on data is summarized and loaded into Data warehouse.

Data Mining: Data mining (sometimes called data or knowledge discovery) is the process of analyzing data from different perspectives and summarizing it into useful information - information that can be used to increase revenue, cuts costs, or both.

Business Intelligence (BI): Business intelligence (BI) is a broad category of applications and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions. It involves data mining, business performance management, benchmarking, text mining, and predictive analytics

Conclusion
We had learn that in Data warehousing data is collected from different sources, cleansed, validated, summarized and stored in a separate repository called data warehouse. This data is then analyzed from different perspective in order to take good business decisions.

Sorting: Character should come first

2008-07-28T20:24:00.001+05:30

When we issue ORDER BY clause Oracle engine sorts data in an ascending order by first bringing all numeric data and then character data. As you can see below:

SELECT id FROM test ORDER BY id;
ID
---
1
2
3
A
B
C

There could be case when we want character data to come first and then numeric data. This can be achieved by simple manipulation of ORDER BY clause as follows:

SELECT id FROM test
ORDER BY DECODE(REGEXP_REPLACE(id, ‘\d+’), null, 1, 0);
ID
---
A
B
C
1
2
3

REGEXP_REPLACE is a regular expression function introduced in Oracle 10g. Here by using this function we are identifying all numeric data and replacing it with NULL. After that using DECODE function all NULLs are replaced with 1 and the remaining character data is replaced with 0. Hay this is only done in ORDER BY field NOT IN SELECT. Now we can ensure that zero (character data) obviously comes before 1 (numeric data in our case).

Loading data into Oracle from Text File

2008-07-03T03:16:00.002+05:30

SQLLOADER is a bulk loader utility used to move data from external files into oracle database. Simple example of loading external file is given below.

First you need to create a control file which should look similar to the below example

CONTROL FILE:

OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'C:\TextFile\emp.csv'
BADFILE 'C:\TextFile\emp.bad'
DISCARDFILE 'C:\TextFile\emp.dsc'

INTO TABLE "SCOTT"."EMP"
INSERT FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'TRAILING NULLCOLS
(
EMP_ID sequence(Max, 1),
EMP_NAME,
EMP_DEPT,
EMP_ADDRESS,
EMP_DOB
)

If your .csv file has headers then you need to add SKIP = 1.

You need to make sure that you have emp.csv exist in the given location. Other files like .bad and .dsc will be generated by sqlloader itself, if required.

Now save this control file on any directory (eg. c:\LoadTest\Emp.ctl)

To execute this, Type following command in the command prompt

c:\sqlldr userid=scott/tiger@orcl control=c:\LoadTest\Emp.ctl log=c:\LoadTest\Emp.log

Copying table from one database instance to another without using dblink

2008-07-03T03:15:00.004+05:30

Copy to UserId/Pwd@MyDB

create NewTable(field1)

using select * from temp1;

Here MyDB is the TNS name of your database.

Here there is no need to create DBLink.

Querying current value of sequence without incrementing it

2008-07-03T03:10:00.001+05:30

As we know when we use SequenceName.NextVal, sequence got increment by whatever increment number is defined while it was created.

Eg.
CREATE SEQUENCE seq1
START WITH 1
INCREMENT BY 1
CACHE 20;

If we try to issue SELECT seq1.CurrVal FROM dual without issuing se1.NextVal. It will give error stating that sequence is not yet defined in this session. But, if we use seq1.NextVal, it will increment sequence by 1 which we don’t want. So here is one easy way for getting this:

ALTER SEQUENCE seq1 NOCACHE;
SELECT last_number FROM user_sequences WHERE sequence_name = ‘seq1’;
ALTER SEQUENCE seq1 CACHE 20; --* or whatever is defined earlier

Loading data into text file from Oracle

2008-07-03T03:04:00.001+05:30

set colsetp,
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt

SELECT col1  ','  col2  ','  col3
  FROM tab1
 WHERE col2 = 'XYZ';

spool off

Finding a Leap Year

2007-11-20T14:44:00.000+05:30

We all very well know what a leap year is? A year which has 366 days is called a leap year. But how many of us know how do you know which year is a leap year? Do you know which calendar year will have 29 days in the month of February?

Most of us have a very wrong perception of a Leap year. If you ask any person how do you calculate a Leap year? His obvious reply will be divide the year by 4, if it gets divided then it is a leap year. Well that seems to be very simple, so why do we need a function to calculate a leap year?

Actually leap year is some thing more then divide by 4. First you need to check whether a year gets divided by 4, if it gets divided by 4 then ensure that it does not get divided by 100, if it does not get divided by 100 then it is a leap year. But, if it gets divided by 100 then it should also get divided by 400 in order to be a leap year.

This function takes year as an input and returns 1, if it is a leap year and 0 if it is not a leap year.

CREATE OR REPLACE FUNCTION IsLeapYear
(
p_year IN NUMBER
)
RETURN NUMBER

IS

v_chkLeapYr NUMBER;
X NUMBER;

BEGIN

--**Leap Year Calculation

SELECT DISTINCT MOD(p_year, 4) INTO v_chkLeapYr FROM DUAL;

IF v_chkLeapYr = 0 THEN
IF mod(p_year,100) = 0 THEN --* If divided by 4 and not by 100 then LEAP YEAR
IF mod(p_year,400) = 0 THEN --* If divided by 4 and 100 then it should also get divided by 400 then LEAP YEAR
X := 366;
ELSE
X := 365;
END IF;
ELSE
X := 366;
END IF;
ELSE
X := 365;
END IF;

If x = 366 Then
Return (1); --* Lear Year
Else
Return (0);
End If;

END IsLeapYear;

SQL Server: Function vs Stored Procedure

2007-10-30T17:48:00.000+05:30

Functions are referred as UDF (User Defined Functions) in SQL Server. There where no UDF till SQL Server 7.0. UDF was first introduced in SQL Server 2000.

Some of the difference between UDF and Stored procedure given in SQL Magazine are as follows:

1) A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn't have to.

2) You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can't use a stored procedure in a SELECT statement.

3) A UDF can't use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.

4) A UDF can't change server environment variables; a stored procedure can.

5) A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you've used proper error handling code.

Oracle: Function vs Stored Procedure

2007-08-23T14:14:00.000+05:30

Difference between procedure and funtion:

Function always have a returns value where as procedure does not have a return value, but you can use OUT parameter for this purpose.
Function can be used in SELECT statement where as procedure can not. eg. SELECT GetName(v_id) FROM DUAL;
You can have DML(Insert, Update, Delete) DDL(TRUNCATE, Drop), TCL(COMMIT, ROLLBACK) in a function but then you can not call that function in any SQL query.

Formatting SQL Code

2007-08-22T18:26:00.001+05:30

Tired of formatting your code manually. Just try this online tool, its free!!
http://www.dpriver.com/pp/sqlformat.htm

Oracle: EXISTS vs IN

2007-08-22T17:21:00.001+05:30

We have a general perception that EXISTS is faster then IN. This is not always true. There can be some cases where IN performs better then EXISTS.

This depends on the size of the tables (and data queried) Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset (nested loops, merge, whatever). If the table in the subquery is small, and the table in the main query is large, then IN usually makes sense.

Using "EXISTS", the optimizer generally runs the main query first, then applies that dataset to the subquery. If the table in the subquery is large, and the table in the main query is small, then EXISTS usually makes sense.

By the way, Full Table Scan are not necessarily bad. If you are querying more than, say 15 - 20%, of the data in a table the Full Table Scan may be faster. Two Full Table Scans with a Hash Join are frequently gobs faster than a Nested Loops type of plan.