One of my first projects as a DBA was to create a RAC environment with ASM connecting from a SAN storage device. To be honest I have never touched a SAN ever!!. So this was an exciting first time learning. Our systems admin taught me so much on SAN(s), it was a great experience. I had followed this guide to set up RAC configuration. Right now I am in the process of creating my own guide that has extras in it. Once it is done I will Post it up.
Copy and paste the link to access:
https://docs.google.com/viewer?a=v&pid=explorer&chrome=true&srcid=0B_DGWSgQzuAnOWU1YzBjMzMtOGNhMy00ZDlhLTlhYzEtMjhiNDc1MGI4ZWRl&hl=en_US
My name is Simpson Samuel - Oracle DBA @ Andera INC
Friday, August 19, 2011
Thursday, July 21, 2011
T-SQL Programming Part 2
Building a T-SQL Loop
This is the second article in our T-SQL programming (Stored Procedure) series. This article will discuss building a program loop using T-SQL. In addition to talking about building a loop, I will also discuss ways of controlling the loop processing, and different methods to break out of a loop. A programming loop is a chunk of code that is executed over and over again. In the loop some logic is executed repeatedly in an iterative fashion until some condition is met that allows the code to break out of the loop. One example of where you might use a loop would be to process through a set of records one record at a time. Another example might be where you need to generate some test data and a loop would allow you to insert a record into your test data table with slightly different column values, each time the loop is executed. In this article I will discuss the WHILE, BREAK, CONTINUE, and GOTO statements.
WHILE Statement
In T-SQL the WHILE statement is the most commonly used way to execute a loop. Here is the basic syntax for a WHILE loops:
WHILE <Boolean expression> <code block>
Where a <Boolean expression> is any expression that equates to a true or false answer, and the <code block> is the desire code to be executed while the <Boolean expression> is true. Let's go through a real simple example. In this example I will increment a counter from 1 to 10 and display the counter each time through the WHILE loop.
declare @counter int
set @counter = 0
while @counter < 10
begin
set @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)
end
Here the code executes the WHILE statement as long as the @counter integer variable is less than 10, this is the Boolean expression of the WHILE loop. The @counter variable starts out at zero, and each time through the WHILE loop it is incremented by 1. The PRINT statement displays the value in the @counter variable each time through the WHILE loop. The output from this sample looks like this:
The counter is 1
The counter is 2
The counter is 3
The counter is 4
The counter is 5
The counter is 6
The counter is 7
The counter is 8
The counter is 9
The counter is 10
As you can see, once the @counter variable reaches 10 the Boolean expression that is controlling the WHILE loop is no longer true, so the code within the while loop is no longer executed. Not only can you have a single while loop, but you can have WHILE loops inside WHILE loops. Or commonly know as nesting of WHILE loops. There are lots of different uses where nesting is valuable. I commonly use nesting of WHILE loops to generate test data. My next example will use the WHILE loop to generate test records for a PART table. A given PART record is uniquely identified by a Part_Id, and a Category_Id. For each Part_Id there are three different Category_Id's. Here is my example that generates 6 unique records for my PART table using a nested WHILE loop.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART
drop table PART
Here is the output from the SELECT statement at the bottom of this nested WHILE loop example.
Part_Id Category_Id Description
----------- ----------- -----------------------------------------
1 1 Part_Id is 1 Category_Id 1
1 2 Part_Id is 1 Category_Id 2
1 3 Part_Id is 1 Category_Id 3
2 1 Part_Id is 2 Category_Id 1
2 2 Part_Id is 2 Category_Id 2
2 3 Part_Id is 2 Category_Id 3
As you can see, by using a nested WHILE loop each combination of Part_Id and Category_Id is unique. The code within the first WHILE loop controlled the incrementing of the Part_Id, where as the second WHILE loop set the Category_Id to a different value each time through the loop. The code within the first while loop was executed only twice, but the code inside the second WHILE loop was executed 6 times. Thus giving me 6 sample PART records.
Wednesday, July 20, 2011
T-Sql Programming Lesson 1 of 5
TOPIC: Defining Variables, and IF...ELSE logic
Hola People!
This is the first of a series of articles discussing various aspects of T-SQL programming. Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T-SQL programming. There are five articles covering this sensitive and crucial topic. This first article will discuss defining variables, and using the IF...ELSE logic. Please study this as it will most definitely help in your everyday duties as a sql server dba.
Local Variables
As with any programming language, T-SQL allows you to define and set variables. A variable holds a single piece of information, similar to a number or a character string. Variables can be used for a number of things. Examples include (but aren’t limited to):
- passing parameters to stored procedures, or function
- Controlling the processing of a loop—I especially like this use.
- Testing for a true or false condition in an IF statement
- Programmatically controlling conditions in a WHERE statement
In SQL Server a variable is typical known as a local variable, due the scope of the variable. The scope of a local variable is only available in the batch, stored procedure or code block in which it is defined. A local variable is defined using the T-SQL "DECLARE" statement. The name of the local variable needs to start with the "@" symbol as the first character of its name. A local variable can be declared as any system or user defined data type. Here is a typical declaration for an integer variable named @CNT:
DECLARE @CNT INT
More than one variable can be defined with a single DECLARE statement. To define multiple variables, with a single DECLARE statement, you separate each variable definition with a comma, like so:
DECLARE @CNT INT, @X INT, @Y INT, @Z CHAR(10)
Above, I have defined 4 local variables with a single DECLARE statement. A local variable is initially assigned a NULL value. A value can be assigned to a local variable by using the SET or SELECT statement. On the SET command you specify the local variable and the value you wish to assign to the local variable. Here is an example of where I have defined my @CNT variable and then initialize the variable to 1.
DECLARE @CNT INT
SET @CNT = 1
Here is an example of how to use the SELECT statement to set the value of a local variable.
DECLARE @ROWCNT int
SELECT @ROWCNT=COUNT(*) FROM pubs.dbo.authors
The above example sets the variable @ROWCNT to the number of rows in the pubs.dbo.authors table.
One of the uses of a variable is to programmatically control the records returned from a SELECT statement. You do this by using a variable in the WHERE clause. Here is an example that returns all the Customers records in the Northwind database where the Customers Country column is equal to 'Germany'
Declare @Country varchar(25)
set @Country = 'Germany'
select CompanyName from Northwind.dbo.Customers
where Country = @Country
IF ... ELSE
T-SQL has the "IF" statement to help with allowing different code to be executed based on the results of a condition. The "IF" statement allows a T-SQL programmer to selectively execute a single line or block of code based upon a Boolean condition. There are two formats for the "IF" statement, both are shown below:
Format one: IF <condition> <then code to be executed when condition true>
Format two: IF <condition> <then code to be executed when condition true>
ELSE < else code to be executed when condition is false>In both of these formats, the <condition> is a Boolean expression or series of Boolean expressions that evaluate to true or false. If the condition evaluates to true, then the "then code" is executed. For format two, if the condition is false, then the "else code" is executed. If there is a false condition when using format one, then the next line following the IF statement is executed, since no else condition exists. The code to be executed can be a single TSQL statement or a block of code. If a block of code is used then it will need to be enclosed in a BEGIN and END statement.
Let's review how "Format one" works. This first example will show how the IF statement would look to execute a single statement, if the condition is true. Here I will test whether a variable is set to a specific value. If the variable is set to a specific value, then I print out the appropriate message.
Declare @x int
set @x = 29
if @x = 29 print 'The number is 29'
if @x = 30 print 'The number is 30'
The above code prints out only the phrase "The number is 29", because the first IF statement evaluates to true. Since the second IF is false the second print statement is not executed.
Now the condition statement can also contain a SELECT statement. The SELECT statement will need to return value or set of values that can be tested. If a SELECT statement is used the statement needs to be enclosed in parentheses.
if (select count(*) from Pubs.dbo.Authors
where au_lname like '[A-D]%') > 0
print 'Found A-D Authors'
Here I printed the message "Found A-D Authors" if the SELECT statement found any authors in the pubs.dbo.authors table that had a last name that started with an A, B, C, or D.
So far my two examples only showed how to execute a single T-SQL statement if the condition is true. T-SQL allows you to execute a block of code as well. A code block is created by using a "BEGIN" statement before the first line of code in the code block, and an "END" statement after that last line of code in the code block. Here is any example that executes a code block when the IF statement condition evaluates to true.
if db_name() = 'master'
begin
Print 'We are in the Master Database'
Print ''
Print 'So be careful what you execute'
End
Above a series of "PRINT" statements will be executed if this IF statement is run in the context of the master database. If the context is some other database then the print statements are not executed.
Sometimes you want to not only execute some code when you have a true condition, but also want to execute a different set of T-SQL statements when you have a false condition. If this is your requirement then you will need to use the IF...ELSE construct, that I called format two above. With this format, if the condition is true then the statement or block of code following the IF clause is executed, but if the condition evaluates to false then the statement or block of code following the ELSE clause will be executed. Let's go through a couple of examples.
For the first example let's say you need to determine whether to update or add a record to the Customers table in the Northwind database. The decision is based on whether the customer exists in the Northwind.dbo.Customers table. Here is the T-SQL code to perform this existence test for two different CustomerId's.
if exists(select * from Northwind.dbo.Customers
where CustomerId = 'ALFKI')
Print 'Need to update Customer Record ALFKI'
else
Print 'Need to add Customer Record ALFKI'
if exists(select * from Northwind.dbo.Customers
where CustomerId = 'LARSE')
Print 'Need to update Customer Record LARSE'
else
Print 'Need to add Customer Record LARSE'
The first IF...ELSE logic checks to see it CustomerId 'ALFKI' exists. If it exists it prints the message "Need to update Customer Record", if it doesn't exist the "Need to add Customer Record" is displayed. This logic is repeated for CustomerId = 'LARS'. When I run this code against my Northwind database I get the following output.
Need to update Customer Record ALFKI
Need to add Customer Record LARSE
As you can see from the results CustomerId 'ALFKI' existed, because the first print statement following the first IF statement was executed. Where as in the second IF statement CustomerId 'LARSE' was not found because the ELSE portion of the IF...ELSE statement was executed.
If you have complicated logic that needs to be performed prior to determining what T-SQL statements to execute you can either use multiple conditions on a single IF statement, or nest your IF statements. Here is a script that determines if the scope of the query is in the 'Northwind' database and if the "Customers" table exists. I have written this query two different ways, one with multiple conditions on a single IF statement, and the other by having nested IF statements.
-- Single IF Statement with multiple conditions
use Northwind
if db_name() = 'Northwind' and
(select count(*) from sysobjects
where name = 'Customers') = 1
print 'Table Customers Exist'
else
print 'Not in the Northwind database' +
' or Table Customer does not exist'
-- Nested IF Statements
use Northwind
if db_name() = 'Northwind'
if (select count(*) from sysobjects
where name = 'Customers') = 1
print 'Table Customers Exist'
else
print 'Table Customer does not exist'
else
print 'Not in the Northwind Database'
As you can see I tested to see if the query was being run from the Northwind database and whether the "Customers" table can be found in sysobjects. If this was true, I printed the message "Table Customers Exists". In the first example I had multiple conditions in a single IF statement. Since I was not able to determine which parts of the conditions in the IF statement where false the ELSE portion printed the message "Not in Northwind database or Table Customer does not exist". Now in the second example where I had a nested IF statement I was able to determine whether I was in the wrong database or the object "Customers" did not exist. This allowed me to have two separate print statements to reflect exactly what condition was getting a false value.
Conclusion
Tuesday, July 19, 2011
ERROR <> FCB::Open failed: Could not open file
Inspired by a discussion with PAstor Mrs. Peju Adefule, i have written this post to address a (Not-too-common-error) with sql server permissions.
FCB::Open failed: Could not open file
You start the SQL Server service and notice that the database is unavailable. You look in the error log and see this message:"FCB::Open failed: Could not open file <filename> for file number <filenumber>. OS error: 5(access denied)".
The access denied part of the message may be replaced with error not found.
This happens because the account the SQL Server service runs under does not have access to the folder containing the database or log files. If it had these permissions b4, the check the password policy and the login user mapping for any changes. Also look at the server roles. After this is confirmed as the cause, Check which account the service runs as by starting SQL Server Configuration Manager.
Now perform the following steps:
- Right-click on the folder containing one of the database files and selectSharing and Security...
- Select the Security tab
- Click the Add... button
- In the text box type in the fully qualified user name of the service account (e.g. MyDomain\MySQLAccount)
- Ensure the Full Control checkbox in the Allow column is checked, and clickOK
WHY YOU SHOULD AVOID SHRINKING YOUR DATAFILE!
One of my biggest hot-buttons is around shrinking data files. Although I used to own the shrink code while I was at Microsoft, I never had a chance to rewrite it so that data file shrink is a more palatable operation. I really don't like shrink.
Now, don't confuse shrinking the transaction log with shrinking data files. Shrinking the log is necessary if your log has grown out of control, or as part of a process to remove excessive VLF fragmentation (see Kimberly's excellent posts on this here and here). However, shrinking the log should be a rare operation and should not be part of any regular maintenance you perform.
Shrinking of data files should be performed even more rarely, if at all. Here's why - data file shrink causes *massive* index fragmentation. Let me demonstrate with a simple script you can run. The script below will create a data file, create a 10MB 'filler' table at the start of the data file, create a 10MB 'production' clustered index, drop the 'filler' table and then run a shrink to reclaim the space.
USE MASTER;
GO
IF DATABASEPROPERTYEX ('DBMaint2008', 'Version') > 0
DROP DATABASE DBMaint2008;
CREATE DATABASE DBMaint2008;
GO
USE DBMaint2008;
GO
SET NOCOUNT ON;
GO
-- Create the 10MB filler table at the 'front' of the data file
CREATE TABLE FillerTable (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'filler');
GO
-- Fill up the filler table
INSERT INTO FillerTable DEFAULT VALUES;
GO 1280
-- Create the production table, which will be 'after' the filler table in the data file
CREATE TABLE ProdTable (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'production');
CREATE CLUSTERED INDEX prod_cl ON ProdTable (c1);
GO
INSERT INTO ProdTable DEFAULT VALUES;
GO 1280
-- check the fragmentation of the production table
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats (
DB_ID ('DBMaint2008'), OBJECT_ID ('ProdTable'), 1, NULL, 'LIMITED');
GO
-- drop the filler table, creating 10MB of free space at the 'front' of the data file
DROP TABLE FillerTable;
GO
-- shrink the database
DBCC SHRINKDATABASE (DBMaint2008);
GO
-- check the index fragmentation again
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats (
DB_ID ('DBMaint2008'), OBJECT_ID ('ProdTable'), 1, NULL, 'LIMITED');
GO
avg_fragmentation_in_percent
----------------------------
0.390625
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
6 1 1456 152 1448 1440
6 2 63 63 56 56
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
avg_fragmentation_in_percent
----------------------------
99.296875
Look at the output from the script! The logical fragmentation of the clustered index before the shrink is a near-perfect 0.4%. After the shrink, it's almost 100%. The shrink operation *completely* fragmented the index, removing any chance of efficient range scans on it by ensuring the all range-scan readahead I/Os will be single-page I/Os.
Why does this happen? A data file shrink operation works on a single file at a time, and uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps) to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented.
The same code is used for DBCC SHRINKFILE, DBCC SHRINKDATABASE, and auto-shrink - they're equally as bad. As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU, and generates *loads* of transaction log - as everything it does is fully logged.
Data file shrink should never be part of regular maintenance, and you should NEVER, NEVER have auto-shrink enabled. I tried to have it removed from the product for SQL 2005 and SQL 2008 when I was in a position to do so - the only reason it's still there is for backwards compatibility. Don't fall into the trap of having a maintenance plan that rebuilds all indexes and then tries to reclaim the space required to rebuild the indexes by running a shrink - that's a zero-sum game where all you do is generate a log of transaction log for no actual gain in performance.
So what if you *do* need to run a shrink? For instance, if you've deleted a large proportion of a very large database and the database isn't likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
- Create a new filegroup
- Move all affected tables and indexes into the new filegroup using the CREATE INDEX ... WITH (DROP_EXISTING) ON <filegroup> syntax, to move the tables and remove fragmentation from them at the same time
- Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
Basically you need to provision some more space before you can shrink the old files, but it's a much cleaner mechanism.
If you absolutely have no choice and have to run a data file shrink operation, be aware that you're going to cause index fragmentation and you should take steps to remove it afterwards if it's going to cause performance problems. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAGor ALTER INDEX ... REORGANIZE. These commands only require a single 8KB page of extra space, instead of needing to build a whole new index in the case of an index rebuild operation.
Bottom line - try to avoid running data file shrink at all costs!
Subscribe to:
Posts (Atom)